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 |
|
SQLTim
Starting Member
21 Posts |
 Posted - 2006-05-11 : 11:45:33
|
| Two questions.1) SyntaxThe current version is a single SP that returns a single "record".The bulk of the query (98%) I have split into a seperate SP so I can parameterize it for exec plan reuse. SP_1 sets up the variables and calls SP_2.I've created testing T-SQL that emulates SP_1 which sets up the variables and calls SP_2. The result set looks returns correctly.However, when I implement the real SP_1, nothing returns to the application. I must be missing some syntax? Should the end of SP_2 have a "return (0)"? The end of my SP_1 does.It does not seem to make a difference.2) Execution Plan changes when the SP is split out.After some extensive index tuning, I've added some indexes that greatly speed up the larger query. Why would the execution plan no longer use those indexes when splitting it out into it's own SP?The speed difference is 70ms if it is all one procedure including the Cache miss, versus 1140ms when split with a cache hit.I'm about ready to call it a day and leave it all one SP. But the Cache misses really bothers me. Any ideas?Many thanks in advance!Tim |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-05-11 : 12:11:13
|
| 1) Should the end of SP_2 have a "return (0)"?There will be an implied return statement which will be with a 0 code if it is successful.2) Why would the execution plan no longer use those indexes when splitting it out into it's own SP?The sp will be optimised separately. It could be that a parameter prevents it from getting a good execution plan.The cache miss is a bit odd.How are you calling it? Maybe it is getting a cahce miss from the sql statement then I would expoect a cache hit when it tries with the SP id - unless the SP has something iin it which causesa recompile maybe - but I thought that would give a cache hit followed by a recompile. Is something causing the cache to empty?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
SQLTim
Starting Member
21 Posts |
Posted - 2006-05-11 : 12:29:14
|
| Hello nr, Thanks for your reply.1) It probably doesn't need one. I feel like I'm missing something with syntax.If SP_1 looks like this:EXECUTE SP_2and SP_2 looks likeselect top 1* from transactWould SP_1 return to it's caller the record that SP_2 returned?2) This is a rather large sp, and the part split out is the only part of the whole that would use the new indexes. Also there is a bit of variable use that I believe will cause it to recompile each time. That's why I had the second part split out. Do my variable work in the SP_1 and call SP_2 in a parameterized way. It's just weird that would then stop using those indexes. Was wondering if there was anything intrinsic.I'd be happy to post the SPs if you think it will help, but they are rather large.Again, thanks for your help! |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-05-11 : 12:39:58
|
| It will return a resultset. It is up to the calling sp whether or not it picks it up or lets it output to the client.To process it in sp_1insert #mytblexec sp_2#mytbl needs to have the correct structure.As it's a single row you could use output parameters instead.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
SQLTim
Starting Member
21 Posts |
Posted - 2006-05-11 : 12:48:24
|
| Hmmm,What if all I want to do is pass the results of SP_2 through to the caller of SP_1?The caller of SP_1 currently recieves that record set if all is contained in a single SP. In this case, I'm not able to change the maner in which values are returned. |
|
|
|
SQLTim
Starting Member
21 Posts |
Posted - 2006-05-11 : 13:38:34
|
| Good Grief...About #1... it was a permission's issue for the second procedure. The login for the application had not been granted access to that.Sorry about that...Still trying to flush out the execution plan differences though. Will have to scrap this whole approach if it cant make proper of the needed indexes... |
|
|
|
SQLTim
Starting Member
21 Posts |
Posted - 2006-05-11 : 16:07:34
|
| OK, so far I have similar execution times between the two approaches.The single SP approach is slightly faster but causes a cache miss every time.The split SP approach is slightly slower but causes a cache hit on the larger second SP.What is also strange is that according to the profiler, after the SPs run, it shows two cache misses and then a cache hit.Anyone know why this would be?At any rate, my question is this. If the two approaches are similar in times, should I favor the one that causes a cache hit?I know it seems like an obvious answer, but it is slightly faster to go with the one that misses the cache every time.Is this likely to erode when under load? Many clients will hit the server with 10 to 20 calls at a time.That's why I'm asking if I should favor the cache hit scenario even though it is slower in my tests...Thanks again!Tim |
|
|
|
|
|
|
|
|
SP calling SP for parameter performance sake