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 |
|
machina
Starting Member
2 Posts |
 Posted - 2009-01-13 : 10:54:33
|
| Hi AllThis problem seems extremely strange to me. Essentially I have a stored procedure that when run from query analyzer takes ~12 seconds to run. This is far too long given the amount of data being checked (joining a table of approx 100 rows against a table with approx 12k rows and returning about 200 rows).There are two paths of execution in the SP. The simple path, which selects much less data and joins against less tables, is the one being executed in this case. The other path is very complex and is only executed in extreme cases from our application.The strange part is that if I comment out the second more complex query (that wasn't being run anyway), the SP runs in < 1 second. So in both cases the offending code wasn't being run, but there is an 11 second difference.Is anyone able to explain this?Thanks in advance |
|
|
machina
Starting Member
2 Posts |
Posted - 2009-01-13 : 11:16:56
|
| It's probably worth mentioning that the stored proc runs fine (< 1 sec) on other, much larger databases. So for some reason it's very specific to this particular database. |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-01-13 : 11:29:51
|
Sound like the procedure cache holds "wrong" version of the code.This is common if you have a "multiple purpose" stored procedure and code depends on a few IF's.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
|
|
|
Non-executed code slowing down stored proc