June 28, 2013 at 11:46 am
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
June 28, 2013 at 12:14 pm
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/
June 28, 2013 at 4:55 pm
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.
June 28, 2013 at 5:21 pm
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/
June 28, 2013 at 5:39 pm
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
June 28, 2013 at 8:17 pm
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.
July 1, 2013 at 1:09 pm
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
July 1, 2013 at 6:13 pm
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?
July 1, 2013 at 6:18 pm
Did you create the covering index I suggested?
July 1, 2013 at 6:32 pm
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!
July 1, 2013 at 6:33 pm
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.
July 2, 2013 at 2:45 am
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.
July 2, 2013 at 6:00 am
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.
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
July 3, 2013 at 12:34 am
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)
July 3, 2013 at 12:56 am
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