Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question

1 Answer 1

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.

share|improve this answer
    
Will check AutoShrink setting. You say "tempdb"...I don't reference any other DB's so is this where all temp tables are being stored? Is it better to "hard" create a table to hold my temp data and truncate it afterwards? I can re-factor the cursor into a "while" loop and re-test. Thx –  BlueChippy Sep 24 '12 at 5:08
    
No - a while loop is essentially the same as a cursor. Try to get all the rows in one fell swoop, instead of doing it one customer at a time. TempDB is likely to be used to store the intermediate results of your cursor, so it's important that it's not a bottleneck. Also, I wouldn't put it in SINGLE_USER mode. It won't make it faster to remove that, but you shouldn't turn on that setting without an excellent reason. –  Rob Farley Sep 24 '12 at 5:13
    
"Try to get all the rows in one fell swoop" - do you mean re-write to "join" my temp table with my results for all rows? –  BlueChippy Sep 24 '12 at 5:16
    
Yes - that's exactly what I mean. You can wrap the UNION ALL bits in parentheses, and join it to the CusNum field. But you don't really need that table variable at all - you could do it all in the one query, which could well run significantly quicker (and certainly be more tunable, since it doesn't have a reliance on so much programmatic code). –  Rob Farley Sep 24 '12 at 5:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.