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
)
精彩评论