Massive slowdown in SELECT statement in Cursor from SQL 2000 to 2008 -- need help!

  • I have a SELECT statement in a cursor in a stored procedure that ran extremely fast in SQL Server 2000. I just migrated our database to SQL Server 2008 R2 (SP2) for a customer, and the same SELECT now takes fifty times longer to execute. I have logging that measure this performance, which is how I found out. The database on the new 2008 R2 server is set to 2008 compatibility. The slow performance occurs in both 2000 and 2008 compatibility mode.

    Some notes: I need to keep the SELECT statement inside the cursor. It calculates the number of cross sales for an employee in a given day, which is then used to figure out what incentive earnings they get for the cross sales. The SVAccountsHistory table has three million rows; SVCrossSales has 16,000; SVSales has 74,000; SVAccounts has 90,000. The joins between table use the primary keys in these tables. I have a number of other SELECT statements that run just fine for the incentive calculations that I do elsewhere in the stored procedure, so it's just this SELECT in particular. What I'm guessing is the problem in SQL 2008 is that it doesn't like the DATEDIFF compare or the COUNT(SELECT DISTINCT xxx) calculation. It's extremely maddening, I have to say. I'm at my wit's end, and desperately need to figure out what SQL 2008 is choking on in comparison to SQL 2000. Thank you for your help!

    Here's the SELECT statement:

    SELECT

    @tmpCalcValue1 = COUNT(DISTINCT SVCrossSalesHistory.SalesNum) * @EmpRuleValue1

    FROM

    SVCrossSalesHistory

    INNER JOIN SVSales ON (

    SVCrossSalesHistory.INum = SVSales.INum AND

    SVCrossSalesHistory.SalesNum = SVSales.SalesNum AND

    SVSales.SaleDate = @tmpDate AND -- Go back to the day of consideration

    SVSales.OrgNum = @OrgNum AND -- Selected emp

    SVSales.DeleteFlg = 'N')

    INNER JOIN SVGoalProdXref ON (

    SVSales.INum = SVGoalProdXref.INum AND

    SVSales.ProdNum = SVGoalProdXref.ProdNum AND

    SVGoalProdXref.GoalNum = @GoalNum AND -- Go against all the prods for the selected goal

    SVGoalProdXref.DeleteFlg = 'N')

    INNER JOIN SVAccounts ON (

    SVSales.INum = SVAccounts.INum AND

    SVSales.FullAcctNum = SVAccounts.FullAcctNum AND ( -- If the account was closed, determine if the minimum # active days was met during the life of the account

    DATEDIFF(DD,OpenDate, CASE WHEN CloseDate = '1/1/1900' THEN NULL ELSE CloseDate END) > @EmpRuleValue2 OR -- @EmpRuleValue2 = 'x # days active'

    DATEDIFF(DD,OpenDate, CASE WHEN CloseDate = '1/1/1900' THEN NULL ELSE CloseDate END) IS NULL))

    INNER JOIN SVAccountsHistory ON (

    SVAccounts.INum = SVAccountsHistory.INum AND

    SVAccounts.FullAcctNum = SVAccountsHistory.FullAcctNum AND

    SVAccountsHistory.HistoryDate = @StartTime AND -- Today

    SVAccountsHistory.Balance > ISNULL(@EmpRuleValue5,0)) -- '$' value in 'balance > $'

    WHERE

    SVCrossSalesHistory.INum = @INum AND

    SVCrossSalesHistory.CSFlg = 'Y' AND -- Must be a cross sale

    SVCrossSalesHistory.IsNewCustFlg = 'Y' -- Consider new customers only

  • steve.pantazis (6/28/2013)


    I have a SELECT statement in a cursor in a stored procedure that ran extremely fast in SQL Server 2000. I just migrated our database to SQL Server 2008 R2 (SP2) for a customer, and the same SELECT now takes fifty times longer to execute. I have logging that measure this performance, which is how I found out. The database on the new 2008 R2 server is set to 2008 compatibility. The slow performance occurs in both 2000 and 2008 compatibility mode.

    Some notes: I need to keep the SELECT statement inside the cursor. It calculates the number of cross sales for an employee in a given day, which is then used to figure out what incentive earnings they get for the cross sales. The SVAccountsHistory table has three million rows; SVCrossSales has 16,000; SVSales has 74,000; SVAccounts has 90,000. The joins between table use the primary keys in these tables. I have a number of other SELECT statements that run just fine for the incentive calculations that I do elsewhere in the stored procedure, so it's just this SELECT in particular. What I'm guessing is the problem in SQL 2008 is that it doesn't like the DATEDIFF compare or the COUNT(SELECT DISTINCT xxx) calculation. It's extremely maddening, I have to say. I'm at my wit's end, and desperately need to figure out what SQL 2008 is choking on in comparison to SQL 2000. Thank you for your help!

    Here's the SELECT statement:

    Did you update stats after you upgraded or restored to 2008?

    Also, cursors are notoriously slow. It would be far better to remove the cursor entirely. Given the nature of your select statement I am confident that we can remove the cursor completely. The performance improvement will blow your mind.

    In order to help we will need a few things:

    1. Sample DDL in the form of CREATE TABLE statements

    2. Sample data in the form of INSERT INTO statements

    3. Expected results based on the sample data

    Please take a few minutes and read the first article in my signature for best practices when posting questions.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • I hear what you're saying about not using a cursor. Unfortunately, there are five-thousand lines of code for the dozens of incentive rules in place. We don't have the programming resources right now to do away with the cursor logic. Also, the other incentive rules take 1-5 seconds to complete; this one takes 9 minutes!

    I ran sp_recompile on the stored procedure and got a "successfully marked for recompilation" message--didn't update statistics. Interesting thing is that I had to update part of the sproc when we upgraded the database, but that part of the sproc has no impact on the section where the slowdown occurs. So the bottom line is that I ran the ALTER PROCEDURE for the sproc after restoring the database in SQL Server 2008. Wouldn't that negate the need to update statistics, or does my lack of knowledge on what "update statistics" really does require that I run it?

    By the way, I commented out the DATEDIFF logic in the sproc, and it still takes 9 minutes per day of processing. The estimated query execution plan says that 97% of the resources are taken up by a "key lookup (clustered) in SVSales.PK_SVSales". The primary key for SVSales (PK_SVSales) is comprised of the INum and SeqNum fields, the same fields and primary key in the joining table, SVCrossSalesHistory. So I don't know why the estimated execution plan would think the lookup is eating all the resources. I ran a straight-out join of the records between the two tables, and my output windows renders with all 13,000 rows in one second.

    I haven't had a chance to put together any sample data...will get on it as soon as I get a chance.

  • Actually, statistics refer to the tables and are independent from the Stored Procedure.

    If you post the actual execution plan, you could get more help. Actually, I would recommend you to read the following article: http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Attach the table definitions and actual execution plan (not estimated).

    The key lookup means you do not have a covering index for the query. You need to create a covering index for the query to eliminate the key lookup.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • CREATE NONCLUSTERED INDEX [ix_new] ON [dbo].[SVSales]

    (

    [INum] ASC,

    [ProdNum] ASC

    )

    INCLUDE ( [FullAccNum])

    You might have to also include the OpenDate column if it's in SVSales.

  • Attached is the actual execution plan. Here are the table definitions, including primary keys and indexes for the five tables used in the problem query. I gave row counts for the tables in an earlier part of my posting. Let me know if I'm missing anything. Thanks!

    /****** Object: Table [dbo].[SVSales] Script Date: 07/01/2013 14:01:26 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SVSales]') AND type in (N'U'))

    BEGIN

    CREATE TABLE [dbo].[SVSales](

    [INum] [int] NOT NULL,

    [SalesNum] [int] IDENTITY(1,1) NOT NULL,

    [FullAcctNum] [varchar](50) NULL,

    [ProdNum] [int] NULL,

    [GroupOrgNum] [int] NULL,

    [OrgNum] [int] NULL,

    [EmpNum] [int] NULL,

    [SaleAmt] [money] NULL,

    [SaleDate] [smalldatetime] NULL,

    [SaleDateID] [char](8) NULL,

    [SaleStatus] [int] NULL,

    [LastUpdated] [smalldatetime] NULL,

    [DeleteFlg] [char](1) NULL,

    [RefDate] [smalldatetime] NULL,

    [CIFNum] [varchar](50) NULL,

    [LinkedSalesNum] [int] NULL,

    [RefKey] [int] NULL,

    [MultiLocationOrgNum] [int] NULL,

    [OriginatorOrgNum] [int] NULL,

    [OriginatorMultiLocationOrgNum] [int] NULL,

    [PromoNum] [int] NULL,

    CONSTRAINT [PK_SVSales] PRIMARY KEY CLUSTERED

    (

    [INum] ASC,

    [SalesNum] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

    ) ON [PRIMARY]

    END

    GO

    SET ANSI_PADDING OFF

    GO

    IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[SVSales]') AND name = N'EmpNum')

    CREATE NONCLUSTERED INDEX [EmpNum] ON [dbo].[SVSales]

    (

    [EmpNum] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

    GO

    IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[SVSales]') AND name = N'FullAcctNum')

    CREATE NONCLUSTERED INDEX [FullAcctNum] ON [dbo].[SVSales]

    (

    [FullAcctNum] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

    GO

    IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[SVSales]') AND name = N'INum')

    CREATE NONCLUSTERED INDEX [INum] ON [dbo].[SVSales]

    (

    [INum] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

    GO

    IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[SVSales]') AND name = N'IX_SVSales')

    CREATE NONCLUSTERED INDEX [IX_SVSales] ON [dbo].[SVSales]

    (

    [GroupOrgNum] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

    GO

    IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[SVSales]') AND name = N'IX_SVSales_1')

    CREATE NONCLUSTERED INDEX [IX_SVSales_1] ON [dbo].[SVSales]

    (

    [OrgNum] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

    GO

    IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[SVSales]') AND name = N'MultiLocationOrgNum')

    CREATE NONCLUSTERED INDEX [MultiLocationOrgNum] ON [dbo].[SVSales]

    (

    [MultiLocationOrgNum] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    GO

    IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[SVSales]') AND name = N'OrgNum')

    CREATE NONCLUSTERED INDEX [OrgNum] ON [dbo].[SVSales]

    (

    [OrgNum] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

    GO

    IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[SVSales]') AND name = N'OriginatorMultiLocationOrgNum')

    CREATE NONCLUSTERED INDEX [OriginatorMultiLocationOrgNum] ON [dbo].[SVSales]

    (

    [INum] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    GO

    IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[SVSales]') AND name = N'OriginatorOrgNum')

    CREATE NONCLUSTERED INDEX [OriginatorOrgNum] ON [dbo].[SVSales]

    (

    [INum] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    GO

    IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[SVSales]') AND name = N'ProdNum')

    CREATE NONCLUSTERED INDEX [ProdNum] ON [dbo].[SVSales]

    (

    [ProdNum] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

    GO

    IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[SVSales]') AND name = N'SaleType')

    CREATE NONCLUSTERED INDEX [SaleType] ON [dbo].[SVSales]

    (

    [SaleStatus] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

    GO

    /****** Object: Table [dbo].[SVGoalProdXref] Script Date: 07/01/2013 14:01:26 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SVGoalProdXref]') AND type in (N'U'))

    BEGIN

    CREATE TABLE [dbo].[SVGoalProdXref](

    [INum] [int] NOT NULL,

    [GoalNum] [int] NOT NULL,

    [ProdNum] [int] NOT NULL,

    [DeleteFlg] [char](1) NOT NULL,

    [ChgKey] [int] NOT NULL,

    CONSTRAINT [PK_SVGoalProdXref] PRIMARY KEY CLUSTERED

    (

    [INum] ASC,

    [GoalNum] ASC,

    [ProdNum] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

    ) ON [PRIMARY]

    END

    GO

    SET ANSI_PADDING OFF

    GO

    /****** Object: Table [dbo].[SVCrossSalesHistory] Script Date: 07/01/2013 14:01:26 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SVCrossSalesHistory]') AND type in (N'U'))

    BEGIN

    CREATE TABLE [dbo].[SVCrossSalesHistory](

    [INum] [int] NOT NULL,

    [SalesNum] [int] NOT NULL,

    [SaleDate] [smalldatetime] NOT NULL,

    [CSNum] [int] NULL,

    [CSFlg] [char](1) NOT NULL,

    [CSCt] [int] NOT NULL,

    [TrackerSessionNum] [int] NULL,

    [TrackerCt] [int] NOT NULL,

    [TrackerFlg] [char](1) NULL,

    [RealSessionNum] [int] NULL,

    [RealFlg] [char](1) NULL,

    [RealCt] [int] NULL,

    [IsNewCustFlg] [char](1) NOT NULL,

    CONSTRAINT [PK_SVCrossSalesHistory] PRIMARY KEY CLUSTERED

    (

    [INum] ASC,

    [SalesNum] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

    ) ON [PRIMARY]

    END

    GO

    SET ANSI_PADDING OFF

    GO

    /****** Object: Table [dbo].[SVAccountsHistory] Script Date: 07/01/2013 14:01:26 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SVAccountsHistory]') AND type in (N'U'))

    BEGIN

    CREATE TABLE [dbo].[SVAccountsHistory](

    [INum] [int] NOT NULL,

    [FullAcctNum] [varchar](50) NOT NULL,

    [HistoryDate] [smalldatetime] NOT NULL,

    [Balance] [money] NOT NULL,

    [AvgBal] [money] NOT NULL,

    [IBLastActiveDate] [smalldatetime] NULL,

    [NumBillsPaid] [int] NOT NULL,

    [EmpID] [varchar](50) NULL,

    [Branch] [varchar](50) NULL,

    CONSTRAINT [PK_SVAccountHistory] PRIMARY KEY CLUSTERED

    (

    [INum] ASC,

    [FullAcctNum] ASC,

    [HistoryDate] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

    ) ON [PRIMARY]

    END

    GO

    SET ANSI_PADDING OFF

    GO

    /****** Object: Table [dbo].[SVAccounts] Script Date: 07/01/2013 14:01:26 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SVAccounts]') AND type in (N'U'))

    BEGIN

    CREATE TABLE [dbo].[SVAccounts](

    [INum] [int] NOT NULL,

    [AcctNum] [varchar](50) NOT NULL,

    [ApplID] [varchar](50) NOT NULL,

    [AcctType] [varchar](50) NOT NULL,

    [ProdID] [varchar](50) NULL,

    [FullAcctNum] [varchar](50) NOT NULL,

    [OpenDate] [smalldatetime] NULL,

    [LastActiveDate] [smalldatetime] NULL,

    [OpenAmt] [money] NOT NULL,

    [Branch] [varchar](50) NULL,

    [EmpID] [varchar](50) NULL,

    [Balance] [money] NOT NULL,

    [CloseReason] [varchar](50) NULL,

    [CloseDate] [smalldatetime] NULL,

    [ServicingEmpID] [varchar](50) NULL,

    [Status] [int] NOT NULL,

    [HasATMCardFlg] [char](1) NOT NULL,

    [ATMCardType] [int] NULL,

    [HasEStmtFlg] [char](1) NOT NULL,

    [ISVisaCreditCardFlg] [char](1) NOT NULL,

    [CreditCardType] [int] NULL,

    [ATMCardDate] [smalldatetime] NULL,

    [EStmtDate] [smalldatetime] NULL,

    [CreditCardDate] [smalldatetime] NULL,

    [AcctClass] [int] NOT NULL,

    [HasIBFlg] [char](1) NOT NULL,

    [IBDate] [smalldatetime] NULL,

    [HasBillPayFlg] [char](1) NOT NULL,

    [BillPayDate] [smalldatetime] NULL,

    [NumBillsPaid] [int] NOT NULL,

    [HasDebtProtectFlg] [char](1) NOT NULL,

    [DebtProtectDate] [smalldatetime] NULL,

    [AvgBal] [money] NOT NULL,

    [IsSecondMtgFlg] [char](1) NOT NULL,

    [SecondMtgDate] [smalldatetime] NULL,

    [HasDirectDepositFlg] [char](1) NOT NULL,

    [DirectDepositDate] [smalldatetime] NULL,

    [HasConsumerDebitCardFlg] [char](1) NOT NULL,

    [ConsumerDebitCardDate] [smalldatetime] NULL,

    [HasBusinessDebitCardFlg] [char](1) NOT NULL,

    [BusinessDebitCardDate] [smalldatetime] NULL,

    [DebtProtectAmt] [money] NOT NULL,

    [AlliedCode] [int] NULL,

    [NoDisabilityFlg] [char](1) NULL,

    [DisabilityEligible] [char](1) NOT NULL,

    [LifeEligible] [char](1) NOT NULL,

    [IBLastActiveDate] [smalldatetime] NULL,

    [ProtectedLoan] [char](1) NOT NULL,

    [GAPEligible] [char](1) NOT NULL,

    [DPIEligible] [char](1) NOT NULL,

    [MBIEligible] [char](1) NOT NULL,

    [DisabilityApplicable] [char](1) NOT NULL,

    [LifeApplicable] [char](1) NOT NULL,

    [GAPApplicable] [char](1) NOT NULL,

    [DPIApplicable] [char](1) NOT NULL,

    [MBIApplicable] [char](1) NOT NULL,

    [LoanEligible] [char](1) NOT NULL,

    [ForDisabilityPct] [char](1) NOT NULL,

    [ForLifePct] [char](1) NOT NULL,

    [ForGAPPct] [char](1) NOT NULL,

    [ForMBIPct] [char](1) NOT NULL,

    [ForDPIPct] [char](1) NOT NULL,

    [IUIEligible] [char](1) NOT NULL,

    [IUIApplicable] [char](1) NOT NULL,

    [ForIUIPct] [char](1) NOT NULL,

    [AlliedProd7Eligible] [char](1) NOT NULL,

    [AlliedProd7Applicable] [char](1) NOT NULL,

    [ForAlliedProd7Pct] [char](1) NOT NULL,

    [ADDEligible] [char](1) NOT NULL,

    [ADDApplicable] [char](1) NOT NULL,

    [ForADDPct] [char](1) NOT NULL,

    [LifeEligibleAmt] [money] NOT NULL,

    [DPIEligibleAmt] [money] NOT NULL,

    [DisabilityEligibleAmt] [money] NOT NULL,

    [IUIEligibleAmt] [money] NOT NULL,

    [AlliedProd7EligibleAmt] [money] NOT NULL,

    [ADDEligibleAmt] [money] NOT NULL,

    [HasInvestmentFlg] [char](1) NOT NULL,

    [InvestmentDate] [smalldatetime] NULL,

    [HasMortgageFlg] [char](1) NOT NULL,

    [MortgageDate] [smalldatetime] NULL,

    [HasOptInFlg] [char](1) NOT NULL,

    [OptInDate] [smalldatetime] NULL,

    [OptInEmpID] [varchar](50) NULL,

    [BillPayEmpID] [varchar](50) NULL,

    [IBEmpID] [varchar](50) NULL,

    [EStmtEmpID] [varchar](50) NULL,

    [SEGID] [varchar](50) NULL,

    [ProcessorEmpID] [varchar](50) NULL,

    [CloserEmpID] [varchar](50) NULL,

    [FunderEmpID] [varchar](50) NULL,

    [LoanOfficerEmpID] [varchar](50) NULL,

    CONSTRAINT [PK_SVAccounts] PRIMARY KEY CLUSTERED

    (

    [INum] ASC,

    [FullAcctNum] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

    ) ON [PRIMARY]

    END

    GO

    SET ANSI_PADDING OFF

  • Okay, I did two things that marginally improved performance.

    First, I researched the issue online and saw that "Key Lookups" are bad when it comes to execution plans. I guess you need to convert "predicates" (whatever those are) to "seek predicates" (whatever they are) to boost performance.

    Second, I eliminated "AND SVSales.DeleteFlg = 'N'" from my query, since that field is deprecated in the SVSales table, which means I eliminated DeleteFlg as a predicate (a good thing, it seems); I also created a non-unique, non-clustered index for SaleDate and rebuilt the index.

    After I regenerated the execution plan, the evil Key Lookup was gone. So I ran my sproc and noticed a 50% boost in performance. While that's good, it's not great considerning it was taking 8-12 minutes per day before, and now it's taking 2-6 minutes. Remember, we're supposed be down to a few seconds when all is said and done. Attached is the updated execution plan. Someone mentioned that I should create a "Covered Index" to boost performance even more, but I'm not sure which fields to include or whether that would really do anything. Any opinions?

  • Did you create the covering index I suggested?

  • I'm still trying to make heads and tails out of what covering indexes do. I've got a dozen non-unique, non-clustered indexes in SVSales, including the primary key. The SELECT statement in my query is referencing SaleDate and OrgNum in SVSales, but SVSales joins to SVAccounts using INum and FullAcctNum, and it joins to SVGoalProdXref using INum and ProdNum. So I'm not sure what I use for the Covered Index. I think you mentioned INum, ProdNum and FullAcctNum, and potentially SaleDate. I have other fields that are indexed that join to other tables, such as GroupOrgNum, but that goes to a table NOT shown in my SELECT (since I didn't need to use it). Does GroupOrgNum also get added to the Covered index, or do I leave it out? The interesting thing is that my query cost in my execution plan is 100%, and another site suggested that a Covered Index could lower it. I apologize, but Covered Indexes are new to me. The crazy thing is that my query works in seconds on SQL Server 2000, and in 2008 it takes minutes--so maddening!

  • Also, let me re-emphasize that I created a non-unique, non-clustered index for SaleDate, which eliminated the Key Lookup in the execution plan, but peformance is still slow.

  • Not to be rude, but do you suppose there might be a reason I suggested you create that particular index? And that creating it, testing and perhaps dropping it might yield valuable information? And that looking at the INCLUDE keyword and then consulting BOL might teach you something about covering indexes? And that looking at the index and then looking back at the query might yield some information you might find useful?

    Keep in mind I suggested it before you posted a plan, and I still think it may be useful. But suit yourself.

  • steve.pantazis (7/1/2013)


    Also, let me re-emphasize that I created a non-unique, non-clustered index for SaleDate, which eliminated the Key Lookup in the execution plan, but peformance is still slow.

    Steve

    The indexing strategy for table SVSales is somewhere between 'weird' and 'disastrous';

    CREATE NONCLUSTERED INDEX [EmpNum] ON [dbo].[SVSales] ([EmpNum] ASC)

    CREATE NONCLUSTERED INDEX [FullAcctNum] ON [dbo].[SVSales] ([FullAcctNum] ASC)

    CREATE NONCLUSTERED INDEX [INum] ON [dbo].[SVSales] ([INum] ASC)

    CREATE NONCLUSTERED INDEX [IX_SVSales] ON [dbo].[SVSales] ([GroupOrgNum] ASC)

    CREATE NONCLUSTERED INDEX [IX_SVSales_1] ON [dbo].[SVSales] ([OrgNum] ASC)

    CREATE NONCLUSTERED INDEX [MultiLocationOrgNum] ON [dbo].[SVSales] ([MultiLocationOrgNum] ASC)

    CREATE NONCLUSTERED INDEX [OrgNum] ON [dbo].[SVSales] ([OrgNum] ASC)

    CREATE NONCLUSTERED INDEX [OriginatorMultiLocationOrgNum] ON [dbo].[SVSales] ([INum] ASC)

    CREATE NONCLUSTERED INDEX [OriginatorOrgNum] ON [dbo].[SVSales] ([INum] ASC)

    CREATE NONCLUSTERED INDEX [ProdNum] ON [dbo].[SVSales] ([ProdNum] ASC)

    CREATE NONCLUSTERED INDEX [SaleType] ON [dbo].[SVSales] ([SaleStatus] ASC)

    Single-column indexes aren't of much use unless you're performing an existence check on that column alone. Now, whilst your query does perform existence checks - against several tables - they're all multi-column checks, which means SQL Server cannot use just one index, it has to check several or revert to the table (clustered index).

    A covering index contains all of the columns which SQL Server needs to access to satisfy the requirements of the query. I'd recommend a new index on SVSales as follows;

    CREATE NONCLUSTERED INDEX [ix_SaleDate_INum_OrgNum] ON [dbo].[SVSales] (SaleDate, INum, OrgNum) INCLUDE (FullAcctNum, SalesNum, ProdNum)

    This index contains all of the columns the query references, and the first three match predicates in the query to support index seeks. Give it a try.

    I haven't looked at the indexes for the other tables but I'd guess the pattern is the same. There's scope for a quick win here if you are interested and have the time to play.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Guys, thanks for the suggestion regarding the covered index. Sorry it took so long to get back to you, but I spent all day (and I mean all day!) on the stupid problem. Turns out I was barking up the wrong tree (more info below).

    Out of the five tables in the joins in my SELECT statement, I added two covered indexes, one for SVSales and one for SVCrossSalesHistory, as shown below. The performance gain was marginal over my solitary non-unique, unclustered index on the SaleDate field in SVSales, which made me suspect my problem was elsewhere. Lo and behold, it was in a completely different query in my 6,000-line sproc. Funny thing is the execution plan for the query suggested a covered index for the table, with suggested index fields and include fields! Who knew Microsoft was that crafty? After adding the covered index, I noticed a very nice boost in peformance. There's still some tweaking I need to do, but the bottom line is that the covered index helped!

    Thanks again for your tireless assistance. Now that I understand how to evaluate predicates and seeks in execution plans, I can continue optimizing the rest of my sproc (and hopefully speed things up further).

    CREATE NONCLUSTERED INDEX [ix_SaleDate_INum_OrgNum] ON [dbo].[SVSales] (SaleDate, INum, OrgNum) INCLUDE (FullAcctNum, SalesNum, ProdNum)

    CREATE NONCLUSTERED INDEX [ix_Covered_CSFlg_IsNewCustFlg] ON [dbo].[SVCrossSalesHistory] (CSFlg, IsNewCustFlg) INCLUDE (SalesNum)

  • Okay, I couldn't help it--I had to post Microsoft's covered index recommendation for the real problem query (see attached image). Below is the query that caused the performance problem. You'll notice that the table SVSales is involved again, and that rascally field SaleDate! However, the missing covered index was on the SVPromoInteractions table. The RefKey and ContactDate fields needed the covered index to improve efficiency. After adding the covered index, my sproc processing time decreased between 50-75%, depending on which day in the batch processing the sproc was called for.

    SELECT

    @tmpCalcValue1 = COUNT(*)

    FROM

    SVSales

    INNER JOIN SVTimePeriods ON (

    SVSales.INum = SVTimePeriods.INum AND

    SVSales.SaleDate < SVTimePeriods.TimeEnd AND -- Do compare of sale (not referral) date, since an actual sale occurred

    SVSales.SaleDate >= SVTimePeriods.TimeStart AND

    SVTimePeriods.TPNum = @TPNum)

    INNER JOIN SVGoalProdXref ON (

    SVSales.INum = SVGoalProdXref.INum AND

    SVSales.ProdNum = SVGoalProdXref.ProdNum AND

    SVGoalProdXref.GoalNum = @GoalNum AND

    SVGoalProdXref.DeleteFlg = 'N')

    INNER JOIN (

    SELECT

    SVPromoInteractions.RefKey

    FROM

    SVPromoInteractions

    INNER JOIN (

    SELECT

    RefKey,

    MIN(ContactDate) ContactDate -- Finds the oldest interaction, in case there are more than one 321 rec

    FROM

    SVPromoInteractions

    WHERE

    INum = @INum AND

    PromoOrRef = 'R' AND

    ResultStatus = 321 AND

    DeleteFlg = 'N'

    GROUP BY

    RefKey) GroupedRecs ON (

    SVPromoInteractions.RefKey = GroupedRecs.RefKey AND

    SVPromoInteractions.ContactDate = GroupedRecs.ContactDate)

    WHERE

    INum = @INum AND

    OrgNum = @OrgNum AND -- Referrer

    ResultStatus = 321 -- Referred

    GROUP BY

    SVPromoInteractions.RefKey) Referrer ON (

    SVSales.RefKey = Referrer.RefKey)

    WHERE

    SVSales.INum = @INum AND

    SVSales.SaleStatus = 264 -- Referred (and sold)

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply