SQL2K Question

I have a Stored Procedure that is taking an unusual amount of time
(gradually getting slower) to run.
I have analyzed the execution plan via Query Analyzer and I have identified
what portion of the Stored Procedure is causing the problem. I have
analyzed the indexes and added other index fields in the query with no luck.
The stored procedure accepts parameters, and I use those in the where
clause. Breaking down the query, I have tried changing the data types of
the
@parameter variables to exactly match the columns in the table. But still
sluggish performance.

One thing I noticed, and this has happened to me before, is that if I hard
code some values in the same Select statement, the stored procedure seems to
run much faster. If I hard code a date ranges in the proc ie between
'01/01/2006'
and '01/25/2006' , it runs much faster (as there are no bookmark lookups),
but if I use
the same values assigning them to varchar, char, datetime, or smalldate
time (tried most logical ones,
to equal the table column) it triggers a bookmark lookup. With the hard
coded date values the
execution plan shows that the query is now utilizing the indexes and it
reduces the bookmark
lookups in some cases from 49% to 15% or less.

Any ideas??

Thanks


.

Relevant Pages

  • Re: Database Engine Tuning Advisor suggestion to replace syntax.
    ... Actually, what I said was "IF this code is running in a stored procedure", ... execution with the execution plan generated for the first execution. ... SQL Server does not have to compile the second statement." ... replace query 1 below with the syntax in query 2 below. ...
    (microsoft.public.sqlserver.tools)
  • Incomplete result set returned by record select - Crystal XI, MS SQL stored procedure
    ... I've got a stored procedure written in T-SQL. ... When I use Query ... query analyzer. ... That functions fine, the prompts seem to work OK, I ...
    (microsoft.public.vb.crystal)
  • Re: TSQL Help
    ... What you can do on your own, is to put the query from Query Analyzer and ... This gives you the estimated execution plan. ... However, if this code is in a stored procedure, I suspect that several of ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: Help with where clause in Stored proc and Qry Analyzer getting different rec. counts
    ... type of fkarInvFlagRefID is charor some nullable char ... you run this query. ... analyzer) and stored procedure and procedure call. ... If you are calling the stored procedure from somewhere other than query ...
    (microsoft.public.sqlserver.programming)
  • SP **
    ... I've a Stored procedure in SQL server 2000 and when I run it in Query ... executing it throght a command and ofcourse I make the connection timout ... when I alter procedure with it's script again in Query analyzer whithout any ...
    (microsoft.public.sqlserver.programming)