Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Massive slowdown in SELECT statement in Cursor from SQL 2000 to 2008 -- need help! Expand / Collapse
Author
Message
Posted Friday, June 28, 2013 11:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, December 16, 2013 8:31 PM
Points: 18, Visits: 90
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
Post #1468656
Posted Friday, June 28, 2013 12:14 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:10 AM
Points: 13,082, Visits: 12,546
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1468668
Posted Friday, June 28, 2013 4:55 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, December 16, 2013 8:31 PM
Points: 18, Visits: 90
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.
Post #1468739
Posted Friday, June 28, 2013 5:21 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:06 AM
Points: 3,761, Visits: 8,440
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1468743
Posted Friday, June 28, 2013 5:39 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:54 AM
Points: 17,814, Visits: 15,740
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
Post #1468747
Posted Friday, June 28, 2013 8:17 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, August 16, 2014 8:16 AM
Points: 107, Visits: 513
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.
Post #1468758
Posted Monday, July 1, 2013 1:09 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, December 16, 2013 8:31 PM
Points: 18, Visits: 90
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![color=#990000][/color]


/****** 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




  Post Attachments 
Problem query.sqlplan (9 views, 53.51 KB)
Post #1469200
Posted Monday, July 1, 2013 6:13 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, December 16, 2013 8:31 PM
Points: 18, Visits: 90
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?


  Post Attachments 
Improved query.sqlplan (3 views, 60.18 KB)
Post #1469249
Posted Monday, July 1, 2013 6:18 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, August 16, 2014 8:16 AM
Points: 107, Visits: 513
Did you create the covering index I suggested?
Post #1469254
Posted Monday, July 1, 2013 6:32 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, December 16, 2013 8:31 PM
Points: 18, Visits: 90
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!
Post #1469259
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse