Я унаследовал большую и медленную хранимую процедуру, и это вызывает у меня кошмар:
У меня на рабочем столе установлен SQL Server 2008 с копией рабочей базы данных. Я запускаю все из SSMS, и я пробовал прямой SQL и SP. Время для Sp vs SQL достаточно близко к тому же, чтобы не волноваться по этому поводу - меня беспокоит время локального vs сервера.
Local:
2Ghz dual-core
4Gb RAM
SQL 2008 Sp1
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86)
Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
Server:
2Ghz dual-core
4Gb RAM
Microsoft SQL Server 2008 (SP1) - 10.0.2573.0 (X64)
Standard Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2) (VM)
Когда я запускаю sproc «локально», это занимает около 6 секунд.
Когда я запускаю sproc на сервере, это занимает около 25 минут!!!
[Всего вставок = 45 500]
Я пытался максимально настроить SP и избегал прослушивания параметров, но не уверен, ПОЧЕМУ это должно быть НАМНОГО медленнее на почти эквивалентной машине (я знаю, что версия SQL Server немного отличается, но не могу понять, почему это будет НАСТОЛЬКО разница?)
Я знаю, что SP не является фантастическим, но если он работает за 6 секунд локально, то это не полный штаны! Если бы на сервере это заняло 60 секунд, то это все равно было бы нормально. На этот SP поступает 15 вызовов... так что 15*0:06 приемлемо...15*25:00 - нет!
Сервер имеет довольно низкую нагрузку, когда эти SP не работают, и около 50% ЦП, когда они выполняются (одно ядро 100%?). Локально CPU никогда не превышает 25%.
Apols за публикацию всего SP ... но не вижу, как еще показать, что он делает, если есть опция, которую можно настроить для улучшения ситуации :(
ALTER PROCEDURE [dbo].[LoadProfitabilitySummaryByCustomer]
@inCustomerType char(2),
@inClearTable int = 0,
@inStartNum int,
@inEndNum int
WITH RECOMPILE
AS
BEGIN
SET ANSI_NULLS ON
DECLARE @customers TABLE (
CUSNUM CHAR(6) DEFAULT 0
,IILQAR DECIMAL(20,5) DEFAULT 0
,IILUSD DECIMAL(20,5) DEFAULT 0
,IILGPB DECIMAL(20,5) DEFAULT 0
,IILEUR DECIMAL(20,5) DEFAULT 0
,IILKWD DECIMAL(20,5) DEFAULT 0
,IILOTH DECIMAL(20,5) DEFAULT 0
,IILMTD DECIMAL(20,5) DEFAULT 0
,IILYTD DECIMAL(20,5) DEFAULT 0
,TCQAR DECIMAL(20,5) DEFAULT 0
,TCUSD DECIMAL(20,5) DEFAULT 0
,TCGPB DECIMAL(20,5) DEFAULT 0
,TCEUR DECIMAL(20,5) DEFAULT 0
,TCKWD DECIMAL(20,5) DEFAULT 0
,TCOTH DECIMAL(20,5) DEFAULT 0
,TCMTD DECIMAL(20,5) DEFAULT 0
,TCYTD DECIMAL(20,5) DEFAULT 0
)
DECLARE @CustomerType char(2)
DECLARE @ClearTable int
DECLARE @StartNum int
DECLARE @EndNum int
SET @CustomerType = @inCustomerType
SET @ClearTable = @inClearTable
SET @StartNum = @inStartNum
SET @EndNum = @inEndNum
DECLARE @sCustomerNumber char(6)
DECLARE @iDaysInMonth decimal
DECLARE @iDaysInYear decimal
DECLARE @dAvgCostQAR DECIMAL(20,5)
DECLARE @dAvgCostUSD DECIMAL(20,5)
DECLARE @dAvgCostGBP DECIMAL(20,5)
DECLARE @dAvgCostEUR DECIMAL(20,5)
DECLARE @dAvgCostKWD DECIMAL(20,5)
DECLARE @dAvgCostOTH DECIMAL(20,5)
DECLARE @dAvgCostTOT DECIMAL(20,5)
DECLARE @dTIIntIncLnsQAR DECIMAL(20,5)
DECLARE @dTIIntIncLnsUSD DECIMAL(20,5)
DECLARE @dTIIntIncLnsGBP DECIMAL(20,5)
DECLARE @dTIIntIncLnsEUR DECIMAL(20,5)
DECLARE @dTIIntIncLnsKWD DECIMAL(20,5)
DECLARE @dTIIntIncLnsOTH DECIMAL(20,5)
DECLARE @dTIIntIncLnsTOT DECIMAL(20,5)
DECLARE @dTIIntIncLnsYTD DECIMAL(20,5)
DECLARE @dTITradeCommQAR DECIMAL(20,5)
DECLARE @dTITradeCommUSD DECIMAL(20,5)
DECLARE @dTITradeCommGBP DECIMAL(20,5)
DECLARE @dTITradeCommEUR DECIMAL(20,5)
DECLARE @dTITradeCommKWD DECIMAL(20,5)
DECLARE @dTITradeCommOTH DECIMAL(20,5)
DECLARE @dTITradeCommTOT DECIMAL(20,5)
DECLARE @dTITradeCommYTD DECIMAL(20,5)
DECLARE @dtMaxDate varchar(30)
DECLARE @iReportYear int
DECLARE @START_DATE datetime
DECLARE @FIRST_MONTH datetime
DECLARE @END_MONTH datetime
DECLARE @iNumMonths int
DECLARE @iNumDaysToday int
INSERT INTO @customers (CUSNUM)
SELECT DISTINCT CUSNUM
FROM EQPRF_SUMMARY
WHERE CUSTYP = @CustomerType
AND CUSNUM >= @StartNum
AND CUSNUM < @EndNum
GROUP BY CUSNUM
UPDATE @customers
SET IILQAR = t2.Amount
FROM @customers c INNER JOIN
(
SELECT CUSNUM,
-SUM(ISNULL(CONVERT(decimal(20,5),t.TOTALMTDLCY),0)) as Amount
FROM TI_INTINCLOANS t
WHERE t.CCY = 'IILQAR'
AND t.LINEID = 'LN17'
GROUP BY t.CUSNUM
) AS t2
ON c.CUSNUM = t2.CUSNUM
UPDATE @customers
SET IILUSD = t2.Amount
FROM @customers c INNER JOIN
(
SELECT CUSNUM,
-SUM(ISNULL(CONVERT(decimal(20,5),t.TOTALMTDLCY),0)) as Amount
FROM TI_INTINCLOANS t
WHERE t.CCY = 'IILUSD'
AND t.LINEID = 'LN17'
GROUP BY t.CUSNUM
) AS t2
ON c.CUSNUM = t2.CUSNUM
--REPEAT FOR 14 OTHER CRITERIA
IF (@ClearTable = 1)
BEGIN
IF EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_GROUP_ACCT') DROP INDEX IX_GROUP_ACCT ON PROFITABILITY_SUMMARY
TRUNCATE TABLE [PROFITABILITY_SUMMARY]
IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_GROUP_ACCT')
CREATE NONCLUSTERED INDEX IX_GROUP_ACCT
ON dbo.PROFITABILITY_SUMMARY(CUST_TYPE,GROUP_CODE,ACCOUNT)
WITH (FILLFACTOR = 90,PAD_INDEX = ON);
END
IF MONTH(getdate())= 1 SET @iReportYear = YEAR(DATEADD(m,-1,getdate())) -- get last month's year
ELSE SET @iReportYear = YEAR(getdate())
SET @START_DATE = CAST('1/1/'+cast(@iReportYear as varchar(4)) as datetime) --first day of report year (If Jan, it's last year)
SET @FIRST_MONTH = DATEADD(day,-DAY(getdate())+1,getdate())--first day of current month
SET @END_MONTH = DATEADD(day,-1,@FIRST_MONTH)--determine last day of the previous month
SET @FIRST_MONTH = DATEADD(M,-1,@FIRST_MONTH)--reset to first day of the previous month
SET @FIRST_MONTH = CAST(FLOOR( CAST( @FIRST_MONTH AS FLOAT ) ) AS DATETIME) --TRUNCATE HOURS
SET @END_MONTH = CAST(FLOOR( CAST( @END_MONTH AS FLOAT ) ) AS DATETIME) --TRUNCATE HOURS
SELECT TOP 1
@dAvgCostQAR = AVGCOSTQAR
,@dAvgCostUSD = AVGCOSTUSD
,@dAvgCostGBP = AVGCOSTGBP
,@dAvgCostEUR = AVGCOSTEUR
,@dAvgCostKWD = AVGCOSTKWD
,@dAvgCostOTH = AVGCOSTOTH
,@dAvgCostTOT = AVGCOSTTOT
FROM dbo.PRFCOSTF
SET @dtMaxDate = (select top 1 s.txnstmp from EQPRF_SUMMARY s)
SELECT @iDaysInMonth = dbo.ufn_GetDaysInMonth(CAST(@dtMaxDate as datetime))
SELECT @iDaysInYear = 360
SET @iNumDaysToday = (DATEDIFF(dd, @START_DATE, @END_MONTH)+1)
DECLARE CustomerCursor CURSOR FAST_FORWARD
FOR
SELECT CUSNUM
FROM @customers
OPEN CustomerCursor
FETCH NEXT FROM CustomerCursor
INTO @sCustomerNumber
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT
@dTIIntIncLnsQAR = c.IILQAR
,@dTIIntIncLnsUSD = c.IILUSD
,@dTIIntIncLnsGBP = c.IILGPB
,@dTIIntIncLnsEUR = c.IILEUR
,@dTIIntIncLnsKWD = c.IILKWD
,@dTIIntIncLnsOTH = c.IILOTH
,@dTIIntIncLnsTOT = c.IILMTD
,@dTIIntIncLnsYTD = c.IILYTD
,@dTITradeCommQAR = c.TCQAR
,@dTITradeCommUSD = c.TCUSD
,@dTITradeCommGBP = c.TCGPB
,@dTITradeCommEUR = c.TCEUR
,@dTITradeCommKWD = c.TCKWD
,@dTITradeCommOTH = c.TCOTH
,@dTITradeCommTOT = c.TCMTD
,@dTITradeCommYTD = c.TCYTD
FROM @customers c
WHERE c.CUSNUM = @sCustomerNumber
INSERT INTO PROFITABILITY_SUMMARY
SELECT SORT_ORDER = 1, LINE_NO = 'LN01', RPT_DATE=cast(TXNSTMP as datetime),
LINE_TITLE='Month Loans Outstanding',
ACCOUNT = RTRIM(CUSNUM),
CUST_TYPE = RTRIM(CUSTYP),
GROUP_CODE = (CASE
WHEN RTRIM(CUSGRP) IS NULL OR RTRIM(CUSGRP)='' THEN LEFT(CUSNAME,3)+'--'+RTRIM(CUSNUM)
ELSE RTRIM(CUSGRP)
END),
PARENT_COUNTRY = RTRIM(CUSNAP),
RM_CODE = RTRIM(CUSACO),
RM_NAME = RTRIM(CUSRNAM),
CUST_NAME = RTRIM(CUSNAME),
RISK_CODE = RTRIM(CUSRSKE),
QAR = ISNULL((SELECT CAST(CURDLAMT AS DECIMAL(20,5)) FROM [MISReportInterface].[dbo].[EQPRF_SUMMARY]
where CUSNUM=@sCustomerNumber AND CUSCCY='QAR'),0),
USD = ISNULL((SELECT CAST(CURDLAMT AS DECIMAL(20,5)) FROM [MISReportInterface].[dbo].[EQPRF_SUMMARY]
where CUSNUM=@sCustomerNumber AND CUSCCY='USD'),0),
GBP = ISNULL((SELECT CAST(CURDLAMT AS DECIMAL(20,5)) FROM [MISReportInterface].[dbo].[EQPRF_SUMMARY]
where CUSNUM=@sCustomerNumber AND CUSCCY='GBP'),0),
EUR = ISNULL((SELECT CAST(CURDLAMT AS DECIMAL(20,5)) FROM [MISReportInterface].[dbo].[EQPRF_SUMMARY]
where CUSNUM=@sCustomerNumber AND CUSCCY='EUR'),0),
KWD = ISNULL((SELECT CAST(CURDLAMT AS DECIMAL(20,5)) FROM [MISReportInterface].[dbo].[EQPRF_SUMMARY]
where CUSNUM=@sCustomerNumber AND CUSCCY='KWD'),0),
OTHER = ISNULL((SELECT sum(CAST(CURDLAMT AS DECIMAL(20,5))) FROM [MISReportInterface].[dbo].[EQPRF_SUMMARY]
where CUSNUM=@sCustomerNumber AND CUSCCY NOT IN ('QAR', 'USD', 'GBP', 'EUR', 'KWD')),0),
TOTAL = SUM(CAST(CURDLAMT AS DECIMAL(20,5))),
YTD = 0
FROM [MISReportInterface].[dbo].[EQPRF_SUMMARY] where CUSNUM=@sCustomerNumber
GROUP BY CUSNUM,CUSTYP,CUSGRP,CUSNAP,CUSACO,CUSRNAM,CUSNAME,CUSRSKE,TXNSTMP
UNION ALL
SELECT SORT_ORDER = 2,LINE_NO = 'LN02', RPT_DATE=cast(TXNSTMP as datetime),
LINE_TITLE ='Average Loans Outstanding',
ACCOUNT = RTRIM(CUSNUM),
CUST_TYPE = RTRIM(CUSTYP),
GROUP_CODE = (CASE
WHEN RTRIM(CUSGRP) IS NULL OR RTRIM(CUSGRP)='' THEN LEFT(CUSNAME,3)+'--'+RTRIM(CUSNUM)
ELSE RTRIM(CUSGRP)
END),
PARENT_COUNTRY = RTRIM(CUSNAP),
RM_CODE = RTRIM(CUSACO),
RM_NAME = RTRIM(CUSRNAM),
CUST_NAME = RTRIM(CUSNAME),
RISK_CODE = RTRIM(CUSRSKE),
QAR = ISNULL((SELECT CAST(LNAVGMTD AS DECIMAL(20,5)) FROM [MISReportInterface].[dbo].[EQPRF_SUMMARY]
where CUSNUM=@sCustomerNumber AND CUSCCY='QAR'),0),
USD = ISNULL((SELECT CAST(LNAVGMTD AS DECIMAL(20,5)) FROM [MISReportInterface].[dbo].[EQPRF_SUMMARY]
where CUSNUM=@sCustomerNumber AND CUSCCY='USD'),0),
GBP = ISNULL((SELECT CAST(LNAVGMTD AS DECIMAL(20,5)) FROM [MISReportInterface].[dbo].[EQPRF_SUMMARY]
where CUSNUM=@sCustomerNumber AND CUSCCY='GBP'),0),
EUR = ISNULL((SELECT CAST(LNAVGMTD AS DECIMAL(20,5)) FROM [MISReportInterface].[dbo].[EQPRF_SUMMARY]
where CUSNUM=@sCustomerNumber AND CUSCCY='EUR'),0),
KWD = ISNULL((SELECT CAST(LNAVGMTD AS DECIMAL(20,5)) FROM [MISReportInterface].[dbo].[EQPRF_SUMMARY]
where CUSNUM=@sCustomerNumber AND CUSCCY='KWD'),0),
OTHER = ISNULL((SELECT SUM(CAST(LNAVGMTD AS DECIMAL(20,5))) FROM [MISReportInterface].[dbo].[EQPRF_SUMMARY]
where CUSNUM=@sCustomerNumber AND CUSCCY NOT IN ('QAR', 'USD', 'GBP', 'EUR', 'KWD')),0),
TOTAL = SUM(CAST(LNAVGMTD AS DECIMAL(20,5))),
YTD = SUM(CAST(LNAVGYTD AS DECIMAL(20,5)))
FROM [MISReportInterface].[dbo].[EQPRF_SUMMARY] where CUSNUM=@sCustomerNumber
GROUP BY CUSNUM,CUSTYP,CUSGRP,CUSNAP,CUSACO,CUSRNAM,CUSNAME,CUSRSKE,TXNSTMP
------------------------------------------------------------------------------
--THERE ARE 32 LINES IN TOTAL...ALL FOLLOWING ROUGHLY SAME PATTERN
------------------------------------------------------------------------------
UNION ALL
SELECT SORT_ORDER = 32,LINE_NO = 'GRANTOTAL',RPT_DATE=cast(TXNSTMP as datetime),
LINE_TITLE ='Total Income',
ACCOUNT = RTRIM(CUSNUM),
CUST_TYPE = RTRIM(CUSTYP),
GROUP_CODE = (CASE
WHEN RTRIM(CUSGRP) IS NULL OR RTRIM(CUSGRP)='' THEN LEFT(CUSNAME,3)+'--'+RTRIM(CUSNUM)
ELSE RTRIM(CUSGRP)
END),
PARENT_COUNTRY = RTRIM(CUSNAP),
RM_CODE = RTRIM(CUSACO),
RM_NAME = RTRIM(CUSRNAM),
CUST_NAME = RTRIM(CUSNAME),
RISK_CODE = RTRIM(CUSRSKE),
QAR = @dTITradeCommQAR+@dTIIntIncLnsQAR+ISNULL((SELECT CAST(SUSINTMTD AS DECIMAL(20,5)) + CAST(INTINCMTD AS DECIMAL(20,5))- cast(COSTLNMTD as DECIMAL(20,5))+cast(COSTDPMTD as DECIMAL(20,5)) - CAST(INTEXPMTD AS DECIMAL(20,5))+CAST(LNFEEMTD AS DECIMAL(20,5))+CAST(OTRINCMTD AS DECIMAL(20,5))+CAST(EXCHINCMTD AS DECIMAL(20,5)) FROM [MISReportInterface].[dbo].[EQPRF_SUMMARY]
where CUSNUM=@sCustomerNumber AND CUSCCY='QAR'),0),
USD = @dTITradeCommUSD+@dTIIntIncLnsUSD+ISNULL((SELECT CAST(SUSINTMTD AS DECIMAL(20,5)) + CAST(INTINCMTD AS DECIMAL(20,5))- cast(COSTLNMTD as DECIMAL(20,5))+cast(COSTDPMTD as DECIMAL(20,5)) - CAST(INTEXPMTD AS DECIMAL(20,5))+CAST(LNFEEMTD AS DECIMAL(20,5))+CAST(OTRINCMTD AS DECIMAL(20,5))+CAST(EXCHINCMTD AS DECIMAL(20,5)) FROM [MISReportInterface].[dbo].[EQPRF_SUMMARY]
where CUSNUM=@sCustomerNumber AND CUSCCY='USD'),0),
GBP = @dTITradeCommGBP+@dTIIntIncLnsGBP+ISNULL((SELECT CAST(SUSINTMTD AS DECIMAL(20,5)) + CAST(INTINCMTD AS DECIMAL(20,5))- cast(COSTLNMTD as DECIMAL(20,5))+cast(COSTDPMTD as DECIMAL(20,5)) - CAST(INTEXPMTD AS DECIMAL(20,5))+CAST(LNFEEMTD AS DECIMAL(20,5))+CAST(OTRINCMTD AS DECIMAL(20,5))+CAST(EXCHINCMTD AS DECIMAL(20,5)) FROM [MISReportInterface].[dbo].[EQPRF_SUMMARY]
where CUSNUM=@sCustomerNumber AND CUSCCY='GBP'),0),
EUR = @dTITradeCommEUR+@dTIIntIncLnsEUR+ISNULL((SELECT CAST(SUSINTMTD AS DECIMAL(20,5)) + CAST(INTINCMTD AS DECIMAL(20,5))- cast(COSTLNMTD as DECIMAL(20,5))+cast(COSTDPMTD as DECIMAL(20,5)) - CAST(INTEXPMTD AS DECIMAL(20,5))+CAST(LNFEEMTD AS DECIMAL(20,5))+CAST(OTRINCMTD AS DECIMAL(20,5))+CAST(EXCHINCMTD AS DECIMAL(20,5)) FROM [MISReportInterface].[dbo].[EQPRF_SUMMARY]
where CUSNUM=@sCustomerNumber AND CUSCCY='EUR'),0),
KWD = @dTITradeCommKWD+@dTIIntIncLnsKWD+ISNULL((SELECT CAST(SUSINTMTD AS DECIMAL(20,5)) + CAST(INTINCMTD AS DECIMAL(20,5))- cast(COSTLNMTD as DECIMAL(20,5))+cast(COSTDPMTD as DECIMAL(20,5)) - CAST(INTEXPMTD AS DECIMAL(20,5))+CAST(LNFEEMTD AS DECIMAL(20,5))+CAST(OTRINCMTD AS DECIMAL(20,5))+CAST(EXCHINCMTD AS DECIMAL(20,5)) FROM [MISReportInterface].[dbo].[EQPRF_SUMMARY]
where CUSNUM=@sCustomerNumber AND CUSCCY='KWD'),0),
OTHER=@dTITradeCommOTH+@dTIIntIncLnsOTH+ISNULL((SELECT SUM(CAST(SUSINTMTD AS DECIMAL(20,5)))+ SUM(CAST(INTINCMTD AS DECIMAL(20,5)))-SUM(CAST(COSTLNMTD AS DECIMAL(20,5)))+SUM(CAST(COSTDPMTD AS DECIMAL(20,5))) - SUM(CAST(INTEXPMTD AS DECIMAL(20,5)))+SUM(CAST(LNFEEMTD AS DECIMAL(20,5)))+SUM(CAST(OTRINCMTD AS DECIMAL(20,5)))+SUM(CAST(EXCHINCMTD AS DECIMAL(20,5))) FROM [MISReportInterface].[dbo].[EQPRF_SUMMARY]
where CUSNUM=@sCustomerNumber AND CUSCCY NOT IN ('QAR', 'USD', 'GBP', 'EUR', 'KWD')),0),
TOTAL = @dTITradeCommTOT+@dTIIntIncLnsTOT+ SUM(CAST(SUSINTMTD AS DECIMAL(20,5)))+SUM(CAST(INTINCMTD AS DECIMAL(20,5)))-SUM(CAST(COSTLNMTD AS DECIMAL(20,5)))+SUM(cast(COSTDPMTD as DECIMAL(20,5))) - SUM(CAST(INTEXPMTD AS DECIMAL(20,5)))+SUM(CAST(LNFEEMTD AS DECIMAL(20,5)))+SUM(CAST(OTRINCMTD AS DECIMAL(20,5)))+SUM(CAST(EXCHINCMTD AS DECIMAL(20,5))),
YTD = @dTITradeCommYTD+@dTIIntIncLnsYTD+ SUM(CAST(SUSINTYTD AS DECIMAL(20,5)))+SUM(CAST(INTINCYTD AS DECIMAL(20,5)))-SUM(CAST(COSTLNYTD AS DECIMAL(20,5)))+SUM(cast(COSTDPYTD as DECIMAL(20,5))) - SUM(CAST(INTEXPYTD AS DECIMAL(20,5)))+SUM(CAST(LNFEEYTD AS DECIMAL(20,5)))+SUM(CAST(OTRINCYTD AS DECIMAL(20,5)))+SUM(CAST(EXCHINCYTD AS DECIMAL(20,5)))
FROM [MISReportInterface].[dbo].[EQPRF_SUMMARY] where CUSNUM=@sCustomerNumber
GROUP BY CUSNUM,CUSTYP,CUSGRP,CUSNAP,CUSACO,CUSRNAM,CUSNAME,CUSRSKE,TXNSTMP
ORDER BY 1
FETCH NEXT
FROM CustomerCursor
INTO @sCustomerNumber
END
CLOSE CustomerCursor
DEALLOCATE CustomerCursor
END
