Some SQL server developers hate dynamic SQL queries.
There might be below possible causes:
1. Readability
of dynamic SQL queries are less compare to normal T-SQL script.
2. They
might think dynamic-SQL is cause of SQL-Injection.
3. They
might think dynamic T-SQL forces query recompilation.
4. They
might think dynamic-TSQ degrade query performance.
Yes I agree with point 1 but point 2, 3 and 3 are
partially incorrect. Dynamic SQL can be prepared and parametrized and some
cases it performs much better than normal T-SQL queries. In the application
code all the in line queries are example of dynamic SQL. So most of cases we
will not get significant performance difference. So don’t fear with dynamic
SQL. Don’t execute dynamic SQL queries
like:
EXECUTE(@vcDynamicQuery)
Instead of this use system stored procedure
SYS.SP_EXECUTESQL which reduces the probability of SQL injection and
parameterized the SQL queries. Here we
are going to discuss the scenario where dynamic SQL queries will perform better
than T-SQL queries:
When columns and its values in the parameter is very
much dynamic in nature and it is not practically possible to write using
IF-ELSE. For example: Let’s assume there is a business need to get the mobile
numbers of all users on the base if value of parameter and if value parameter
is NULL then ignore that filter. T-SQL query would be something like this:
DECLARE
@ntUserID AS BIGINT
DECLARE
@vcUserName AS VARCHAR(100) = 'User_101'
DECLARE
@ntUserPoint AS SMALLINT
= 70
DECLARE
@ntUserTypeID AS TINYINT
SELECT
vcMobileNumber
FROM
tblUser
WHERE
(
((@ntUserID
IS NULL) OR (@ntUserID IS NOT NULL AND ntUserID =
@ntUserID)) AND
((@vcUserName
IS NULL) OR (@vcUserName IS NOT NULL AND vcUserName = @vcUserName)) AND
((@ntUserPoint
IS NULL) OR (@ntUserPoint IS NOT NULL AND ntUserPoint =
@ntUserPoint)) AND
((@ntUserTypeID
IS NULL) OR (@ntUserTypeID IS NOT NULL AND ntUserTypeID =
@ntUserTypeID) )
)
Total logical read: 815
Note: Let’s assume user table have below index:
CREATE INDEX NCI_vcUserName
ON tblUser (vcUserName)
INCLUDE(vcMobileNumber,ntUserPoint)
In
this scenario would better to write same script using dynamic SQL:
DECLARE
@ntUserID AS BIGINT
DECLARE
@vcUserName AS VARCHAR(100) = 'User_101'
DECLARE
@ntUserPoint AS SMALLINT
= 70
DECLARE
@ntUserTypeID AS TINYINT
DECLARE
@vcQueryText AS NVARCHAR(MAX)
SET
@vcQueryText = N'SELECT
vcMobileNumber
FROM
tblUser
WHERE 1 =
1 '
IF
@ntUserID IS NOT
NULL
SET @vcQueryText += ' AND ntUserID = @ntUserID'
IF
@vcUserName IS NOT
NULL
SET @vcQueryText += ' AND vcUserName = @vcUserName'
IF
@ntUserPoint IS NOT
NULL
SET @vcQueryText += ' AND ntUserPoint = @ntUserPoint'
IF
@ntUserTypeID IS NOT
NULL
SET @vcQueryText += ' AND ntUserTypeID = @ntUserTypeID'
EXECUTE SP_EXECUTESQL
@vcQueryText,
N'@ntUserID AS BIGINT ,@vcUserName AS
VARCHAR(100),@ntUserPoint AS SMALLINT ,@ntUserTypeID AS TINYINT',
@ntUserID,
@vcUserName,
@ntUserPoint,
@ntUserTypeID
Total
logical read: 3
Comparison
of cost using execution plan:
It is obvious that in this example dynamic SQL in
much better than normal T-SQL queries since dynamic SQL is able to use index.
Note: Dynamic SQL queries are not allowed in SQL
functions so in that cast think alternative of dynamic SQL. Don’t create stored
procedure instead of SQL function to use dynamic SQL.
No comments:
Post a Comment