Re: SP runs slower than same query in Query analyzer. Why??
From: hwadhwa (hwadhwa.18gkka_at_mail.mcse.ms)
Date: 06/25/04
- Next message: Mike Epprecht [SQL Server MVP]: "Re: SQL client network utility"
- Previous message: MajorTom: "SQL client network utility"
- Maybe in reply to: eknapp: "Re: SP runs slower than same query in Query analyzer. Why??"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 25 Jun 2004 16:23:41 -0500
Andrew your reply was useful but I'm quite new to sql server and I'm
trying to do somethign very similar but am getting a bit lost... I have
the following query which is basically getting counts of items for 7
different days. I'm sure there is a better way to do this but this
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 that
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 where
feedgroups like '1' and messagePubDate > @StartDate and messagePubDate
< @StartDate + 1),
count2 = (select count(*) from contentsubscription_test where
feedgroups like '0%' and messagePubDate > @StartDate and messagePubDate
< @StartDate + 1),
count3 = (select count(*) from contentsubscription_test where
feedgroups like '501%' and messagePubDate > @StartDate and
messagePubDate < @StartDate + 1),
count4 = (select count(*) from contentsubscription_test where
feedgroups like '502%' and messagePubDate > @StartDate and
messagePubDate < @StartDate + 1),
count5 = (select count(*) from contentsubscription_test where
feedgroups like '1000%' and messagePubDate > @StartDate and
messagePubDate < @StartDate +1)
union
select
count1 = (select count(*) from contentsubscription_test where
feedgroups like '1' and messagePubDate > @StartDate + 1 and
messagePubDate < @StartDate + 2),
count2 = (select count(*) from contentsubscription_test where
feedgroups like '0%' and messagePubDate > @StartDate + 1 and
messagePubDate < @StartDate + 2),
count3 = (select count(*) from contentsubscription_test where
feedgroups like '501%' and messagePubDate > @StartDate + 1 and
messagePubDate < @StartDate + 2),
count4 = (select count(*) from contentsubscription_test where
feedgroups like '502%' and messagePubDate > @StartDate + 1 and
messagePubDate < @StartDate + 2),
count5 = (select count(*) from contentsubscription_test where
feedgroups like '1000%' and messagePubDate > @StartDate + 1 and
messagePubDate < @StartDate + 2)
union
select
count1 = (select count(*) from contentsubscription_test where
feedgroups like '1' and messagePubDate > @StartDate + 2 and
messagePubDate < @StartDate + 3),
count2 = (select count(*) from contentsubscription_test where
feedgroups like '0%' and messagePubDate > @StartDate +2 and
messagePubDate < @StartDate + 3),
count3 = (select count(*) from contentsubscription_test where
feedgroups like '501%' and messagePubDate > @StartDate +2 and
messagePubDate < @StartDate + 3 ),
count4 = (select count(*) from contentsubscription_test where
feedgroups like '502%' and messagePubDate > @StartDate + 2 and
messagePubDate < @StartDate + 3),
count5 = (select count(*) from contentsubscription_test where
feedgroups like '1000%' and messagePubDate > @StartDate + 2 and
messagePubDate < @StartDate + 3)
union
select
count1 = (select count(*) from contentsubscription_test where
feedgroups like '1' and messagePubDate > @StartDate + 3 and
messagePubDate < @StartDate + 4),
count2 = (select count(*) from contentsubscription_test where
feedgroups like '0%' and messagePubDate > @StartDate + 3 and
messagePubDate < @StartDate + 4),
count3 = (select count(*) from contentsubscription_test where
feedgroups like '501%' and messagePubDate > @StartDate + 3 and
messagePubDate < @StartDate + 4),
count4 = (select count(*) from contentsubscription_test where
feedgroups like '502%' and messagePubDate > @StartDate + 3 and
messagePubDate < @StartDate + 4),
count5 = (select count(*) from contentsubscription_test where
feedgroups like '1000%' and messagePubDate > @StartDate + 3 and
messagePubDate < @StartDate + 4)
union
select
count1 = (select count(*) from contentsubscription_test where
feedgroups like '1' and messagePubDate > @StartDate + 4 and
messagePubDate < @StartDate + 5),
count2 = (select count(*) from contentsubscription_test where
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:
> *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 [c1] 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.
>
>
>
> --
>
> Andrew J. Kelly
> SQL Server MVP
>
>
> "jokes54321" <jokes54321@nospam.com> wrote in message
> news:Od$0FTb9DHA.2672@TK2MSFTNGP10.phx.gbl...
> > 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@shadhawk.com> wrote in message
> > news:eakMcXa9DHA.2752@TK2MSFTNGP09.phx.gbl...
> By[color=blue]
> make[color=blue]
> > partitioned
> > created
> 5-10[color=blue]
> >
> > *
-- hwadhwa ------------------------------------------------------------------------ Posted via http://www.mcse.ms ------------------------------------------------------------------------ View this thread: http://www.mcse.ms/message402647.html
- Next message: Mike Epprecht [SQL Server MVP]: "Re: SQL client network utility"
- Previous message: MajorTom: "SQL client network utility"
- Maybe in reply to: eknapp: "Re: SP runs slower than same query in Query analyzer. Why??"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
