August 26, 2005 at 4:01 pm
This really has me on tilt.
I can run a select statement in query anal and get a result set back in about 3 minutes. I put this baby in a sproc and have it do a select into (an otherwise empty table) and it takes 20 friggin minutes to complete.
I tried writing the select statement into a temp table next, then having the temp table insert into the production table. Same slow result. 20 minutes.
Cut and paste the select statement from the sproc back into query anal, delete the select "into" table part and get my data back in 2 minutes.
I can't wrap my head around THAT much of a performance hit by moving it into a sproc, then to an INSERT INTO table.
(For the record, I have watched it both ways in Profiler and that didn't take the scales off my eyes.)
Theories? Speculation?
August 26, 2005 at 4:14 pm
Theories (on search on this site will get you the info you need in case it's urgent) :
Parameter sniffing (if slow only in the proc for the exact same code than QA)
Are you doing select into or insert (...) select ...?
If yes, how many rows are you inserting and do you have a lot of indexes/constraints/triggers on that table ?
if no, Did you see any deal locks during that transfer?
August 26, 2005 at 4:14 pm
Without seeing your schema I am just guesing, but try and create a physical table and run the code. It could be the sp is recompiling because of the temp table, if you run it this way and all is well try either using the physical table or try using a table variable.
August 26, 2005 at 4:14 pm
Surely the time difference here is due to the fact that SELECT INTO is writing to disk and SELECT is not? How much data are we talking about?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply