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
