SQL Server Performance Forum – Threads Archive

SP calling SP for parameter performance sake

Two questions. 1) Syntax
The 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

Hi ya, are you able to post the two stored procs? Hard to say what may be happening without seeing the code… In terms of number 2, what happens if you put a with recompile to the exec call for sp_2? Cheers
Twan

Hi Twain, Thanks for your response. I could post them here but they are rather large and intricate. My concern was that the basic issue might be lost with regaurd to the 1rst issue. Namely how does an SP return a record from an SP this it itself calls?
It seems to in QA, but not in the calling SP. I figured I am missing some basic syntax. Like
–SP_1 contents: EXECUTE SP_2
RETURN(0) –SP_2 contents:
select top 1 * from transact As to the 2nd suggestion, the reason I’m splitting them out is to avoid recompiles. How would this help? If you would still like me to post the SPs, I’d be more than happy to. Just let me know. Again, thanks for your help

So your output from the TSQL emulation of SP1 and SP2 looks fine? I’m assuming your emulation code callse SP2. If this is the case, the problem is with SP1 (stating the obvious). I would do some prints to see what you are getting as output for SP1. If that looks correct also, check that your exec command for SP2 is running by putting a simple print or something in the beginning of SP2 and running SP1 again. We can troubleshoot from there. John

Hi ya, I’d suspect that the execution plan for sp1 was one that was called with different parameters, and causing a suboptimal execution. recompiles are never caused by different parameter values being passed in, they are the result of things such as:
– changes of data in the tables used by the procs (thresholds are used, say 10% of the table changes, etc.)
– use of temporary table for the first time in that proc
– unresolved name or changed uid, e.g. if you drop and recreate a table then all procs who use that table will be recompiled next time they are called To avoid the problem of parameter sniffing causing suboptimal execution, you’d have to ensure that all calls to a stored procedure would be expected to use the same execution plan regardless of the values passed in as parameters Cheers
Twan

Hi Spacemonkey, I put some print statements around as you suggested and called SP_1 from QA.
It looks like it is returning something after all…
SP_1: Calling SP_2
SP2: Performing main calculation
department category item idescrip descrip limit qty_sold qty_res qty_rem
———- ———- ———- ————————- ————————- ———– ———– ———– ———–
FRFT 14:30 NULL NULL 75 0 0 19 SP_1: After Calling SP_2 Guess I need to figure out what is being added that confuses the caller of SP_1…
Ideas?

Hi Twain, I was using variables in the original SP. Same parameters used calling that SP. This would cause it to recompile each time.
Since I can’t toss out my variables, I took the bulk of the original SP and split it out into a second SP. I called that SP using parameters so SQL wouldn’t recompile each time but instead use the same execution plan for subsequent calls. What I don’t understand is that the execution plan would be so different when I did just that. Would you like me to post these SP’s?

SpaceMonkey, So it looks like it is in fact returning the info I need. I cannot however figure out why it looks different to the application. If I execute SP_1 in QA, the output looks identical to executing the original SP in QA. I’m stumped!

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…

Outside of the performance problem, where is SP1 getting the parameters passed to it? User input from an application somewhere? There must be a disconnect between the input and SP1 or the output of SP2 and the application. I agree with Twan about the parameters and recompiles. If the procedure is recompiling each time, there is probably another reason for it. It would be best to find the cause since it is your root problem here. John

OK Here is SP_1: <pre id="code"> SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO – New Version calling Sub ALTER procedure siriussp_GetQtyRemaining_By_DCI_base @idate varchar(20), @dept char(10), @cat char(10), @item char(10), @tnSaleNo numeric(17,0) as set nocount on SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED [email protected] datetime, @nMax4Savedtinyint, @tOldestDatedatetime, @tGetdatedatetime, @cDatechar(10), @cEDatechar(10), @cTimePart char(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />, @ckm4s_rttinyint set @tGetdate = GETDATE() set @tDate = cast(@idate as datetime) set @cDate = CONVERT(char(10), @tdate, 102) set @cEDate = CONVERT(char(10), dateadd(Day,1,@tDate), 102) set @cTimePart = convert(char(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />,@tDate,14) set @tOldestDate = cast( (cast(@tDate as int) -1) as datetime) select top 1 @nMax4Saved = isnull(max4_saved,0) from prefs_sl select @ckm4s_rt = i.ckm4s_rt from items i where [email protected] and [email protected] and [email protected] – m.type<10 means that it’s 1, 6, or 7 and therefore datetime – m.type>=10 means that it’s 51,56,57 and therefore time only – m.type =1 or m.type=51 means that it’s dci – m.type=6 or 56 means that its dept/cat – m.type=7 or 57 means that it’s dept only –for optimization purposes, get the oldest date we need to look at and use that in the query: select top 1 @tOldestDate=m.start_time from ((max4sale m LEFT OUTER JOIN departme d ON d.department=m.department) LEFT OUTER JOIN category c ON c.department=m.department and c.category=m.category) LEFT OUTER JOIN items i ON i.department=m.department and i.category=m.category and i.item=m.item where([email protected]) and ( (m.type=1 and i.ckmax4sale=1 and @tDate BETWEEN m.start_time and m.end_time and [email protected] and [email protected]) or (m.type=6 and c.ckmax4sale=1 and @tDate BETWEEN m.start_time and m.end_time and [email protected]) or (m.type=7 and d.ckmax4sale=1 and @tDate BETWEEN m.start_time and m.end_time) ) order bym.start_time asc – Get the limits EXECUTE siriussp_GetQtyRemaining_By_DCI_base_sub @tOldestDate, @tDate, @tGetdate, @tnSaleNo , @cTimePart, @cDate, @cEDate, @nMax4Saved, @ckm4s_rt, @dept, @cat, @item –return (0) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO </pre id="code"> ————————————————————————- Here is SP_2 <pre id="code"> SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO – New Version ALTER procedure siriussp_GetQtyRemaining_By_DCI_base_sub @tOldestDate datetime, @tDate datetime, @tGetdate datetime, @tnSaleNo numeric(17,0), @cTimePart char(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />, @cDate char(10), @cEDate char(10), @nMax4Saved tinyint, @ckm4s_rt tinyint, @dept char(10), @cat char(10), @item char(10) as set nocount on SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED –print ‘SP2: Performing main calculation' – Get the limits select top 1 m.department as department, m.category as category, m.item as item, i.descrip as idescrip, i.descrip as descrip, m.max_sale as limit, 0 as qty_sold, 0 as qty_res, m.max_sale –subtract out the number of things sold in transact. – isnull((select sum(t.quantity) from transact t where t.start_date >= @tOldestDate and t.sale_no<>@tnSaleNo and t.department=m.department and (m.type=7 or m.type=57 or t.category=m.category) and (m.type=7 or m.type=57 or m.type=6 or m.type=56 or t.item=m.item) and ( (m.type<10 and (t.start_date between m.start_time and m.end_time) ) or (m.type>=10 and ( t.start_date between @cDate and @cEDate and t.start_date between convert(char(11),@cDate,20)+convert(char(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />,m.start_time,14) and convert(char(11),@cDate,20)+convert(char(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />,m.end_time,14) ) ) )),0) –subtract out the number of things in tr_save. – isnull(case when @nMax4Saved = 2 then (select sum(tr.quantity) from tr_save tr LEFT OUTER JOIN sh_save sh ON tr.sale_no=sh.sale_no where (tr.start_date >= @tOldestDate and tr.department=m.department and tr.sale_no<>@tnSaleNo and sh.finalized=0 and tr.finalized=0 and (m.type=7 or m.type=57 or tr.category=m.category) and (m.type=7 or m.type=57 or m.type=6 or m.type=56 or tr.item=m.item) and ( (m.type<10 and (tr.start_date between m.start_time and m.end_time) ) or (m.type>=10 and ( tr.start_date between @cDate and @cEDate and tr.start_date between convert(char(11),@cDate,20)+convert(char(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />,m.start_time,14) and convert(char(11),@cDate,20)+convert(char(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />,m.end_time,14) ) ) ) )) else 0 end ,0) –subtract out real time inventory if necessary – isnull(case when @ckm4s_rt = 1 then (select sum(ir.quantity) from item_res ir where (ir.st art_date >= @tOldestDate and ir.department=m.department and (m.type=7 or m.type=57 or ir.category=m.category) and (m.type=7 or m.type=57 or m.type=6 or m.type=56 or ir.item=m.item) and ir.expires>[email protected] and ( (m.type<10 and (ir.start_date between m.start_time and m.end_time) ) or (m.type>=10 and ( ir.start_date between @cDate and @cEDate and ir.start_date between convert(char(11),@cDate,20)+convert(char(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />,m.start_time,14) and convert(char(11),@cDate,20)+convert(char(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />,m.end_time,14) ) ) ) )) else 0 end ,0) as qty_rem from ((max4sale m LEFT OUTER JOIN departme d ON d.department=m.department) LEFT OUTER JOIN category c ON c.department=m.department and c.category=m.category) LEFT OUTER JOIN items i ON i.department=m.department and i.category=m.category and i.item=m.item where [email protected] and ( (m.type=1 and i.ckmax4sale=1 and @tDate BETWEEN m.start_time and m.end_time and [email protected] and [email protected]) or (m.type=6 and c.ckmax4sale=1 and @tDate BETWEEN m.start_time and m.end_time and [email protected]) or (m.type=7 and d.ckmax4sale=1 and @tDate BETWEEN m.start_time and m.end_time ) or (m.type=51 and i.ckmax4sale=1 and [email protected] and [email protected] and convert(char(11),m.start_time,20)[email protected] between m.start_time and m.end_time ) or (m.type=56 and c.ckmax4sale=1 and [email protected] and convert(char(11),m.start_time,20)[email protected] between m.start_time and m.end_time ) or (m.type=57 and d.ckmax4sale=1 and convert(char(11),m.start_time,20)[email protected] between m.start_time and m.end_time ) ) order byqty_rem asc –return (0) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO </pre id="code"> It is always called in the following format: exec siriussp_GetQtyRemaining_By_DCI_Base ‘20060511 13:03:00’, ‘FRFT ‘, ’14:30 ‘, ’14:30 ‘, 0 Sorry for the size…

Nothing stands out to me in the procedures that would cause it to recompile. There are no set commands that would do it. There is also not any DDL that would change anything. How did you find that the old SP was being recompiled each time? Did you do a profiler trace? Is anything happening to the underlying tables or indexes to change them? John

The second SP is in fact not recompiling, but of course the first one is.
When this was all one SP, it would always recompile. I am using profiler to tell me if the SP hits the cache or misses it.
What is weird in the split case is that in profiler, after the SP, it tells me that there were two misses, and one hit.
The indexes it is using is giving it fairly favourable results but I am amazed that it would not touch the ones I created for it in the first place. That which the tuning wizard suggested. Any idea where the second cache miss comes from? If the queries are close 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 asking 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 for your help!
Tim

Well in the meantime, I would stay with the original as it is faster at the moment and that is what clients understand. Then let’s figure out why the first SP is recompiling. (Unless you know and I’m just missing it) Here is a way to help track down the source of the problem. http://support.microsoft.com/?id=308737 John

One thing I’m not clear about your recompiling question…
You do see why the original query recompiles no? As well as SP_1?
Or do you think they should not recompile? I thought that if there were variables set in the the SP…especially like the following:
set @tGetdate = GETDATE() That would cause a re-compile. Do you agree or am I missing something?

I wasn’t sure of that myself so I checked books online, and anything I could find in google searches. Nothing mentions getdate() for causing recompiles. It makes sense that getdate() would not cause a recompile because it is just basically querying the server for its system datetime. Once again, this comes back to my background being in Oracle so I’m still learning the ins and outs of SQL Server 2k. That being said, it is my understanding for code in general that compilation is just conversion to machine code. It may work differently in a database environments but setting variables would not cause recompilation of the code would it? My view is that larger changes such as those posted by Twan would cause it. It would be kind of like a C program recompiling after it was passed user input. The code is already compiled and running at that point. In this case, I would expect the SP to compile and be moved into cache for later use. Are you seeing the SP recompile in profiler or are you assuming it recompiles? Please, if I am wrong and/or ignorant on this subject someone please let me know. John

Hi ya, use of the getdate() function won’t cause a recompile not 100% sure about cache hit and cache miss in terms of exactly what it means in your context. there is a sp recompile event that you can trace with profiler, which will give you whether the proc recompiles or not. The 2nd proc does concern me, because it has a query with things like between and greater than based on parameters passed in. The first time the proc is pulled into the cache, if the parameters happen to be too wide apart to have a useful index (i.e. they’d select too much data) then SQL may opt to use index scans or table scans which would hurt performance. There is also the set of OR statements at the end which again may preclude the use of good indexes. without having the time to analyse the statement fully (nor understanding the underlying data…) I’d probably try to break the statement up if possible…? Cheers
Twan

]]>