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 |
|
ThreePea
Yak Posting Veteran
83 Posts |
 Posted - 2002-01-29 : 14:49:52
|
| A developer came to me with a stored procedure that was taking 20-30 seconds to run and asked for some optimization help. As a matter of habit I copied the code directly into the Query Analyzer so I could run it directly, and to my surprise it took 1 second to run before I even touched anything!When I "EXEC proc_name" the execution plan is radically different than the one created by the direct code. I have tried doing "sp_recompile proc_name", as well as saving the proc with "WITH RECOMPILE" in order to get it to update its execution plan, but it doesn't change. Why is the stored procedure coming up with a different (and MUCH slower) execution plan then the direct T-SQL in Query Analyzer, and how can I force it to be similar to the one generated by the direct code?Thanks.Jerry======================================================="The world has achieved brilliance without conscience.Ours is a world of nuclear giants and ethical infants." -- General Omar N. Bradley (1893-1981) |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-01-29 : 15:24:52
|
| Sometimes code in an SP gets a different execution plan to the same code in query analyser.You can get round this by an optimiser hint.Or maybe by changing the query.I can remember one where I was joining to a table on a single row and putting that join value into a variable cured the problem.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-01-29 : 18:17:35
|
| Have you tried dropping the SP and then recreating it? Don't ask me why that might work, but I had a very similar problem to what you describe, and that was the only way to get the plan to change. It may depend on which service pack you have installed, if any. If you can install the latest service pack it might correct the problem. |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2002-01-29 : 19:10:18
|
| An SP execution plan is created on the FIRST run of the SP...So....The values you first executed against the proc will be used....The most likely cause is that you executed it with a NULL value as one of the parameters....I agree with Rob, drop it and recreate and use sensible values on the first execution....HTHDavidMTomorrow is the same day as Today was the day before. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-01-30 : 04:23:55
|
| Using the recompile option should have the same effect but it's worth a go.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
Lavos
Posting Yak Master
200 Posts |
Posted - 2002-01-30 : 13:34:01
|
| Does the procedure involve branching on if's? I've read somewhere (Whether it was this site, or the Guru's Guide to Transact Sql, I don't know) about multiple branches through code adversely affecting the ability of the engine to create optimized plans for procedures. You might try breaking up some of those conditional blocks into seperate procedures.eg:IF (some condition) BEGIN { Complex Query } ENDELSE BEGIN { Some Other Complex Query } ENDBecomes:IF (some condition) BEGIN EXEC SubProc1 ENDELSE BEGIN EXEC SubProc2 ENDThe main procedure would become a "delegator" (Doing a quick search for delegator in the article search didn't come up with anything, so it was probably Henderson's book.)----------------------"O Theos mou! Echo ten labrida en te mou kephale!" |
|
|
|
|
|
|
|
|
Help - Different execution plan for same code