Scalar functions as discussed in the last post can be a performance bottleneck.Another place where Scalar functions ( both user defined and system ) can become a performance bottleneck is when used on a where clause of the query. Scalar functions when used on a where clause of a query can make the optimizer pick a table scan, even if the index is present, making the index useless.
/****** Object: Index [ncix_Supplier_product_customer_trans_date] ******/
CREATE NONCLUSTERED INDEX [ncix_Supplier_product_customer_trans_date]
ON [dbo].[Supplier_Product_Customer] ( [trans_date] ASC )
A non clustered index on trans_date column of supplier_product_customer table has been created.When one needs to find the list of transactions for a particular day ( cutting the time part of the , the most common method of writing the query would be
WHERE Datediff(dd, [trans_date], '11/19/2009') = 0
But,the problem with such a query is that it takes table scan instead of using the Non clustered index created on trans_date. The reason is that scalar function datediff used on trans_date column stops the query analyzer from using the index.
A better way of writing it would be
WHERE trans_date >= '20091119'
AND trans_date < '20091120'
The picture above shows a non clustered index seek instead of table scan. There are few other common scenarios where functions can be avoided. Let me briefly list them here
* Left() string fuction can be effectively replaced by like
* Usage of upper/lower string functions can be avoided
when the database has a case insensitive collation.
* isnull(col1,'xyz') = 'xyz' can be replaced by col1 = 'xyz'
or col1 is null
Note that OR conditions do use indexes and but at times they don't.
Please check before use.
* Getting data older than 60/n days query.
Standard way of doing it would be
Where datediff(dd, trans_date, getdate()) > 60
Where trans_date < CONVERT(VARCHAR, getdate()-60, 112)
To Conclude, one should try the best to avoid having scalar functions in where clauses.