Execution penalty depending on column size

Hello!
Yesterday we were reported a problem when querying a particular item in =
a =
table:
SELECT * FROM [Parameter]
WHERE Parameter.ParameterID IN (
SELECT DISTINCT V.ParameterID
FROM Sample AS S
INNER JOIN [Value] AS V ON S.SampleID =3D V.SampleID
INNER JOIN Buoy ON S.BuoyID =3D Buoy.BuoyID
WHERE Buoy.BuoyID IN (2922594)
)
The preceding query times out and never returns a value.
When Buoy.BuoyID equals a lower number (e.g. 470000) it works fine and =
returns the expected result. The set of data associated to each buoy is =
=
similar in size.
A strange thing is that when the execution plan for key 470000 is =
DIFFERENT from the execution plan for 2922594.
Another curious behaviour is that when the query is executed with =
Buoy.BuoyID IN (470000, 2922594) it works fine and with a similar plan t=
o =
the 470000 case.
When we tested with Buoy.BuoyID IN (1922594) that does not exist in the =
=
database, the execution plan was equal to the pathological case.
We replaced the preceding query for
SELECT * FROM [Parameter] P WHERE P.ParameterID IN(
SELECT V.ParameterID
FROM Sample AS S
LEFT JOIN [Value] AS V ON S.SampleID =3D V.SampleID
LEFT JOIN Buoy ON S.BuoyID =3D Buoy.BuoyID
WHERE Buoy.BuoyID IN (2922594)
GROUP BY V.ParameterID
)
which works in all the situations and with improved performance.
Can you help us on what is happening with the first query that executes =
=
fine with all the buoys except that particular one? And why does the =
execution plan changes when the size of the number increases from 470000=
=
to 2922594?
Thanks for your help! Sounds like param sniffing issues. Have a look at this document for more
details:
http://www.microsoft.com/technet/pr...005/recomp.mspx
Andrew J. Kelly SQL MVP
"Gerardo Gonzlez" <ggonzalez@.odyssey.com.uy> wrote in message
news:op.ts85v9dflva8xe@.sapereaude...
Hello!
Yesterday we were reported a problem when querying a particular item in a
table:
SELECT * FROM [Parameter]
WHERE Parameter.ParameterID IN (
SELECT DISTINCT V.ParameterID
FROM Sample AS S
INNER JOIN [Value] AS V ON S.SampleID = V.SampleID
INNER JOIN Buoy ON S.BuoyID = Buoy.BuoyID
WHERE Buoy.BuoyID IN (2922594)
)
The preceding query times out and never returns a value.
When Buoy.BuoyID equals a lower number (e.g. 470000) it works fine and
returns the expected result. The set of data associated to each buoy is
similar in size.
A strange thing is that when the execution plan for key 470000 is
DIFFERENT from the execution plan for 2922594.
Another curious behaviour is that when the query is executed with
Buoy.BuoyID IN (470000, 2922594) it works fine and with a similar plan to
the 470000 case.
When we tested with Buoy.BuoyID IN (1922594) that does not exist in the
database, the execution plan was equal to the pathological case.
We replaced the preceding query for
SELECT * FROM [Parameter] P WHERE P.ParameterID IN(
SELECT V.ParameterID
FROM Sample AS S
LEFT JOIN [Value] AS V ON S.SampleID = V.SampleID
LEFT JOIN Buoy ON S.BuoyID = Buoy.BuoyID
WHERE Buoy.BuoyID IN (2922594)
GROUP BY V.ParameterID
)
which works in all the situations and with improved performance.
Can you help us on what is happening with the first query that executes
fine with all the buoys except that particular one? And why does the
execution plan changes when the size of the number increases from 470000
to 2922594?
Thanks for your help! |||Hello Andrew, I'm Gerardo's partner. I didn't find anything in the paper
that pointed to parameter sniffing as a cause for degraded performance in
a query.
I want to know if it's normal for a query to change its execution plan
based on the values of it's parameters...that's what's happening here, we
have the same query with two wildly differing execution plans depending
only on a single value (that's being matched to a Primary key).
Many thanks for your help,
Pablo
--
I'm glad my Mom was pro-life.
Pablo Montilla
www.odyssey.com.uy|||Not sure if batch queries can be 'forcibly recompiled' for each execution.
If you put the code into a sproc, you CAN do this, using the WITH RECOMPILE
option. This will ensure a fresh query plan is generated for every call and
is a normal action to respond to varying parameter value performance issues.
TheSQLGuru
President
Indicium Resources, Inc.
"Pablo Montilla" <melkor@.odyssey.com.uy> wrote in message
news:op.ts897ujmcj6shk@.chimera.odyssey.com.uy...
> Hello Andrew, I'm Gerardo's partner. I didn't find anything in the paper
> that pointed to parameter sniffing as a cause for degraded performance in
> a query.
> I want to know if it's normal for a query to change its execution plan
> based on the values of it's parameters...that's what's happening here, we
> have the same query with two wildly differing execution plans depending
> only on a single value (that's being matched to a Primary key).
> Many thanks for your help,
> Pablo
> --
>
> I'm glad my Mom was pro-life.
> Pablo Montilla
> www.odyssey.com.uy|||In addition to the other replies: make sure the index statistics are up
to date. When in doubt, update them WITH FULLSCAN.
Gert-Jan
Gerardo Gonzlez wrote:
> Hello!
> Yesterday we were reported a problem when querying a particular item in a
> table:
> SELECT * FROM [Parameter]
> WHERE Parameter.ParameterID IN (
> SELECT DISTINCT V.ParameterID
> FROM Sample AS S
> INNER JOIN [Value] AS V ON S.SampleID = V.SampleID
> INNER JOIN Buoy ON S.BuoyID = Buoy.BuoyID
> WHERE Buoy.BuoyID IN (2922594)
> )
> The preceding query times out and never returns a value.
> When Buoy.BuoyID equals a lower number (e.g. 470000) it works fine and
> returns the expected result. The set of data associated to each buoy is
> similar in size.
> A strange thing is that when the execution plan for key 470000 is
> DIFFERENT from the execution plan for 2922594.
> Another curious behaviour is that when the query is executed with
> Buoy.BuoyID IN (470000, 2922594) it works fine and with a similar plan to
> the 470000 case.
> When we tested with Buoy.BuoyID IN (1922594) that does not exist in the
> database, the execution plan was equal to the pathological case.
> We replaced the preceding query for
> SELECT * FROM [Parameter] P WHERE P.ParameterID IN(
> SELECT V.ParameterID
> FROM Sample AS S
> LEFT JOIN [Value] AS V ON S.SampleID = V.SampleID
> LEFT JOIN Buoy ON S.BuoyID = Buoy.BuoyID
> WHERE Buoy.BuoyID IN (2922594)
> GROUP BY V.ParameterID
> )
> which works in all the situations and with improved performance.
> Can you help us on what is happening with the first query that executes
> fine with all the buoys except that particular one? And why does the
> execution plan changes when the size of the number increases from 470000
> to 2922594?
> Thanks for your help! |||The section on Parameter sniffing makes it clear that the plan is based on
the value you pass to the sp the first time it is run or the first time it
is marked for recompiles. So if one value works best as a scan because it
returns many rows the plan will be bad for one that returns only a few rows
and visa versa. There must be more to this than you say because the
optimizer would know there is only one row that matches a PK and would
produce the same query each time regardless of the value. Is there a PK
constraint on the PK column? Have statistics been disabled for that column
or table? I think we need to see the actual query along with the DDL for
the tables accessed.
Andrew J. Kelly SQL MVP
"Pablo Montilla" <melkor@.odyssey.com.uy> wrote in message
news:op.ts897ujmcj6shk@.chimera.odyssey.com.uy...
> Hello Andrew, I'm Gerardo's partner. I didn't find anything in the paper
> that pointed to parameter sniffing as a cause for degraded performance in
> a query.
> I want to know if it's normal for a query to change its execution plan
> based on the values of it's parameters...that's what's happening here, we
> have the same query with two wildly differing execution plans depending
> only on a single value (that's being matched to a Primary key).
> Many thanks for your help,
> Pablo
> --
>
> I'm glad my Mom was pro-life.
> Pablo Montilla
> www.odyssey.com.uy|||I'll check those things with Gerardo and reply you.
Many thanks for your answer!
Regards,
Pablo
--
I've fallen... and can't BOOT up!
Pablo Montilla
www.odyssey.com.uy