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

Work Around for Filtered Index Parameter Sniffing Problem

Problem Statement: The problem with Filtered index is, when a local variable is passed as a parameter to the query, Query Optimizer does not select the correct Index (Filtered index) even though the WHERE  condition (the selectivity) falls in the range of Filtered index.

Again I am using AdventureWork2008R2 database for explanation. We will make use of Person.Person table.

We are interested to fire this query

select PersonType  from Person.Person where PersonType = 'SP'

Here I am creating 2 indexes on the PersonType column, one Non Custered Index and another Filtered Index

create index ix_Person_PersonType

on Person.Person(PersonType)

Command(s) completed successfully.

create index fix_Person_PersonType

on Person.Person(PersonType) where PersonType = 'SP'

Command(s) completed successfully.

Now our indexes are in place, lets fire query to see the execution plan.

We are firing the query against Person table for PersonType like ‘SP’, and expecting Optimizer to pick up newly created Filtered Index.

select PersonType  from Person.Person where PersonType = 'SP'

Thats fantastic it picked up the right index what we were expecting. Here in the above query we have explicitly passed the value in the where predicate that is PersonType = ‘SP’

Let see what Optimizer will do if we pass the value through local variable.

declare @para varchar(2) = 'SP'

select PersonType  from Person.Person where PersonType = @para

Optimizer did not use the Filtered index even though the where predicate search the same records.

The reason behind is Optimizer don’t have idea what value the local variable will hold at compile time, so it trade off Filtered Index and choose the Non clustered index.

Workaround Solution: Option(RECOMPILE) we can use the statement level recompile statement to tell the engine to recompile the plan at execution time.

declare @para varchar(2) = 'SP'

select PersonType  from Person.Person where PersonType = @para

Here in this query we can see the @para value is not available at compile time only at run time it is exposed. So the alternative solution would be recompiling the  plan at run time with @para value is available.

declare @para varchar(2) = 'SP'

select PersonType  from Person.Person  where PersonType = @para option(recompile)

So the execution pan clearly tells that when you use execution level RECOMPILE option the correct index will be picked up. Still try to avoid statement level recompilation and make use of stored cache plan. This is just an work around solution.

If your code has bliss then do you think you need to go out in search of materialized happiness?? J

Post Reference: Vikram Aristocratic Elfin Share 

Parameter Sniffing with Filtered Index

Filtered Index is one of the fantastic advancement in the indexing arena, but with each great feature there is always a trade off, which we called it as an exception. The problem with Filtered index is, when a local variable is passed as a parameter to the query, Query Optimizer does not select the correct Index (Filtered index) even though the WHERE  condition (the selectivity) falls in the range of Filtered index.

Let’s take an example to clarify our point. Here I am using AdventureWork2008R2 database for explanation. We will make use of Person.Person table.

We are interested to fire this query

select PersonType  from Person.Person where PersonType = 'SP'

Now let me check Is there any index build up for PersonType column.

select name from sys.indexes where object_id = OBJECT_ID('Person.Person')

AK_Person_rowguid

PXML_Person_AddContact

PXML_Person_Demographics

XMLPATH_Person_Demographics

XMLPROPERTY_Person_Demographics

XMLVALUE_Person_Demographics

Ah! There is no index created on PersonType column, why wait lets create it

Here I am creating 2 indexes on the PersonType column, one Non Custered Index and another Filtered Index

create index ix_Person_PersonType

on Person.Person(PersonType)

Command(s) completed successfully.

create index fix_Person_PersonType

on Person.Person(PersonType) where PersonType = 'SP'

Command(s) completed successfully.

Lets fire the query to see our indexes created

select name from sys.indexes where object_id = OBJECT_ID('Person.Person')

AK_Person_rowguid

ix_Person_PersonType

fix_Person_PersonType

PXML_Person_AddContact

PXML_Person_Demographics

XMLPATH_Person_Demographics

XMLPROPERTY_Person_Demographics

XMLVALUE_Person_Demographics

Now our indexes are in place, its time to fire query to see whether optimizer is picking up correct index.

We are firing the query against Person table for PersonType like ‘SP’, and expecting Optimizer to pick up newly created Filtered Index.

select PersonType  from Person.Person where PersonType = 'SP'

Thats fantastic it picked up the right index what we were expecting. Here in the above query we have explicitly passed the value in the where predicate that is PersonType = ‘SP’

Let see what Optimizer will do if we pass the value through local variable.

declare @para varchar(2) = 'SP'

select PersonType  from Person.Person where PersonType = @para

Optimizer did not use the Filtered index even though the where predicate search the same records.

The reason behind is Optimizer don’t have idea what value the local variable will hold at compile time, so it trade off Filtered Index and choose the Non clustered index.

Conclusion: If your query hold local variable as a parameter passed, then try to avoid creating Filtered index on the same.

If something very precious to you is parting u, no need to pain up, there must be some good reason, don’t be sad, best moment yet to come, still waiting for u at your door step, knocking; listen the beauty of her knock, Celebrate happiness my friend. Here it goes happiness of completing 50^th article of the year, thank you my friend   :)

Post Reference: Vikram Aristocratic Elfin Share