I have inherited a large and slow stored procedure and it's giving me a nightmare:
I'm not a DBA, although I have some knowledge, but I don't know where to start to identify this bottleneck.
I have SQL Server 2008 installed on my desktop with an exact copy of the production database. I am running everything from SSMS and I have tried straight SQL and SP. Time for SP vs SQL is close enough to the same to not worry unduly about – its the time of local vs server that's concerning me.
Local:
- 2Ghz dual-core
- 4Gb RAM
- SQL Server 2008 Sp1
- Microsoft SQL Server 2008 R2 (SP1) – 10.50.2500.0 (Intel X86)
- Enterprise Edition on Windows NT 6.1 (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 (Build 3790: Service Pack 2) (VM)
When I run "locally", it takes around 10 minutes to complete.
When I run against the server, it takes around 14 hours to complete!!!
[Total inserts approx = 14,000,000]
I have tried to tune the SP as much as I can and avoided parameter sniffing but not sure WHY this should be SO much slower on a pretty much equivalent machine (I know SQL Server version is slightly different, but can't see why it would make THAT much difference?)
I know the SP isn't fantastic, but if it runs in 10 minutes locally, then its not total pants! If it took 60 seconds on the server then that would still be fine.
Server has a pretty low load on it when these SP's are not running, and around 50% CPU when they are in progress (one core 100%?). Locally CPU never gets above 25%.
I've broken down the SP so it is called a number of times in the hope that might help…it did locally, but not by much, but made no difference on the server.
Script to call the SP
USE master
GO
ALTER DATABASE MISReportInterface
SET AUTO_UPDATE_STATISTICS_ASYNC OFF
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE MISReportInterface
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
USE MISReportInterface
EXEC sp_executesql N'EXEC [LoadProfitabilitySummaryByCustomer] @inCustomerType=''CG'', @inClearTable=1, @inStartNum=0, @inEndNum=1000000'
print 'CG'
EXEC sp_executesql N'EXEC [LoadProfitabilitySummaryByCustomer] @inCustomerType=''CP'', @inClearTable=0, @inStartNum=0, @inEndNum=1000000'
print 'CP'
EXEC sp_executesql N'EXEC [LoadProfitabilitySummaryByCustomer] @inCustomerType=''DA'', @inClearTable=0, @inStartNum=0, @inEndNum=1000000'
print 'DA'
EXEC sp_executesql N'EXEC [LoadProfitabilitySummaryByCustomer] @inCustomerType=''DB'', @inClearTable=0, @inStartNum=0, @inEndNum=1000000'
print 'DB'
EXEC sp_executesql N'EXEC [LoadProfitabilitySummaryByCustomer] @inCustomerType=''ED'', @inClearTable=0, @inStartNum=0, @inEndNum=1000000'
print 'ED'
EXEC sp_executesql N'EXEC [LoadProfitabilitySummaryByCustomer] @inCustomerType=''EE'', @inClearTable=0, @inStartNum=0, @inEndNum=1000000'
print 'EE'
EXEC sp_executesql N'EXEC [LoadProfitabilitySummaryByCustomer] @inCustomerType=''EF'', @inClearTable=0, @inStartNum=0, @inEndNum=1000000'
print 'EF'
EXEC sp_executesql N'EXEC [LoadProfitabilitySummaryByCustomer] @inCustomerType=''EG'', @inClearTable=0, @inStartNum=0, @inEndNum=1000000'
print 'EG'
EXEC sp_executesql N'EXEC [LoadProfitabilitySummaryByCustomer] @inCustomerType=''EH'', @inClearTable=0, @inStartNum=0, @inEndNum=1000000'
print 'EH'
EXEC sp_executesql N'EXEC [LoadProfitabilitySummaryByCustomer] @inCustomerType=''ES'', @inClearTable=0, @inStartNum=0, @inEndNum=1000000'
print 'ES'
EXEC sp_executesql N'EXEC [LoadProfitabilitySummaryByCustomer] @inCustomerType=''EX'', @inClearTable=0, @inStartNum=0, @inEndNum=1000000'
print 'EX'
EXEC sp_executesql N'EXEC [LoadProfitabilitySummaryByCustomer] @inCustomerType=''EA'', @inClearTable=0, @inStartNum=0, @inEndNum=150000'
print 'EA 0 150000'
EXEC sp_executesql N'EXEC [LoadProfitabilitySummaryByCustomer] @inCustomerType=''EA'', @inClearTable=0, @inStartNum=150000, @inEndNum=160000'
print 'EA 150000 160000'
EXEC sp_executesql N'EXEC [LoadProfitabilitySummaryByCustomer] @inCustomerType=''EA'', @inClearTable=0, @inStartNum=160000, @inEndNum=170000'
print 'EA 160000 170000'
EXEC sp_executesql N'EXEC [LoadProfitabilitySummaryByCustomer] @inCustomerType=''EA'', @inClearTable=0, @inStartNum=170000, @inEndNum=180000'
print 'EA 170000 180000'
EXEC sp_executesql N'EXEC [LoadProfitabilitySummaryByCustomer] @inCustomerType=''EA'', @inClearTable=0, @inStartNum=180000, @inEndNum=190000'
print 'EA 180000 190000'
EXEC sp_executesql N'EXEC [LoadProfitabilitySummaryByCustomer] @inCustomerType=''EA'', @inClearTable=0, @inStartNum=190000, @inEndNum=200000'
print 'EA 190000 200000'
EXEC sp_executesql N'EXEC [LoadProfitabilitySummaryByCustomer] @inCustomerType=''EA'', @inClearTable=0, @inStartNum=200000, @inEndNum=210000'
print 'EA 200000 210000'
EXEC sp_executesql N'EXEC [LoadProfitabilitySummaryByCustomer] @inCustomerType=''EA'', @inClearTable=0, @inStartNum=210000, @inEndNum=220000'
print 'EA 210000 220000'
EXEC sp_executesql N'EXEC [LoadProfitabilitySummaryByCustomer] @inCustomerType=''EA'', @inClearTable=0, @inStartNum=220000, @inEndNum=230000'
print 'EA 220000 230000'
EXEC sp_executesql N'EXEC [LoadProfitabilitySummaryByCustomer] @inCustomerType=''EA'', @inClearTable=0, @inStartNum=230000, @inEndNum=240000'
print 'EA 230000 240000'
EXEC sp_executesql N'EXEC [LoadProfitabilitySummaryByCustomer] @inCustomerType=''EA'', @inClearTable=0, @inStartNum=240000, @inEndNum=1000000'
print 'EA 240000 1000000'
USE master
ALTER DATABASE MISReportInterface
SET MULTI_USER;
GO
Apols for posting the whole SP…but can't see how else to show what its doing in case there is an option that can be set to improve things 🙁
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
Best Answer
It's definitely ugly - you could remove the cursor, which is going to be a significant culprit to the general slowness, creating a more set-based solution. But it depends on your general comfort in refactoring as to whether that's really a good idea.
But there are a number of things about servers you could investigate.
For example - does the database on the server have "AutoShrink" turned on? Is tempdb on a particularly slow disk?
You could compare the execution plans on the different servers to see if there's a massive difference between one and the other. One difficulty with cursors is that you can end up with a plan being worked out on an early iteration, despite it being a bad plan for later iterations. But I assume you have the same data on the two boxes, so this shouldn't really be a factor.
I'd be checking the other conditions on the server, such as disk, and other things which may be running. If your files keep growing and shrinking (for example), then this could be bad news for any query that's running there.