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

Mr or Mrs. 500

Points: 547

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