Wrong Execution Plan question

Question

Wrong Execution Plan question

sqlnes

Mr or Mrs. 500

Points: 595
More actions
January 16, 2013 at 1:33 am

#287966

Hi,
Our developer send me a query that takes long time to run. it has a query structure like this,
select T1.* from
(select T0.a, T0.b, ROW_NUMBER() OVER (ORDER BY T0.A) as RN
FROM (
SELECT a, b FROM KEY_TABLE kt JOIN......
UNION
SELECT a, b FROM KEY_TABLE kt JOIN......
UNION
SELECT a, b FROM KEY_TABLE kt JOIN......
) T0
) T1
WHERE T1.RN BETWEEN 1 to 50
I checked the execution plan and it costs a lot on the Clustered index of the KEY_TABLE and took more than 2 minute to finish. However, if i put "SET FORCEPLAN ON" before it and it took only 1 second to finish.
Could anyone get idea of why SQL Server did not use the "correct" plan as the one when FORCEPLAN was set ON?
Thanks in advance
V

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

Can you post the actual execution plans for both versions of the query? The one without the hint and the one with the hint?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

sqlnes (1/16/2013)
Could anyone get idea of why SQL Server did not use the "correct" plan as the one when FORCEPLAN was set ON?

from BOL SQL Server query optimizer processes a join in the same order as the tables appear in the FROM clause of a query. So thats the reason the selected plan didnt provide optimal performance.

Also post the index definition along with opc.three request.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

sqlnes Mr or Mrs. 500 Points: 595 More actions · Answer 3

Thanks guys!

It's weird, when I tried it today, it has back to the "correct" plan without set FORCEPLAN. it has been running fine now.

I just wondering now would it take some time after re-analyzing table/indexes then optimizer can do accurate calculation?

V

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

sqlnes (1/16/2013)
It's weird, when I tried it today, it has back to the "correct" plan without set FORCEPLAN. it has been running fine now. I just wondering now would it take some time after re-analyzing table/indexes then optimizer can do accurate calculation?

1) Have you/any job updated the statistics ?

2) have you recomplied the SP/script ?

3) did both execution have same parameter's values ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

sqlnes Mr or Mrs. 500 Points: 595 More actions · Answer 5

Bhuvnesh (1/16/2013)
sqlnes (1/16/2013)
It's weird, when I tried it today, it has back to the "correct" plan without set FORCEPLAN. it has been running fine now. I just wondering now would it take some time after re-analyzing table/indexes then optimizer can do accurate calculation?
1) Have you/any job updated the statistics ?
2) have you recomplied the SP/script ?
3) did both execution have same parameter's values ?

Yes, I did "update statistics" yesterday. but it did not help. Actually, I just retried and I was wrong, the plan is still go the "incorrect" one.

And I just found that in my query,

select T1.* from

(select T0.a, T0.b, ROW_NUMBER() OVER (ORDER BY T0.A) as RN

FROM (

SELECT a, b FROM KEY_TABLE kt JOIN......

UNION

SELECT a, b FROM KEY_TABLE kt JOIN......

UNION

SELECT a, b FROM KEY_TABLE kt JOIN......

) T0

) T1

WHERE T1.RN BETWEEN 1 to 50

If I commented out the last line (WHERE...), it went normal. And if I added the where clause back, it went bad again. strange...

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 6

Place your (

SELECT a, b FROM KEY_TABLE kt JOIN......

UNION

SELECT a, b FROM KEY_TABLE kt JOIN......

UNION

SELECT a, b FROM KEY_TABLE kt JOIN......

) T0

code in temp table and add appropraite indexes (acc to outer query ) to it

then use that temp table in main query

PLUS

POST exec plan here (as OPC suggested above)

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

sqlnes Mr or Mrs. 500 Points: 595 More actions · Answer 7

Bhuvnesh (1/17/2013)
Place your ( SELECT a, b FROM KEY_TABLE kt JOIN...... UNION SELECT a, b FROM KEY_TABLE kt JOIN...... UNION SELECT a, b FROM KEY_TABLE kt JOIN...... ) T0
code in temp table and add appropraite indexes (acc to outer query ) to it
then use that temp table in main query
PLUS
POST exec plan here (as OPC suggested above)

Thank you for sharing.

I hope i can change the code but I could not. it's auto generated code based on criterion set to application. What i can do so far is explained the current situation to the application owner.

V

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 8

sqlnes (1/17/2013)

What i can do so far is explained the current situation to the application owner.

What you can before explanting to them, test the code with the suggestion and show them and what and why we need those changes ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)