Pages

Thursday, November 26, 2015

Scalar Function Used Multiple Times In Query ... will Impact Performance

If you can see the below query I have used scalar function multiple times in the query.When we used scalar function multiple times it will impact the overall execution of the query. 

Sample Query:
-- Scalar function Used Multiple Times in Query
SELECT 
 CASE WHEN [isValidEmailTableValue](Email) = 1 THEN 'Good Email Address' 
   WHEN [isValidEmailTableValue](Email) = 0 THEN 'Bad Email Address'  
 END As EmailOutput,
FROM CUSTOMER WITH (NOLOCK)

How to avoid the above situation and try to remove the multiple uses of the scalar function. Same above query I have generated without using a scalar function.


-- Avoid to use Same scalar function multiple times in Query
SELECT 
 CASE WHEN EmailFun.EmailValue = 1 THEN 'Good Email Address' 
   WHEN EmailFun.EmailValue = 0 THEN 'Bad Email Address'  
 END As EmailOutput,
FROM CUSTOMER WITH (NOLOCK)
CROSS APPLY  [isValidEmailTableValue](Email) EmailFun -- Table Value Function Used as a Table

No comments:

Post a Comment