In a large web application we had a query that was running slow in code, that seemed to be working blazing fast in
SSMS. After checking the basics like the same
SET options, we found that there was one difference:
declare @variable1 and the code used an inline parameter.
I never though that would make a difference, but the microsoft docs gave the solution and even a short explanations.
The following queries are different. The first query uses Average Density from the histogram for cardinality estimation, while the second query uses the histogram step for cardinality estimation:
declare @variable1 = 123 select * from table where c1 = @variable1
select * from table where c1 = 123
Great, problem solved. But I have no idea what the explanation actually means. I've read up on cardinality estimation and understand that it's used to select that best query plan. But how does that change when using a variable or not?
- Is using a variable always better?
- If not, is there a better way to find out what to use other then "use one until it fails and check the other"?
This is the first time I really noticed this because it changed from <10ms to 10 seconds, but I'm wondering if other queries are affected by this, but just a lot less.