Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gsephton
Starting Member
2 Posts |
 Posted - 2011-04-19 : 07:28:01
|
| Hi all,First of all, be kind, I'm currently just try to teach myself the blackart of indexing and statistics.I've got a problem where a query generated by an application is taking 6-10 seconds to process. I've run the execution plan on the query and it's doing a full table scan which is most likely the cause. My confusion however is if i drop part of the query then the execution plan changes and no full table scan.The original query select * from slarecords where ownertable = 'WORKORDER' and (ownerid = 198590 or ownerid in (select workorderid from workorder where parent = 'W101601' and istask=1)) OPTION (FAST 1000) My amended query select * from slarecords where ownertable = 'WORKORDER' and (ownerid in (select workorderid from workorder where parent = 'W101601' and istask=1)) OPTION (FAST 1000) All i've done is removed the "ownerid = 198590" part of the clause. Why would this dramatically change the execution plan?Any help would be great.Thanks |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-04-19 : 07:39:14
|
| Tryselect * from slarecordswhere ownertable = 'WORKORDER'and (ownerid in (select workorderid from workorder where parent = 'W101601' and istask=1 union select ownerid = 198590)) OPTION (FAST 1000) What datatype is ownerid? Is it an int?==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-04-19 : 08:28:53
|
| Why the hint?--Gail ShawSQL Server MVP |
|
|
|
gsephton
Starting Member
2 Posts |
Posted - 2011-04-19 : 09:07:05
|
| ownerid is an int.The hint comes from the application which i think may be causing the problem. I've created an index on the table just for ownerid and without the hint it comes back almost instantly but with the hint it takes 5 seconds. If i use a WITH (FASTFIRSTROW,INDEX(testindex)) with the FAST hint i get an instant result....nigelrivett - your query also cam back instantly, however it's going to be very difficult to make any changes to the original query as they are generated by the IBM Maximo application.As a side point, I believe the application has the hint built in because of this original issuehttp://support.microsoft.com/kb/946793I can at least remove that but it's a system wide change to all sql generated by the application will inherit, hence I'm reluctant (but not unwilling) to change.I'd love to be able to understand indexing properly! Your help is greatly appreciated. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
|
|
Jahanzaib
Posting Yak Master
115 Posts |
Posted - 2011-04-20 : 08:51:59
|
| Because you have removed PREDICATE or we can say OPERATOR (=),Optimizer first check the predicates or operator in the query then design the execution planRegards,Syed Jahanzaib Bin HassanMCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBAMy Blogwww.aureus-salah.com |
|
|
|
|
|
|
|
|
Execution plan question...