Please Help With Parameter in SP Query

[bzss7x]

Ok Guys I have a problem. I'm sure someone know the answer to this. I'd appreciate any advice....

Query:

declare @PRODN_DATE_START datetime, @PRODN_DATE_END datetime

set @PRODN_DATE_START = '04/17/02'
set @PRODN_DATE_END = '04/17/02'

(SELECT COUNT(EVENT_TRACKER.EVENT_CD)
FROM
{ oj (EVENT_TRACKER INNER JOIN SOURCE_MASTER ON
EVENT_TRACKER.SOURCE = SOURCE_MASTER.SOURCE)
INNER JOIN PART_EVENT_LINK ON
EVENT_TRACKER.EVENT_CD = PART_EVENT_LINK.EVENT_CD AND
EVENT_TRACKER.SOURCE = PART_EVENT_LINK.SOURCE AND
EVENT_TRACKER.PART_CD = PART_EVENT_LINK.PART_CD}
WHERE
SOURCE_MASTER.SOURCE_GRP = 'P_VS01' AND
PART_EVENT_LINK.HOT = 'PP' AND
EVENT_TRACKER.PART_CD <> 'OKV000' AND
EVENT_TRACKER.PRODN_DATE >= @PRODN_DATE_START AND
EVENT_TRACKER.PRODN_DATE <= @PRODN_DATE_END)


Don't worry about anything in the query except the last two lines where the variables are referenced. When I run this query, it takes about a minute to run.


Next query:

(SELECT COUNT(EVENT_TRACKER.EVENT_CD)
FROM
{ oj (EVENT_TRACKER INNER JOIN SOURCE_MASTER ON
EVENT_TRACKER.SOURCE = SOURCE_MASTER.SOURCE)
INNER JOIN PART_EVENT_LINK ON
EVENT_TRACKER.EVENT_CD = PART_EVENT_LINK.EVENT_CD AND
EVENT_TRACKER.SOURCE = PART_EVENT_LINK.SOURCE AND
EVENT_TRACKER.PART_CD = PART_EVENT_LINK.PART_CD}
WHERE
SOURCE_MASTER.SOURCE_GRP = 'P_VS01' AND
PART_EVENT_LINK.HOT = 'PP' AND
EVENT_TRACKER.PART_CD <> 'OKV000' AND
EVENT_TRACKER.PRODN_DATE >= '04/17/02' AND
EVENT_TRACKER.PRODN_DATE <= '04/17/02')


Notice there are no parameters, just the values in their place in the last two lines. The second query runs in about a second. How do I get the first one to run as fast as the second one? I have to have the parameters for my stored procedure....

Thanks in advance.

 

[SQLBill]

I don't think you can get the first to run as fast....look at it this way...

you run script #2...it runs in a second....
now you take the same script but without the dates and as it's running, you stop it and type in the dates then let it continue...now it's going to take longer because you had to supply the dates.

The difference in running time is due to the fact that the script has to pause and get the parameters and then continue.

at least that's how I see it happening.

-SQLBill

 

[bperry]

Here's my guess, I think this is a well-known phenomenon, but if I'm wrong then someone please correct me.

Is there an index (esp a clustered index) on ProdnDate?

Before SQL Server can run a query, it must develop an execution plan, for how it will implement and carry out the retrieval. (The Query Optimizer component of SQL Server does this.) It looks at the sizes of the tables, the available indexes, and esp the index statistics (if any) that are available for those indexes, and makes a decision about how to proceed.

(As a for instance, if you have an index(s) on a small table with only a few rows, the QO might decide to not even bother using those indexes on retrieval, if it 'thinks' that it will be quicker to just scan the table anyway.)

Okay. In the second query, all the information is available for the QO to make a decision. It sees the values in the where clause, knows what indexes and statistics are available, and makes up an execution plan. Probably to use an index on ProdnDate, is my guess.

In the first query, all information is not available, since the value of the variables is at that point unknown. Still, it tries to make up the best execution plan that it can, and this times makes up a different one. Since it doesn't know what to think about the possible incoming variables, it (probably) decides to to a table scan somewhere. (that's my guess)

This can be determined by examining the exeution plans that were used for each query. (See Graphical Execution plans in BOL and Query Analyzer.) Also Set STATISTICS IO ON is another very useful way of determining what each query is doing.

In this situation, you may be able to provide INDEX HINTS to the query, to force your first query to use a particular index. (Although this is not usually recommended, it is a very useful strategy in certain situations like this.)
-------------------------
Okay, there's my guess. If anyone can explain it better, blast away!

bp