August 28, 2003 at 4:54 pm
The following example is simplified from my real-world problem, but basically I need to know if something like this is possible:
select (colA+colB) as sum1,
sum1/colC as calc1
from SomeTable
This T-SQL does not work, as I cannot reference the first column by its alias. Obviously, I can express the second column as (colA+colB)/colC (seems redundant and less efficient), but is there a way to build upon something that has already been calculated within the select statement?
August 28, 2003 at 5:40 pm
Create Table Tmp(ColA Money,COlB Money,ColC Money)
GO
Insert Tmp Values(1,2,3)
Insert Tmp Values(4,5,6)
Insert Tmp Values(7,8,9)
GO
Select ASum,Asum/ColC
From (
Select ColA+ColB as ASum,ColC
From Tmp) as F(ASum,COlC)
GO
Drop Table Tmp
August 28, 2003 at 7:59 pm
How about:
DECLARE @sum1 int
SELECT @sum1 = colA + colB
FROM SomeTable
SELECT @sum1/colC
FROM SomeTable
GO
August 29, 2003 at 10:54 am
Jnelson,
There is a much easier way that does not require variables or temp tables. Just use a derived table as follows:
select sum1/colc
from sometable s1
inner join (select (cola + colb) as Sum1,
iRowId
from sometable) as s2
on s1.iRowId = s2.iRowId
This example assumes that iRowId is a PK, or unique valued column.
Best of luck!
wavie davie 🙂
August 29, 2003 at 1:46 pm
A sincere thanks to those who replied.
Mr. 5409045121009: What does the F(..) do? I haven't seen this function before? It does work, I just don't understand it.
Lightning: The value returned by the sum is for the last row only, and I need the sum for each row.
waviedavie: This is very clear and works.
August 29, 2003 at 11:54 pm
Well Jnelson, Mr. 5409045121009 is using corelated Sub-query. Thats one way of doing it. The other way to do is,
Select (ColA/ColB) as Sum1, ColB into #TempTab from SomeTable
Go
Select (Sum1/ColB) as Calc1 from #TempTab
Go
In doing so, if there other places in your Stored procedure that u need to use this value(Sum1), then its better you take it into a temp table and then refer it. The Sub query may take a longer time if the calculate value is refered more than once in the Stored procedure. Hope I have been help full.
August 31, 2003 at 5:18 am
5409045121009's solution is a derived table, not a correlated subquery. The F(..) that looks like a function is the table alias of the derived table. It's just like the derived table waviedavie used ("inner join (...) as s2") except 5409045121009 added the column names.
I would use the 5409045121009 solution myself, although I don't personally bother adding column names to aliases. I don't know why you would use a self-join like waviedavie did.
September 1, 2003 at 10:55 pm
All the info mentioned in this chain very very useful.
But r we forgetting the fundamental problem tht was mentioned by jnelson?? Will it not be better to repeat the computation rather than use all these approaches?? What i mean is
Select (colA+ColB), (colA+colB)/ColC From SomeTable
Is the above query not going to perform better than the other suggest methods??.....
September 2, 2003 at 11:49 am
The time to do most math calculations is small compared to network IO. I'd be very surprised if you could see much difference. An index on a wide table will probably have much more effect. If the expression is commonly used and complicated, maybe a computed column can be used. If you are real desperate for speed, the value can be pre-computed in a trigger.
If the query does not return results, then a local variable can be used to store the value. I tried this just to see if there was much difference (5 columns using 5 adds vs. 5 columns using 1 add). Performance improved by about 10% (206000 records in .813 seconds improved to .713 seconds). This query was done using a wide table. Adding an index using the columns in the calculation increased performance by 20% (.713 seconds to .53 seconds).
Adding network IO by returning a recordset washes out the difference on my connection.
Randy
Helpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
September 3, 2003 at 7:55 am
You have to look at the execution plans before making assumptions about the efficiency of different approaches.
Just because you repeat a calculation several times does not mean it will have to be executed twice. It does give you the opportunity to make a mistake in one of the copies that may be very hard to debug.
I like the derived table approach because I believe it is easier to read. Take a query with a complex calculation, put parenthesis around it and give it an alias, and now you can write a query around it that uses the calculated field as many times as you like; for other calculated fields, in the GROUP BY and ORDER BY clauses, etc.
I have taken fairly complicated procedural code like this:
Read next record
Calculate some values
Calculate more values based on those
Lookup other related data
Calculate some more
Write out result record
Repeat until EOF
and turned it into:
INSERT INTO OutputTable (...)
SELECT <final calculations>
FROM (
SELECT <more calculations>
FROM (
SELECT <original fields>,<calculated fields>
FROM Table1 JOIN Table2 ...
) x1
) x2
As rstone points out, the calculations end up in one COMPUTE box in the execution plan that takes 0% of the time.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply