Performance issues using Parameters

microsoft.public.sqlserver.programming

4 Aug 2006 11:30 AM

Reg Besseling

Hi all,
(DDL's etc below)

I hope someone can help with this as I have tried everything I can think of
and had no luck

I have a table with 11M rows and have created a simple report/query on it.

When I run a "simple" sql on the table I get results in sub second times,
however when using parameters it takes over half an hour.

After reading about parameter sniffing, I tried putting this into a sp
using defaults for all parameters no change, I also tried the sp with a
recompile clause still no change.

I have checked the execution plans and found that the "Fast" query starts
with a index seek on idx_polnum and an estimated rowcount of 4

on the slow queries the plan shows it is using the clustered index seek with
the sp estimating 11m rows and the parameter query 1M rows

I have tried setting the default of the @policy parameter to a unique value
with no change in the sp execution plan

i managed to get barly acceptable (30 seconds ) performance when i add the
following table hint with (INDEX(idx_polnum,Idx_Producercd))

i do not want to use hints as ive heard it is not recomended , is this the
only way or am i missing something?

I am using SQL 2000 with sp 3a

Thanks in advance

Reg Besseling

============================
here is the "simple" SQL Fast
============================

SELECT     LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3), POLPFX))
+ RIGHT('000000' + RTRIM(CONVERT(char, POLNUM)), 7) AS PolicyKey,
                      POLNUM, PACCTNAME AS Insured, MAX(RENPER) AS
RenewalMonth, MAJORLINE, SUM(GRSOSINC) AS GOS, SUM(GRSINC) AS GPW,
SUM(GRSPDINC)
                      AS GPD, SUM(GRSPDINC + GRSOSINC) AS GLI, AGENTNAME
FROM         RIPRMCLM
WHERE   1=1
AND (30 = 9999999 or POLNUM = 30 )
AND (TRNPER BETWEEN 0 AND 999999)
AND (99999 = 99999 OR RIPRMCLM.PRODUCERCD = 99999 )
AND ( 99 = 99 OR RIPRMCLM.MAJORLINE = 99)
GROUP BY LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3), POLPFX)) +
RIGHT('000000' + RTRIM(CONVERT(char, POLNUM)), 7), POLNUM,
                      PACCTNAME, MAJORLINE, AGENTNAME
ORDER BY MAJORLINE DESC

==========================
hers is the query using parameters SLOW!!!!
==========================

declare @policy decimal (7,0)
declare @Start char(6)
declare @End char(6)
declare @Agent decimal (7,0)
declare @Major decimal (7,0)

set @policy = 30
set @Start = '0'
set @End = '999999'
set @Agent = 99999
set @Major = 99

SELECT     LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3), POLPFX))
+ RIGHT('000000' + RTRIM(CONVERT(char, POLNUM)), 7) AS PolicyKey,
                      POLNUM, PACCTNAME AS Insured, MAX(RENPER) AS
RenewalMonth, MAJORLINE, SUM(GRSOSINC) AS GOS, SUM(GRSINC) AS GPW,
SUM(GRSPDINC)
                      AS GPD, SUM(GRSPDINC + GRSOSINC) AS GLI, AGENTNAME
FROM         RIPRMCLM
WHERE   1=1
AND (@policy = 9999999 or POLNUM = @policy )
AND (TRNPER BETWEEN @Start AND @End)
AND (@Agent = 99999 OR RIPRMCLM.PRODUCERCD = @Agent )
AND (@Major = 99 OR RIPRMCLM.MAJORLINE = @Major)
GROUP BY LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3), POLPFX)) +
RIGHT('000000' + RTRIM(CONVERT(char, POLNUM)), 7), POLNUM,
                      PACCTNAME, MAJORLINE, AGENTNAME
ORDER BY MAJORLINE DESC

=======================
in a SP SLOW
=======================

create procedure usp_ReportClaimsExpMain
@policy decimal (7,0) = 30
, @Start char(6) = '0'
, @End char(6) = '999999'
, @Agent decimal (7,0) = 99999
, @Major decimal (7,0) = 99

with recompile
as

SELECT     LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3), POLPFX))
+ RIGHT('000000' + RTRIM(CONVERT(char, POLNUM)), 7) AS PolicyKey,
                      POLNUM, PACCTNAME AS Insured, MAX(RENPER) AS
RenewalMonth, MAJORLINE, SUM(GRSOSINC) AS GOS, SUM(GRSINC) AS GPW,
SUM(GRSPDINC)
                      AS GPD, SUM(GRSPDINC + GRSOSINC) AS GLI, AGENTNAME
FROM         RIPRMCLM
WHERE   1=1
AND (@policy = 9999999 or POLNUM = @policy )
AND (TRNPER BETWEEN @Start AND @End)
AND (@Agent = 99999 OR RIPRMCLM.PRODUCERCD = @Agent )
AND (@Major = 99 OR RIPRMCLM.MAJORLINE = @Major)
GROUP BY LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3), POLPFX)) +
RIGHT('000000' + RTRIM(CONVERT(char, POLNUM)), 7), POLNUM,
                      PACCTNAME, MAJORLINE, AGENTNAME
ORDER BY MAJORLINE DESC

go

exec usp_ReportClaimsExpMain @policy = 30, @Start = '0', @End = '999999',
@Agent = 99999, @Major = 99
==========================
Table DDL
==========================
CREATE TABLE [RIPRMCLM] (
[COMPANY] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RICOMPANY] [decimal](5, 0) NOT NULL ,
[SOURCECDE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RIIND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RILOB] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TRTYNUM] [decimal](7, 0) NOT NULL ,
[TRTYYR] [decimal](4, 0) NOT NULL ,
[POLCO] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[POLPFX] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[POLNUM] [decimal](7, 0) NOT NULL ,
[LOCNUM] [decimal](3, 0) NOT NULL ,
[RISKNUM] [decimal](3, 0) NOT NULL ,
[CLMNUM] [decimal](9, 0) NOT NULL ,
[RISKTYPE] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PREMCLAS] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[BATCHYEAR] [decimal](4, 0) NOT NULL ,
[BATCHMTH] [decimal](2, 0) NOT NULL ,
[EFFDATE] [decimal](8, 0) NOT NULL ,
[TRANDATE] [decimal](8, 0) NOT NULL ,
[PRMEXPDATE] [decimal](8, 0) NOT NULL ,
[RPTTYPE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[GRSTRANAMT] [decimal](13, 2) NOT NULL ,
[PREMIUM] [decimal](11, 2) NOT NULL ,
[COMMISSION] [decimal](10, 2) NOT NULL ,
[STAMPDUTY] [decimal](10, 2) NOT NULL ,
[RSTRANTYPE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RSLOSSTYPE] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PYEXPTYPE] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RECOVTYPE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MAJORLINE] [decimal](2, 0) NOT NULL ,
[CLASS] [decimal](3, 0) NOT NULL ,
[MINORLINE] [decimal](2, 0) NOT NULL ,
[PY] [decimal](4, 0) NOT NULL ,
[PRODUCERCD] [decimal](7, 0) NOT NULL ,
[LEADER] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MSTRPLCY] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[INTPREXAM] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CLIENTNO] [decimal](7, 0) NOT NULL ,
[STAMPDTYCD] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PRODCOMM] [decimal](10, 2) NOT NULL ,
[OURSHARE] [decimal](7, 4) NOT NULL ,
[LOSSCAUSE] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DATEOFLOSS] [decimal](8, 0) NOT NULL ,
[DATEADVISD] [decimal](8, 0) NOT NULL ,
[ANATPROPCD] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[INJDAMCODE] [decimal](3, 0) NOT NULL ,
[THRDPRTY] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[OCCUPATION] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PERCENTAGE] [decimal](5, 2) NOT NULL ,
[CLMTRANTYP] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TRANTIME] [decimal](6, 0) NOT NULL ,
[USERID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[VATIND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TRNTYPE] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PACCTNAME] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PSTATUS] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PPOLTERM] [decimal](2, 0) NOT NULL ,
[PRENEWCODE] [decimal](2, 0) NOT NULL ,
[PPOLEFFDTE] [decimal](10, 0) NOT NULL ,
[PPOLEXPDTE] [decimal](10, 0) NOT NULL ,
[AGENTNAME] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[GRSOSINC] [decimal](11, 2) NOT NULL ,
[GRSOSEXC] [decimal](11, 2) NOT NULL ,
[GRSPDINC] [decimal](11, 2) NOT NULL ,
[GRSPDEXC] [decimal](11, 2) NOT NULL ,
[GRSINC] [decimal](11, 2) NOT NULL ,
[GRSEXC] [decimal](11, 2) NOT NULL ,
[NETOSINC] [decimal](11, 2) NOT NULL ,
[NETOSEXC] [decimal](11, 2) NOT NULL ,
[NETPDINC] [decimal](11, 2) NOT NULL ,
[NETPDEXC] [decimal](11, 2) NOT NULL ,
[NETINC] [decimal](11, 2) NOT NULL ,
[NETEXC] [decimal](11, 2) NOT NULL ,
[GRSRECINC] [decimal](11, 2) NOT NULL ,
[GRSRECEXC] [decimal](11, 2) NOT NULL ,
[NETRECINC] [decimal](11, 2) NOT NULL ,
[NETRECEXC] [decimal](11, 2) NOT NULL ,
[GRSTRNINC] [decimal](11, 2) NOT NULL ,
[GRSTRNEXC] [decimal](11, 2) NOT NULL ,
[TRNPER] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RENPER] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[GRSERNINC] [decimal](11, 2) NOT NULL ,
[GRSERNEXC] [decimal](11, 2) NOT NULL ,
[TMSTDT] [decimal](8, 0) NULL ,
[TMEDDT] [decimal](8, 0) NULL ,
[UnPolicykey] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE CLUSTERED INDEX [IX_RIPRMCLM] ON [dbo].[RIPRMCLM]([TRNPER],
[MAJORLINE]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_RIPRMCLM_1] ON [dbo].[RIPRMCLM]([POLCO], [POLPFX]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_RIPRMCLM_2] ON [dbo].[RIPRMCLM]([CLIENTNO]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_RIPRMCLM_3] ON [dbo].[RIPRMCLM]([CLMNUM]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [Idx_polnum] ON [dbo].[RIPRMCLM]([POLNUM]) WITH FILLFACTOR =
90 ON [PRIMARY]
GO

CREATE INDEX [RIPRMCLM3] ON [dbo].[RIPRMCLM]([RICOMPANY], [MAJORLINE],
[MINORLINE], [TRTYNUM], [TRTYYR]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [Idx_Producercd] ON [dbo].[RIPRMCLM]([PRODUCERCD]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

4 Aug 2006 1:00 PM

Robbe Morris [C# MVP]

You'll want to read this to fix your issue.

http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=1181

--
Robbe Morris - 2004-2006 Microsoft MVP C#
Microsoft .NET Search Engine Scoring Analysis
How does your site rate?
http://www.topichound.com

Show quote

"Reg Besseling" <regbes.nospam@hotmail.com> wrote in message
news:oNKdnUYh0Zkisk7ZnZ2dnUVZ_tWdnZ2d@is.co.za...
> Hi all,
> (DDL's etc below)
>
> I hope someone can help with this as I have tried everything I can think
> of and had no luck
>
> I have a table with 11M rows and have created a simple report/query on it.
>
> When I run a "simple" sql on the table I get results in sub second times,
> however when using parameters it takes over half an hour.
>
> After reading about parameter sniffing, I tried putting this into a sp
> using defaults for all parameters no change, I also tried the sp with a
> recompile clause still no change.
>
> I have checked the execution plans and found that the "Fast" query starts
> with a index seek on idx_polnum and an estimated rowcount of 4
>
> on the slow queries the plan shows it is using the clustered index seek
> with the sp estimating 11m rows and the parameter query 1M rows
>
> I have tried setting the default of the @policy parameter to a unique
> value with no change in the sp execution plan
>
> i managed to get barly acceptable (30 seconds ) performance when i add the
> following table hint with (INDEX(idx_polnum,Idx_Producercd))
>
> i do not want to use hints as ive heard it is not recomended , is this the
> only way or am i missing something?
>
> I am using SQL 2000 with sp 3a
>
> Thanks in advance
>
> Reg Besseling
>
> ============================
> here is the "simple" SQL Fast
> ============================
>
>
> SELECT     LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3),
> POLPFX)) + RIGHT('000000' + RTRIM(CONVERT(char, POLNUM)), 7) AS PolicyKey,
>                      POLNUM, PACCTNAME AS Insured, MAX(RENPER) AS
> RenewalMonth, MAJORLINE, SUM(GRSOSINC) AS GOS, SUM(GRSINC) AS GPW,
> SUM(GRSPDINC)
>                      AS GPD, SUM(GRSPDINC + GRSOSINC) AS GLI, AGENTNAME
> FROM         RIPRMCLM
> WHERE   1=1
> AND (30 = 9999999 or POLNUM = 30 )
> AND (TRNPER BETWEEN 0 AND 999999)
> AND (99999 = 99999 OR RIPRMCLM.PRODUCERCD = 99999 )
> AND ( 99 = 99 OR RIPRMCLM.MAJORLINE = 99)
> GROUP BY LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3), POLPFX))
> + RIGHT('000000' + RTRIM(CONVERT(char, POLNUM)), 7), POLNUM,
>                      PACCTNAME, MAJORLINE, AGENTNAME
> ORDER BY MAJORLINE DESC
>
> ==========================
> hers is the query using parameters SLOW!!!!
> ==========================
>
> declare @policy decimal (7,0)
> declare @Start char(6)
> declare @End char(6)
> declare @Agent decimal (7,0)
> declare @Major decimal (7,0)
>
> set @policy = 30
> set @Start = '0'
> set @End = '999999'
> set @Agent = 99999
> set @Major = 99
>
> SELECT     LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3),
> POLPFX)) + RIGHT('000000' + RTRIM(CONVERT(char, POLNUM)), 7) AS PolicyKey,
>                      POLNUM, PACCTNAME AS Insured, MAX(RENPER) AS
> RenewalMonth, MAJORLINE, SUM(GRSOSINC) AS GOS, SUM(GRSINC) AS GPW,
> SUM(GRSPDINC)
>                      AS GPD, SUM(GRSPDINC + GRSOSINC) AS GLI, AGENTNAME
> FROM         RIPRMCLM
> WHERE   1=1
> AND (@policy = 9999999 or POLNUM = @policy )
> AND (TRNPER BETWEEN @Start AND @End)
> AND (@Agent = 99999 OR RIPRMCLM.PRODUCERCD = @Agent )
> AND (@Major = 99 OR RIPRMCLM.MAJORLINE = @Major)
> GROUP BY LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3), POLPFX))
> + RIGHT('000000' + RTRIM(CONVERT(char, POLNUM)), 7), POLNUM,
>                      PACCTNAME, MAJORLINE, AGENTNAME
> ORDER BY MAJORLINE DESC
>
>
> =======================
> in a SP SLOW
> =======================
>
>
>
> create procedure usp_ReportClaimsExpMain
> @policy decimal (7,0) = 30
> , @Start char(6) = '0'
> , @End char(6) = '999999'
> , @Agent decimal (7,0) = 99999
> , @Major decimal (7,0) = 99
>
> with recompile
> as
>
> SELECT     LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3),
> POLPFX)) + RIGHT('000000' + RTRIM(CONVERT(char, POLNUM)), 7) AS PolicyKey,
>                      POLNUM, PACCTNAME AS Insured, MAX(RENPER) AS
> RenewalMonth, MAJORLINE, SUM(GRSOSINC) AS GOS, SUM(GRSINC) AS GPW,
> SUM(GRSPDINC)
>                      AS GPD, SUM(GRSPDINC + GRSOSINC) AS GLI, AGENTNAME
> FROM         RIPRMCLM
> WHERE   1=1
> AND (@policy = 9999999 or POLNUM = @policy )
> AND (TRNPER BETWEEN @Start AND @End)
> AND (@Agent = 99999 OR RIPRMCLM.PRODUCERCD = @Agent )
> AND (@Major = 99 OR RIPRMCLM.MAJORLINE = @Major)
> GROUP BY LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3), POLPFX))
> + RIGHT('000000' + RTRIM(CONVERT(char, POLNUM)), 7), POLNUM,
>                      PACCTNAME, MAJORLINE, AGENTNAME
> ORDER BY MAJORLINE DESC
>
> go
>
>
> exec usp_ReportClaimsExpMain @policy = 30, @Start = '0', @End =
> '999999', @Agent = 99999, @Major = 99
> ==========================
> Table DDL
> ==========================
> CREATE TABLE [RIPRMCLM] (
> [COMPANY] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [RICOMPANY] [decimal](5, 0) NOT NULL ,
> [SOURCECDE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [RIIND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [RILOB] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [TRTYNUM] [decimal](7, 0) NOT NULL ,
> [TRTYYR] [decimal](4, 0) NOT NULL ,
> [POLCO] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [POLPFX] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [POLNUM] [decimal](7, 0) NOT NULL ,
> [LOCNUM] [decimal](3, 0) NOT NULL ,
> [RISKNUM] [decimal](3, 0) NOT NULL ,
> [CLMNUM] [decimal](9, 0) NOT NULL ,
> [RISKTYPE] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [PREMCLAS] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [BATCHYEAR] [decimal](4, 0) NOT NULL ,
> [BATCHMTH] [decimal](2, 0) NOT NULL ,
> [EFFDATE] [decimal](8, 0) NOT NULL ,
> [TRANDATE] [decimal](8, 0) NOT NULL ,
> [PRMEXPDATE] [decimal](8, 0) NOT NULL ,
> [RPTTYPE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [GRSTRANAMT] [decimal](13, 2) NOT NULL ,
> [PREMIUM] [decimal](11, 2) NOT NULL ,
> [COMMISSION] [decimal](10, 2) NOT NULL ,
> [STAMPDUTY] [decimal](10, 2) NOT NULL ,
> [RSTRANTYPE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [RSLOSSTYPE] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [PYEXPTYPE] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [RECOVTYPE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [MAJORLINE] [decimal](2, 0) NOT NULL ,
> [CLASS] [decimal](3, 0) NOT NULL ,
> [MINORLINE] [decimal](2, 0) NOT NULL ,
> [PY] [decimal](4, 0) NOT NULL ,
> [PRODUCERCD] [decimal](7, 0) NOT NULL ,
> [LEADER] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [MSTRPLCY] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [INTPREXAM] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [CLIENTNO] [decimal](7, 0) NOT NULL ,
> [STAMPDTYCD] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [PRODCOMM] [decimal](10, 2) NOT NULL ,
> [OURSHARE] [decimal](7, 4) NOT NULL ,
> [LOSSCAUSE] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [DATEOFLOSS] [decimal](8, 0) NOT NULL ,
> [DATEADVISD] [decimal](8, 0) NOT NULL ,
> [ANATPROPCD] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [INJDAMCODE] [decimal](3, 0) NOT NULL ,
> [THRDPRTY] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [OCCUPATION] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [PERCENTAGE] [decimal](5, 2) NOT NULL ,
> [CLMTRANTYP] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [TRANTIME] [decimal](6, 0) NOT NULL ,
> [USERID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [VATIND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [TRNTYPE] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [PACCTNAME] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [PSTATUS] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [PPOLTERM] [decimal](2, 0) NOT NULL ,
> [PRENEWCODE] [decimal](2, 0) NOT NULL ,
> [PPOLEFFDTE] [decimal](10, 0) NOT NULL ,
> [PPOLEXPDTE] [decimal](10, 0) NOT NULL ,
> [AGENTNAME] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [GRSOSINC] [decimal](11, 2) NOT NULL ,
> [GRSOSEXC] [decimal](11, 2) NOT NULL ,
> [GRSPDINC] [decimal](11, 2) NOT NULL ,
> [GRSPDEXC] [decimal](11, 2) NOT NULL ,
> [GRSINC] [decimal](11, 2) NOT NULL ,
> [GRSEXC] [decimal](11, 2) NOT NULL ,
> [NETOSINC] [decimal](11, 2) NOT NULL ,
> [NETOSEXC] [decimal](11, 2) NOT NULL ,
> [NETPDINC] [decimal](11, 2) NOT NULL ,
> [NETPDEXC] [decimal](11, 2) NOT NULL ,
> [NETINC] [decimal](11, 2) NOT NULL ,
> [NETEXC] [decimal](11, 2) NOT NULL ,
> [GRSRECINC] [decimal](11, 2) NOT NULL ,
> [GRSRECEXC] [decimal](11, 2) NOT NULL ,
> [NETRECINC] [decimal](11, 2) NOT NULL ,
> [NETRECEXC] [decimal](11, 2) NOT NULL ,
> [GRSTRNINC] [decimal](11, 2) NOT NULL ,
> [GRSTRNEXC] [decimal](11, 2) NOT NULL ,
> [TRNPER] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [RENPER] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [GRSERNINC] [decimal](11, 2) NOT NULL ,
> [GRSERNEXC] [decimal](11, 2) NOT NULL ,
> [TMSTDT] [decimal](8, 0) NULL ,
> [TMEDDT] [decimal](8, 0) NULL ,
> [UnPolicykey] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
>
> CREATE CLUSTERED INDEX [IX_RIPRMCLM] ON [dbo].[RIPRMCLM]([TRNPER],
> [MAJORLINE]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
>
> CREATE INDEX [IX_RIPRMCLM_1] ON [dbo].[RIPRMCLM]([POLCO], [POLPFX]) WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
>
> CREATE INDEX [IX_RIPRMCLM_2] ON [dbo].[RIPRMCLM]([CLIENTNO]) WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
>
> CREATE INDEX [IX_RIPRMCLM_3] ON [dbo].[RIPRMCLM]([CLMNUM]) WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
>
> CREATE INDEX [Idx_polnum] ON [dbo].[RIPRMCLM]([POLNUM]) WITH FILLFACTOR
> = 90 ON [PRIMARY]
> GO
>
> CREATE INDEX [RIPRMCLM3] ON [dbo].[RIPRMCLM]([RICOMPANY], [MAJORLINE],
> [MINORLINE], [TRTYNUM], [TRTYYR]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
>
> CREATE INDEX [Idx_Producercd] ON [dbo].[RIPRMCLM]([PRODUCERCD]) WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
>
>
>

7 Aug 2006 11:25 AM

Reg Besseling

Thanks for the suggestion Robbie

I tried the below and it was still slow. I stopped the execution after 5
minutes the query plan changed from the 11M rows to the 1M of the parameter
query but no other change.

I did read the info you pointed to on parameter sniffing and if i read it
right the "best mentod" to use is the assign typical values the the
parameters as a default and i should get a efficent query plan from that but
not in this case.

once i used dynamic SQL (using replace to fill in the values of the
parameters) as suggested by Steve Kas it speeded up

Thanks for the help

Reg.

create procedure usp_ReportClaimsExpMain
@policyO decimal (7,0) = 30
, @StartO char(6) = '0'
, @EndO char(6) = '999999'
, @AgentO decimal (7,0) = 99999
, @MajorO decimal (7,0) = 99

with recompile
as

declare @policy decimal (7,0)
declare @Start char(6)
declare @End char(6)
declare @Agent decimal (7,0)
declare @Major decimal (7,0)

set @policy = @policyO
set @Start = @StartO
set @End = @EndO
set @Agent = @AgentO
set @Major = @MajorO

SELECT     LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3), POLPFX))
+ RIGHT('000000' + RTRIM(CONVERT(char, POLNUM)), 7) AS PolicyKey,
                      POLNUM, PACCTNAME AS Insured, MAX(RENPER) AS
RenewalMonth, MAJORLINE, SUM(GRSOSINC) AS GOS, SUM(GRSINC) AS GPW,
SUM(GRSPDINC)
                      AS GPD, SUM(GRSPDINC + GRSOSINC) AS GLI, AGENTNAME
FROM         RIPRMCLM
WHERE   1=1
AND (@policy = 9999999 or POLNUM = @policy )
AND (TRNPER BETWEEN @Start AND @End)
AND (@Agent = 99999 OR RIPRMCLM.PRODUCERCD = @Agent )
AND (@Major = 99 OR RIPRMCLM.MAJORLINE = @Major)
GROUP BY LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3), POLPFX)) +
RIGHT('000000' + RTRIM(CONVERT(char, POLNUM)), 7), POLNUM,
                      PACCTNAME, MAJORLINE, AGENTNAME
ORDER BY MAJORLINE DESC

go

exec usp_ReportClaimsExpMain @policyO = 30, @StartO = '0', @EndO =
'999999',
@AgentO = 99999, @MajorO = 99
Show quote

"Robbe Morris [C# MVP]" <i***@eggheadcafe.com> wrote in message
news:%2392MwV8tGHA.4872@TK2MSFTNGP02.phx.gbl...
> You'll want to read this to fix your issue.
>
> http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=1181
>
> --
> Robbe Morris - 2004-2006 Microsoft MVP C#
> Microsoft .NET Search Engine Scoring Analysis
> How does your site rate?
> http://www.topichound.com
>
>
>
>
> "Reg Besseling" <regbes.nospam@hotmail.com> wrote in message
> news:oNKdnUYh0Zkisk7ZnZ2dnUVZ_tWdnZ2d@is.co.za...
>> Hi all,
>> (DDL's etc below)
>>
>> I hope someone can help with this as I have tried everything I can think
>> of and had no luck
>>
>> I have a table with 11M rows and have created a simple report/query on
>> it.
>>
>> When I run a "simple" sql on the table I get results in sub second times,
>> however when using parameters it takes over half an hour.
>>
>> After reading about parameter sniffing, I tried putting this into a sp
>> using defaults for all parameters no change, I also tried the sp with a
>> recompile clause still no change.
>>
>> I have checked the execution plans and found that the "Fast" query starts
>> with a index seek on idx_polnum and an estimated rowcount of 4
>>
>> on the slow queries the plan shows it is using the clustered index seek
>> with the sp estimating 11m rows and the parameter query 1M rows
>>
>> I have tried setting the default of the @policy parameter to a unique
>> value with no change in the sp execution plan
>>
>> i managed to get barly acceptable (30 seconds ) performance when i add
>> the following table hint with (INDEX(idx_polnum,Idx_Producercd))
>>
>> i do not want to use hints as ive heard it is not recomended , is this
>> the only way or am i missing something?
>>
>> I am using SQL 2000 with sp 3a
>>
>> Thanks in advance
>>
>> Reg Besseling
>>
>> ============================
>> here is the "simple" SQL Fast
>> ============================
>>
>>
>> SELECT     LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3),
>> POLPFX)) + RIGHT('000000' + RTRIM(CONVERT(char, POLNUM)), 7) AS
>> PolicyKey,
>>                      POLNUM, PACCTNAME AS Insured, MAX(RENPER) AS
>> RenewalMonth, MAJORLINE, SUM(GRSOSINC) AS GOS, SUM(GRSINC) AS GPW,
>> SUM(GRSPDINC)
>>                      AS GPD, SUM(GRSPDINC + GRSOSINC) AS GLI, AGENTNAME
>> FROM         RIPRMCLM
>> WHERE   1=1
>> AND (30 = 9999999 or POLNUM = 30 )
>> AND (TRNPER BETWEEN 0 AND 999999)
>> AND (99999 = 99999 OR RIPRMCLM.PRODUCERCD = 99999 )
>> AND ( 99 = 99 OR RIPRMCLM.MAJORLINE = 99)
>> GROUP BY LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3), POLPFX))
>> + RIGHT('000000' + RTRIM(CONVERT(char, POLNUM)), 7), POLNUM,
>>                      PACCTNAME, MAJORLINE, AGENTNAME
>> ORDER BY MAJORLINE DESC
>>
>> ==========================
>> hers is the query using parameters SLOW!!!!
>> ==========================
>>
>> declare @policy decimal (7,0)
>> declare @Start char(6)
>> declare @End char(6)
>> declare @Agent decimal (7,0)
>> declare @Major decimal (7,0)
>>
>> set @policy = 30
>> set @Start = '0'
>> set @End = '999999'
>> set @Agent = 99999
>> set @Major = 99
>>
>> SELECT     LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3),
>> POLPFX)) + RIGHT('000000' + RTRIM(CONVERT(char, POLNUM)), 7) AS
>> PolicyKey,
>>                      POLNUM, PACCTNAME AS Insured, MAX(RENPER) AS
>> RenewalMonth, MAJORLINE, SUM(GRSOSINC) AS GOS, SUM(GRSINC) AS GPW,
>> SUM(GRSPDINC)
>>                      AS GPD, SUM(GRSPDINC + GRSOSINC) AS GLI, AGENTNAME
>> FROM         RIPRMCLM
>> WHERE   1=1
>> AND (@policy = 9999999 or POLNUM = @policy )
>> AND (TRNPER BETWEEN @Start AND @End)
>> AND (@Agent = 99999 OR RIPRMCLM.PRODUCERCD = @Agent )
>> AND (@Major = 99 OR RIPRMCLM.MAJORLINE = @Major)
>> GROUP BY LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3), POLPFX))
>> + RIGHT('000000' + RTRIM(CONVERT(char, POLNUM)), 7), POLNUM,
>>                      PACCTNAME, MAJORLINE, AGENTNAME
>> ORDER BY MAJORLINE DESC
>>
>>
>> =======================
>> in a SP SLOW
>> =======================
>>
>>
>>
>> create procedure usp_ReportClaimsExpMain
>> @policy decimal (7,0) = 30
>> , @Start char(6) = '0'
>> , @End char(6) = '999999'
>> , @Agent decimal (7,0) = 99999
>> , @Major decimal (7,0) = 99
>>
>> with recompile
>> as
>>
>> SELECT     LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3),
>> POLPFX)) + RIGHT('000000' + RTRIM(CONVERT(char, POLNUM)), 7) AS
>> PolicyKey,
>>                      POLNUM, PACCTNAME AS Insured, MAX(RENPER) AS
>> RenewalMonth, MAJORLINE, SUM(GRSOSINC) AS GOS, SUM(GRSINC) AS GPW,
>> SUM(GRSPDINC)
>>                      AS GPD, SUM(GRSPDINC + GRSOSINC) AS GLI, AGENTNAME
>> FROM         RIPRMCLM
>> WHERE   1=1
>> AND (@policy = 9999999 or POLNUM = @policy )
>> AND (TRNPER BETWEEN @Start AND @End)
>> AND (@Agent = 99999 OR RIPRMCLM.PRODUCERCD = @Agent )
>> AND (@Major = 99 OR RIPRMCLM.MAJORLINE = @Major)
>> GROUP BY LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3), POLPFX))
>> + RIGHT('000000' + RTRIM(CONVERT(char, POLNUM)), 7), POLNUM,
>>                      PACCTNAME, MAJORLINE, AGENTNAME
>> ORDER BY MAJORLINE DESC
>>
>> go
>>
>>
>> exec usp_ReportClaimsExpMain @policy = 30, @Start = '0', @End =
>> '999999', @Agent = 99999, @Major = 99
>> ==========================
>> Table DDL
>> ==========================
>> CREATE TABLE [RIPRMCLM] (
>> [COMPANY] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [RICOMPANY] [decimal](5, 0) NOT NULL ,
>> [SOURCECDE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [RIIND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [RILOB] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [TRTYNUM] [decimal](7, 0) NOT NULL ,
>> [TRTYYR] [decimal](4, 0) NOT NULL ,
>> [POLCO] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [POLPFX] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [POLNUM] [decimal](7, 0) NOT NULL ,
>> [LOCNUM] [decimal](3, 0) NOT NULL ,
>> [RISKNUM] [decimal](3, 0) NOT NULL ,
>> [CLMNUM] [decimal](9, 0) NOT NULL ,
>> [RISKTYPE] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [PREMCLAS] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [BATCHYEAR] [decimal](4, 0) NOT NULL ,
>> [BATCHMTH] [decimal](2, 0) NOT NULL ,
>> [EFFDATE] [decimal](8, 0) NOT NULL ,
>> [TRANDATE] [decimal](8, 0) NOT NULL ,
>> [PRMEXPDATE] [decimal](8, 0) NOT NULL ,
>> [RPTTYPE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [GRSTRANAMT] [decimal](13, 2) NOT NULL ,
>> [PREMIUM] [decimal](11, 2) NOT NULL ,
>> [COMMISSION] [decimal](10, 2) NOT NULL ,
>> [STAMPDUTY] [decimal](10, 2) NOT NULL ,
>> [RSTRANTYPE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [RSLOSSTYPE] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [PYEXPTYPE] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [RECOVTYPE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [MAJORLINE] [decimal](2, 0) NOT NULL ,
>> [CLASS] [decimal](3, 0) NOT NULL ,
>> [MINORLINE] [decimal](2, 0) NOT NULL ,
>> [PY] [decimal](4, 0) NOT NULL ,
>> [PRODUCERCD] [decimal](7, 0) NOT NULL ,
>> [LEADER] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [MSTRPLCY] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [INTPREXAM] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [CLIENTNO] [decimal](7, 0) NOT NULL ,
>> [STAMPDTYCD] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [PRODCOMM] [decimal](10, 2) NOT NULL ,
>> [OURSHARE] [decimal](7, 4) NOT NULL ,
>> [LOSSCAUSE] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [DATEOFLOSS] [decimal](8, 0) NOT NULL ,
>> [DATEADVISD] [decimal](8, 0) NOT NULL ,
>> [ANATPROPCD] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [INJDAMCODE] [decimal](3, 0) NOT NULL ,
>> [THRDPRTY] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [OCCUPATION] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [PERCENTAGE] [decimal](5, 2) NOT NULL ,
>> [CLMTRANTYP] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [TRANTIME] [decimal](6, 0) NOT NULL ,
>> [USERID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [VATIND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [TRNTYPE] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [PACCTNAME] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [PSTATUS] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [PPOLTERM] [decimal](2, 0) NOT NULL ,
>> [PRENEWCODE] [decimal](2, 0) NOT NULL ,
>> [PPOLEFFDTE] [decimal](10, 0) NOT NULL ,
>> [PPOLEXPDTE] [decimal](10, 0) NOT NULL ,
>> [AGENTNAME] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [GRSOSINC] [decimal](11, 2) NOT NULL ,
>> [GRSOSEXC] [decimal](11, 2) NOT NULL ,
>> [GRSPDINC] [decimal](11, 2) NOT NULL ,
>> [GRSPDEXC] [decimal](11, 2) NOT NULL ,
>> [GRSINC] [decimal](11, 2) NOT NULL ,
>> [GRSEXC] [decimal](11, 2) NOT NULL ,
>> [NETOSINC] [decimal](11, 2) NOT NULL ,
>> [NETOSEXC] [decimal](11, 2) NOT NULL ,
>> [NETPDINC] [decimal](11, 2) NOT NULL ,
>> [NETPDEXC] [decimal](11, 2) NOT NULL ,
>> [NETINC] [decimal](11, 2) NOT NULL ,
>> [NETEXC] [decimal](11, 2) NOT NULL ,
>> [GRSRECINC] [decimal](11, 2) NOT NULL ,
>> [GRSRECEXC] [decimal](11, 2) NOT NULL ,
>> [NETRECINC] [decimal](11, 2) NOT NULL ,
>> [NETRECEXC] [decimal](11, 2) NOT NULL ,
>> [GRSTRNINC] [decimal](11, 2) NOT NULL ,
>> [GRSTRNEXC] [decimal](11, 2) NOT NULL ,
>> [TRNPER] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [RENPER] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [GRSERNINC] [decimal](11, 2) NOT NULL ,
>> [GRSERNEXC] [decimal](11, 2) NOT NULL ,
>> [TMSTDT] [decimal](8, 0) NULL ,
>> [TMEDDT] [decimal](8, 0) NULL ,
>> [UnPolicykey] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>> ) ON [PRIMARY]
>> GO
>>
>>
>> CREATE CLUSTERED INDEX [IX_RIPRMCLM] ON [dbo].[RIPRMCLM]([TRNPER],
>> [MAJORLINE]) WITH FILLFACTOR = 90 ON [PRIMARY]
>> GO
>>
>> CREATE INDEX [IX_RIPRMCLM_1] ON [dbo].[RIPRMCLM]([POLCO], [POLPFX]) WITH
>> FILLFACTOR = 90 ON [PRIMARY]
>> GO
>>
>> CREATE INDEX [IX_RIPRMCLM_2] ON [dbo].[RIPRMCLM]([CLIENTNO]) WITH
>> FILLFACTOR = 90 ON [PRIMARY]
>> GO
>>
>> CREATE INDEX [IX_RIPRMCLM_3] ON [dbo].[RIPRMCLM]([CLMNUM]) WITH
>> FILLFACTOR = 90 ON [PRIMARY]
>> GO
>>
>> CREATE INDEX [Idx_polnum] ON [dbo].[RIPRMCLM]([POLNUM]) WITH FILLFACTOR
>> = 90 ON [PRIMARY]
>> GO
>>
>> CREATE INDEX [RIPRMCLM3] ON [dbo].[RIPRMCLM]([RICOMPANY], [MAJORLINE],
>> [MINORLINE], [TRTYNUM], [TRTYYR]) WITH FILLFACTOR = 90 ON [PRIMARY]
>> GO
>>
>> CREATE INDEX [Idx_Producercd] ON [dbo].[RIPRMCLM]([PRODUCERCD]) WITH
>> FILLFACTOR = 90 ON [PRIMARY]
>> GO
>>
>>
>>
>
>

4 Aug 2006 1:14 PM

Steve Kass

Reg,

You've looked at all the right things, and there is no easy
answer to this problem - writing an all-purpose search
procedure. It's a tough one. Here's a bit of explanation
of the problem, then some things to try (One of the
options below, #3 will probably work, though it
may be the one to try last, in case anything else
works well enough.)

By the way, some options may improve the
situation when @policy <> 9999999, but if you
end up forcing a nested loop against an index
seek when @policy = 9999999, you're dead,
so be sure to test that situation also.

The problem is when changing
30 = 9999999 or POLNUM = 30
to @policy = 9999999 or POLNUM = @policy

The query processor can no longer remove the false
expression 30 = 9999999 right away and get a
good rowcount estimate for POLNUM = 30. With
parameters, the processor doesn't know that the filter will
match a single POLNUM value, and it uses a generic
(too high as you see) rowcount estimate for the OR
expression.

Recompiling won't help here, I don't think.

There is always a trade off between generality (one sp
that works for selecting one policy or all policies, with
many search options) and efficiency and maintainability.

If you have to provide a single stored procedure that
can do all these different searches (which require very
different query plans), your options probably are:

1. Use RECOMPILE along with a few IF statements:

IF @policy = 30
SELECT ...
WHERE POLNUM = @policy
ELSE
SELECT ...
<no condition on POLNUM>

You may need just a couple of nested IFs on the one or two
most crucial columns (probably the indexed ones). If you are
using SQL Server 2005, you

2. Use temporary tables, if this doesn't have to have perfect
concurrency. (Hard to say if this will work, but it might be
worth a try and avoid the nested IFs.)

CREATE TABLE #Policies(
POLNUM DECIMAL(7,0)
)
IF @policy = 30
INSERT INTO #Policies
SELECT POLNUM FROM PolicyTable
-- use the table where POLNUM is the key
ELSE
INSERT INTO #Policies
   SELECT POLNUM FROM PolicyTable
   WHERE POLNUM = @policy

... same for the other columns

SELECT joining with these temporary tables instead of
filtering by parameters.

3. Create the entire query dynamically:

declare @sql nvarchar(2000)
set @sql = 'SELECT     LTRIM(CONVERT(char(2), POLCO)) +
RTRIM(CONVERT(char(3), POLPFX)) + RIGHT('000000' + RTRIM(CONVERT(char,
POLNUM)), 7) AS PolicyKey,
                     POLNUM, PACCTNAME AS Insured, MAX(RENPER) AS
RenewalMonth, MAJORLINE, SUM(GRSOSINC) AS GOS, SUM(GRSINC) AS GPW,
SUM(GRSPDINC)
                     AS GPD, SUM(GRSPDINC + GRSOSINC) AS GLI, AGENTNAME
FROM         RIPRMCLM
WHERE   1=1
AND (@policy = 9999999 or POLNUM = @policy )
AND (TRNPER BETWEEN @Start AND @End)
AND (@Agent = 99999 OR RIPRMCLM.PRODUCERCD = @Agent )
AND (@Major = 99 OR RIPRMCLM.MAJORLINE = @Major)
GROUP BY LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3),
POLPFX)) + RIGHT('000000' + RTRIM(CONVERT(char, POLNUM)), 7), POLNUM,
                     PACCTNAME, MAJORLINE, AGENTNAME
ORDER BY MAJORLINE DESC '

set @sql = replace(@sql,'@policy',@policy)
set @sql = replace(@sql,'@Start',@Start)
....
If any of the parameters are strings, do it this way (important to avoid
SQL injection)
set @sql = replace(@sql,'@strprm',char(27) +
replace(@strprm,char(27),char(27)+char(27))+char(27))

exec(@sql)

This will probably work the best, but is harder to maintain. (There are
other issues like filling up the procedure cache with ad hoc query plans
that could bite you, too). Be sure no parameter names are prefixes of
other parameter names, or if so, be careful about the replacement order.

4. Think hard about how this procedure is used. If searches on a
specific policy number are the most common and must be fast, and none of
the above are good solutions, write a separate procedure for that case.

-- Steve Kass
-- Drew University
-- http://www.stevekass.com
-- 6AE9794F-3A18-4110-B896-C7893BB606D3

Reg Besseling wrote:

Show quote

> Hi all,
> (DDL's etc below)
>
> I hope someone can help with this as I have tried everything I can
> think of and had no luck
>
> I have a table with 11M rows and have created a simple report/query on
> it.
>
> When I run a "simple" sql on the table I get results in sub second
> times, however when using parameters it takes over half an hour.
>
> After reading about parameter sniffing, I tried putting this into a
> sp using defaults for all parameters no change, I also tried the sp
> with a recompile clause still no change.
>
> I have checked the execution plans and found that the "Fast" query
> starts with a index seek on idx_polnum and an estimated rowcount of 4
>
> on the slow queries the plan shows it is using the clustered index
> seek with the sp estimating 11m rows and the parameter query 1M rows
>
> I have tried setting the default of the @policy parameter to a unique
> value with no change in the sp execution plan
>
> i managed to get barly acceptable (30 seconds ) performance when i add
> the following table hint with (INDEX(idx_polnum,Idx_Producercd))
>
> i do not want to use hints as ive heard it is not recomended , is this
> the only way or am i missing something?
>
> I am using SQL 2000 with sp 3a
>
> Thanks in advance
>
> Reg Besseling
>
> ============================
> here is the "simple" SQL Fast
> ============================
>
>
> SELECT     LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3),
> POLPFX)) + RIGHT('000000' + RTRIM(CONVERT(char, POLNUM)), 7) AS
> PolicyKey,
>                      POLNUM, PACCTNAME AS Insured, MAX(RENPER) AS
> RenewalMonth, MAJORLINE, SUM(GRSOSINC) AS GOS, SUM(GRSINC) AS GPW,
> SUM(GRSPDINC)
>                      AS GPD, SUM(GRSPDINC + GRSOSINC) AS GLI, AGENTNAME
> FROM         RIPRMCLM
> WHERE   1=1
> AND (30 = 9999999 or POLNUM = 30 )
> AND (TRNPER BETWEEN 0 AND 999999)
> AND (99999 = 99999 OR RIPRMCLM.PRODUCERCD = 99999 )
> AND ( 99 = 99 OR RIPRMCLM.MAJORLINE = 99)
> GROUP BY LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3),
> POLPFX)) + RIGHT('000000' + RTRIM(CONVERT(char, POLNUM)), 7), POLNUM,
>                      PACCTNAME, MAJORLINE, AGENTNAME
> ORDER BY MAJORLINE DESC
>
> ==========================
> hers is the query using parameters SLOW!!!!
> ==========================
>
> declare @policy decimal (7,0)
> declare @Start char(6)
> declare @End char(6)
> declare @Agent decimal (7,0)
> declare @Major decimal (7,0)
>
> set @policy = 30
> set @Start = '0'
> set @End = '999999'
> set @Agent = 99999
> set @Major = 99
>
> SELECT     LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3),
> POLPFX)) + RIGHT('000000' + RTRIM(CONVERT(char, POLNUM)), 7) AS
> PolicyKey,
>                      POLNUM, PACCTNAME AS Insured, MAX(RENPER) AS
> RenewalMonth, MAJORLINE, SUM(GRSOSINC) AS GOS, SUM(GRSINC) AS GPW,
> SUM(GRSPDINC)
>                      AS GPD, SUM(GRSPDINC + GRSOSINC) AS GLI, AGENTNAME
> FROM         RIPRMCLM
> WHERE   1=1
> AND (@policy = 9999999 or POLNUM = @policy )
> AND (TRNPER BETWEEN @Start AND @End)
> AND (@Agent = 99999 OR RIPRMCLM.PRODUCERCD = @Agent )
> AND (@Major = 99 OR RIPRMCLM.MAJORLINE = @Major)
> GROUP BY LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3),
> POLPFX)) + RIGHT('000000' + RTRIM(CONVERT(char, POLNUM)), 7), POLNUM,
>                      PACCTNAME, MAJORLINE, AGENTNAME
> ORDER BY MAJORLINE DESC
>
>
> =======================
> in a SP SLOW
> =======================
>
>
>
> create procedure usp_ReportClaimsExpMain
> @policy decimal (7,0) = 30
> , @Start char(6) = '0'
> , @End char(6) = '999999'
> , @Agent decimal (7,0) = 99999
> , @Major decimal (7,0) = 99
>
> with recompile
> as
>
> SELECT     LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3),
> POLPFX)) + RIGHT('000000' + RTRIM(CONVERT(char, POLNUM)), 7) AS
> PolicyKey,
>                      POLNUM, PACCTNAME AS Insured, MAX(RENPER) AS
> RenewalMonth, MAJORLINE, SUM(GRSOSINC) AS GOS, SUM(GRSINC) AS GPW,
> SUM(GRSPDINC)
>                      AS GPD, SUM(GRSPDINC + GRSOSINC) AS GLI, AGENTNAME
> FROM         RIPRMCLM
> WHERE   1=1
> AND (@policy = 9999999 or POLNUM = @policy )
> AND (TRNPER BETWEEN @Start AND @End)
> AND (@Agent = 99999 OR RIPRMCLM.PRODUCERCD = @Agent )
> AND (@Major = 99 OR RIPRMCLM.MAJORLINE = @Major)
> GROUP BY LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3),
> POLPFX)) + RIGHT('000000' + RTRIM(CONVERT(char, POLNUM)), 7), POLNUM,
>                      PACCTNAME, MAJORLINE, AGENTNAME
> ORDER BY MAJORLINE DESC
>
> go
>
>
> exec usp_ReportClaimsExpMain @policy = 30, @Start = '0', @End =
> '999999', @Agent = 99999, @Major = 99
> ==========================
> Table DDL
> ==========================
> CREATE TABLE [RIPRMCLM] (
> [COMPANY] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [RICOMPANY] [decimal](5, 0) NOT NULL ,
> [SOURCECDE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [RIIND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [RILOB] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [TRTYNUM] [decimal](7, 0) NOT NULL ,
> [TRTYYR] [decimal](4, 0) NOT NULL ,
> [POLCO] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [POLPFX] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [POLNUM] [decimal](7, 0) NOT NULL ,
> [LOCNUM] [decimal](3, 0) NOT NULL ,
> [RISKNUM] [decimal](3, 0) NOT NULL ,
> [CLMNUM] [decimal](9, 0) NOT NULL ,
> [RISKTYPE] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [PREMCLAS] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [BATCHYEAR] [decimal](4, 0) NOT NULL ,
> [BATCHMTH] [decimal](2, 0) NOT NULL ,
> [EFFDATE] [decimal](8, 0) NOT NULL ,
> [TRANDATE] [decimal](8, 0) NOT NULL ,
> [PRMEXPDATE] [decimal](8, 0) NOT NULL ,
> [RPTTYPE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [GRSTRANAMT] [decimal](13, 2) NOT NULL ,
> [PREMIUM] [decimal](11, 2) NOT NULL ,
> [COMMISSION] [decimal](10, 2) NOT NULL ,
> [STAMPDUTY] [decimal](10, 2) NOT NULL ,
> [RSTRANTYPE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [RSLOSSTYPE] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [PYEXPTYPE] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [RECOVTYPE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [MAJORLINE] [decimal](2, 0) NOT NULL ,
> [CLASS] [decimal](3, 0) NOT NULL ,
> [MINORLINE] [decimal](2, 0) NOT NULL ,
> [PY] [decimal](4, 0) NOT NULL ,
> [PRODUCERCD] [decimal](7, 0) NOT NULL ,
> [LEADER] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [MSTRPLCY] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [INTPREXAM] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [CLIENTNO] [decimal](7, 0) NOT NULL ,
> [STAMPDTYCD] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [PRODCOMM] [decimal](10, 2) NOT NULL ,
> [OURSHARE] [decimal](7, 4) NOT NULL ,
> [LOSSCAUSE] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [DATEOFLOSS] [decimal](8, 0) NOT NULL ,
> [DATEADVISD] [decimal](8, 0) NOT NULL ,
> [ANATPROPCD] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [INJDAMCODE] [decimal](3, 0) NOT NULL ,
> [THRDPRTY] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [OCCUPATION] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [PERCENTAGE] [decimal](5, 2) NOT NULL ,
> [CLMTRANTYP] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [TRANTIME] [decimal](6, 0) NOT NULL ,
> [USERID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [VATIND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [TRNTYPE] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [PACCTNAME] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [PSTATUS] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [PPOLTERM] [decimal](2, 0) NOT NULL ,
> [PRENEWCODE] [decimal](2, 0) NOT NULL ,
> [PPOLEFFDTE] [decimal](10, 0) NOT NULL ,
> [PPOLEXPDTE] [decimal](10, 0) NOT NULL ,
> [AGENTNAME] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [GRSOSINC] [decimal](11, 2) NOT NULL ,
> [GRSOSEXC] [decimal](11, 2) NOT NULL ,
> [GRSPDINC] [decimal](11, 2) NOT NULL ,
> [GRSPDEXC] [decimal](11, 2) NOT NULL ,
> [GRSINC] [decimal](11, 2) NOT NULL ,
> [GRSEXC] [decimal](11, 2) NOT NULL ,
> [NETOSINC] [decimal](11, 2) NOT NULL ,
> [NETOSEXC] [decimal](11, 2) NOT NULL ,
> [NETPDINC] [decimal](11, 2) NOT NULL ,
> [NETPDEXC] [decimal](11, 2) NOT NULL ,
> [NETINC] [decimal](11, 2) NOT NULL ,
> [NETEXC] [decimal](11, 2) NOT NULL ,
> [GRSRECINC] [decimal](11, 2) NOT NULL ,
> [GRSRECEXC] [decimal](11, 2) NOT NULL ,
> [NETRECINC] [decimal](11, 2) NOT NULL ,
> [NETRECEXC] [decimal](11, 2) NOT NULL ,
> [GRSTRNINC] [decimal](11, 2) NOT NULL ,
> [GRSTRNEXC] [decimal](11, 2) NOT NULL ,
> [TRNPER] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [RENPER] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [GRSERNINC] [decimal](11, 2) NOT NULL ,
> [GRSERNEXC] [decimal](11, 2) NOT NULL ,
> [TMSTDT] [decimal](8, 0) NULL ,
> [TMEDDT] [decimal](8, 0) NULL ,
> [UnPolicykey] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
>
> CREATE CLUSTERED INDEX [IX_RIPRMCLM] ON [dbo].[RIPRMCLM]([TRNPER],
> [MAJORLINE]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
>
> CREATE INDEX [IX_RIPRMCLM_1] ON [dbo].[RIPRMCLM]([POLCO], [POLPFX])
> WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
>
> CREATE INDEX [IX_RIPRMCLM_2] ON [dbo].[RIPRMCLM]([CLIENTNO]) WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
>
> CREATE INDEX [IX_RIPRMCLM_3] ON [dbo].[RIPRMCLM]([CLMNUM]) WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
>
> CREATE INDEX [Idx_polnum] ON [dbo].[RIPRMCLM]([POLNUM]) WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
>
> CREATE INDEX [RIPRMCLM3] ON [dbo].[RIPRMCLM]([RICOMPANY],
> [MAJORLINE], [MINORLINE], [TRTYNUM], [TRTYYR]) WITH FILLFACTOR = 90
> ON [PRIMARY]
> GO
>
> CREATE INDEX [Idx_Producercd] ON [dbo].[RIPRMCLM]([PRODUCERCD]) WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
>
>
>

7 Aug 2006 11:27 AM

Reg Besseling

Thanks Steve for the time taken to send the detailed explanation and
solutions

As you predicted option 3 worked great, a bit of a surprise for me as when i
tried

exec sp_executesql N'SELECT...Major int', @policy = 30, @Start = N'0', @End
= N'999999', @Agent = 99999, @Major = 99
it was slow but when i tried your replace trick and left off the parameters
it was as fast as expected.

Thanks Again

Reg.

Show quote

"Steve Kass" <sk***@drew.edu> wrote in message
news:O$W$kf8tGHA.3392@TK2MSFTNGP04.phx.gbl...
> Reg,
>
> You've looked at all the right things, and there is no easy
> answer to this problem - writing an all-purpose search
> procedure. It's a tough one. Here's a bit of explanation
> of the problem, then some things to try (One of the
> options below, #3 will probably work, though it
> may be the one to try last, in case anything else
> works well enough.)
>
> By the way, some options may improve the
> situation when @policy <> 9999999, but if you
> end up forcing a nested loop against an index
> seek when @policy = 9999999, you're dead,
> so be sure to test that situation also.
>
> The problem is when changing
> 30 = 9999999 or POLNUM = 30
> to @policy = 9999999 or POLNUM = @policy
>
> The query processor can no longer remove the false
> expression 30 = 9999999 right away and get a
> good rowcount estimate for POLNUM = 30. With
> parameters, the processor doesn't know that the filter will
> match a single POLNUM value, and it uses a generic
> (too high as you see) rowcount estimate for the OR
> expression.
>
> Recompiling won't help here, I don't think.
>
> There is always a trade off between generality (one sp
> that works for selecting one policy or all policies, with
> many search options) and efficiency and maintainability.
>
> If you have to provide a single stored procedure that
> can do all these different searches (which require very
> different query plans), your options probably are:
>
> 1. Use RECOMPILE along with a few IF statements:
>
> IF @policy = 30
> SELECT ...
> WHERE POLNUM = @policy
> ELSE
> SELECT ...
> <no condition on POLNUM>
>
> You may need just a couple of nested IFs on the one or two
> most crucial columns (probably the indexed ones). If you are
> using SQL Server 2005, you
>
> 2. Use temporary tables, if this doesn't have to have perfect
> concurrency. (Hard to say if this will work, but it might be
> worth a try and avoid the nested IFs.)
>
> CREATE TABLE #Policies(
> POLNUM DECIMAL(7,0)
> )
> IF @policy = 30
> INSERT INTO #Policies
> SELECT POLNUM FROM PolicyTable
> -- use the table where POLNUM is the key
> ELSE
> INSERT INTO #Policies
>   SELECT POLNUM FROM PolicyTable
>   WHERE POLNUM = @policy
>
> .. same for the other columns
>
> SELECT joining with these temporary tables instead of
> filtering by parameters.
>
> 3. Create the entire query dynamically:
>
> declare @sql nvarchar(2000)
> set @sql = 'SELECT     LTRIM(CONVERT(char(2), POLCO)) +
> RTRIM(CONVERT(char(3), POLPFX)) + RIGHT('000000' + RTRIM(CONVERT(char,
> POLNUM)), 7) AS PolicyKey,
>                     POLNUM, PACCTNAME AS Insured, MAX(RENPER) AS
> RenewalMonth, MAJORLINE, SUM(GRSOSINC) AS GOS, SUM(GRSINC) AS GPW,
> SUM(GRSPDINC)
>                     AS GPD, SUM(GRSPDINC + GRSOSINC) AS GLI, AGENTNAME
> FROM         RIPRMCLM
> WHERE   1=1
> AND (@policy = 9999999 or POLNUM = @policy )
> AND (TRNPER BETWEEN @Start AND @End)
> AND (@Agent = 99999 OR RIPRMCLM.PRODUCERCD = @Agent )
> AND (@Major = 99 OR RIPRMCLM.MAJORLINE = @Major)
> GROUP BY LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3), POLPFX))
> + RIGHT('000000' + RTRIM(CONVERT(char, POLNUM)), 7), POLNUM,
>                     PACCTNAME, MAJORLINE, AGENTNAME
> ORDER BY MAJORLINE DESC '
>
> set @sql = replace(@sql,'@policy',@policy)
> set @sql = replace(@sql,'@Start',@Start)
> ...
> If any of the parameters are strings, do it this way (important to avoid
> SQL injection)
> set @sql = replace(@sql,'@strprm',char(27) +
> replace(@strprm,char(27),char(27)+char(27))+char(27))
>
> exec(@sql)
>
> This will probably work the best, but is harder to maintain. (There are
> other issues like filling up the procedure cache with ad hoc query plans
> that could bite you, too). Be sure no parameter names are prefixes of
> other parameter names, or if so, be careful about the replacement order.
>
> 4. Think hard about how this procedure is used. If searches on a specific
> policy number are the most common and must be fast, and none of the above
> are good solutions, write a separate procedure for that case.
>
> -- Steve Kass
> -- Drew University
> -- http://www.stevekass.com
> -- 6AE9794F-3A18-4110-B896-C7893BB606D3
>
> Reg Besseling wrote:
>
>> Hi all,
>> (DDL's etc below)
>>
>> I hope someone can help with this as I have tried everything I can think
>> of and had no luck
>>
>> I have a table with 11M rows and have created a simple report/query on
>> it.
>>
>> When I run a "simple" sql on the table I get results in sub second times,
>> however when using parameters it takes over half an hour.
>>
>> After reading about parameter sniffing, I tried putting this into a sp
>> using defaults for all parameters no change, I also tried the sp with a
>> recompile clause still no change.
>>
>> I have checked the execution plans and found that the "Fast" query starts
>> with a index seek on idx_polnum and an estimated rowcount of 4
>>
>> on the slow queries the plan shows it is using the clustered index seek
>> with the sp estimating 11m rows and the parameter query 1M rows
>>
>> I have tried setting the default of the @policy parameter to a unique
>> value with no change in the sp execution plan
>>
>> i managed to get barly acceptable (30 seconds ) performance when i add
>> the following table hint with (INDEX(idx_polnum,Idx_Producercd))
>>
>> i do not want to use hints as ive heard it is not recomended , is this
>> the only way or am i missing something?
>>
>> I am using SQL 2000 with sp 3a
>>
>> Thanks in advance
>>
>> Reg Besseling
>>
>> ============================
>> here is the "simple" SQL Fast
>> ============================
>>
>>
>> SELECT     LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3),
>> POLPFX)) + RIGHT('000000' + RTRIM(CONVERT(char, POLNUM)), 7) AS
>> PolicyKey,
>>                      POLNUM, PACCTNAME AS Insured, MAX(RENPER) AS
>> RenewalMonth, MAJORLINE, SUM(GRSOSINC) AS GOS, SUM(GRSINC) AS GPW,
>> SUM(GRSPDINC)
>>                      AS GPD, SUM(GRSPDINC + GRSOSINC) AS GLI, AGENTNAME
>> FROM         RIPRMCLM
>> WHERE   1=1
>> AND (30 = 9999999 or POLNUM = 30 )
>> AND (TRNPER BETWEEN 0 AND 999999)
>> AND (99999 = 99999 OR RIPRMCLM.PRODUCERCD = 99999 )
>> AND ( 99 = 99 OR RIPRMCLM.MAJORLINE = 99)
>> GROUP BY LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3), POLPFX))
>> + RIGHT('000000' + RTRIM(CONVERT(char, POLNUM)), 7), POLNUM,
>>                      PACCTNAME, MAJORLINE, AGENTNAME
>> ORDER BY MAJORLINE DESC
>>
>> ==========================
>> hers is the query using parameters SLOW!!!!
>> ==========================
>>
>> declare @policy decimal (7,0)
>> declare @Start char(6)
>> declare @End char(6)
>> declare @Agent decimal (7,0)
>> declare @Major decimal (7,0)
>>
>> set @policy = 30
>> set @Start = '0'
>> set @End = '999999'
>> set @Agent = 99999
>> set @Major = 99
>>
>> SELECT     LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3),
>> POLPFX)) + RIGHT('000000' + RTRIM(CONVERT(char, POLNUM)), 7) AS
>> PolicyKey,
>>                      POLNUM, PACCTNAME AS Insured, MAX(RENPER) AS
>> RenewalMonth, MAJORLINE, SUM(GRSOSINC) AS GOS, SUM(GRSINC) AS GPW,
>> SUM(GRSPDINC)
>>                      AS GPD, SUM(GRSPDINC + GRSOSINC) AS GLI, AGENTNAME
>> FROM         RIPRMCLM
>> WHERE   1=1
>> AND (@policy = 9999999 or POLNUM = @policy )
>> AND (TRNPER BETWEEN @Start AND @End)
>> AND (@Agent = 99999 OR RIPRMCLM.PRODUCERCD = @Agent )
>> AND (@Major = 99 OR RIPRMCLM.MAJORLINE = @Major)
>> GROUP BY LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3), POLPFX))
>> + RIGHT('000000' + RTRIM(CONVERT(char, POLNUM)), 7), POLNUM,
>>                      PACCTNAME, MAJORLINE, AGENTNAME
>> ORDER BY MAJORLINE DESC
>>
>>
>> =======================
>> in a SP SLOW
>> =======================
>>
>>
>>
>> create procedure usp_ReportClaimsExpMain
>> @policy decimal (7,0) = 30
>> , @Start char(6) = '0'
>> , @End char(6) = '999999'
>> , @Agent decimal (7,0) = 99999
>> , @Major decimal (7,0) = 99
>>
>> with recompile
>> as
>>
>> SELECT     LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3),
>> POLPFX)) + RIGHT('000000' + RTRIM(CONVERT(char, POLNUM)), 7) AS
>> PolicyKey,
>>                      POLNUM, PACCTNAME AS Insured, MAX(RENPER) AS
>> RenewalMonth, MAJORLINE, SUM(GRSOSINC) AS GOS, SUM(GRSINC) AS GPW,
>> SUM(GRSPDINC)
>>                      AS GPD, SUM(GRSPDINC + GRSOSINC) AS GLI, AGENTNAME
>> FROM         RIPRMCLM
>> WHERE   1=1
>> AND (@policy = 9999999 or POLNUM = @policy )
>> AND (TRNPER BETWEEN @Start AND @End)
>> AND (@Agent = 99999 OR RIPRMCLM.PRODUCERCD = @Agent )
>> AND (@Major = 99 OR RIPRMCLM.MAJORLINE = @Major)
>> GROUP BY LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3), POLPFX))
>> + RIGHT('000000' + RTRIM(CONVERT(char, POLNUM)), 7), POLNUM,
>>                      PACCTNAME, MAJORLINE, AGENTNAME
>> ORDER BY MAJORLINE DESC
>>
>> go
>>
>>
>> exec usp_ReportClaimsExpMain @policy = 30, @Start = '0', @End =
>> '999999', @Agent = 99999, @Major = 99
>> ==========================
>> Table DDL
>> ==========================
>> CREATE TABLE [RIPRMCLM] (
>> [COMPANY] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [RICOMPANY] [decimal](5, 0) NOT NULL ,
>> [SOURCECDE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [RIIND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [RILOB] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [TRTYNUM] [decimal](7, 0) NOT NULL ,
>> [TRTYYR] [decimal](4, 0) NOT NULL ,
>> [POLCO] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [POLPFX] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [POLNUM] [decimal](7, 0) NOT NULL ,
>> [LOCNUM] [decimal](3, 0) NOT NULL ,
>> [RISKNUM] [decimal](3, 0) NOT NULL ,
>> [CLMNUM] [decimal](9, 0) NOT NULL ,
>> [RISKTYPE] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [PREMCLAS] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [BATCHYEAR] [decimal](4, 0) NOT NULL ,
>> [BATCHMTH] [decimal](2, 0) NOT NULL ,
>> [EFFDATE] [decimal](8, 0) NOT NULL ,
>> [TRANDATE] [decimal](8, 0) NOT NULL ,
>> [PRMEXPDATE] [decimal](8, 0) NOT NULL ,
>> [RPTTYPE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [GRSTRANAMT] [decimal](13, 2) NOT NULL ,
>> [PREMIUM] [decimal](11, 2) NOT NULL ,
>> [COMMISSION] [decimal](10, 2) NOT NULL ,
>> [STAMPDUTY] [decimal](10, 2) NOT NULL ,
>> [RSTRANTYPE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [RSLOSSTYPE] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [PYEXPTYPE] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [RECOVTYPE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [MAJORLINE] [decimal](2, 0) NOT NULL ,
>> [CLASS] [decimal](3, 0) NOT NULL ,
>> [MINORLINE] [decimal](2, 0) NOT NULL ,
>> [PY] [decimal](4, 0) NOT NULL ,
>> [PRODUCERCD] [decimal](7, 0) NOT NULL ,
>> [LEADER] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [MSTRPLCY] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [INTPREXAM] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [CLIENTNO] [decimal](7, 0) NOT NULL ,
>> [STAMPDTYCD] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [PRODCOMM] [decimal](10, 2) NOT NULL ,
>> [OURSHARE] [decimal](7, 4) NOT NULL ,
>> [LOSSCAUSE] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [DATEOFLOSS] [decimal](8, 0) NOT NULL ,
>> [DATEADVISD] [decimal](8, 0) NOT NULL ,
>> [ANATPROPCD] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [INJDAMCODE] [decimal](3, 0) NOT NULL ,
>> [THRDPRTY] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [OCCUPATION] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [PERCENTAGE] [decimal](5, 2) NOT NULL ,
>> [CLMTRANTYP] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [TRANTIME] [decimal](6, 0) NOT NULL ,
>> [USERID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [VATIND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [TRNTYPE] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [PACCTNAME] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [PSTATUS] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [PPOLTERM] [decimal](2, 0) NOT NULL ,
>> [PRENEWCODE] [decimal](2, 0) NOT NULL ,
>> [PPOLEFFDTE] [decimal](10, 0) NOT NULL ,
>> [PPOLEXPDTE] [decimal](10, 0) NOT NULL ,
>> [AGENTNAME] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [GRSOSINC] [decimal](11, 2) NOT NULL ,
>> [GRSOSEXC] [decimal](11, 2) NOT NULL ,
>> [GRSPDINC] [decimal](11, 2) NOT NULL ,
>> [GRSPDEXC] [decimal](11, 2) NOT NULL ,
>> [GRSINC] [decimal](11, 2) NOT NULL ,
>> [GRSEXC] [decimal](11, 2) NOT NULL ,
>> [NETOSINC] [decimal](11, 2) NOT NULL ,
>> [NETOSEXC] [decimal](11, 2) NOT NULL ,
>> [NETPDINC] [decimal](11, 2) NOT NULL ,
>> [NETPDEXC] [decimal](11, 2) NOT NULL ,
>> [NETINC] [decimal](11, 2) NOT NULL ,
>> [NETEXC] [decimal](11, 2) NOT NULL ,
>> [GRSRECINC] [decimal](11, 2) NOT NULL ,
>> [GRSRECEXC] [decimal](11, 2) NOT NULL ,
>> [NETRECINC] [decimal](11, 2) NOT NULL ,
>> [NETRECEXC] [decimal](11, 2) NOT NULL ,
>> [GRSTRNINC] [decimal](11, 2) NOT NULL ,
>> [GRSTRNEXC] [decimal](11, 2) NOT NULL ,
>> [TRNPER] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [RENPER] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [GRSERNINC] [decimal](11, 2) NOT NULL ,
>> [GRSERNEXC] [decimal](11, 2) NOT NULL ,
>> [TMSTDT] [decimal](8, 0) NULL ,
>> [TMEDDT] [decimal](8, 0) NULL ,
>> [UnPolicykey] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>> ) ON [PRIMARY]
>> GO
>>
>>
>> CREATE CLUSTERED INDEX [IX_RIPRMCLM] ON [dbo].[RIPRMCLM]([TRNPER],
>> [MAJORLINE]) WITH FILLFACTOR = 90 ON [PRIMARY]
>> GO
>>
>> CREATE INDEX [IX_RIPRMCLM_1] ON [dbo].[RIPRMCLM]([POLCO], [POLPFX]) WITH
>> FILLFACTOR = 90 ON [PRIMARY]
>> GO
>>
>> CREATE INDEX [IX_RIPRMCLM_2] ON [dbo].[RIPRMCLM]([CLIENTNO]) WITH
>> FILLFACTOR = 90 ON [PRIMARY]
>> GO
>>
>> CREATE INDEX [IX_RIPRMCLM_3] ON [dbo].[RIPRMCLM]([CLMNUM]) WITH
>> FILLFACTOR = 90 ON [PRIMARY]
>> GO
>>
>> CREATE INDEX [Idx_polnum] ON [dbo].[RIPRMCLM]([POLNUM]) WITH FILLFACTOR
>> = 90 ON [PRIMARY]
>> GO
>>
>> CREATE INDEX [RIPRMCLM3] ON [dbo].[RIPRMCLM]([RICOMPANY], [MAJORLINE],
>> [MINORLINE], [TRTYNUM], [TRTYYR]) WITH FILLFACTOR = 90 ON [PRIMARY]
>> GO
>>
>> CREATE INDEX [Idx_Producercd] ON [dbo].[RIPRMCLM]([PRODUCERCD]) WITH
>> FILLFACTOR = 90 ON [PRIMARY]
>> GO
>>
>>
>>

4 Aug 2006 1:43 PM

Alejandro Mesa

Reg,

Try using dynamic sql.

declare @sql nvarchar(4000)
declare @params nvarchar(4000)

set @params = N'
@policy decimal (7,0),
@Start char(6),
@End char(6),
@Agent decimal (7,0),
@Major decimal (7,0),
'

set @sql = N'
SELECT     LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3), POLPFX))
+ RIGHT(''000000'' + RTRIM(CONVERT(char, POLNUM)), 7) AS PolicyKey,
                      POLNUM, PACCTNAME AS Insured, MAX(RENPER) AS
RenewalMonth, MAJORLINE, SUM(GRSOSINC) AS GOS, SUM(GRSINC) AS GPW,
SUM(GRSPDINC)
                      AS GPD, SUM(GRSPDINC + GRSOSINC) AS GLI, AGENTNAME
FROM         RIPRMCLM
WHERE   1=1'

if @policy != 9999999
   set @sql = @sql + N' AND POLNUM = @policy'

set @sql = @sql + N' AND (TRNPER BETWEEN @Start AND @End)'

if @Agent != 99999
   set @sql = @sql + N' AND RIPRMCLM.PRODUCERCD = @Agent'

if @Major != 99
   set @sql = @sql + N' AND RIPRMCLM.MAJORLINE = @Major'

set @sql = @sql + N'
GROUP BY LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3), POLPFX)) +
RIGHT(''000000'' + RTRIM(CONVERT(char, POLNUM)), 7), POLNUM,
                      PACCTNAME, MAJORLINE, AGENTNAME
ORDER BY MAJORLINE DESC'

exec sp_executesql @sql, @params, @policy, @Start, @End, @Agent, @Major
go

The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html

Dynamic Search Conditions in T-SQL
http://www.sommarskog.se/dyn-search.html

AMB

Show quote

"Reg Besseling" wrote:

> Hi all,
> (DDL's etc below)
>
> I hope someone can help with this as I have tried everything I can think of
> and had no luck
>
> I have a table with 11M rows and have created a simple report/query on it.
>
> When I run a "simple" sql on the table I get results in sub second times,
> however when using parameters it takes over half an hour.
>
> After reading about parameter sniffing, I tried putting this into a sp
> using defaults for all parameters no change, I also tried the sp with a
> recompile clause still no change.
>
> I have checked the execution plans and found that the "Fast" query starts
> with a index seek on idx_polnum and an estimated rowcount of 4
>
> on the slow queries the plan shows it is using the clustered index seek with
> the sp estimating 11m rows and the parameter query 1M rows
>
> I have tried setting the default of the @policy parameter to a unique value
> with no change in the sp execution plan
>
> i managed to get barly acceptable (30 seconds ) performance when i add the
> following table hint with (INDEX(idx_polnum,Idx_Producercd))
>
> i do not want to use hints as ive heard it is not recomended , is this the
> only way or am i missing something?
>
> I am using SQL 2000 with sp 3a
>
> Thanks in advance
>
> Reg Besseling
>
> ============================
> here is the "simple" SQL Fast
> ============================
>
>
> SELECT     LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3), POLPFX))
> + RIGHT('000000' + RTRIM(CONVERT(char, POLNUM)), 7) AS PolicyKey,
>                       POLNUM, PACCTNAME AS Insured, MAX(RENPER) AS
> RenewalMonth, MAJORLINE, SUM(GRSOSINC) AS GOS, SUM(GRSINC) AS GPW,
> SUM(GRSPDINC)
>                       AS GPD, SUM(GRSPDINC + GRSOSINC) AS GLI, AGENTNAME
> FROM         RIPRMCLM
> WHERE   1=1
> AND (30 = 9999999 or POLNUM = 30 )
> AND (TRNPER BETWEEN 0 AND 999999)
> AND (99999 = 99999 OR RIPRMCLM.PRODUCERCD = 99999 )
> AND ( 99 = 99 OR RIPRMCLM.MAJORLINE = 99)
> GROUP BY LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3), POLPFX)) +
> RIGHT('000000' + RTRIM(CONVERT(char, POLNUM)), 7), POLNUM,
>                       PACCTNAME, MAJORLINE, AGENTNAME
> ORDER BY MAJORLINE DESC
>
> ==========================
> hers is the query using parameters SLOW!!!!
> ==========================
>
> declare @policy decimal (7,0)
> declare @Start char(6)
> declare @End char(6)
> declare @Agent decimal (7,0)
> declare @Major decimal (7,0)
>
> set @policy = 30
> set @Start = '0'
> set @End = '999999'
> set @Agent = 99999
> set @Major = 99
>
> SELECT     LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3), POLPFX))
> + RIGHT('000000' + RTRIM(CONVERT(char, POLNUM)), 7) AS PolicyKey,
>                       POLNUM, PACCTNAME AS Insured, MAX(RENPER) AS
> RenewalMonth, MAJORLINE, SUM(GRSOSINC) AS GOS, SUM(GRSINC) AS GPW,
> SUM(GRSPDINC)
>                       AS GPD, SUM(GRSPDINC + GRSOSINC) AS GLI, AGENTNAME
> FROM         RIPRMCLM
> WHERE   1=1
> AND (@policy = 9999999 or POLNUM = @policy )
> AND (TRNPER BETWEEN @Start AND @End)
> AND (@Agent = 99999 OR RIPRMCLM.PRODUCERCD = @Agent )
> AND (@Major = 99 OR RIPRMCLM.MAJORLINE = @Major)
> GROUP BY LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3), POLPFX)) +
> RIGHT('000000' + RTRIM(CONVERT(char, POLNUM)), 7), POLNUM,
>                       PACCTNAME, MAJORLINE, AGENTNAME
> ORDER BY MAJORLINE DESC
>
>
> =======================
> in a SP SLOW
> =======================
>
>
>
> create procedure usp_ReportClaimsExpMain
> @policy decimal (7,0) = 30
> , @Start char(6) = '0'
> , @End char(6) = '999999'
> , @Agent decimal (7,0) = 99999
> , @Major decimal (7,0) = 99
>
> with recompile
> as
>
> SELECT     LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3), POLPFX))
> + RIGHT('000000' + RTRIM(CONVERT(char, POLNUM)), 7) AS PolicyKey,
>                       POLNUM, PACCTNAME AS Insured, MAX(RENPER) AS
> RenewalMonth, MAJORLINE, SUM(GRSOSINC) AS GOS, SUM(GRSINC) AS GPW,
> SUM(GRSPDINC)
>                       AS GPD, SUM(GRSPDINC + GRSOSINC) AS GLI, AGENTNAME
> FROM         RIPRMCLM
> WHERE   1=1
> AND (@policy = 9999999 or POLNUM = @policy )
> AND (TRNPER BETWEEN @Start AND @End)
> AND (@Agent = 99999 OR RIPRMCLM.PRODUCERCD = @Agent )
> AND (@Major = 99 OR RIPRMCLM.MAJORLINE = @Major)
> GROUP BY LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3), POLPFX)) +
> RIGHT('000000' + RTRIM(CONVERT(char, POLNUM)), 7), POLNUM,
>                       PACCTNAME, MAJORLINE, AGENTNAME
> ORDER BY MAJORLINE DESC
>
> go
>
>
> exec usp_ReportClaimsExpMain @policy = 30, @Start = '0', @End = '999999',
> @Agent = 99999, @Major = 99
> ==========================
> Table DDL
> ==========================
> CREATE TABLE [RIPRMCLM] (
> [COMPANY] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [RICOMPANY] [decimal](5, 0) NOT NULL ,
> [SOURCECDE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [RIIND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [RILOB] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [TRTYNUM] [decimal](7, 0) NOT NULL ,
> [TRTYYR] [decimal](4, 0) NOT NULL ,
> [POLCO] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [POLPFX] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [POLNUM] [decimal](7, 0) NOT NULL ,
> [LOCNUM] [decimal](3, 0) NOT NULL ,
> [RISKNUM] [decimal](3, 0) NOT NULL ,
> [CLMNUM] [decimal](9, 0) NOT NULL ,
> [RISKTYPE] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [PREMCLAS] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [BATCHYEAR] [decimal](4, 0) NOT NULL ,
> [BATCHMTH] [decimal](2, 0) NOT NULL ,
> [EFFDATE] [decimal](8, 0) NOT NULL ,
> [TRANDATE] [decimal](8, 0) NOT NULL ,
> [PRMEXPDATE] [decimal](8, 0) NOT NULL ,
> [RPTTYPE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [GRSTRANAMT] [decimal](13, 2) NOT NULL ,
> [PREMIUM] [decimal](11, 2) NOT NULL ,
> [COMMISSION] [decimal](10, 2) NOT NULL ,
> [STAMPDUTY] [decimal](10, 2) NOT NULL ,
> [RSTRANTYPE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [RSLOSSTYPE] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [PYEXPTYPE] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [RECOVTYPE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [MAJORLINE] [decimal](2, 0) NOT NULL ,
> [CLASS] [decimal](3, 0) NOT NULL ,
> [MINORLINE] [decimal](2, 0) NOT NULL ,
> [PY] [decimal](4, 0) NOT NULL ,
> [PRODUCERCD] [decimal](7, 0) NOT NULL ,
> [LEADER] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [MSTRPLCY] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [INTPREXAM] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [CLIENTNO] [decimal](7, 0) NOT NULL ,
> [STAMPDTYCD] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [PRODCOMM] [decimal](10, 2) NOT NULL ,
> [OURSHARE] [decimal](7, 4) NOT NULL ,
> [LOSSCAUSE] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [DATEOFLOSS] [decimal](8, 0) NOT NULL ,
> [DATEADVISD] [decimal](8, 0) NOT NULL ,
> [ANATPROPCD] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [INJDAMCODE] [decimal](3, 0) NOT NULL ,
> [THRDPRTY] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [OCCUPATION] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [PERCENTAGE] [decimal](5, 2) NOT NULL ,
> [CLMTRANTYP] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [TRANTIME] [decimal](6, 0) NOT NULL ,
> [USERID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [VATIND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [TRNTYPE] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [PACCTNAME] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [PSTATUS] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [PPOLTERM] [decimal](2, 0) NOT NULL ,
> [PRENEWCODE] [decimal](2, 0) NOT NULL ,
> [PPOLEFFDTE] [decimal](10, 0) NOT NULL ,
> [PPOLEXPDTE] [decimal](10, 0) NOT NULL ,
> [AGENTNAME] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [GRSOSINC] [decimal](11, 2) NOT NULL ,
> [GRSOSEXC] [decimal](11, 2) NOT NULL ,
> [GRSPDINC] [decimal](11, 2) NOT NULL ,
> [GRSPDEXC] [decimal](11, 2) NOT NULL ,
> [GRSINC] [decimal](11, 2) NOT NULL ,
> [GRSEXC] [decimal](11, 2) NOT NULL ,
> [NETOSINC] [decimal](11, 2) NOT NULL ,
> [NETOSEXC] [decimal](11, 2) NOT NULL ,
> [NETPDINC] [decimal](11, 2) NOT NULL ,
> [NETPDEXC] [decimal](11, 2) NOT NULL ,
> [NETINC] [decimal](11, 2) NOT NULL ,
> [NETEXC] [decimal](11, 2) NOT NULL ,
> [GRSRECINC] [decimal](11, 2) NOT NULL ,
> [GRSRECEXC] [decimal](11, 2) NOT NULL ,
> [NETRECINC] [decimal](11, 2) NOT NULL ,
> [NETRECEXC] [decimal](11, 2) NOT NULL ,
> [GRSTRNINC] [decimal](11, 2) NOT NULL ,
> [GRSTRNEXC] [decimal](11, 2) NOT NULL ,
> [TRNPER] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [RENPER] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [GRSERNINC] [decimal](11, 2) NOT NULL ,
> [GRSERNEXC] [decimal](11, 2) NOT NULL ,
> [TMSTDT] [decimal](8, 0) NULL ,
> [TMEDDT] [decimal](8, 0) NULL ,
> [UnPolicykey] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
>
> CREATE CLUSTERED INDEX [IX_RIPRMCLM] ON [dbo].[RIPRMCLM]([TRNPER],
> [MAJORLINE]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
>
> CREATE INDEX [IX_RIPRMCLM_1] ON [dbo].[RIPRMCLM]([POLCO], [POLPFX]) WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
>
> CREATE INDEX [IX_RIPRMCLM_2] ON [dbo].[RIPRMCLM]([CLIENTNO]) WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
>
> CREATE INDEX [IX_RIPRMCLM_3] ON [dbo].[RIPRMCLM]([CLMNUM]) WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
>
> CREATE INDEX [Idx_polnum] ON [dbo].[RIPRMCLM]([POLNUM]) WITH FILLFACTOR =
> 90 ON [PRIMARY]
> GO
>
> CREATE INDEX [RIPRMCLM3] ON [dbo].[RIPRMCLM]([RICOMPANY], [MAJORLINE],
> [MINORLINE], [TRTYNUM], [TRTYYR]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
>
> CREATE INDEX [Idx_Producercd] ON [dbo].[RIPRMCLM]([PRODUCERCD]) WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
>
>
>
>

7 Aug 2006 11:31 AM

Reg Besseling

Thanks Alejandro

Dynamic SQL was indeed the answer however I had to use Steve Kass's trick of
replacing the parameter names with the actual values then doing the
sp_executesql before it speeded up

Regards

Reg

Show quote

"Alejandro Mesa" <AlejandroM***@discussions.microsoft.com> wrote in message
news:AB976217-F8E0-494A-83E8-3E91C34126BD@microsoft.com...
> Reg,
>
> Try using dynamic sql.
>
> declare @sql nvarchar(4000)
> declare @params nvarchar(4000)
>
> set @params = N'
> @policy decimal (7,0),
> @Start char(6),
> @End char(6),
> @Agent decimal (7,0),
> @Major decimal (7,0),
> '
>
> set @sql = N'
> SELECT     LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3),
> POLPFX))
> + RIGHT(''000000'' + RTRIM(CONVERT(char, POLNUM)), 7) AS PolicyKey,
>                      POLNUM, PACCTNAME AS Insured, MAX(RENPER) AS
> RenewalMonth, MAJORLINE, SUM(GRSOSINC) AS GOS, SUM(GRSINC) AS GPW,
> SUM(GRSPDINC)
>                      AS GPD, SUM(GRSPDINC + GRSOSINC) AS GLI, AGENTNAME
> FROM         RIPRMCLM
> WHERE   1=1'
>
> if @policy != 9999999
>   set @sql = @sql + N' AND POLNUM = @policy'
>
> set @sql = @sql + N' AND (TRNPER BETWEEN @Start AND @End)'
>
> if @Agent != 99999
>   set @sql = @sql + N' AND RIPRMCLM.PRODUCERCD = @Agent'
>
> if @Major != 99
>   set @sql = @sql + N' AND RIPRMCLM.MAJORLINE = @Major'
>
> set @sql = @sql + N'
> GROUP BY LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3), POLPFX))
> +
> RIGHT(''000000'' + RTRIM(CONVERT(char, POLNUM)), 7), POLNUM,
>                      PACCTNAME, MAJORLINE, AGENTNAME
> ORDER BY MAJORLINE DESC'
>
> exec sp_executesql @sql, @params, @policy, @Start, @End, @Agent, @Major
> go
>
>
> The Curse and Blessings of Dynamic SQL
> http://www.sommarskog.se/dynamic_sql.html
>
> Dynamic Search Conditions in T-SQL
> http://www.sommarskog.se/dyn-search.html
>
>
> AMB
>
> "Reg Besseling" wrote:
>
>> Hi all,
>> (DDL's etc below)
>>
>> I hope someone can help with this as I have tried everything I can think
>> of
>> and had no luck
>>
>> I have a table with 11M rows and have created a simple report/query on
>> it.
>>
>> When I run a "simple" sql on the table I get results in sub second times,
>> however when using parameters it takes over half an hour.
>>
>> After reading about parameter sniffing, I tried putting this into a sp
>> using defaults for all parameters no change, I also tried the sp with a
>> recompile clause still no change.
>>
>> I have checked the execution plans and found that the "Fast" query starts
>> with a index seek on idx_polnum and an estimated rowcount of 4
>>
>> on the slow queries the plan shows it is using the clustered index seek
>> with
>> the sp estimating 11m rows and the parameter query 1M rows
>>
>> I have tried setting the default of the @policy parameter to a unique
>> value
>> with no change in the sp execution plan
>>
>> i managed to get barly acceptable (30 seconds ) performance when i add
>> the
>> following table hint with (INDEX(idx_polnum,Idx_Producercd))
>>
>> i do not want to use hints as ive heard it is not recomended , is this
>> the
>> only way or am i missing something?
>>
>> I am using SQL 2000 with sp 3a
>>
>> Thanks in advance
>>
>> Reg Besseling
>>
>> ============================
>> here is the "simple" SQL Fast
>> ============================
>>
>>
>> SELECT     LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3),
>> POLPFX))
>> + RIGHT('000000' + RTRIM(CONVERT(char, POLNUM)), 7) AS PolicyKey,
>>                       POLNUM, PACCTNAME AS Insured, MAX(RENPER) AS
>> RenewalMonth, MAJORLINE, SUM(GRSOSINC) AS GOS, SUM(GRSINC) AS GPW,
>> SUM(GRSPDINC)
>>                       AS GPD, SUM(GRSPDINC + GRSOSINC) AS GLI, AGENTNAME
>> FROM         RIPRMCLM
>> WHERE   1=1
>> AND (30 = 9999999 or POLNUM = 30 )
>> AND (TRNPER BETWEEN 0 AND 999999)
>> AND (99999 = 99999 OR RIPRMCLM.PRODUCERCD = 99999 )
>> AND ( 99 = 99 OR RIPRMCLM.MAJORLINE = 99)
>> GROUP BY LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3), POLPFX))
>> +
>> RIGHT('000000' + RTRIM(CONVERT(char, POLNUM)), 7), POLNUM,
>>                       PACCTNAME, MAJORLINE, AGENTNAME
>> ORDER BY MAJORLINE DESC
>>
>> ==========================
>> hers is the query using parameters SLOW!!!!
>> ==========================
>>
>> declare @policy decimal (7,0)
>> declare @Start char(6)
>> declare @End char(6)
>> declare @Agent decimal (7,0)
>> declare @Major decimal (7,0)
>>
>> set @policy = 30
>> set @Start = '0'
>> set @End = '999999'
>> set @Agent = 99999
>> set @Major = 99
>>
>> SELECT     LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3),
>> POLPFX))
>> + RIGHT('000000' + RTRIM(CONVERT(char, POLNUM)), 7) AS PolicyKey,
>>                       POLNUM, PACCTNAME AS Insured, MAX(RENPER) AS
>> RenewalMonth, MAJORLINE, SUM(GRSOSINC) AS GOS, SUM(GRSINC) AS GPW,
>> SUM(GRSPDINC)
>>                       AS GPD, SUM(GRSPDINC + GRSOSINC) AS GLI, AGENTNAME
>> FROM         RIPRMCLM
>> WHERE   1=1
>> AND (@policy = 9999999 or POLNUM = @policy )
>> AND (TRNPER BETWEEN @Start AND @End)
>> AND (@Agent = 99999 OR RIPRMCLM.PRODUCERCD = @Agent )
>> AND (@Major = 99 OR RIPRMCLM.MAJORLINE = @Major)
>> GROUP BY LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3), POLPFX))
>> +
>> RIGHT('000000' + RTRIM(CONVERT(char, POLNUM)), 7), POLNUM,
>>                       PACCTNAME, MAJORLINE, AGENTNAME
>> ORDER BY MAJORLINE DESC
>>
>>
>> =======================
>> in a SP SLOW
>> =======================
>>
>>
>>
>> create procedure usp_ReportClaimsExpMain
>> @policy decimal (7,0) = 30
>> , @Start char(6) = '0'
>> , @End char(6) = '999999'
>> , @Agent decimal (7,0) = 99999
>> , @Major decimal (7,0) = 99
>>
>> with recompile
>> as
>>
>> SELECT     LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3),
>> POLPFX))
>> + RIGHT('000000' + RTRIM(CONVERT(char, POLNUM)), 7) AS PolicyKey,
>>                       POLNUM, PACCTNAME AS Insured, MAX(RENPER) AS
>> RenewalMonth, MAJORLINE, SUM(GRSOSINC) AS GOS, SUM(GRSINC) AS GPW,
>> SUM(GRSPDINC)
>>                       AS GPD, SUM(GRSPDINC + GRSOSINC) AS GLI, AGENTNAME
>> FROM         RIPRMCLM
>> WHERE   1=1
>> AND (@policy = 9999999 or POLNUM = @policy )
>> AND (TRNPER BETWEEN @Start AND @End)
>> AND (@Agent = 99999 OR RIPRMCLM.PRODUCERCD = @Agent )
>> AND (@Major = 99 OR RIPRMCLM.MAJORLINE = @Major)
>> GROUP BY LTRIM(CONVERT(char(2), POLCO)) + RTRIM(CONVERT(char(3), POLPFX))
>> +
>> RIGHT('000000' + RTRIM(CONVERT(char, POLNUM)), 7), POLNUM,
>>                       PACCTNAME, MAJORLINE, AGENTNAME
>> ORDER BY MAJORLINE DESC
>>
>> go
>>
>>
>> exec usp_ReportClaimsExpMain @policy = 30, @Start = '0', @End =
>> '999999',
>> @Agent = 99999, @Major = 99
>> ==========================
>> Table DDL
>> ==========================
>> CREATE TABLE [RIPRMCLM] (
>> [COMPANY] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [RICOMPANY] [decimal](5, 0) NOT NULL ,
>> [SOURCECDE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [RIIND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [RILOB] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [TRTYNUM] [decimal](7, 0) NOT NULL ,
>> [TRTYYR] [decimal](4, 0) NOT NULL ,
>> [POLCO] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [POLPFX] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [POLNUM] [decimal](7, 0) NOT NULL ,
>> [LOCNUM] [decimal](3, 0) NOT NULL ,
>> [RISKNUM] [decimal](3, 0) NOT NULL ,
>> [CLMNUM] [decimal](9, 0) NOT NULL ,
>> [RISKTYPE] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [PREMCLAS] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [BATCHYEAR] [decimal](4, 0) NOT NULL ,
>> [BATCHMTH] [decimal](2, 0) NOT NULL ,
>> [EFFDATE] [decimal](8, 0) NOT NULL ,
>> [TRANDATE] [decimal](8, 0) NOT NULL ,
>> [PRMEXPDATE] [decimal](8, 0) NOT NULL ,
>> [RPTTYPE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [GRSTRANAMT] [decimal](13, 2) NOT NULL ,
>> [PREMIUM] [decimal](11, 2) NOT NULL ,
>> [COMMISSION] [decimal](10, 2) NOT NULL ,
>> [STAMPDUTY] [decimal](10, 2) NOT NULL ,
>> [RSTRANTYPE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [RSLOSSTYPE] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [PYEXPTYPE] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [RECOVTYPE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [MAJORLINE] [decimal](2, 0) NOT NULL ,
>> [CLASS] [decimal](3, 0) NOT NULL ,
>> [MINORLINE] [decimal](2, 0) NOT NULL ,
>> [PY] [decimal](4, 0) NOT NULL ,
>> [PRODUCERCD] [decimal](7, 0) NOT NULL ,
>> [LEADER] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [MSTRPLCY] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [INTPREXAM] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [CLIENTNO] [decimal](7, 0) NOT NULL ,
>> [STAMPDTYCD] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [PRODCOMM] [decimal](10, 2) NOT NULL ,
>> [OURSHARE] [decimal](7, 4) NOT NULL ,
>> [LOSSCAUSE] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [DATEOFLOSS] [decimal](8, 0) NOT NULL ,
>> [DATEADVISD] [decimal](8, 0) NOT NULL ,
>> [ANATPROPCD] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [INJDAMCODE] [decimal](3, 0) NOT NULL ,
>> [THRDPRTY] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [OCCUPATION] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [PERCENTAGE] [decimal](5, 2) NOT NULL ,
>> [CLMTRANTYP] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [TRANTIME] [decimal](6, 0) NOT NULL ,
>> [USERID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [VATIND] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [TRNTYPE] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [PACCTNAME] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [PSTATUS] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [PPOLTERM] [decimal](2, 0) NOT NULL ,
>> [PRENEWCODE] [decimal](2, 0) NOT NULL ,
>> [PPOLEFFDTE] [decimal](10, 0) NOT NULL ,
>> [PPOLEXPDTE] [decimal](10, 0) NOT NULL ,
>> [AGENTNAME] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [GRSOSINC] [decimal](11, 2) NOT NULL ,
>> [GRSOSEXC] [decimal](11, 2) NOT NULL ,
>> [GRSPDINC] [decimal](11, 2) NOT NULL ,
>> [GRSPDEXC] [decimal](11, 2) NOT NULL ,
>> [GRSINC] [decimal](11, 2) NOT NULL ,
>> [GRSEXC] [decimal](11, 2) NOT NULL ,
>> [NETOSINC] [decimal](11, 2) NOT NULL ,
>> [NETOSEXC] [decimal](11, 2) NOT NULL ,
>> [NETPDINC] [decimal](11, 2) NOT NULL ,
>> [NETPDEXC] [decimal](11, 2) NOT NULL ,
>> [NETINC] [decimal](11, 2) NOT NULL ,
>> [NETEXC] [decimal](11, 2) NOT NULL ,
>> [GRSRECINC] [decimal](11, 2) NOT NULL ,
>> [GRSRECEXC] [decimal](11, 2) NOT NULL ,
>> [NETRECINC] [decimal](11, 2) NOT NULL ,
>> [NETRECEXC] [decimal](11, 2) NOT NULL ,
>> [GRSTRNINC] [decimal](11, 2) NOT NULL ,
>> [GRSTRNEXC] [decimal](11, 2) NOT NULL ,
>> [TRNPER] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [RENPER] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [GRSERNINC] [decimal](11, 2) NOT NULL ,
>> [GRSERNEXC] [decimal](11, 2) NOT NULL ,
>> [TMSTDT] [decimal](8, 0) NULL ,
>> [TMEDDT] [decimal](8, 0) NULL ,
>> [UnPolicykey] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>> ) ON [PRIMARY]
>> GO
>>
>>
>> CREATE CLUSTERED INDEX [IX_RIPRMCLM] ON [dbo].[RIPRMCLM]([TRNPER],
>> [MAJORLINE]) WITH FILLFACTOR = 90 ON [PRIMARY]
>> GO
>>
>> CREATE INDEX [IX_RIPRMCLM_1] ON [dbo].[RIPRMCLM]([POLCO], [POLPFX])
>> WITH
>> FILLFACTOR = 90 ON [PRIMARY]
>> GO
>>
>> CREATE INDEX [IX_RIPRMCLM_2] ON [dbo].[RIPRMCLM]([CLIENTNO]) WITH
>> FILLFACTOR = 90 ON [PRIMARY]
>> GO
>>
>> CREATE INDEX [IX_RIPRMCLM_3] ON [dbo].[RIPRMCLM]([CLMNUM]) WITH
>> FILLFACTOR = 90 ON [PRIMARY]
>> GO
>>
>> CREATE INDEX [Idx_polnum] ON [dbo].[RIPRMCLM]([POLNUM]) WITH
>> FILLFACTOR =
>> 90 ON [PRIMARY]
>> GO
>>
>> CREATE INDEX [RIPRMCLM3] ON [dbo].[RIPRMCLM]([RICOMPANY], [MAJORLINE],
>> [MINORLINE], [TRTYNUM], [TRTYYR]) WITH FILLFACTOR = 90 ON [PRIMARY]
>> GO
>>
>> CREATE INDEX [Idx_Producercd] ON [dbo].[RIPRMCLM]([PRODUCERCD]) WITH
>> FILLFACTOR = 90 ON [PRIMARY]
>> GO
>>
>>
>>
>>