September 11, 2002 at 2:58 am
Hi all
I have a stored proc, create a temporary local table, and want to index it before attempting a variety of deletes against the data inside of it (1000's of rows). When I crank up query analyser, then run the create #table command, then create the indexes, and then do the delete statements, the explain plan tells me the deletes utilised the indexes and I get blisteringly fast performance. BUT, then i package it all up in a stored proc, the delete statements DONT use the indexes, and it seems like the plan generator cant recognise the indexes at all.
Ideas??
Cheers
Chris.
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
September 11, 2002 at 3:01 am
ok,
a hint forced it through, but i dont like it ! 🙂
<delete stateent> etc with (index(ix_enrolment1))
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
September 11, 2002 at 3:11 am
Probably it's because of 1000 rows only, on a bigger table it should start using index.
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
September 11, 2002 at 3:27 am
Maybe force it to recompile each time?
Andy
September 11, 2002 at 4:02 am
I agree with Andy, most likely it stored the first or last query plan generated with the SP. When it did this it now most likely doesn't try to build a query plan for the rest and thus slows down the whole process. Using the WITH RECOMPILE option with SP will have it generate new plans for each step and tell you if this is the root cause.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
September 12, 2002 at 2:34 am
One last point..
Query plan will be generated automatically by procedure, if the data expected is more than some 80% of origional data when procedure compiled.
Normally for thousand records table scan is faster then index scan and thus query plan choose to do table scan.
Cheers,
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
September 13, 2002 at 2:27 am
excellent points made, ill follow up on these and see how we go.
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply