CODEWITHSUNDEEP
sqlsql-serverwhere-clause
Test_User
Test_User

Reputation: 171

SQL Server - Function call in WHERE condition

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.

Upvotes: 1

Views: 2341

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93704

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

Upvotes: 1

Related Questions