SQL : coalesce to avoid OR in optional parameters
Next, we have a string comparison based on product code. These also can be very efficiently rewritten by using LIKE. Normally, LIKE is much slower than using equals, but if you do not use any wild cards and it allows you to eliminate an "OR" in your WHERE clause, it may actually be more efficient:
select *
from Data
where
Date between coalesce(@MinDate, '1/1/1900') and coalesce(@MaxDate, '12/31/2999') and
Amount between coalesce(@MinAmount,-999999999) and coalesce(@MaxAmount,999999999) and
ProductCode like coalesce(@ProductCode,'%') and ...
|
No comments:
Post a Comment