Functions and WHERE clause and suboptimal query plans

02 May,2013 by Jack Vamvas

Using functions in WHERE clauses can cause query performance problems. The query optimizer attempts to create a cardinality estimate. If the Optimizer does not know the value to look up – it will base the cardinality estimate on the average per distinct value.

It is more than likely using the average of every distinct value – will lead to a less than optimized plan.

There is a benefit for developers using functions. Quite often data may not be clean and  a function may be used to deal with formatting or NULL .A developer may develop  a function – to be used across multiple stored procedures.

The Optimizer doesn’t know the value in advance – and as you can see from the example below, a new value is passed in.

 Designing queries to exploit the Statistics is critical for sql performance. There are many workarounds to using functions in the WHERE clause.  Deciding on which workaround is defined by the function complexity.

 An example workaround , would be to place the logic as an INNER JOIN . If the function was using a date lookup table – then creating an INNER JOIN to the date lookup table, would create the data set once , place in memory

An simple example of a function used in a WHERE clause and a workaround. Make sure you check the Execution before and after, to see the difference. Read this post to test properly - SQL Server - Query baseline testing - SQL Server DBA

Declare @period datetime
SET @period = avalue

Select t1.col1,t1.col2,t1.mydate 
FROM 
myTable t1
ON anotherTable T2 ON T2.main_id = t1.main_id
WHERE 
MYDB.dbo.fn_(t1.usedDate) = @period

--an example workaround


Declare @period datetime
SET @period = avalue

Select t1.col1,t1.col2,t1.mydate 
FROM 
myTable t1 
INNER JOIN anotherTable T2 ON T2.main_id = t1.main_id
INNER JOIN dateLookUpTable dl ON dl.adate = t1.mydate

 Read More

Filtered statistics, T-SQL best practises for T-SQL Tuesday

SQL Server Query Optimizer and Statistics

SQL Server - Index missing Statistics

SQL Server Performance Checklist - SQL Server DBA


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Functions and WHERE clause and suboptimal query plans


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer