sql server programming: sum question -- sql server programming

Re: sum question

Tom Cooper
6/24/2007 2:03:39 AM

I suspect it may vary depending on the query (for example how complex is the
query and how easy is it for the query analyzer to recognize that the
expression is duplicated). But the way to tell for any given query is to
look at the execution plan.

Your sample query as you gave it has a syntax error since tbl2.col3 is not
contained in an aggregate function. So I used the following query in the
Northwind database on a box running SQL2K SP3.

Select Sum(p.UnitsInStock*p.UnitsOnOrder) As A,
Sum(p.UnitsInStock*p.UnitsOnOrder) * Max(d.Quantity) As B
From Products p
Inner Join [Order Details] d On p.ProductID = d.ProductID;

The execution plan showed that in this case the sum was only computed once.

Tom

[quoted text, click to view]

Re: sum question

JB
6/25/2007 5:07:00 PM

Thanks for the replies. It appears that there is no definite answer to the
question and that if I want to be sure that the sume is evalutated only once,
I should use a temp table for the sums, from tbl1 in my example, and then
inner join to tbl2 to generate the output. (I wasn't being particualarly
careful about the syntax in my example0 The focus was on whether or not sql
server would always recoginise that the sum appears more than once in the
query and thus evaluate it only once.
--
JB

[quoted text, click to view]

Re: sum question

Tom Cooper
6/25/2007 8:53:37 PM

If it were me, I would not do that (or rather, I would not do that without
more investigation). First, SQL may well not be computing the sum twice, in
which case the temporary table is just additional overhead. Second, it
could well be that even if SQL is computing the sum twice, the cost of
creating the temporary table could exceed the cost of doing the sum twice.
Third, I would expect that whatever you would be doing to build the
temporary table could be done with a derived table in your query, and that
is usually at least as efficient as a temporary table and is often more
efficient. Finally, in my experience, if this is a badly performing query,
it is much more likely that the cause is the data access from the disk
subsystem, not any calculations done after the data has been retrieved.

So I would look at the execution plan and try to determine what the costs of
each part of the query are and focus on the most expensive costs first.
Something as simple as adding an index might be what is needed.

Or, of course, another approach would be just to try to rewrite the query
with a temp table and see if that made a significant improvement. If it
does, ignore everything I've said (except possibly the part about using a
derived table and you might also consider a table variable rather than a
temp table) and go ahead.

As always, YMMV and "it depends".

Tom

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.