开发者

in my sql statement I call sum twice for the same argument, is it duplicating the effort?

consider my sql query below; it is calling sum twice for the same argument. IS this duplicating the work done by the server. Is there a better way to do this?

SELECT      Status_Detail_Code, count(*) as 
   [Number of times assigned], round(sum(Duration)/60,2) as [total dur开发者_StackOverflow社区ation Hr]

 FROM         dbo.V_TIMELINE

 WHERE     (CADATE > N'20080101')  

group by Status_Detail_Code order by sum(Duration) desc


No, SQL Server reuses the aggregates.

In fact, if you build the query plan, you will see the SUM in a result set of an aggregation operator (like Stream Aggregate) denoted as something like Expr****.

The value of this expression will later be used as an input to the other operators.

Here's the sample query:

SELECT  ROUND(SUM(id), -1)
FROM    master
GROUP BY
        name
ORDER BY
        SUM(id) DESC

and it's plan:

  |--Compute Scalar(DEFINE:([Expr1004]=round([Expr1003],(-1))))
       |--Sort(ORDER BY:([Expr1003] DESC))
            |--Stream Aggregate(GROUP BY:([test].[dbo].[master].[name]) DEFINE:([Expr1003]=SUM([test].[dbo].[master].[id])))
                 |--Index Scan(OBJECT:([test].[dbo].[master].[ix_name_desc]), ORDERED BACKWARD)

As you can see, the aggregation is done once and stored in Expr1003.

Expr1003 is then reused in both the Sort operator (which processes the ORDER BY) and Compute Scalar (which processes ROUND)

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜