CODEWITHSUNDEEP
sqlsql-serverperformanceuser-defined-functions
Another Dev on the List
Another Dev on the List

Reputation: 83

Use of function calls in stored procedure sql server 2005?

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.

Upvotes: 1

Views: 1603

Answers (3)

Martin Smith
Martin Smith

Reputation: 453298

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.

Upvotes: 1

devio
devio

Reputation: 37215

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

Upvotes: 0

JNK
JNK

Reputation: 65157

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.

Upvotes: 0

Related Questions