8

I have two SQL queries with about 2-3 INNER JOINS each. I need to do an INTERSECT between them.

Problem is that indiividually the queryes work fast, but after intersecting take about 4 seconds in total.

Now, if I put an OPTION (RECOMPILE) at the end of this whole query, the query works great again working quite fast returning almost instantly!.

I understand that option recopile forces a rebuild of execution plan, so I am confused now if my earler query taking 4 seconds is better or now the one with recompile, but taking 0 seconds is better.

Saurabh Kumar
  • 2,329
  • 6
  • 32
  • 52

2 Answers2

6

Rather than answer the question you asked, here's what you should do:

Update your statistics: 

EXEC sp_updatestats

If that doesn't work, rebuild indexes.

If that doesn't work, look at OPTIMIZE FOR

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
  • 1
    Is it executed periodically? If not, I don't know how to manage the index in my database... Any ideas on how to avoid the need to execute this command periodically? Or if this is not possible, do you the best way to execute this periodically, I mean without the need of the application to do it? – Felipe Sabino Oct 27 '12 at 01:14
  • I suggest you ask a separate question. – Mitch Wheat Oct 27 '12 at 01:15
4

WITH RECOMPILE is specified SQL Server does not cache a plan for this stored procedure, the stored procedure is recompiled each time it is executed.

Whenever a stored procedure is run in SQL Server for the first time, it is optimized and a query plan is compiled and cached in SQL Server's memory. Each time the same stored procedure is run after it is cached, it will use the same query plan eliminating the need for the same stored procedure from being optimized and compiled every time it is run. So if you need to run the same stored procedure 1,000 times a day, a lot of time and hardware resources can be saved and SQL Server doesn't have to work as hard.

you should not use this option because by using this option, you lose most of the advantages you get by substituting SQL queries with the stored procedures.

Pankaj Agarwal
  • 11,191
  • 12
  • 43
  • 59
  • 7
    But for the OP, avoiding this option means that SQL Server spends 100–200 times longer each time the query gets executed and probably wastes a lot of CPU and disk throughput because it always uses a bad query plan. Unless there’s a way to figure out how to get SQL Server to cache a good plan, then `OPTION (RECOMPILE)` is much, much better because the plan generation/optimization time is microscopic compared to the execution time of the bad plan… – binki Oct 14 '14 at 20:00