I'm having a problem with a query where it is not doing
what I would like for it to do. I have a Call table
where helpdesk calls get logged. I then have a Severity
table that stores the severity level of the call. Each
call must have a severity of 1,2 or 3.
I want to query the database and return Call counts for
each of the severity (1,2 and 3) given a date period.
For example, in the month of January, there were a total
of 2 severity 1 calls, 25 severity 2, and 50 severity 3.
My output would look like this.
Severity CallCount
-- --
1 2
2 25
3 50
Now, for the month of February, say there were zero(0)
severity 1 calls, 30 severity 2, and 77 severity 3, I
want my output to look like this
Severity CallCount
-- --
1 0
2 30
3 77
....However, my problem is that I do not get a record
for severity 1 cuz there are no calls for that month.
My query is below. What am I doing wrong? There is no
relationship between the two tables. The Severity table
just stores detail information regarding each of the
severities such as required response time and repair time
to fix a problem based on the severity. Please help.
SELECT
Severity.Severity,
Severity.ResponseTime,
Severity.RepairTime,
Count(Call.CallNo) As CallCount
FROM
Severity
Left Join Call
On Severity.Severity = Call.Severity
WHERE
Call.DateTimeSubmitted Between '01/01/2004'
and '01/31/2004'
AND
Call.Status <> 'CANCELLED'
GROUP BY
Severity.Severity,
Severity.ResponseTime,
Severity.RepairTime,
Order By
Severity.Severity
TIA,
VicPut Your WHERE criteria into the ON clause:
SELECT S.severity, S.responsetime, S.repairtime, COUNT(C.callno) AS
callcount
FROM Severity AS S
LEFT JOIN Call AS C
ON S.Severity = C.Severity
AND C.datetimesubmitted >= '20040101'
AND C.datetimesubmitted < '20050101'
AND C.status <> 'CANCELLED'
GROUP BY S.severity, S.responsetime, S.repairtime
ORDER BY S.Severity
As above, you may want to use >= and < for the date range rather than
BETWEEN. The code you posted will exclude rows where call was submitted on
2004-12-31 but the datetimesubmitted was timed after midnight.
Hope this helps.
David Portas
SQL Server MVP
--|||REPOST: I misread the dates
Put Your WHERE criteria into the ON clause:
SELECT S.severity, S.responsetime, S.repairtime, COUNT(C.callno) AS
callcount
FROM Severity AS S
LEFT JOIN Call AS C
ON S.Severity = C.Severity
AND C.datetimesubmitted >= '20040101'
AND C.datetimesubmitted < '20040201'
AND C.status <> 'CANCELLED'
GROUP BY S.severity, S.responsetime, S.repairtime
ORDER BY S.Severity
As above, you may want to use >= and < for the date range rather than
BETWEEN. The code you posted will exclude rows where call was submitted on
2004-01-31 but the datetimesubmitted was timed after midnight.
Hope this helps.
David Portas
SQL Server MVP
--
David Portas
SQL Server MVP
--
"Vic" <vduran@.specpro-inc.com> wrote in message
news:91cb01c4332b$51b42fb0$a301280a@.phx.gbl...
> I'm having a problem with a query where it is not doing
> what I would like for it to do. I have a Call table
> where helpdesk calls get logged. I then have a Severity
> table that stores the severity level of the call. Each
> call must have a severity of 1,2 or 3.
> I want to query the database and return Call counts for
> each of the severity (1,2 and 3) given a date period.
> For example, in the month of January, there were a total
> of 2 severity 1 calls, 25 severity 2, and 50 severity 3.
> My output would look like this.
> Severity CallCount
> -- --
> 1 2
> 2 25
> 3 50
>
> Now, for the month of February, say there were zero(0)
> severity 1 calls, 30 severity 2, and 77 severity 3, I
> want my output to look like this
> Severity CallCount
> -- --
> 1 0
> 2 30
> 3 77
> ....However, my problem is that I do not get a record
> for severity 1 cuz there are no calls for that month.
> My query is below. What am I doing wrong? There is no
> relationship between the two tables. The Severity table
> just stores detail information regarding each of the
> severities such as required response time and repair time
> to fix a problem based on the severity. Please help.
> SELECT
> Severity.Severity,
> Severity.ResponseTime,
> Severity.RepairTime,
> Count(Call.CallNo) As CallCount
> FROM
> Severity
> Left Join Call
> On Severity.Severity = Call.Severity
> WHERE
> Call.DateTimeSubmitted Between '01/01/2004'
> and '01/31/2004'
> AND
> Call.Status <> 'CANCELLED'
> GROUP BY
> Severity.Severity,
> Severity.ResponseTime,
> Severity.RepairTime,
> Order By
> Severity.Severity
> TIA,
> Vic
Showing posts with label call. Show all posts
Showing posts with label call. Show all posts
Friday, March 30, 2012
Monday, March 12, 2012
Query optimizer issue
Hi,
I have the following problem:
When I call a stored procedure from a COM+ application I get a different query plan than the one I get when calling the same stored procedure within Query Analyzer. The interesting side effect of it is that the COM+ application runs faster as I expected,
since the query takes much longer to execute in Query Analyzer.
I have noticed that the query plan differs, when I call my SP from COM+. Does anybody know why? Are there any magic/secrets flags, that COM+ sets?
One of the cases is the fact that when I run the query in QA, the plan contains a lookup WITH PREFETCH, which takes longer than without prefetch (COM+) case.
I don't know what can be the factor that causes two different query plans for the same stored procedure...
On the other hand, I use an index hint in one of my queries and I noticed that if I don't use the hint, when running in QA the optimizer will use the wrong index, but when the same SP is called from my COM+ app, the optimizer chooses the right index even
if I don't use the hint.
Does anyone have any ideas, thoughts, hints on this?
I'm really confused... :-)
Thanks in advance,
Florin
A lot can depend on how you call it and how the parameters are interpreted.
This blurb from Bart at MS does a good job of explaining how things like
this can occur.
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 MVP
"fmicle" <fmicle@.hotmail.com> wrote in message
news:5BC62C0B-98E3-4415-A6C8-48A704CFD80B@.microsoft.com...
> Hi,
> I have the following problem:
> When I call a stored procedure from a COM+ application I get a different
query plan than the one I get when calling the same stored procedure within
Query Analyzer. The interesting side effect of it is that the COM+
application runs faster as I expected, since the query takes much longer to
execute in Query Analyzer.
> I have noticed that the query plan differs, when I call my SP from COM+.
Does anybody know why? Are there any magic/secrets flags, that COM+ sets?
> One of the cases is the fact that when I run the query in QA, the plan
contains a lookup WITH PREFETCH, which takes longer than without prefetch
(COM+) case.
> I don't know what can be the factor that causes two different query plans
for the same stored procedure...
> On the other hand, I use an index hint in one of my queries and I noticed
that if I don't use the hint, when running in QA the optimizer will use the
wrong index, but when the same SP is called from my COM+ app, the optimizer
chooses the right index even if I don't use the hint.
> Does anyone have any ideas, thoughts, hints on this?
> I'm really confused... :-)
> Thanks in advance,
> Florin
I have the following problem:
When I call a stored procedure from a COM+ application I get a different query plan than the one I get when calling the same stored procedure within Query Analyzer. The interesting side effect of it is that the COM+ application runs faster as I expected,
since the query takes much longer to execute in Query Analyzer.
I have noticed that the query plan differs, when I call my SP from COM+. Does anybody know why? Are there any magic/secrets flags, that COM+ sets?
One of the cases is the fact that when I run the query in QA, the plan contains a lookup WITH PREFETCH, which takes longer than without prefetch (COM+) case.
I don't know what can be the factor that causes two different query plans for the same stored procedure...
On the other hand, I use an index hint in one of my queries and I noticed that if I don't use the hint, when running in QA the optimizer will use the wrong index, but when the same SP is called from my COM+ app, the optimizer chooses the right index even
if I don't use the hint.
Does anyone have any ideas, thoughts, hints on this?
I'm really confused... :-)
Thanks in advance,
Florin
A lot can depend on how you call it and how the parameters are interpreted.
This blurb from Bart at MS does a good job of explaining how things like
this can occur.
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 MVP
"fmicle" <fmicle@.hotmail.com> wrote in message
news:5BC62C0B-98E3-4415-A6C8-48A704CFD80B@.microsoft.com...
> Hi,
> I have the following problem:
> When I call a stored procedure from a COM+ application I get a different
query plan than the one I get when calling the same stored procedure within
Query Analyzer. The interesting side effect of it is that the COM+
application runs faster as I expected, since the query takes much longer to
execute in Query Analyzer.
> I have noticed that the query plan differs, when I call my SP from COM+.
Does anybody know why? Are there any magic/secrets flags, that COM+ sets?
> One of the cases is the fact that when I run the query in QA, the plan
contains a lookup WITH PREFETCH, which takes longer than without prefetch
(COM+) case.
> I don't know what can be the factor that causes two different query plans
for the same stored procedure...
> On the other hand, I use an index hint in one of my queries and I noticed
that if I don't use the hint, when running in QA the optimizer will use the
wrong index, but when the same SP is called from my COM+ app, the optimizer
chooses the right index even if I don't use the hint.
> Does anyone have any ideas, thoughts, hints on this?
> I'm really confused... :-)
> Thanks in advance,
> Florin
Friday, March 9, 2012
Query optimizer issue
Hi,
I have the following problem:
When I call a stored procedure from a COM+ application I get a different que
ry plan than the one I get when calling the same stored procedure within Que
ry Analyzer. The interesting side effect of it is that the COM+ application
runs faster as I expected,
since the query takes much longer to execute in Query Analyzer.
I have noticed that the query plan differs, when I call my SP from COM+. Doe
s anybody know why? Are there any magic/secrets flags, that COM+ sets?
One of the cases is the fact that when I run the query in QA, the plan conta
ins a lookup WITH PREFETCH, which takes longer than without prefetch (COM+)
case.
I don't know what can be the factor that causes two different query plans fo
r the same stored procedure...
On the other hand, I use an index hint in one of my queries and I noticed th
at if I don't use the hint, when running in QA the optimizer will use the wr
ong index, but when the same SP is called from my COM+ app, the optimizer ch
ooses the right index even
if I don't use the hint.
Does anyone have any ideas, thoughts, hints on this?
I'm really confused... :-)
Thanks in advance,
FlorinA lot can depend on how you call it and how the parameters are interpreted.
This blurb from Bart at MS does a good job of explaining how things like
this can occur.
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 MVP
"fmicle" <fmicle@.hotmail.com> wrote in message
news:5BC62C0B-98E3-4415-A6C8-48A704CFD80B@.microsoft.com...
> Hi,
> I have the following problem:
> When I call a stored procedure from a COM+ application I get a different
query plan than the one I get when calling the same stored procedure within
Query Analyzer. The interesting side effect of it is that the COM+
application runs faster as I expected, since the query takes much longer to
execute in Query Analyzer.
> I have noticed that the query plan differs, when I call my SP from COM+.
Does anybody know why? Are there any magic/secrets flags, that COM+ sets?
> One of the cases is the fact that when I run the query in QA, the plan
contains a lookup WITH PREFETCH, which takes longer than without prefetch
(COM+) case.
> I don't know what can be the factor that causes two different query plans
for the same stored procedure...
> On the other hand, I use an index hint in one of my queries and I noticed
that if I don't use the hint, when running in QA the optimizer will use the
wrong index, but when the same SP is called from my COM+ app, the optimizer
chooses the right index even if I don't use the hint.
> Does anyone have any ideas, thoughts, hints on this?
> I'm really confused... :-)
> Thanks in advance,
> Florin
I have the following problem:
When I call a stored procedure from a COM+ application I get a different que
ry plan than the one I get when calling the same stored procedure within Que
ry Analyzer. The interesting side effect of it is that the COM+ application
runs faster as I expected,
since the query takes much longer to execute in Query Analyzer.
I have noticed that the query plan differs, when I call my SP from COM+. Doe
s anybody know why? Are there any magic/secrets flags, that COM+ sets?
One of the cases is the fact that when I run the query in QA, the plan conta
ins a lookup WITH PREFETCH, which takes longer than without prefetch (COM+)
case.
I don't know what can be the factor that causes two different query plans fo
r the same stored procedure...
On the other hand, I use an index hint in one of my queries and I noticed th
at if I don't use the hint, when running in QA the optimizer will use the wr
ong index, but when the same SP is called from my COM+ app, the optimizer ch
ooses the right index even
if I don't use the hint.
Does anyone have any ideas, thoughts, hints on this?
I'm really confused... :-)
Thanks in advance,
FlorinA lot can depend on how you call it and how the parameters are interpreted.
This blurb from Bart at MS does a good job of explaining how things like
this can occur.
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 MVP
"fmicle" <fmicle@.hotmail.com> wrote in message
news:5BC62C0B-98E3-4415-A6C8-48A704CFD80B@.microsoft.com...
> Hi,
> I have the following problem:
> When I call a stored procedure from a COM+ application I get a different
query plan than the one I get when calling the same stored procedure within
Query Analyzer. The interesting side effect of it is that the COM+
application runs faster as I expected, since the query takes much longer to
execute in Query Analyzer.
> I have noticed that the query plan differs, when I call my SP from COM+.
Does anybody know why? Are there any magic/secrets flags, that COM+ sets?
> One of the cases is the fact that when I run the query in QA, the plan
contains a lookup WITH PREFETCH, which takes longer than without prefetch
(COM+) case.
> I don't know what can be the factor that causes two different query plans
for the same stored procedure...
> On the other hand, I use an index hint in one of my queries and I noticed
that if I don't use the hint, when running in QA the optimizer will use the
wrong index, but when the same SP is called from my COM+ app, the optimizer
chooses the right index even if I don't use the hint.
> Does anyone have any ideas, thoughts, hints on this?
> I'm really confused... :-)
> Thanks in advance,
> Florin
Query optimizer issue
Hi
I have the following problem
When I call a stored procedure from a COM+ application I get a different query plan than the one I get when calling the same stored procedure within Query Analyzer. The interesting side effect of it is that the COM+ application runs faster as I expected, since the query takes much longer to execute in Query Analyzer
I have noticed that the query plan differs, when I call my SP from COM+. Does anybody know why? Are there any magic/secrets flags, that COM+ sets
One of the cases is the fact that when I run the query in QA, the plan contains a lookup WITH PREFETCH, which takes longer than without prefetch (COM+) case
I don't know what can be the factor that causes two different query plans for the same stored procedure..
On the other hand, I use an index hint in one of my queries and I noticed that if I don't use the hint, when running in QA the optimizer will use the wrong index, but when the same SP is called from my COM+ app, the optimizer chooses the right index even if I don't use the hint
Does anyone have any ideas, thoughts, hints on this
I'm really confused... :-
Thanks in advance
FlorinA lot can depend on how you call it and how the parameters are interpreted.
This blurb from Bart at MS does a good job of explaining how things like
this can occur.
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 MVP
"fmicle" <fmicle@.hotmail.com> wrote in message
news:5BC62C0B-98E3-4415-A6C8-48A704CFD80B@.microsoft.com...
> Hi,
> I have the following problem:
> When I call a stored procedure from a COM+ application I get a different
query plan than the one I get when calling the same stored procedure within
Query Analyzer. The interesting side effect of it is that the COM+
application runs faster as I expected, since the query takes much longer to
execute in Query Analyzer.
> I have noticed that the query plan differs, when I call my SP from COM+.
Does anybody know why? Are there any magic/secrets flags, that COM+ sets?
> One of the cases is the fact that when I run the query in QA, the plan
contains a lookup WITH PREFETCH, which takes longer than without prefetch
(COM+) case.
> I don't know what can be the factor that causes two different query plans
for the same stored procedure...
> On the other hand, I use an index hint in one of my queries and I noticed
that if I don't use the hint, when running in QA the optimizer will use the
wrong index, but when the same SP is called from my COM+ app, the optimizer
chooses the right index even if I don't use the hint.
> Does anyone have any ideas, thoughts, hints on this?
> I'm really confused... :-)
> Thanks in advance,
> Florin
I have the following problem
When I call a stored procedure from a COM+ application I get a different query plan than the one I get when calling the same stored procedure within Query Analyzer. The interesting side effect of it is that the COM+ application runs faster as I expected, since the query takes much longer to execute in Query Analyzer
I have noticed that the query plan differs, when I call my SP from COM+. Does anybody know why? Are there any magic/secrets flags, that COM+ sets
One of the cases is the fact that when I run the query in QA, the plan contains a lookup WITH PREFETCH, which takes longer than without prefetch (COM+) case
I don't know what can be the factor that causes two different query plans for the same stored procedure..
On the other hand, I use an index hint in one of my queries and I noticed that if I don't use the hint, when running in QA the optimizer will use the wrong index, but when the same SP is called from my COM+ app, the optimizer chooses the right index even if I don't use the hint
Does anyone have any ideas, thoughts, hints on this
I'm really confused... :-
Thanks in advance
FlorinA lot can depend on how you call it and how the parameters are interpreted.
This blurb from Bart at MS does a good job of explaining how things like
this can occur.
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 MVP
"fmicle" <fmicle@.hotmail.com> wrote in message
news:5BC62C0B-98E3-4415-A6C8-48A704CFD80B@.microsoft.com...
> Hi,
> I have the following problem:
> When I call a stored procedure from a COM+ application I get a different
query plan than the one I get when calling the same stored procedure within
Query Analyzer. The interesting side effect of it is that the COM+
application runs faster as I expected, since the query takes much longer to
execute in Query Analyzer.
> I have noticed that the query plan differs, when I call my SP from COM+.
Does anybody know why? Are there any magic/secrets flags, that COM+ sets?
> One of the cases is the fact that when I run the query in QA, the plan
contains a lookup WITH PREFETCH, which takes longer than without prefetch
(COM+) case.
> I don't know what can be the factor that causes two different query plans
for the same stored procedure...
> On the other hand, I use an index hint in one of my queries and I noticed
that if I don't use the hint, when running in QA the optimizer will use the
wrong index, but when the same SP is called from my COM+ app, the optimizer
chooses the right index even if I don't use the hint.
> Does anyone have any ideas, thoughts, hints on this?
> I'm really confused... :-)
> Thanks in advance,
> Florin
Query Optimization
Question regarding performance.
If I have a query such as:
Select UserId, Firstname,lastname from members where country='can'
If I were to call this from an .net executable as straight SQL to the Database vs. encapsulating the command in a stored procedure and calling the procedure.
What would be the performance differences? Would their be any issues (outside of security) that would make me choose to place the call in a Procedure?
Thanks
It probably doesn't matter in this case. Executing the SELECT statement using a parameterized command object will provide same benefit as calling SP with slighly more overhead. RPC execution for SP calls provide better performance than sending the entire SQL text. The answer is that it depends on your needs. Changing SQL statements embedded in applications is often harder than modifyin a SP. You can also take a look at the whitepaper below for more information:
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
Subscribe to:
Posts (Atom)