CODEWITHSUNDEEP
sql-serverperformancet-sqlrefactoring
Levan
Levan

Reputation: 642

Refactor overheaded T-SQL query

Is there a way to refactor this query so it no longer makes so many GetRemainingAmount(@Id) function calls? I tried naming it in select block e.g. Amount and then use it in Where and Group it's compilation error. 

 SELECT SomeAccountId ,
        GetRemainingAmount(@Id) ,
        Iso
 FROM   Accounts
 WHERE  GetRemainingAmount(@Id) > 0
 GROUP BY SomeAccountId ,
        GetRemainingAmount(@Id) ,
        Iso

Upvotes: 2

Views: 99

Answers (3)

liebs19
liebs19

Reputation: 549

Or you can derive it:

select 
    SomeAccountId,
    RemainingAmount,
    Iso
from (
    SELECT 
        SomeAccountId,
        GetRemainingAmount(@Id) as RemainingAmount,
        Iso
    FROM Accounts
) _Data
WHERE RemainingAmount > 0
GROUP BY 
    SomeAccountId,
    RemainingAmount,
    Iso

Upvotes: 1

paparazzo
paparazzo

Reputation: 45096

Could use a distinct to eliminate the GROUP BY

SELECT  distinct 
        SomeAccountId ,
        GetRemainingAmount(@Id) ,
        Iso
 FROM   Accounts
 WHERE  GetRemainingAmount(@Id) > 0

Upvotes: 1

marc_s
marc_s

Reputation: 754488

You can always do something like this, using a CTE (Common Table Expression) to isolate that call and do it only once:

;WITH CTE AS 
(
   SELECT 
      SomeAccountId,
      RemainingAmount = GetRemainingAmount(@Id),
      Iso
   FROM   
      Accounts 
)
SELECT
    SomeAccountId, RemainingAmount, Iso
FROM 
    CTE
WHERE  
    RemainingAmount > 0
GROUP BY 
    SomeAccountId,
    RemainingAmount,
    Iso

But quite honestly, if there's no aggregate function like SUM or AVG involved, I don't see the point of the GROUP BY clause in the first place ....

Upvotes: 1

Related Questions