September 20, 2013 at 11:54 am
I have a stored procedure that takes a while to run that returns a single result. What I was finding was that this stored procedure was getting called a number of times with the exact same parameters. What I did was create a cache that would save the result. So the next time the SP was called with those parameters it would get the result from the cache instead of running the entire SP.
That was working great. There is however 1 issue that I'm trying to resolve.
Here are the basic steps the SP takes
1. Check the cache
2a. If there's a hit, return the result.
2b. If not in the cache, do the processing.
3. Write result to cache
The problem happens when there are 2 identical calls (from different sessions) at the same time when there isn't a result in the cache.
The first call will check the cache and see there's no hit and continue. The second call will check and see there's no hit and continue. The problem happens at step #3. The first session will write to the cache and then right behind it the second session will write to the cache. That second write to the cache fails because there's a unique constraint on the parameters.
So what I'd like to do is when that first session makes the initial check in the cache to put a lock on the row it's going to create. That way when the second session makes the check to the cache it will wait until the first session has written to the cache.
Any suggestions?
Thanks!
September 20, 2013 at 12:12 pm
cgreathouse (9/20/2013)
I have a stored procedure that takes a while to run that returns a single result. What I was finding was that this stored procedure was getting called a number of times with the exact same parameters. What I did was create a cache that would save the result. So the next time the SP was called with those parameters it would get the result from the cache instead of running the entire SP.That was working great. There is however 1 issue that I'm trying to resolve.
Here are the basic steps the SP takes
1. Check the cache
2a. If there's a hit, return the result.
2b. If not in the cache, do the processing.
3. Write result to cache
The problem happens when there are 2 identical calls (from different sessions) at the same time when there isn't a result in the cache.
The first call will check the cache and see there's no hit and continue. The second call will check and see there's no hit and continue. The problem happens at step #3. The first session will write to the cache and then right behind it the second session will write to the cache. That second write to the cache fails because there's a unique constraint on the parameters.
So what I'd like to do is when that first session makes the initial check in the cache to put a lock on the row it's going to create. That way when the second session makes the check to the cache it will wait until the first session has written to the cache.
Any suggestions?
Thanks!
Can you change the logic that does the insert instead of waiting? Especially since the reason you created this is because of poor performance it seems like intentionally adding a wait is counterproductive.
I would look at changing the insert to have a WHERE NOT EXISTS. That would eliminate the duplicate values being inserted.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 20, 2013 at 1:19 pm
True, that would solve the issue of the duplicate insert. But that also means the expensive part would need to be run twice. Since the first session has already started processing I would think it would be better for the second session to wait for the first session to finish.
It's certainly worth trying to see how it performs. But I'd also like to try a solution where the second session waits on the first session and compare. That way the expensive part only needs to be run once.
Thanks for the idea!
September 20, 2013 at 1:30 pm
Maybe you can simply write the parameters to the cache at the beginning and then update that row with the result at the end of processing?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 20, 2013 at 1:38 pm
I think there's a potential issue with that idea. The second session could see an item in the cache but the first session hasn't finished and written the result yet.
September 20, 2013 at 1:49 pm
Sounds feasible for sure. Let me know if that works for you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply