Geek asks and answers

Using an aggregate function once, not several times, inside a stored procedure

I have a stored procedure that uses a function inside it, and the function expects two parameters. My problem is related to performance issue, see below

 CASE 
 WHEN (DATEDIFF(MINUTE,dbo.FunctionName(DatetimeField, DatetimeID), dbo.FunctionName(DatetimeField, DatetimeID))/60.0) > 8 THEN
      (DATEDIFF(MINUTE,dbo.FunctionName(DatetimeField, DatetimeID), dbo.FunctionName(DatetimeField, DatetimeID))/60.0)
 Else 0
 END 
 Else
 0
 END)
 Else
(DATEDIFF(MINUTE,dbo.FunctionName(DatetimeField, DatetimeID), dbo.FunctionName(DatetimeField, DatetimeID))/60.0)-T.lunch
END
     as 'Total'

Now what I want to do is create a temporary table so that I can use it to call the function instead of calling the function every time it gets to these rows with hundreds of thousands of records. Any help is appreciated.

+3
source share
2 answers

You can reuse the expressions inside the query as follows:

select (case when NewValue > 8 then NewValue else 0 end) xyz
from T
cross apply (
 select NewValue = dbo.FunctionName(DatetimeField, DatetimeID)
) x

, , . .

:

DECLARE @NewValue int = dbo.FunctionName(DatetimeField, DatetimeID);
+1

, (, , ), , :

select DatetimeField, DatetimeID, FunctionName(DatetimeField, DatetimeID) as val
into #tmp
from (select distinct DatetimeField, DatetimeID
      from table
     ) t

.

, , 0 , .

, , . , , .

0

More articles:


All Articles