Discussion Locked

Understand the WITH RECOMPILE option

0 helpful
4 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

This week's SQL Server newsletter discusses the WITH RECOMPILE option.

Will you revisit any situations in your database after reading this tip? Do you know of any other helpful hints for using the WITH RECOMPILE option that this newsletter doesn't outline?

If you aren't subscribed to the free SQL Server newsletter, click the following link to automatically sign up:
http://nl.com.com/MiniFormHandler?brand=builder&subs_channel=bldr_front_door&list_id=e046&tag=fb

4 replies

by kengoad

I have had to deal with the same issues which Arthur details in his article and have found that using case or if..else constructs in sprocs can be very dangerous. We supply commercial software and one of the tricks we used for reporting was to call just one sproc which would cover all the the differnent sorts and other options possible for a particular report. This meant we have a stored proc with up to 18 or 20 complex queries in it, all preceed by "If Flag = n " run this query "else If Flag = n" etc. THis worked great for a while, but at a certain point with SQL 7 (we were never able to determine why, although we believe it was a patch that may have triggered it) it stopped working. We would get a call from a client and find out that when they ran a report it would bring the system to it's knees for up 1 or 2 minutes! This was a system which supported 200-300 concurrent users. Turns out that when the sproc was first invoked it compiled and created a query plan based on all 18-20 queries running as one - a huge task. The solution was simple. Create a calling sproc, and put each query into it's own sproc.

by artful

I quite agree. In fact, in my penultimate paragraph I wrote exactly that:

"There is also a third option, which occurred to me a little later. What I could have done was create a separate sproc for each search method, and simply decide which one to execute within the CASE block. That way, the query plan associated with the sub-sprocs remained in the cache, where SQL could take advantage of them. Since each of the sprocs searched exactly one column, there was no need to recompile."

I was unaware that the large CASE block of queries would eventually break SQL 7. Fortunately, in my case I learned the trick of subs-sprocs before it ever got that far. Thanks for pointing out this problem.

Arthur Fuller

by sremiger

Why use the recompile when you can use different stored procedures. Why not use grouping stored procedures and specify based on the column your searching on as to which stored procedure is called. To me it is easier to use many stored procedures and you would not lose the advantage of the cached as well. The whole idea of building a large procedure to handle all searching tasks (in this case) seems to be a lot of work compared to writing multiple smaller procedures.

Example would be
Order Date
EXEC getSearchResults;1 '1-1-2004'
Ship Date
EXEC getSearchResults;2 '1-1-2004'

by artful

I quite agree with you. What I tried to do in the tip was track my progress from there to here. The penultimate paragraph says pretty much what you wrote:

"There is also a third option, which occurred to me a little later. What I could have done was create a separate sproc for each search method, and simply decide which one to execute within the CASE block. That way, the query plan associated with the sub-sprocs remained in the cache, where SQL could take advantage of them. Since each of the sprocs searched exactly one column, there was no need to recompile."

Arthur Fuller

This discussion is closed to new replies.