March 12, 2009 at 11:12 pm
hi all,
i have two tables. In tbl1 a column "nou" and in tbl2 column "qty" and "p_id".
eg
p_id qty nou
a001 1 2
a001 2 3
a001 3 5
a001 2 4
a002 2 3
a002 3 4
and i need the following output
p_id qty nou total
a001 1 2 30
a001 2 3 30
a001 3 5 30
a001 2 4 30
a002 2 3 18
a002 3 4 18
the column total is computed column (1*2+2*3+3*5+2*4) where p_id=a001
and (2*3+3*4) where p_id=a002
March 13, 2009 at 1:09 am
Please post the table structre with sample data. Anyway i have created it for you.
create table price
(
p_id varchar(10),
qty int,
nou int
)
go
insert into price
select 'a001',1,2
union all
select 'a001',2,3
union all
select 'a001',3,5
union all
select 'a001',2,4
union all
select 'a002',2,3
union all
select 'a002',3,4
select Price.p_id,Price.qty,Price.nou,final.sum_calc
from Price,
(select p_id,sum(calc)as sum_calc
from
(select p_id,qty,nou,qty*nou as calc
from price)a
group by p_id)final
where Price.p_id = final.p_id
karthik
March 13, 2009 at 3:29 am
Also asked and answered here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=121584
N 56°04'39.16"
E 12°55'05.25"
March 13, 2009 at 4:45 am
Now the question is,
which method will give good performance?
Peso Method:
SELECT s.p_id,
s.qty,
s.nou,
(SELECT SUM(x.qty * x.nou) FROM Price AS x WHERE x.p_id = s.p_id)
FROM Price AS s
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 6 ms, elapsed time = 6 ms.
(6 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
|--Compute Scalar(DEFINE:([Expr1002]=[Expr1002]))
|--Nested Loops(Left Outer Join, OUTER REFERENCES:(.[p_id]))
|--Table Scan(OBJECT:([IPStatic].[dbo].[price] AS ))
|--Hash Match(Cache, HASH:(.[p_id]), RESIDUAL:(.[p_id]=.[p_id]))
|--Compute Scalar(DEFINE:([Expr1002]=If ([Expr1010]=0) then NULL else [Expr1011]))
|--Stream Aggregate(DEFINE:([Expr1010]=COUNT_BIG([x].[qty]*[x].[nou]), [Expr1011]=SUM([x].[qty]*[x].[nou])))
|--Table Scan(OBJECT:([IPStatic].[dbo].[price] AS [x]), WHERE:([x].[p_id]=.[p_id]))
Mine:
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 4 ms.
(6 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
|--Merge Join(Inner Join, MERGE:([price].[p_id])=([price].[p_id]), RESIDUAL:([price].[p_id]=[price].[p_id]))
|--Compute Scalar(DEFINE:([Expr1002]=If ([Expr1010]=0) then NULL else [Expr1011]))
| |--Stream Aggregate(GROUP BY:([price].[p_id]) DEFINE:([Expr1010]=COUNT_BIG([price].[qty]*[price].[nou]), [Expr1011]=SUM([price].[qty]*[price].[nou])))
| |--Sort(ORDER BY:([price].[p_id] ASC))
| |--Table Scan(OBJECT:([IPStatic].[dbo].[price]))
|--Sort(ORDER BY:([price].[p_id] ASC))
|--Table Scan(OBJECT:([IPStatic].[dbo].[price]))
Please don't miskate me that i am saying mine code is best. Just i wanted to know which method is good? Thats what my intention.
karthik
March 13, 2009 at 4:53 am
A good look at the reads required will reveal something
-- Peso
Table '#73E9A71D'. Scan count 7, logical reads 7, physical reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 111 ms.
-- Karthik
Table 'Worktable'. Scan count 3, logical reads 21, physical reads 0.
Table '#73E9A71D'. Scan count 1, logical reads 1, physical reads 0.
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 117 ms.
N 56°04'39.16"
E 12°55'05.25"
March 16, 2009 at 11:43 pm
Peso (3/13/2009)
A good look at the reads required will reveal something
-- Peso
Table '#73E9A71D'. Scan count 7, logical reads 7, physical reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 111 ms.
-- Karthik
Table 'Worktable'. Scan count 3, logical reads 21, physical reads 0.
Table '#73E9A71D'. Scan count 1, logical reads 1, physical reads 0.
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 117 ms.
How you get this number?
I have tested the statistics time & io. But i got different number.
create table price
(
p_id varchar(10),
qty int,
nou int
)
go
insert into price
select 'a001',1,2
union all
select 'a001',2,3
union all
select 'a001',3,5
union all
select 'a001',2,4
union all
select 'a002',2,3
union all
select 'a002',3,4
set statistics io on
set statistics time on
select Price.p_id,Price.qty,Price.nou,final.sum_calc
from Price,
(select p_id,sum(calc)as sum_calc
from
(select p_id,qty,nou,qty*nou as calc
from price)a
group by p_id)final
where Price.p_id = final.p_id
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 4 ms.
(6 row(s) affected)
Table 'price'. Scan count 2, logical reads 2, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Peso Method:
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 5 ms.
(6 row(s) affected)
Table 'price'. Scan count 3, logical reads 3, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
karthik
March 17, 2009 at 1:17 am
Execution plans
N 56°04'39.16"
E 12°55'05.25"
March 17, 2009 at 1:46 am
Execution plans in my box.
karthik
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply