Today I learned that SQL server does not reliably short-circuit. This means that a SELECT statement with several dynamic WHERE clauses can be very inefficient, such as
SELECT col1
FROM table
WHERE
@a = null OR @a = col2
AND (@b = null OR col3 LIKE '%'+@b+'%')
AND (@c = null OR col3 IN SELECT value FROM STRING_SPLIT(@c, ','))
Here, if all of the parameters are null, SQL Server may still do the comparisons and slow down the query.
Now, I experimented a bit with SQLProvider, and it seems that the query syntax does indeed short-circuit so that certain parts of the query may not end up in the SQL at all. For example:
query {
for order in ... do
where ((true || order.OrderNumber = "foo") && order.OrderType = 9)
select order.OrderNumber
}
The generated SQL is (simplified)
SELECT OrderNumber
FROM ...
WHERE OrderType = @param1
This means that the comparison against OrderNumber is not part of the SQL at all.
This is great news for me, but I'm just wondering whether this is intended and will always happen.
Today I learned that SQL server does not reliably short-circuit. This means that a SELECT statement with several dynamic WHERE clauses can be very inefficient, such as
Here, if all of the parameters are
null
, SQL Server may still do the comparisons and slow down the query.Now, I experimented a bit with
SQLProvider
, and it seems that the query syntax does indeed short-circuit so that certain parts of the query may not end up in the SQL at all. For example:The generated SQL is (simplified)
This means that the comparison against
OrderNumber
is not part of the SQL at all.This is great news for me, but I'm just wondering whether this is intended and will always happen.