Option Recompile makes query fast - good or bad?

Question

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.

Answer 1

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

Answer 2

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.