General discussion

Locked

Understand the WITH RECOMPILE option

By MaryWeilage Editor ·
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

This conversation is currently closed to new comments.

4 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Be careful of combining procs in case statement

by KenGoad In reply to Understand the WITH RECOM ...

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.

Collapse -

Many Sprocs Rather than Many Queries

by artful In reply to Be careful of combining p ...

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

Collapse -

I disagree with using RECOMPILE all together

by sremiger In reply to Understand the WITH RECOM ...

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'

Collapse -

Many Sprocs Rather than Many Queries

by artful In reply to I disagree with using REC ...

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

Back to Software Forum
4 total posts (Page 1 of 1)  

Related Discussions

Related Forums