My boss created three identical tables, future, present, past and data lives
in any one of these tables depending on the date. For reporting I created a
view as follows

SELECT * FROM Future
UNION SELECT * FROM present
UNION SELECT * FROM past


When I query this View via the Query Analyzer I get results within 5-10
seconds. When I put this same query in an SP, it takes 1:00 to 1:10 seconds
to run. I'm at a loss as to what SQL is doing here. Any suggestions?

How are you running the actual query? What is the WHERE clause like? By
the way if you are always going to look in all the tables it doesn't make
much sense to split them up unless you actually are going to use partitioned
views.

While I don't agree with the setup we have to do what the boss says. This is
for a dispatching system. The dozens of dispatching screens query against
the Present table, which contains two days of business. Only the reports
need to query all the tables. A scheduled task runs every night that moves
the records from one table to the next.

Here's the query the report uses

SELECT
*

FROM
vu_Trips DT
INNER JOIN
Vehicles V
ON
DT.citycode = v.citycode
AND DT.VanID = v.vanid

WHERE
DT.CityCode = @CityCode
AND dt.estputime BETWEEN @StartDate and @EndDate
AND dt.rezstatus NOT IN ('n', 'x')
AND V.Fleet = @Fleet
AND dt.vanid > 0


5-10 seconds when run via Query Analyzer
1:00-1:10 when executing the SP (with the exact same query) via Query
Analyzer.


"Andrew J. Kelly" <sqlmvpnooospam> wrote in message
news:2752
[..]

Have you recompiled the stored proc? It is possible that you ran the proc
with certain parameters (which saved the optimization plan) and now when you
run it with new params, that old saved optimization plan is not efficient. I
recall from a Kimberly Tripp session:

recompilation = optimization

Try recompiling (not just running) it with the same params that you use in
your view and see if they are closer to the view performance wise.

HTH

I agree with Ray in that you are most likely running two different cached
plans. If the syntax is not exact (even spaces) they will get 2 different
cached plans. The slow one is most likely using a scan while the other is
using a seek. You can compare the two plans to be sure. The text I will
post below is a very good description of something called parameter sniffing
from Bart at MS that you should be aware of. Just a suggestion. Since you
know where the data is depending on the date (estputime) why not query only
the relevant table(s). Maybe have several stored procs where they only
select from the tables that pertain to the date range in question. Your opt
to get better performance overall. All it takes is a little simple logic to
determine which sp to call.



The reason for the performance difference stems from a feature called
"parameter sniffing". Consider a stored proc defined as follows:

CREATE PROC proc1 @p1 int AS
SELECT * FROM table1 WHERE c1 = @p1
GO

Keep in mind that the server has to compile a complete execution plan for
the proc before the proc begins to execute. In 6.5, at compile time SQL
didn't know what the value of @p1 was, so it had to make a lot of guesses
when compiling a plan. Suppose all of the actual parameter values for
"@p1 int" that a user ever passed into this stored proc were unique
integers that were greater than 0, but suppose 40% of the values in
[table1] were, in fact, 0. SQL would use the average density of the
column to estimate the number of rows that this predicate would return;
this would be an overestimate, and SQL would might choose a table scan
over an index seek based on the rowcount estimates. A table scan would
be the best plan if the parameter value was 0, but unfortunately it
happens that users will never or rarely pass @p1=0, so performance of the
stored proc for more typical parameters suffers.

In SQL 7.0 or 2000, suppose you executed this proc for the first time
(when the sp plan is not in cache) with the command "EXEC proc1 @p1 =
10". Parameter sniffing allows SQL to insert the known value of
parameter @p1 into the query at compile time before a plan for the query
is generated. Because SQL knows that the value of @p1 is not 0, it can
compile a plan that is tailored to the class of parameters that is
actually passed into the proc, so for example it might select an index
seek instead of a table scan based on the smaller estimated rowcount --
this is a good thing if most of the time 0 is not the value passed as
@p1. Generally speaking, this feature allows more efficient stored proc
execution plans, but a key requirement for everything to work as expected
is that the parameter values used for compilation be "typical".

In your case, the problem is that you have default NULL values for your
parameters ("@Today DATETIME = NULL, ...") that are not typical because
the parameter values are changed inside the stored proc before they are
used -- as a result NULL will never actually be used to search the
column. If the first execution of this stored proc doesn't pass in an
explicit value for the @Today parameter, SQL believes that its value will
be NULL. When SQL compiles the plan for this sp it substitutes NULL for
each occurrence of @Today that is embedded within a query.
Unfortunately, after execution begins the first thing the stored proc
does is change @Today to a non-NULL value if it is found to be NULL, but
unfortunately SQL doesn't know about this at compile time. Because NULL
is a very atypical parameter value, the plan that SQL generates may not
be a good one for the new value of the parameter that is assigned at
execution time.

So, the bottom line is that if you assign defaults to your sp parameters
and later use those same parameters in a query, the defaults should be
"typical" because they will be used during plan generation. If you must
use defaults and business logic dictates that they be atypical (as may be
the case here if app modifications are not an option), there are two
possible solutions if you determine that the substitution of atypical
parameter values is causing bad plans:

1. "Disable" parameter sniffing by using local DECLARE'd variables that
you SET equal to the parameters inside the stored proc, and use the local
variables instead of the offending parameters in the queries. This is the
solution that you found yourself. SQL can't use parameter sniffing in
this case so it must make some guesses, but in this case the guess based
on average column density is better than the plan based on a specific but
"wrong" parameter value (NULL).

2. Nest the affected queries somehow so that they run within a different
context that will require a distinct execution plan. There are several
possibilities here. for example:
a. Put the affected queries in a different "child" stored proc. If
you execute that stored proc within this one *after* the parameter @Today
has been changed to its final value, parameter sniffing will suddenly
become your friend because the value SQL uses to compile the queries
inside the child stored proc is the actual value that will be used in the
query.
b. Use sp_executesql to execute the affected queries. The plan won't
be generated until the sp_executesql stmt actually runs, which is of
course after the parameter values have been changed.
c. Use dynamic SQL ("EXEC (@sql)") to execute the affected queries.
An equivalent approach would be to put the query in a child stored proc
just like 2.a, but execute it within the parent proc with EXEC WITH
RECOMPILE.

Option #1 seems to have worked well for you in this case, although
sometimes one of the options in #2 is a preferable choice. Here are some
guidelines, although when you're dealing with something as complicated as
the query optimizer experimentation is often the best approach <g>:

- If you have only one "class" (defined as values that have similar
density in the table) of actual parameter value that is used within a
query (even if there are other classes of data in the base table that are
never or rarely searched on), 2.a. or 2.b is probably the best option.
This is because these options permit the actual parameter values to be
used during compilation which should result in the most efficient query
plan for that class of parameter.
- If you have multiple "classes" of parameter value (for example, for
the column being searched, half the table data is NULL, the other half
are unique integers, and you may do searches on either class), 2.c can be
effective. The downside is that a new plan for the query must be
compiled on each execution, but the upside is that the plan will always
be tailored to the parameter value being used for that particular
execution. This is best when there is no single execution plan that
provides acceptable execution time for all classes of parameters.

HTH -
Bart
------------
Bart Duncan
Microsoft SQL Server Support

Please reply to the newsgroup only - thanks.

This posting is provided "AS IS" with no warranties, and confers no
rights.

Good info Andrew

Thanks for this info. You just solved a hot production problem for me!

Ray Higdon wrote:
[..]

Andrew your reply was useful but I'm quite new to sql server and I'
trying to do somethign very similar but am getting a bit lost... I hav
the following query which is basically getting counts of items for
different days. I'm sure there is a better way to do this but thi
query (when I plug in date values) gives me the results in 3-4 seconds
But when I put it in an SP or even straight in my ASP the dates tha
I'm passing in now cause it to take over a minute. Any ideas?

CREATE PROCEDURE [weeklyChannels]
@sDate datetime
AS


declare @StartDate datetime
set @StartDate = @sDate

Begin
select
count1 = (select count(*) from contentsubscription_test wher
feedgroups like '1' and messagePubDate > @StartDate and messagePubDat
< @StartDate + 1),
count2 = (select count(*) from contentsubscription_test wher
feedgroups like '0%' and messagePubDate > @StartDate and messagePubDat
< @StartDate + 1),
count3 = (select count(*) from contentsubscription_test wher
feedgroups like '501%' and messagePubDate > @StartDate an
messagePubDate < @StartDate + 1),
count4 = (select count(*) from contentsubscription_test wher
feedgroups like '502%' and messagePubDate > @StartDate an
messagePubDate < @StartDate + 1),
count5 = (select count(*) from contentsubscription_test wher
feedgroups like '1000%' and messagePubDate > @StartDate an
messagePubDate < @StartDate +1)

union

select
count1 = (select count(*) from contentsubscription_test wher
feedgroups like '1' and messagePubDate > @StartDate + 1 an
messagePubDate < @StartDate + 2),
count2 = (select count(*) from contentsubscription_test wher
feedgroups like '0%' and messagePubDate > @StartDate + 1 an
messagePubDate < @StartDate + 2),
count3 = (select count(*) from contentsubscription_test wher
feedgroups like '501%' and messagePubDate > @StartDate + 1 an
messagePubDate < @StartDate + 2),
count4 = (select count(*) from contentsubscription_test wher
feedgroups like '502%' and messagePubDate > @StartDate + 1 an
messagePubDate < @StartDate + 2),
count5 = (select count(*) from contentsubscription_test wher
feedgroups like '1000%' and messagePubDate > @StartDate + 1 an
messagePubDate < @StartDate + 2)

union

select
count1 = (select count(*) from contentsubscription_test wher
feedgroups like '1' and messagePubDate > @StartDate + 2 an
messagePubDate < @StartDate + 3),
count2 = (select count(*) from contentsubscription_test wher
feedgroups like '0%' and messagePubDate > @StartDate +2 an
messagePubDate < @StartDate + 3),
count3 = (select count(*) from contentsubscription_test wher
feedgroups like '501%' and messagePubDate > @StartDate +2 an
messagePubDate < @StartDate + 3 ),
count4 = (select count(*) from contentsubscription_test wher
feedgroups like '502%' and messagePubDate > @StartDate + 2 an
messagePubDate < @StartDate + 3),
count5 = (select count(*) from contentsubscription_test wher
feedgroups like '1000%' and messagePubDate > @StartDate + 2 an
messagePubDate < @StartDate + 3)

union

select
count1 = (select count(*) from contentsubscription_test wher
feedgroups like '1' and messagePubDate > @StartDate + 3 an
messagePubDate < @StartDate + 4),
count2 = (select count(*) from contentsubscription_test wher
feedgroups like '0%' and messagePubDate > @StartDate + 3 an
messagePubDate < @StartDate + 4),
count3 = (select count(*) from contentsubscription_test wher
feedgroups like '501%' and messagePubDate > @StartDate + 3 an
messagePubDate < @StartDate + 4),
count4 = (select count(*) from contentsubscription_test wher
feedgroups like '502%' and messagePubDate > @StartDate + 3 an
messagePubDate < @StartDate + 4),
count5 = (select count(*) from contentsubscription_test wher
feedgroups like '1000%' and messagePubDate > @StartDate + 3 an
messagePubDate < @StartDate + 4)

union

select
count1 = (select count(*) from contentsubscription_test wher
feedgroups like '1' and messagePubDate > @StartDate + 4 an
messagePubDate < @StartDate + 5),
count2 = (select count(*) from contentsubscription_test wher
feedgroups like '0%' and messagePubDate > @StartDate + 4 and
messagePubDate < @StartDate + 5),
count3 = (select count(*) from contentsubscription_test where
feedgroups like '501%' and messagePubDate > @StartDate + 4 and
messagePubDate < @StartDate + 5),
count4 = (select count(*) from contentsubscription_test where
feedgroups like '502%' and messagePubDate > @StartDate + 4 and
messagePubDate < @StartDate + 5),
count5 = (select count(*) from contentsubscription_test where
feedgroups like '1000%' and messagePubDate > @StartDate + 4 and
messagePubDate < @StartDate + 5)

union

select
count1 = (select count(*) from contentsubscription_test where
feedgroups like '1' and messagePubDate > @StartDate + 5 and
messagePubDate < @StartDate + 6),
count2 = (select count(*) from contentsubscription_test where
feedgroups like '0%' and messagePubDate > @StartDate + 5 and
messagePubDate < @StartDate + 6),
count3 = (select count(*) from contentsubscription_test where
feedgroups like '501%' and messagePubDate > @StartDate + 5 and
messagePubDate < @StartDate + 6),
count4 = (select count(*) from contentsubscription_test where
feedgroups like '502%' and messagePubDate > @StartDate + 5 and
messagePubDate < @StartDate + 6),
count5 = (select count(*) from contentsubscription_test where
feedgroups like '1000%' and messagePubDate > @StartDate + 5 and
messagePubDate < @StartDate + 6)

union

select
count1 = (select count(*) from contentsubscription_test where
feedgroups like '1' and messagePubDate > @StartDate + 6 and
messagePubDate < @StartDate + 7),
count2 = (select count(*) from contentsubscription_test where
feedgroups like '0%' and messagePubDate > @StartDate + 6 and
messagePubDate < @StartDate + 7),
count3 = (select count(*) from contentsubscription_test where
feedgroups like '501%' and messagePubDate > @StartDate + 6 and
messagePubDate < @StartDate + 7),
count4 = (select count(*) from contentsubscription_test where
feedgroups like '502%' and messagePubDate > @StartDate + 6 and
messagePubDate < @StartDate + 7),
count5 = (select count(*) from contentsubscription_test where
feedgroups like '1000%' and messagePubDate > @StartDate + 6 and
messagePubDate < @StartDate + 7)
End
GO






Andrew J. Kelly wrote:
[..]