1

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.

Improve this question
2
  • Definitely not enough information! What function call? May we see the SQL? Commented Sep 21, 2011 at 19:26
  • 4
    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. Commented Sep 21, 2011 at 19:28

3 Answers 3

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

see this post for more information on non-sargable queries and some strategies to avoid them.
ok will built in functions like LOWER, UPPER , LTRIM hits performance if i use it in where clause ?
@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.
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.

Improve this answer

2 Comments

LEFT is not sargable AFAIK. It is included in this connect item as one that could be but isn't.
@MartinSmith - Thought it was but it was not. Corrected the post.
0

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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.