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 |
|
wyx2000
Starting Member
5 Posts |
 Posted - 2009-12-08 : 13:16:52
|
| I like SQL, I tend to put logic into SP. I have a SP with 500 lines of SQL code, I found it takes 500ms to call into the SP when server has load. Call into I mean from the time to call the SP to the first line of SQL code in the SP, that means it should not be the codes in the SP that causes the delay.I have tried to remove some lines from the SP, and I do see the delay changed, for example, if I remove all but the first line, it takes 20ms for the call into. if I remove half of the total lines, the call into time is around 200ms.It seems the size of SP does matter, so I want to know what is the guideline on this, how should I handle this?thanks! |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-12-08 : 13:36:08
|
| It's not the number of lines that is important it is what those lines of code are doing. It's possible your SP code is causing (at least one) recompile. You can confirm that by using Sql Profiler. You need to add re-compile to the events though.If that is the case read this:http://www.sql-server-performance.com/articles/per/optimizing_sp_recompiles_p1.aspxBe One with the OptimizerTG |
 |
|
|
wyx2000
Starting Member
5 Posts |
Posted - 2009-12-08 : 14:34:52
|
quote:
Originally posted by TG
It's not the number of lines that is important it is what those lines of code are doing. It's possible your SP code is causing (at least one) recompile. You can confirm that by using Sql Profiler. You need to add re-compile to the events though.If that is the case read this:http://www.sql-server-performance.com/articles/per/optimizing_sp_recompiles_p1.aspxBe One with the OptimizerTG
just tested with the what is said in the link, the SP doesn't recompile.So still trying to figure out where the delay from. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-12-08 : 15:07:42
|
| how are you measuring this? how do you know how long b4 the first line is executed? |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-12-08 : 16:51:46
|
| I'm also curious for the answer to russell's question.You say this delay only occurs under a "load"? perhaps the first line of code is being blocked by a concurrent process. Have you looked for blocking while the delay is happening?Be One with the OptimizerTG |
|
|
|
|
|
|
|
|
Stored procedure size, affect performance?