Query Plans differs

Why shoudl the query plan derived by an SP and one on Query Analyser would
differ widely?
I have a system that has been running stably for a while now when one of the
queries started behaving badly. The SP version of the query comes out with
an estimated cost of 0.311 whereas if I take the same TSQL and paste it into
Query Analyser it estimates as 0.153.
Unfortuately, the query plan cost isn't the whole story as when estimated
the SP performs about 5m page reads whereas the explicit code only takes 800
page reads - this makes a big difference in execution time :-)
Any ideas how to solve this - I've tried WITH RECOMPILE on the SP, but it
doesn't effect the QP choosen.
Regards
PaulPaul,
The two pieces of code, TSQL script and stored procedure, are not optimized
the same because of the knowledge available at the time of optimization.
For example, the TSQL script has the variables set with the values that you
want to run.
Here is one thing that causes stored procedures to be less than optimal:
If internally to a stored proc you set a variable, the optimizer (which has
no value for the variable when it optimizes) may have selected a suboptimal
plan. So, if this is your case, rather than:
DECLARE @.MyVal INT
SET @.MyVal = 123
SELECT ... WHERE MyCol = @.MyVal
You could do:
DECLARE @.MyVal INT
SET @.MyVal = 123
EXEC AnotherStoredProcedure @.MyVal
The called stored procedure will optimize with the value supplied in @.MyVal.
Remember, of course, that recompiles of stored procedures are common, so
this is not as simple as black and white.
And, of course, there are other factors that could mislead the optimizer.
RLF
"Paul Hatcher" <phatcher@.spamless.cix.co.uk> wrote in message
news:uU1mWm$HFHA.1580@.TK2MSFTNGP10.phx.gbl...
> Why shoudl the query plan derived by an SP and one on Query Analyser would
> differ widely?
> I have a system that has been running stably for a while now when one of
> the
> queries started behaving badly. The SP version of the query comes out with
> an estimated cost of 0.311 whereas if I take the same TSQL and paste it
> into
> Query Analyser it estimates as 0.153.
> Unfortuately, the query plan cost isn't the whole story as when estimated
> the SP performs about 5m page reads whereas the explicit code only takes
> 800
> page reads - this makes a big difference in execution time :-)
> Any ideas how to solve this - I've tried WITH RECOMPILE on the SP, but it
> doesn't effect the QP choosen.
> Regards
> Paul
>|||Thanks, there was one such variable, so I changed the code as per your
suggestion - reduced it from 5m reads to around 4.5m :-)
The optimiser normally does such a good job that it's just a pain when it
gets it wrong. Given the nature of this query, it's almost impossible to put
any useful hints that wouldn't cause much more pain elsewhere in the system.
I'll have to see if I can program around it with a different query just to
get it back running - previously it took around 100ms now it takes 10s+
Paul
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:#u#Rlw$HFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Paul,
> The two pieces of code, TSQL script and stored procedure, are not
optimized
> the same because of the knowledge available at the time of optimization.
> For example, the TSQL script has the variables set with the values that
you
> want to run.
> Here is one thing that causes stored procedures to be less than optimal:
> If internally to a stored proc you set a variable, the optimizer (which
has
> no value for the variable when it optimizes) may have selected a
suboptimal
> plan. So, if this is your case, rather than:
> DECLARE @.MyVal INT
> SET @.MyVal = 123
> SELECT ... WHERE MyCol = @.MyVal
> You could do:
> DECLARE @.MyVal INT
> SET @.MyVal = 123
> EXEC AnotherStoredProcedure @.MyVal
> The called stored procedure will optimize with the value supplied in
@.MyVal.
> Remember, of course, that recompiles of stored procedures are common, so
> this is not as simple as black and white.
> And, of course, there are other factors that could mislead the optimizer.
> RLF
> "Paul Hatcher" <phatcher@.spamless.cix.co.uk> wrote in message
> news:uU1mWm$HFHA.1580@.TK2MSFTNGP10.phx.gbl...
would
with
estimated
it
>|||Paul,
Maybe if you post the query in question (accompanied with the relevant
DDL) someone might be able to give a more specific advice...
Gert-Jan
Paul Hatcher wrote:
> Thanks, there was one such variable, so I changed the code as per your
> suggestion - reduced it from 5m reads to around 4.5m :-)
> The optimiser normally does such a good job that it's just a pain when it
> gets it wrong. Given the nature of this query, it's almost impossible to p
ut
> any useful hints that wouldn't cause much more pain elsewhere in the syste
m.
> I'll have to see if I can program around it with a different query just to
> get it back running - previously it took around 100ms now it takes 10s+
> Paul
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:#u#Rlw$HFHA.2924@.TK2MSFTNGP15.phx.gbl...
> optimized
> you
> has
> suboptimal
> @.MyVal.
> would
> with
> estimated
> it|||Gert-Jan
Thanks for the offer, but it's too complicated - as an example I would guess
that the query pans out to about a 30 table join, mostly self-joins as it's
a triple-based schema, but still fairly hairy.
I've now managed to solve it by a different structured query.
Paul
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:422766C1.A0FE26A1@.toomuchspamalready.nl...
> Paul,
> Maybe if you post the query in question (accompanied with the relevant
> DDL) someone might be able to give a more specific advice...
> Gert-Jan
>
> Paul Hatcher wrote:
it
put
system.
to
optimization.
that
optimal:
(which
so
optimizer.
one of
out
it
takes
butsql

Query Performance Question

I have a query that takes approximately 2 mins to run. The query is derived from two tables that have a one to many relationship. When I comment out 4 of the fields in the 'select' portion of the query, it speeds up to 18 seconds. I know that I am doin
g a bookmark lookup (this is 60% of the query time) but I am not sure how to avoid this. Any ideas?
Hi,
The covering index is only effective when the query
does a Bookmark lookup. if you have covering index the query will not hit
the source table for data. but , the disadvantage is that a covering index
increases the
number of writes when one or more of the columns in the index is updated.
This might be a issue durng insert./update and delete.
Thanks
Hari
MCDBA
"Daniel Avsec" <Daniel Avsec@.discussions.microsoft.com> wrote in message
news:1611776E-29E8-4F46-BB8F-97A8E6A32C45@.microsoft.com...
> I have a query that takes approximately 2 mins to run. The query is
derived from two tables that have a one to many relationship. When I
comment out 4 of the fields in the 'select' portion of the query, it speeds
up to 18 seconds. I know that I am doing a bookmark lookup (this is 60% of
the query time) but I am not sure how to avoid this. Any ideas?

Query Performance Question

I have a query that takes approximately 2 mins to run. The query is derived from two tables that have a one to many relationship. When I comment out 4 of the fields in the 'select' portion of the query, it speeds up to 18 seconds. I know that I am doing a bookmark lookup (this is 60% of the query time) but I am not sure how to avoid this. Any ideas?Hi,
The covering index is only effective when the query
does a Bookmark lookup. if you have covering index the query will not hit
the source table for data. but , the disadvantage is that a covering index
increases the
number of writes when one or more of the columns in the index is updated.
This might be a issue durng insert./update and delete.
Thanks
Hari
MCDBA
"Daniel Avsec" <Daniel Avsec@.discussions.microsoft.com> wrote in message
news:1611776E-29E8-4F46-BB8F-97A8E6A32C45@.microsoft.com...
> I have a query that takes approximately 2 mins to run. The query is
derived from two tables that have a one to many relationship. When I
comment out 4 of the fields in the 'select' portion of the query, it speeds
up to 18 seconds. I know that I am doing a bookmark lookup (this is 60% of
the query time) but I am not sure how to avoid this. Any ideas?

Query Performance Question

I have a query that takes approximately 2 mins to run. The query is derived
from two tables that have a one to many relationship. When I comment out 4
of the fields in the 'select' portion of the query, it speeds up to 18 seco
nds. I know that I am doin
g a bookmark lookup (this is 60% of the query time) but I am not sure how to
avoid this. Any ideas?Hi,
The covering index is only effective when the query
does a Bookmark lookup. if you have covering index the query will not hit
the source table for data. but , the disadvantage is that a covering index
increases the
number of writes when one or more of the columns in the index is updated.
This might be a issue durng insert./update and delete.
Thanks
Hari
MCDBA
"Daniel Avsec" <Daniel Avsec@.discussions.microsoft.com> wrote in message
news:1611776E-29E8-4F46-BB8F-97A8E6A32C45@.microsoft.com...
> I have a query that takes approximately 2 mins to run. The query is
derived from two tables that have a one to many relationship. When I
comment out 4 of the fields in the 'select' portion of the query, it speeds
up to 18 seconds. I know that I am doing a bookmark lookup (this is 60% of
the query time) but I am not sure how to avoid this. Any ideas?sql

Query Performance

Hello all-

I have a general question. I realize that joins are most of the time better than subqueries. Now that we have the derived tables, if I put the subquery as a derived table and join it with the other tables, will it make any difference in the performance?

eg:

SELECT EmpId
FROM Emp
WHERE EmpSalary = (SELECT MAX(EmpSalary)
FROM Emp Emp2)

Vs

SELECT Emp1.EmpId
FROM Emp Emp1
INNER JOIN (SELECT MAX(EmpSalary) FROM Emp) Emp2
ON Emp1.EmpSalary = Emp2.EmpSalary

(I know that there are better ways of writing this query, sorting my empsalary and getting the top 1 etc. This is just an example to demostrate my question.)

Appreciate your time
ThnxDid you do a show plan on the 2?

That will show you...but I don't think you'll see a difference...

It depeneds more on the number of rows you have and the indexing...

query on derived table

Hi,
If I join a table with a derived table, will the index be used? Thanks> If I join a table with a derived table, will the index be used?
Which index?
use northwind
go
set showplan_text on
go
select
c.customerid, c.companyname, a.orderid, a.orderdate
from
customers as c
inner join
(
select customerid, orderid, orderdate
from orders
where shipvia = 3
) as a
on c.customerid = a.customerid
go
set showplan_text off
go
AMB
"Jen" wrote:

> Hi,
> If I join a table with a derived table, will the index be used? Thanks|||You have not provied enough information to answer that question... At least
not without presenting a course in Index Tuning and optimization... Generall
y
whether or not an Index will be used for any specific query is determined by
a whole host of things
1) Whether any columns used as Predicates, Join conditions, or Order By
items, are in the index.
2) Whether the optimizer "Guesstimates" that using the index will result in
fewer IOs than doing a complete Table Scan. And this estimate is influenced
by factors such as:
The percentage of records (from the table(s)) that the query will result
in. (The higher the percentage, the more likely it is to ignore the index.)
wheether the index is clustered, Whether any predicates produce a "Range" o
f
results (like betweene two dates) which is based on two boundary values whic
h
are the columns in a clustered index. etc..
30
"Jen" wrote:

> Hi,
> If I join a table with a derived table, will the index be used? Thanks|||I have clusterd index for table1's primary key accountId, and table2 has
accountId foreign key reference that column and has index for it too. I used
to join these 2 tables on accountId and I know both tables will use index fo
r
accountId.
Now for table2, I only need subset data meaning less columns and by using
group to reduce rows. So I made it a derived table:
select table1.accountId, table1.userName, table1.moreUserInfo,
t2.accountType, t2.amount
from table1 inner join
(select accountId, accountType, min(amount) as amount from table2 where
accountType<>'C' group by accountId, accountType) t2
on table1.accountId = t2.accountId
In this case when the join happens the accountId in the t2 is still indexed?
My concern is that the derived table will affect performance or not?Thanks
"CBretana" wrote:
> You have not provied enough information to answer that question... At leas
t
> not without presenting a course in Index Tuning and optimization... Genera
lly
> whether or not an Index will be used for any specific query is determined
by
> a whole host of things
> 1) Whether any columns used as Predicates, Join conditions, or Order By
> items, are in the index.
> 2) Whether the optimizer "Guesstimates" that using the index will result i
n
> fewer IOs than doing a complete Table Scan. And this estimate is influenc
ed
> by factors such as:
> The percentage of records (from the table(s)) that the query will resu
lt
> in. (The higher the percentage, the more likely it is to ignore the index.
)
> wheether the index is clustered, Whether any predicates produce a "Range"
of
> results (like betweene two dates) which is based on two boundary values wh
ich
> are the columns in a clustered index. etc..
> 30
> "Jen" wrote:
>|||Jen, he answer is Yes, (I am pretty sure!) your query CAN use the index on
AccountID in table2. Whether it actually will or not depends on, again,
whether the query optimizer "decides" that using the index will result in
fewer page reads (IOs, than reading the table directly. Reading your query,
the only predivate is on accountType, so the answer to this question is
somewhat determined by "How many (percentage) of the records in Table2 have
accountType <> 'C' ' If the answer to this is more than 15- 20% (This
threshold is different in every case, but it is surprisingly lower than you
would think. ) than the query will not use the index.
By the way, (I think - try it out) that your query...
Select T1.accountId, T1.userName,
T1.moreUserInfo,
T2.accountType, T2.amount
From table1 T1
Join (Select accountId, accountType,
min(amount) amount
From Table2
Where accountType <> 'C'
Group by accountId, accountType) T2
On T1.AccountId = T2.AccountId
is equivilent to this one, (which does NOT use a derived table)
Select T1.accountId, T1.userName,
T1.moreUserInfo,
T2.accountType, T2.amount
From table1 T1 Join Table2 T2
On T2.AccountId = T1.AccountId
Group By T1.accountId, T1.userName,
T1.moreUserInfo, T2.accountType
"Jen" wrote:
> I have clusterd index for table1's primary key accountId, and table2 has
> accountId foreign key reference that column and has index for it too. I us
ed
> to join these 2 tables on accountId and I know both tables will use index
for
> accountId.
> Now for table2, I only need subset data meaning less columns and by using
> group to reduce rows. So I made it a derived table:
> select table1.accountId, table1.userName, table1.moreUserInfo,
> t2.accountType, t2.amount
> from table1 inner join
> (select accountId, accountType, min(amount) as amount from table2 where
> accountType<>'C' group by accountId, accountType) t2
> on table1.accountId = t2.accountId
> In this case when the join happens the accountId in the t2 is still indexe
d?
> My concern is that the derived table will affect performance or not?Thanks
> "CBretana" wrote:
>|||Is YES for first or second question?
I need as many as 18 columns from table1, so it will be a long group by
clause, do you think this is better than derived table?
Thanks
"CBretana" wrote:
> Jen, he answer is Yes, (I am pretty sure!) your query CAN use the index on
> AccountID in table2. Whether it actually will or not depends on, again,
> whether the query optimizer "decides" that using the index will result in
> fewer page reads (IOs, than reading the table directly. Reading your quer
y,
> the only predivate is on accountType, so the answer to this question is
> somewhat determined by "How many (percentage) of the records in Table2 hav
e
> accountType <> 'C' ' If the answer to this is more than 15- 20% (This
> threshold is different in every case, but it is surprisingly lower than y
ou
> would think. ) than the query will not use the index.
> By the way, (I think - try it out) that your query...
> Select T1.accountId, T1.userName,
> T1.moreUserInfo,
> T2.accountType, T2.amount
> From table1 T1
> Join (Select accountId, accountType,
> min(amount) amount
> From Table2
> Where accountType <> 'C'
> Group by accountId, accountType) T2
> On T1.AccountId = T2.AccountId
> is equivilent to this one, (which does NOT use a derived table)
> Select T1.accountId, T1.userName,
> T1.moreUserInfo,
> T2.accountType, T2.amount
> From table1 T1 Join Table2 T2
> On T2.AccountId = T1.AccountId
> Group By T1.accountId, T1.userName,
> T1.moreUserInfo, T2.accountType
>
> "Jen" wrote:
>