SQL Server - Function call in WHERE condition

Question

I've a question regarding making a function call in WHERE condition of a SELECT statement in my stored procedure. Here is my sample query that mimics my original. I've 3 million records in the production database for primary table CDP. My concern is if there would be any performance impact of using the function testFunction(@UserId) in my WHERE clause.

SELECT C.AwardId, C.ProgramName, Count(ClientId) as Intakes FROM CDP C 
     LEFT JOIN UserRoleEntity URE ON C.AwardId = URE.EntityId 
     LEFT JOIN UserRole UR ON URE.UserRoleId = UR.Id AND UR.CDPUserId = @UserId
WHERE (1=1) -- here I've other filtering criteria for the results
 AND (   testFunction(@UserId) = 0
      OR (UR.ID IS NOT NULL AND UR.CDPUserId = @UserId))
GROUP BY C.AwardId, C.ProgramName

My question is - Does the function get called for EVERY record in the result set? Since there are 3 million records in CDP table, does the function get called 3 million times and impact the performace?

My assumption is that - the function in WHERE clause would be called ONLY ONCE on the final result set, to filter the results.

Will it be ONCE or 3 million times is my question. BTW - my function contains a simple select statement, with inner joins to 2 tables, and returns a numeric value.

Pரதீப் · Accepted Answer · 2015-02-12 16:45:56Z

1

Instead you can do this. Assign the function result to a variable and use it in where clause

declare @check int,
select @check = dbo.testFunction(@UserId)

SELECT C.AwardId, C.ProgramName, Count(ClientId) as Intakes FROM CDP C 
     LEFT JOIN UserRoleEntity URE ON C.AwardId = URE.EntityId 
     LEFT JOIN UserRole UR ON URE.UserRoleId = UR.Id AND UR.CDPUserId = @UserId
WHERE (1=1) -- here I've other filtering criteria for the results
 AND (   @check = 0
      OR (UR.ID IS NOT NULL AND UR.CDPUserId = @UserId))
GROUP BY C.AwardId, C.ProgramName

answered Feb 12, 2015 at 16:45

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

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

2 Comments

Tarik Over a year ago

Good workaround. Yet, I would first determine if you need it by looking at the query plan.

Test_User Over a year ago

Awesome! That totally works.. I don't know why I haven't thought about it. Thank you so much!!!

Collectives™ on Stack Overflow

SQL Server - Function call in WHERE condition

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related