Does the Recompile option make the request fast - good or bad?

Question

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

The problem is that individually requests are executed quickly, but after crossing it takes only about 4 seconds.

Now, if I put OPTION (RECOMPILE) at the end of this entire query, the query works fine, working pretty fast again, returning almost instantly!

I understand that the recopile option forces me to rebuild the execution plan, so I'm confused now if my request for the early ones, which takes 4 seconds, is better or now the one that is recompiled, but best for 0 seconds.

Answer 1

Instead of answering the question you asked, here is what you should do:

Update your stats:

EXEC sp_updatestats 

If this does not work, rebuild the indexes.

If this does not work, see OPTIMIZATION FOR

Answer 2

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

Whenever a stored procedure is launched in SQL Server for the first time, it is optimized and the query plan is compiled and cached in SQL Server memory. Each time the same stored procedure is launched after its caching, it will use the same query plan, eliminating the need to optimize and compile the same stored procedure each time it is started. Therefore, if you need to run the same stored procedure 1000 times a day, you can save a lot of time and hardware resources, and SQL Server will not work so hard.

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