Greetings,
I have a table with 13M Row.  When I write a non-paramterized query against
this data a result set is returned in 18 - 20 Seconds.  In a parameterized
Stored procedure it's takes over 4 minutes.  Any help would be appreciated

Here is the table
CREATE TABLE [dbo].[gltrns1](
    [AAPrimaryKey] [int] IDENTITY(1,1) NOT NULL,
    [BudYr] [smallint] NULL,
    [TrnsCode] [smallint] NULL,
    [Fund] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [RC] [smallint] NOT NULL,
    [Function] [smallint] NOT NULL,
    [Object] [int] NOT NULL,
    [PostDate] [smalldatetime] NOT NULL,
    [WO] [int] NULL,
    [RefNum] [char](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Vendor] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Journal] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [EncAmt] [money] NOT NULL CONSTRAINT [DF_gltrns1_EncAmt]  DEFAULT (0),
    [ExpAmt] [money] NOT NULL CONSTRAINT [DF_gltrns1_ExpAmt]  DEFAULT (0),
    [METype] [tinyint] NULL,
    [FunctName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [MOName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [RCName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [TS] [smalldatetime] NULL CONSTRAINT [DF_gltrns1_TS]  DEFAULT (getdate()),
    [Fiscalyear] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Dept] [int] NULL,
    [DeptName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [MonthNumber]  AS (datepart(month,[PostDate])),
    [Time_Stamp] [timestamp] NOT NULL,
CONSTRAINT [PK_GL145] PRIMARY KEY CLUSTERED
(
    [AAPrimaryKey] ASC
) ON [PRIMARY]
) ON [PRIMARY]
Index used in both execution plans
CREATE NONCLUSTERED INDEX [IX_gltrns1_1] ON [dbo].[gltrns1]
(
    [RC] ASC,
    [PostDate] ASC,
    [Fiscalyear] ASC,
    [Function] ASC,
    [FunctName] ASC,
    [Object] ASC,
    [ExpAmt] ASC
) ON [PRIMARY]

Good Query (Paramters included not used)
SET STATISTICS IO ON
Declare 
@RC smallint,
@StartDate smalldatetime,
@EndDate smalldatetime,
@FiscalYear char(5)

SET @RC = 161
set @StartDate = NULL
set @EndDate = NULL
Set @FiscalYear = '05/06'

SELECT gltrns1.RC,
       gltrns1.[Function],
       gltrns1.FunctName,
       Left([object], 2) * 1000 AS MMO,
       left(DATENAME(month, gltrns1.postdate), 3) as Expr1,
       Sum(gltrns1.ExpAmt) AS SumOfExpAmt
  FROM btd.dbo.gltrns1 gltrns1
WHERE gltrns1.RC = 161
   AND gltrns1.PostDate Between COALESCE(NULL, gltrns1.PostDate) And
COALESCE(NULL, gltrns1.PostDate)
   AND gltrns1.Fiscalyear = COALESCE('05/06', gltrns1.Fiscalyear) 
GROUP BY gltrns1.RC,
          gltrns1.[Function],
          gltrns1.FunctName,
          Left([object], 2) * 1000,
left(DATENAME(month, gltrns1.postdate), 3)
Bad Query

SET STATISTICS IO ON
Declare 
@RC smallint,
@StartDate smalldatetime,
@EndDate smalldatetime,
@FiscalYear char(5)

SET @RC = 161
set @StartDate = NULL
set @EndDate = NULL
Set @FiscalYear = '05/06'

SELECT gltrns1.RC,
       gltrns1.[Function],
       gltrns1.FunctName,
       Left([object], 2) * 1000 AS MMO,
       left(DATENAME(month, gltrns1.postdate), 3) as Expr1,
       Sum(gltrns1.ExpAmt) AS SumOfExpAmt
  FROM btd.dbo.gltrns1 gltrns1
WHERE gltrns1.RC = @RC
   AND gltrns1.PostDate Between COALESCE(@StartDate, gltrns1.PostDate) And
COALESCE(@EndDate, gltrns1.PostDate)
   AND gltrns1.Fiscalyear = COALESCE(@FiscalYear, gltrns1.Fiscalyear) 
GROUP BY gltrns1.RC,
          gltrns1.[Function],
          gltrns1.FunctName,
          Left([object], 2) * 1000,
          left(DATENAME(month, gltrns1.postdate), 3)

Sorry, Need to add that I'm using SQL2000 Enterprise Edition WIN Server 2003
Enterprise. Multi-processor HP Box


Cheers

More Information
Good Execution Plan
StmtText                                                                    

--------                                                                    

Parallelism(Gather Streams)                                                 


[gltrns1].[FunctName], [Expr1001], [Expr1002]),
RESIDUAL:((([gltrns1].[Function]=[gltrns1].[Function] AND
[gltrns1].[FunctName]=[gltrns1].[FunctName]) AND [Expr1001]=[Expr1001]) AND
[Expr1002]=[Expr1002]) DEFINE:([Expr1003]=SUM([gltrns1].[ExpAmt]),
[gltrns1].[RC]=ANY([gltrns1].[RC])))
COLUMNS:([gltrns1].[Function], [gltrns1].[FunctName], [Expr1001],
[Expr1002]))                                                                 


Scalar(DEFINE:([Expr1001]=Convert(substring(Convert([gltrns1].[Object]), 1,
2))*1000, [Expr1002]=substring(datename(month,
Convert([gltrns1].[PostDate])), 1, 3)))                                      


AS [gltrns1]), SEEK:([gltrns1].[RC]=161),  WHERE:(([gltrns1].[Fiscalyear]=If
1 then '05/06' else [gltrns1].[Fiscalyear] AND
[gltrns1].[PostDate]<=[gltrns1].[PostDate]) AND
[gltrns1].[PostDate]>=[gltrns1].[PostDate]) ORDERED FORWARD) 

Bad Execution Plan

StmtText                                                                    
                  --------                                                   

Stream Aggregate(GROUP BY:([gltrns1].[Function], [gltrns1].[FunctName],
[Expr1001], [Expr1002]) DEFINE:([Expr1003]=SUM([gltrns1].[ExpAmt]),
[gltrns1].[RC]=ANY([gltrns1].[RC])))                                         


[Expr1001] ASC, [Expr1002] ASC))                                             



Scalar(DEFINE:([Expr1001]=Convert(substring(Convert([gltrns1].[Object]), 1,
2))*1000, [Expr1002]=substring(datename(month,
Convert([gltrns1].[PostDate])), 1, 3)))                                      


[gltrns1]), SEEK:([gltrns1].[RC]=[@RC]),  WHERE:(([gltrns1].[Fiscalyear]=If
([@FiscalYear]<>NULL) then [@FiscalYear] else [gltrns1].[Fiscalyear] AND
[gltrns1].[PostDate]<=If ([@EndDate]<>NULL) then [@EndDate] else
[gltrns1].[PostDate]) AND [gltrns1].[PostDate]>=If ([@StartDate]<>NULL) then
[@StartDate] else [gltrns1].[PostDate]) ORDERED FORWARD)

ChuckCraig (ChuckCr***@discussions.microsoft.com) writes:
Show quoteHide quoteAre the variables above parameters to the procedure or truly variables?

When SQL Server builds a query plan it does not know the values of
variables, and will make standard assumptions. In this case, the
optimizer may be overly optimistic about how selective the
condition on PostDate might be. (It's not selective at all.)

If the variables are indeed parameters to the procedure, the optimzer
will perform "parameter sniffing" and use the values of the first
invocation as a guide. This can lead to that different invocations
with different needs gets bad performance. It could be a good idea to
add WITH RECOMPILE to the procedure definition to force recompilation
each time.

With the given values, an index where FiscalYear comes before RC in the
index may help.

Also, the index on RC is non-clustered. Had it been clustered the
performace might have been better. (Assuming that RC = 161 is really
selective that is.)

This can also be fixed using a technique suggested by a sql server mvp.
declare variables matching your input parameters to the proc, set
the variables = input parameters and use the local variables instead.

It is screwy but it does generally fix this problem.

create procedure dbo.stuff
(
   @Param1 int
)
as

declare @TmpParam1 int

set @TmpParam1 = @Param1

select * from table where colA = @tmpParam1

Show quoteHide quote

Robbe Morris [C# MVP] (i***@eggheadcafe.com) writes:
Show quoteHide quoteSometimes it can address the problem, but most of the time it aggrevates it.

But it's good if:
1) The first call to the procedure is for an atypical value for "load all",
   and next calls are for "load recent rows", and using a local variable   
   gives an acceptable plan.
2) The parameter has a default value of NULL, and the procedure then
   interprets NULL as "today" or "now" and select rows from that value.
   Since the NULL is completely irrelevant it should be removed from
   sniffing.

In both these cases there are typically range queries involved, that is
conditions:

   WHERE col > @inputvalue

Thanks Robert,
I had tried this in my SP and in my case it did make the situation worse. 
I'm still searching!

Cheers!

Chuck

Show quoteHide quote

Erland, Thanks for getting back to me.

The parms are truely parms going into a SP.  I've also used the with
Recompile option to no avail.  On my development box I've changed around the
clustered index and the execution time decreased to about 3 minutes from 4. 
Still a far cry rfom 20 seconds.

Any additional thoughts?

Cheers!

Chuck

Show quoteHide quote

ChuckCraig (ChuckCr***@discussions.microsoft.com) writes:
The sort of query you are trying to run is problematic. Let's look at the
WHERE clause again:

If you supply @FiscalYear, an index on (RC, FiscalYear) is the best
index for the query. But if you leave out @FiscalYear, and instead
specify a range for @StartDate and @EndDate then an index on (RC, PostDate)
is better. If you have these two indexes, the best is probably to have
an IF statement and separate the query into two. You may even go as far
as having different procedures.

I happen to have an article on my wen site that discusses several solutions
to this problem, see http://www.sommarskog.se/dyn-search.html.

Also you might want to keep the number of indexes low and have just one
index on (RC, PostDate, FiscalYear) or on (RC, FiscalYear, PostDate).

Join Question
How to use "Not Exists" ?
Generating multiple rows with xml data column
delete rows timeout
Selecting TOP 1 of each group