SQL query performance explanation

Question

I have this query(of course with couple joins across tables and some views which I will call x for simplicity).

Case 1 : select * from x -> returns in 10 sec, still acceptable, fairly heavy on joins and lot of data
Case 2 : select * from x where userid = 1 -> returns in 0-1 sec, good enough
Case 3 : using SP : if @userid = -1 select * from x else select from x where userid = @userid -> now calling sp with paramenter userid 1 should return on 0-1 sec as it should be comparable to case 2 but in fact it returns in 10 sec.

Now, parameter masking OR WITH RECOMPILE on sp OR OPTION (recompile) on query with where clause doesn't help, What makes the SP run faster with userid = something is putting OPTION(recompile) on the first part of the SP ie on the query without where clause. :
Case 4 : using SP : if @userid = -1 select * from x option (recompile) else select * from x where userid = @userid

Any explanation?

I can guess earlier it was using optimization based of query without where clause even for the query with where clause, but why is that?

Please use the code block for code. You should get used to it after 65 question. — juergen d
– juergen d, Commented Sep 19, 2012 at 16:28
thanks Nelson for editing! sorry about not putting it in codeblocks. — n12345
– n12345, Commented Sep 19, 2012 at 16:35
Are you asking us to explain your queries? Why not just look at the Execution Plan in SSMS? — Kermit
– Kermit, Commented Sep 19, 2012 at 17:03
No, I'm asking the reason for improved performance by adding option(recompile) on the query without the where clause — n12345
– n12345, Commented Sep 19, 2012 at 17:17
I mean, I'm guessing it might be figured from execution plans, which I did look into and can't pin point what's the reason. A lot of questions (on stackoverflow or otherwise) could be understood by just looking and analyzing execution plans in depth. But then why would I post the question, if you're asking me to attach execution plans here, then that's a different story. — n12345
– n12345, Commented Sep 19, 2012 at 17:31

Gordon Linoff · Accepted Answer · 2012-09-19 18:18:46Z

1

Stored procedures cache execution plans, so they don't have the overhead of recompiling a query every time the SP is called. This is particularly important when using stored procedures for transactions. It is much less important whent the queries are running for several seconds.

When a query takes parameters the stored procedure must decide what values to optimize for. I am guessing that your SQL Server recognizes the two queries as being identical, and is using the same cached plan for them. It is optimizing the plan based on the first query. This is speculation, but it would explain what you are seeing.

You can readily fix this with the following version of the query:

select *
from x
where @userid = -1 or userid = @userid
option (recompile)

answered Sep 19, 2012 at 18:18

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL query performance explanation

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related