Re: SP runs slower than same query in Query analyzer. Why??
From: Ray Higdon (sqlhigdon_at_nospam.yahoo.com)
Date: 02/19/04
- Next message: sharon: "BCP Command do not support every SQL Queries?"
- Previous message: Scott Austin: "Communication Link failure"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 18 Feb 2004 19:48:32 -0500
Good info Andrew
--
Ray Higdon MCSE, MCDBA, CCNA
---
"Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message
news:uYZ5k8c9DHA.3200@TK2MSFTNGP09.phx.gbl...
> 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...
> > > 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.
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "jokes54321" <jokes54321@nospam.com> wrote in message
> > > news:OK0vx7Z9DHA.2404@TK2MSFTNGP11.phx.gbl...
> > > > 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?
> > > >
> > > >
> > >
> > >
> >
> >
>
>
- Next message: sharon: "BCP Command do not support every SQL Queries?"
- Previous message: Scott Austin: "Communication Link failure"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
