Rewriting query to prevent multiple calls to a function

Question

How would I rewrite this query to be performant by executing the SQL function only once?

SELECT Top 1 Id, Name
  FROM Users U 
       INNER JOIN UserDetail D on U.Id = D.Id
       LEFT OUTER JOIN CreditCards C ON C.Id = U.Id AND UserHasCC(U.Id) = 1
       LEFT OUTER JOIN CreditCardDetails CD on C.CCID = C.CCID AND UserHasCC(U.Id) = 1
WHERE
       ((CD.active = 1 and UserHasCC(U.Id) = 1) OR UserHasCC(U.Id) = 0) and
       U.active = 1 and
       ((C.IsInternational = 1 and UserHasCC(U.Id) = 1) OR UserHasCC(U.id = 0)

Basically, the query gets all users that don't have credit cards and those that have active international credit cards.

Performaxed it is because the `SELECT` function of SQL is once called. Or are you concerned that the optimizer doesn't realize that it doesn't need to call `UserHasCC()` more than once per row? If you really wanted to be explicit you could change the `WHERE` clause to start with a `CASE UserHasCC...` to explicitly invoke the function only once. Oh, and that last function call with the boolean expression as an argument is probably a bit off. (Bad pun?) — HABO, Mar 28 '12 at 19:23
What purpose does the `UserHasCC()` function serve that isn't already encapsulated in the database structure? If a user doesn't have a credit card, wouldn't a `LEFT JOIN` from U to C return a null set? — mikurski, Mar 28 '12 at 19:29
@DotnetDude It seems I was wrong regarding how often SQL will execute a function but at least [I'm in good company](http://stackoverflow.com/a/6036296/119477) In any case check your query plan and see if its evaluated once or multiple times. — Conrad Frix, Mar 28 '12 at 20:00

score 1 · Answer 1 · answered Mar 28 '12 at 19:33

Based on your explanation of the intented query behavior, I believe that this might be close:

SELECT Top 1 Id, Name
  FROM Users U 
       INNER JOIN UserDetail D on U.Id = D.Id
       LEFT OUTER JOIN CreditCards C ON C.Id = U.Id
       LEFT OUTER JOIN CreditCardDetails CD on C.CCID = C.CCID AND CD.Active = 1
WHERE U.Active = 1 and (c.id is null 
                        or (c.IsInternational = 1 
                            and UserHasCC(U.Id) = 1))

I could not be certain but connections and names of objects suggest to me that UserHasCC has no real value. Perhaps it encapsulates flags from tables not shown here.

John Sobolewski · Accepted Answer · 2012-03-28T19:29:57.543

0

This looks like it accomplishes the same logic.

 SELECT Top 1 Id, Name
  FROM Users U 
       INNER JOIN UserDetail D on U.Id = D.Id
       LEFT OUTER JOIN CreditCards C ON C.Id = U.Id 
       LEFT OUTER JOIN CreditCardDetails CD on C.CCID = C.CCID 
   WHERE
         U.active = 1
         AND
         (
             UserHasCC(U.Id) = 0
             OR
             (
                UserHasCC(U.Id) = 1
                and
                (
                  C.IsInternational = 1
                  OR
                  CD.active = 1
                )

         )

edited Mar 28 '12 at 19:29

answered Mar 28 '12 at 19:22

John Sobolewski

4,512
1
20
26

Ok. I think that simplifies the logic you were looking for. removes the redundant stuff from the left joins and I think this is easier to understand. – John Sobolewski Mar 28 '12 at 19:31

Rewriting query to prevent multiple calls to a function

2 Answers2