May 22, 2019 at 9:42 am
Morning,
I've inherited a system which passes a lot of variables around, and I'm wondering if anyone has any thoughts on what might be an optimal approach to refactoring, because I can't find anything on the topic online. it's kind of tricky to work out a valid test as I'm working with single records so the differences are difficult to measure.
The basic question is - is it faster to keep 10 variables in memory and pass them to another stored procedure to use or pass a single primary key to another procedure and select the other 9 based on that key?
My control procedure is selecting about 30 variables and calling about 10 procedures, passing 10 or so of the variables each time it calls something.
I wonder also whether the procs are passing the actual string values of the in memory variables or just pointers to them? Does anyone know if there is a tipping point here where the number of variables outweighs the overhead of doing a select?
Also this is quite a fast moving system, so the control procedure can be called concurrently a few times.
I'm coming down on the side of passing a single key around and selecting the data I need at point of use purely because the code is so much cleaner and easier to follow and test, this is such a mess I'm finding instances of in memory variables being manipulated in the process flow then being completely ignored at the end of process in favour of doing a select from the original table.
Having said that speed is of the essence so maybe I just need to suck it up....
May 22, 2019 at 11:25 am
It is always difficult to say without seeing the code and not knowing the system. But if you only pass a key around and read data in every procedure this can potentially be slower, since for each access you could be blocked by another process which has acquired a conflicting table or page lock. Or for that matter a lock on that very row you want to read. Which opens for an interesting question, what if data is updated while your process is running? Do you want to get the new fresh data? Or do you want a consistent view, and have the same information all the time?
In any case, only reading data once into variable will always be faster, although as long as everything is running smoothly, you may not be able to notice any significant difference. But I can agree with the sentiment that you get cleaner code if you only pass a key around.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply