Sometimes we need to write WHERE predicate dynamically. For example: Let assume there is
business need to filter data on the basis of ntUserID when @btIsUser = 1 and on
the basis of ntPostID when @btIsUser = 0.
This script with dymaical predicate is one of the solution of this requirement:
This script with dymaical predicate is one of the solution of this requirement:
DECLARE @btIsUser AS BIT = 1
DECLARE @ntID AS BIGINT = 100
SELECT *
FROM tblComment
WHERE (
(
@btIsUser = 1
AND ntUserID = @ntID
)
OR (
@btIsUser = 0
AND ntPostID = @ntID
)
)
Total logical read: 64781
This script work fine except it is not able to use
indexes efficiently those are created with key column ntUserID and ntPostID due to logical operator OR. Due to which it has very
high number of logical read.
Best practice: Avoid using Logical opearors “OR” and “NOT” in the WHERE,ON
and HAVING caluses.
Altenatively we can write same
script in following way:
DECLARE @btIsUser AS BIT = 1
DECLARE @ntID AS BIGINT = 100
IF @btIsUser =
1
SELECT
*
FROM
tblComment
WHERE
ntUserID = @ntID
ELSE
SELECT
*
FROM
tblComment
WHERE
ntPostID = @ntID
Total logical read: 5
Query cost comparion :
We can see there is huge difference
of query cost. This diiference is due to first script is not able to perfom
index seek oprtaion.
Note: If number of IF-ELSE
statements are significanly very large to wite an equivalent script then use
Dynamic-SQL.
No comments:
Post a Comment