Quick one today:

Let’s look at an Interesting, albeit, seemingly anomalous behavior with in Sql Server.

Question

• When does a query with literals perform faster than query with variables? Even with a simple query.

Let’s look at some examples:

The only difference between both they queries is that in the first one, we are providing literals in the WHERE clause; In second one, we provide values through variables. Even for a simple query like this, the execution plan is different along with the execution duration. This applies only for large tables (for small tables it does not make much difference).

Explanation

In the first query, the Query Optimizer, has clear idea on what is being executed. You know the query and its bounds; So it uses statistics available on this column to come up with the best possible query plan. Sql Server knows the exact number of records that are returned, so it comes up with most appropriate plan.

Whereas, the second query, at the time of compilation, the bounds are not available. Sql Server does not know if the query would return 100 records or 100 million records. It still uses statistics, but it uses it to come up with most appropriate plan including the worst case scenario. So, if you end up providing values that return only 10 records; Too bad. Sql Server still uses the ‘gargantuan query plan’ it designed for worst case scenario. So, it ends up being inefficient for most cases.

Alternative

If I run is using dynamic query execution, it runs faster:

Explanation

Obviously, by the time the execution comes to the EXEC (@SQL) section, the query variables are already replaced with literals. So, essentially Query 3 is same as Query 1.

Hope this helps,
_Sqltimes