Short circuit in sql server with full text predicates
I am new to t-sql and sql server and I have a question about short circuit and full text search. Suppose I have an operator like this:
SELECT *
FROM t1
LEFT JOIN t2
ON t1.Id = t2.Id
WHERE contains(t1.Field1,@word) or contains(t2.Field2,@word)
My idea here is to check if t1.Field1 contains @word, if it is true - no need to check the second conditon, if it is false - check if the second contains. But I already realized that this does not work, and this request fulfills and contains, and wastes time on unnecessary work. So I want to ask how I can avoid these unnecessary predicates being executed.
source to share
I once had a similar question and came across Mladen Praidich's insightful article:
In short, T-SQL does not support short-circuiting boolean languages in the way that imperative languages like C # are not guaranteed, since the order in which predicates are evaluated is not guaranteed.
The only way to achieve ordered evaluation of predicates would be to use a CASE WHEN clause, which evaluates the predicates from left to right and stops at the first one that is true. If the potential performance gain is warranted, you can rewrite your query as:
SELECT *
FROM t1
LEFT JOIN t2
ON t1.Id = t2.Id
WHERE
Case
When contains(t1.Field1,@word) Then 1
When contains(t2.Field2,@word) Then 1
Else 0
= 1
There are certain situations where this approach can be useful. However, I would not recommend it as a common practice as it makes the code less readable and may even slow things down.
source to share