Function in where clause degrade performance

When function are used in where clause it majorly affect the performance of query if query is intended to result large number of rows.

This is because function will force to evaluate for each row of table data involved to make the comparison which will force optimizer to use Index scan or Table Scan instead of Index Seek if proper index involve.

Lets see with an example how function in where clause affect performance. For demo purpose I am making use of AdventureWorks2008R2 database table Person.Person

First I am creating an Nonclustered index on FirstName column of Person table.

create index ix_person_firstname

on Person.Person(FirstName) include(LastName,MiddleName)

Command(s) completed successfully.

Now lets fire two  query, one which is using substring function in where clause and another which is simply written but both are producing the same output. And check the relative batch cost of each query. Set the execution plan on

select FirstName,LastName,MiddleName from Person.Person

where FirstName like 'AB%'

select FirstName,LastName,MiddleName from Person.Person

where substring(FirstName,0,3) = 'AB'

 Finding from execution Plan:

1        The first query use Index Seek to find the record where as the second query with Substring function in where clause uses Index scan which affect the performance as compare to index seek. Because Function in where clause force optimizer to evaluate the value of where predicate for each record in table to bring the matching criteria of records.

2.       

           If  we see the relative batch cost of both the query, then we are finding query without function in where predicate took just 3% of total batch cost and query with function in where clause took 97% of total cost of batch.

Conclusion: Query without function in where predicate perform very well as compared to query with function in where clause. Try to avoid function in where predicate.    

If there is only one thing which makes coder always chill whatever would be the situation, probably it would be code, hmm I love :)

 

Post Reference: Vikram Aristocratic Elfin Share