How do I avoid this corelated subquery?

[Kannan]

Hello,
I have a situation which would essentially use a co-related subquery.
I am trying to avoid using a co-related subquery due to its slow
performanc and use a join statement instead.

Here is what I am trying to do:

Tables:
========
Limit
-------
RelID ProdID Days Amt1 Amt2
----- ------ ---- ---- ------
148 2 30 15000 30000
148 2 7 1000 2500

Temp Limits
-----------
RelID ProdID MaxDays Amt1 Amt2
----- ------ ----- ------ ------
148 2 1 7500 1000
148 2 7 17500 500
148 2 30 2500 2000
148 2 90 25000 750
148 2 180 7500 -300

I want the totals of Amt1 and Amt2 in the TempLimits table added to
each row in the Limits table where the Days in the Limits Table >=
MaxDays in TempLimits
So, when I run the query with the above data
I need to get

RelID ProdID Days Amt1 Amt2
----- ------ ---- ------ ------
148 2 30 42500 (15000+7500+17500+2500) 33500
(30000+1000+500+2000)
148 2 7 26000 (1000+7500+17500) 4000
(2500+1000+500)

************************************************** **************************
Here is the query that I have right now...

update Limit
set Amt1 = isnull(Amt1,0) + IsNull(temptable.temp_Amt1, 0)
, Amt2 = isnull(Amt2,0) + IsNull(temptable.temp_Amt2, 0)
from Limit inner join
(
select Templimits.relationship_id relationship_id,
Templimits.product_id product_id, sum(Templimits.Amt1) temp_Amt1,
sum(Templimits.Amt2) temp_Amt2
from TempLimits
WHERE TEMPLIMITS.MAXDAYS <= LIMIT.DAYS
group by limit.relationship_id, limit.product_id
) temptable
on Limit.relid = temptable.relid and
Limit.prodid = temptable.prodid

************************************************** ****************************
As you would see here, I am trying to use the condition for Days
(listed in the query in CAPS) inside the inner query which would not
work... Is there any way I can do this apart from using temp tables
and co-related subqueries?

Thanks in advance
Kannan

[John Gilson]

"Kannan" <pv*******@yahoo.com> wrote in message
news:b4*************************@posting.google.co m...

Hello,
I have a situation which would essentially use a co-related subquery.
I am trying to avoid using a co-related subquery due to its slow
performanc and use a join statement instead.

Here is what I am trying to do:

Tables:
========
Limit
-------
RelID ProdID Days Amt1 Amt2
----- ------ ---- ---- ------
148 2 30 15000 30000
148 2 7 1000 2500

Temp Limits
-----------
RelID ProdID MaxDays Amt1 Amt2
----- ------ ----- ------ ------
148 2 1 7500 1000
148 2 7 17500 500
148 2 30 2500 2000
148 2 90 25000 750
148 2 180 7500 -300

I want the totals of Amt1 and Amt2 in the TempLimits table added to
each row in the Limits table where the Days in the Limits Table >=
MaxDays in TempLimits
So, when I run the query with the above data
I need to get

RelID ProdID Days Amt1 Amt2
----- ------ ---- ------ ------
148 2 30 42500 (15000+7500+17500+2500) 33500
(30000+1000+500+2000)
148 2 7 26000 (1000+7500+17500) 4000
(2500+1000+500)

************************************************** **************************
Here is the query that I have right now...

update Limit
set Amt1 = isnull(Amt1,0) + IsNull(temptable.temp_Amt1, 0)
, Amt2 = isnull(Amt2,0) + IsNull(temptable.temp_Amt2, 0)
from Limit inner join
(
select Templimits.relationship_id relationship_id,
Templimits.product_id product_id, sum(Templimits.Amt1) temp_Amt1,
sum(Templimits.Amt2) temp_Amt2
from TempLimits
WHERE TEMPLIMITS.MAXDAYS <= LIMIT.DAYS
group by limit.relationship_id, limit.product_id
) temptable
on Limit.relid = temptable.relid and
Limit.prodid = temptable.prodid

************************************************** ****************************
As you would see here, I am trying to use the condition for Days
(listed in the query in CAPS) inside the inner query which would not
work... Is there any way I can do this apart from using temp tables
and co-related subqueries?

Thanks in advance
Kannan

CREATE TABLE Limits
(
relid INT NOT NULL,
prodid INT NOT NULL,
days INT NOT NULL,
amt1 INT NOT NULL,
amt2 INT NOT NULL,
PRIMARY KEY (relid, prodid, days)
)

CREATE TABLE TempLimits
(
relid INT NOT NULL,
prodid INT NOT NULL,
maxdays INT NOT NULL,
amt1 INT NOT NULL,
amt2 INT NOT NULL,
PRIMARY KEY (relid, prodid, maxdays)
)

UPDATE Limits
SET amt1 = amt1 + (SELECT SUM(amt1)
FROM TempLimits AS TL
WHERE TL.relid = relid AND
TL.prodid = prodid AND
TL.maxdays <= days),
amt2 = amt2 + (SELECT SUM(amt2)
FROM TempLimits AS TL
WHERE TL.relid = relid AND
TL.prodid = prodid AND
TL.maxdays <= days)
WHERE EXISTS (SELECT *
FROM TempLimits AS TL
WHERE TL.relid = relid AND
TL.prodid = prodid AND
TL.maxdays <= days)

--
JAG

[Erland Sommarskog]

Kannan (pv*******@yahoo.com) writes:
************************************************** **************************

Here is the query that I have right now...

update Limit
set Amt1 = isnull(Amt1,0) + IsNull(temptable.temp_Amt1, 0)
, Amt2 = isnull(Amt2,0) + IsNull(temptable.temp_Amt2, 0)
from Limit inner join
(
select Templimits.relationship_id relationship_id,
Templimits.product_id product_id, sum(Templimits.Amt1) temp_Amt1,
sum(Templimits.Amt2) temp_Amt2
from TempLimits
WHERE TEMPLIMITS.MAXDAYS <= LIMIT.DAYS
group by limit.relationship_id, limit.product_id
) temptable
on Limit.relid = temptable.relid and
Limit.prodid = temptable.prodid
************************************************* **************************

I can't see that that query can even compile. You are referring to
limit in the derived table, but I was under the belief that a derived
table cannot refer to tables in the outer part of the query. And in any
case, there are no relid and prodid columns in the derived table temptable.

I would suggest that you post:

o CREATE TABLE and CREATE INDEX statements for your tables.
o The actual query you run.
o The output when you run the UPDATE statement with
SET STATISTICS PROFILE ON.

This makes it a lot easier to give a more accurate response, and relieves
us from what guess what your query actually is.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

John Gilson (ja*@acm.org) writes:

UPDATE Limits
SET amt1 = amt1 + (SELECT SUM(amt1)
FROM TempLimits AS TL
WHERE TL.relid = relid AND
TL.prodid = prodid AND
TL.maxdays <= days),
amt2 = amt2 + (SELECT SUM(amt2)
FROM TempLimits AS TL
WHERE TL.relid = relid AND
TL.prodid = prodid AND
TL.maxdays <= days)
WHERE EXISTS (SELECT *
FROM TempLimits AS TL
WHERE TL.relid = relid AND
TL.prodid = prodid AND
TL.maxdays <= days)

John, when you say "TL.relid = relid", does not the prefix-less "relid"
refer to TempLimits?

Also, I am afraid that this is not going to perform overly well, since
SQL Server will resolve the same condition thrice. It is usually better
to have a derived table. If I understand this right it would be:

CREATE TABLE Limits
(
relid INT NOT NULL,
prodid INT NOT NULL,
days INT NOT NULL,
amt1 INT NOT NULL,
amt2 INT NOT NULL,
PRIMARY KEY (relid, prodid, days)
)

CREATE TABLE Templimits
(
relid INT NOT NULL,
prodid INT NOT NULL,
maxdays INT NOT NULL,
amt1 INT NOT NULL,
amt2 INT NOT NULL,
PRIMARY KEY (relid, prodid, maxdays)
)

INSERT Limits VALUES (148, 2, 30, 15000, 30000)
INSERT Limits VALUES (148, 2, 7, 1000, 2500)

INSERT Templimits VALUES (148, 2, 1, 7500, 1000)
INSERT Templimits VALUES (148, 2, 7, 17500, 500)
INSERT Templimits VALUES (148, 2, 30, 2500, 2000)
INSERT Templimits VALUES (148, 2, 90, 25000, 750)
INSERT Templimits VALUES (148, 2, 180, 7500, -300)

UPDATE Limits
SET amt1 = L.amt1 + coalesce(d.amt1, 0),
amt2 = L.amt2 + coalesce(d.amt2, 0)
FROM Limits L
JOIN (SELECT TL.relid, TL.prodid, L1.days,
amt1 = SUM(TL.amt1), amt2 = SUM(TL.amt2)
FROM Templimits TL
JOIN Limits L1 ON L1.relid = TL.relid
AND L1.prodid = TL.prodid
AND TL.maxdays <= L1.days
GROUP BY TL.relid, TL.prodid, L1.days) AS d
ON d.relid = L.relid
AND d.prodid = L.prodid
AND d.days = L.days
go
select * from Limits
go
drop table Limits, Templimits
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[John Gilson]

"Erland Sommarskog" <so****@algonet.se> wrote in message news:Xn********************@127.0.0.1...

John Gilson (ja*@acm.org) writes:

UPDATE Limits
SET amt1 = amt1 + (SELECT SUM(amt1)
FROM TempLimits AS TL
WHERE TL.relid = relid AND
TL.prodid = prodid AND
TL.maxdays <= days),
amt2 = amt2 + (SELECT SUM(amt2)
FROM TempLimits AS TL
WHERE TL.relid = relid AND
TL.prodid = prodid AND
TL.maxdays <= days)
WHERE EXISTS (SELECT *
FROM TempLimits AS TL
WHERE TL.relid = relid AND
TL.prodid = prodid AND
TL.maxdays <= days)
John, when you say "TL.relid = relid", does not the prefix-less "relid"
refer to TempLimits?

Nice catch Erland, yes, those columns not prefixed should refer to the
table to update, Limits, and therefore should be qualified accordingly, e.g.,
TL.relid = Limits.relid.
Also, I am afraid that this is not going to perform overly well, since
SQL Server will resolve the same condition thrice. It is usually better
to have a derived table. If I understand this right it would be:

CREATE TABLE Limits
(
relid INT NOT NULL,
prodid INT NOT NULL,
days INT NOT NULL,
amt1 INT NOT NULL,
amt2 INT NOT NULL,
PRIMARY KEY (relid, prodid, days)
)

CREATE TABLE Templimits
(
relid INT NOT NULL,
prodid INT NOT NULL,
maxdays INT NOT NULL,
amt1 INT NOT NULL,
amt2 INT NOT NULL,
PRIMARY KEY (relid, prodid, maxdays)
)

INSERT Limits VALUES (148, 2, 30, 15000, 30000)
INSERT Limits VALUES (148, 2, 7, 1000, 2500)

INSERT Templimits VALUES (148, 2, 1, 7500, 1000)
INSERT Templimits VALUES (148, 2, 7, 17500, 500)
INSERT Templimits VALUES (148, 2, 30, 2500, 2000)
INSERT Templimits VALUES (148, 2, 90, 25000, 750)
INSERT Templimits VALUES (148, 2, 180, 7500, -300)

UPDATE Limits
SET amt1 = L.amt1 + coalesce(d.amt1, 0),
amt2 = L.amt2 + coalesce(d.amt2, 0)
FROM Limits L
JOIN (SELECT TL.relid, TL.prodid, L1.days,
amt1 = SUM(TL.amt1), amt2 = SUM(TL.amt2)
FROM Templimits TL
JOIN Limits L1 ON L1.relid = TL.relid
AND L1.prodid = TL.prodid
AND TL.maxdays <= L1.days
GROUP BY TL.relid, TL.prodid, L1.days) AS d
ON d.relid = L.relid
AND d.prodid = L.prodid
AND d.days = L.days
go
select * from Limits
go
drop table Limits, Templimits
I like to stick with Standard SQL where possible and stay away from
T-SQL proprietary constructs like this form of UPDATE. However,
one has to be pragmatic and I might give up a little purity if performance
was atrocious.

Staying pure and depending on the data, I might also try:

UPDATE Limits
SET amt1 = amt1 + (SELECT COALESCE(SUM(amt1), 0)
FROM TempLimits AS TL
WHERE TL.relid = Limits.relid AND
TL.prodid = Limits.prodid AND
TL.maxdays <= Limits.days),
amt2 = amt2 + (SELECT COALESCE(SUM(amt2), 0)
FROM TempLimits AS TL
WHERE TL.relid = Limits.relid AND
TL.prodid = Limits.prodid AND
TL.maxdays <= Limits.days)

--
JAG
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Michael D]

Why not use a function ?

CREATE FUNCTION sumLimitAmt (@Amt_Num int)
RETURNS int
AS
BEGIN
DECLARE @RtnValue int

if(@Amt_Num = 1)

SELECT @RtnValue = SUM(l.amt1)
FROM Limits l
INNER JOIN TempLimits tl
ON (
l.relid = tl.relid AND
l.prodid = tl.prodid
)
WHERE tl.maxdays <= l.days

if(@Amt_Num = 2)

SELECT @RtnValue = SUM(l.amt2)
FROM Limits l
INNER JOIN TempLimits tl
ON (
l.relid = tl.relid AND
l.prodid = tl.prodid
)
WHERE tl.maxdays <= l.days

RETURN(@RtnValue)
END
go

UPDATE Limits
SET amt1 = amt1 + dbo.sumLimitAmt(1),
amt2 = amt2 + dbo.sumLimitAmt(2)
--select dbo.sumLimitAmt(1)
--select dbo.sumLimitAmt(2)
Michael D.

[Erland Sommarskog]

Michael D (so*****@yahoo.com) writes:

Why not use a function ?

CREATE FUNCTION sumLimitAmt (@Amt_Num int)
RETURNS int

I guess since John wanted to stick to standard SQL, that function
are out of the question.

Also, putting a scalar function is definitely not going to address
the performance problem that the original poster appeared to have,
but instead could aggrevate them quite a bit.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Kannan Pulacode]

Yes.. use of a function is ruled out since it would be a hit on
performance...

I finally had to use a query that was very much similar to the solution
from Erland.

Thanks for all your input, guys.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!