Use of function calls in stored procedure sql server 2005?

Question

Use of function calls in where clause of stored procedure slows down performance in sql server 2005?

SELECT * FROM Member M 
WHERE LOWER(dbo.GetLookupDetailTitle(M.RoleId,'MemberRole')) != 'administrator' 
AND LOWER(dbo.GetLookupDetailTitle(M.RoleId,'MemberRole')) != 'moderator'

In this query GetLookupDetailTitle is a user defined function and LOWER() is built in function i am asking about both.

Definitely not enough information! What function call? May we see the SQL? — John Hartsock, Sep 21 '11 at 19:26
Applying a function to a column in the where clause negates the ability to use any index on that column. This is often referred to as a non-sargable query. — Joe Stefanelli, Sep 21 '11 at 19:28

Martin Smith · Accepted Answer · 2011-09-21T19:59:27.570

Yes.

Both of these are practices to be avoided where possible.

Applying almost any function to a column makes the expression unsargable which means an index cannot be used and even if the column is not indexed it makes cardinality estimates incorrect for the rest of the plan.

Additionally your dbo.GetLookupDetailTitle scalar function looks like it does data access and this should be inlined into the query.

The query optimiser does not inline logic from scalar UDFs and your query will be performing this lookup for each row in your source data, which will effectively enforce a nested loops join irrespective of its suitability.

Additionally this will actually happen twice per row because of the 2 function invocations. You should probably rewrite as something like

SELECT M.* /*But don't use * either, list columns explicitly... */
FROM Member M 
WHERE NOT EXISTS(SELECT * 
                 FROM MemberRoles R 
                 WHERE R.MemberId = M.MemberId 
                 AND R.RoleId IN (1,2)
                 )

Don't be tempted to replace the literal values 1,2 with variables with more descriptive names as this too can mess up cardinality estimates.

see [this post](http://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargable) for more information on non-sargable queries and some strategies to avoid them. — Maciej, Sep 21 '11 at 19:41
ok will built in functions like LOWER, UPPER , LTRIM hits performance if i use it in where clause ? — Another Dev on the List, Sep 21 '11 at 20:18
@user441052 - Yes, they will prevent use of an index to look up rows matching the predicate. If you want case insensitive comparisons make sure you are storing your data in columns with case insensitive collation. If you typically have left trailing space that messes up your comparisons do a one off tidy up and fix your queries so it gets removed at `insert` / `update` time. — Martin Smith, Sep 21 '11 at 20:21

score 0 · Answer 2 · answered Sep 21 '11 at 19:38

0

Using a function in a WHERE clause forces a table scan.

There's no way to use an index since the engine can't know what the result will be until it runs the function on every row in the table.

answered Sep 21 '11 at 19:38

JNK

63,321
15
122
138

`LEFT` is not sargable AFAIK. [It is included in this connect item as one that could be but isn't.](http://connect.microsoft.com/SQLServer/feedback/details/526431/make-more-functions-sargable) – Martin Smith Sep 21 '11 at 19:40
@MartinSmith - Thought it was but it was not. Corrected the post. – JNK Sep 21 '11 at 19:43

score 0 · Answer 3 · answered Sep 21 '11 at 19:46

You can avoid both the user-defined function and the built-in by

defining "magic" values for administrator and moderator roles and compare Member.RoleId against these scalars
defining IsAdministrator and IsModerator flags on a MemberRole table and join with Member to filter on those flags

Use of function calls in stored procedure sql server 2005?

3 Answers3