SQL SIMPLIFICATION

jennifer jennifer

SQL SIMPLIFICATION
Hi List , here is simplification to the folowing query I need. I wrote the
query below. Could any body say why it is taking so long to execute ? Is
there any simplification can be done.
-------------
SELECT
relation.CID AS CID
,relation.VALUE AS RELATION
,relsince.VALUE AS RELATIONSHIPSINCE
,sameaddr.VALUE AS SAMEADDRESS
FROM
(
SELECT
ipext1.BID AS PERS
,ipext1.CID AS CID
,irelation.VALUE AS VALUE
FROM
A AS ipext1 LEFT OUTER JOIN
B AS irelation
ON ipext1.BID = irelation.PERS
AND ipext1EXT = 1
AND irelation.DEF = 3
) AS relation
,(
SELECT
ipext2.BID AS PERS
,ipext2.CID AS CID
,irelsince.VALUE AS VALUE
FROM
A AS ipext2 LEFT OUTER JOIN
B AS irelsince
ON ipext2.BID = irelsince.PERS
AND ipext2EXT = 1
AND irelsince.DEF = 2
) AS relsince
,(
SELECT
ipext3.BID AS PERS
,ipext3.CID AS CID
,isameaddr.VALUE AS VALUE
FROM
A AS ipext3 LEFT OUTER JOIN
B AS isameaddr
ON ipext3.BID = isameaddr.PERS
AND ipext3EXT = 1
AND isameaddr.DEF = 1
) AS sameaddr
WHERE
relation.PERS = relsince.PERS
AND relation.CID = relsince.CID
AND relation.PERS = sameaddr.PERS
AND relation.CID = sameaddr.CID;

---------------------
Thanking you all in advance.
Jennifer
;






_________________________________________________________________
Join the world’s largest e-mail service with MSN Hotmail.
http://www.hotmail.com



Dave Nance

Re: SQL SIMPLIFICATION
(in response to jennifer jennifer)
Jennifer,
I can't answer directly as to why its taking so long. Have you done an explain, to ensure you have good index access to the tables? How much data is being retireved in each of these in-line views? If its quite a bit that could very well be the answer. As far as simplification goes it seems that from your example below that the A table is actually the same table in each nested expression and the B tables are actually different tables? If that is the case how about the following?
SELECT
relation.CID AS CID
,irelation.VALUE AS RELATION
,irelsince.VALUE AS RELATIONSHIPSINCE
,isameaddr.VALUE AS SAMEADDRESS
FROM FROM A AS ipext1
LEFT OUTER JOIN B AS irelation
ON ipext1.BID = irelation.PERS
AND ipext1EXT = 1
AND irelation.DEF = 3
LEFT OUTER JOIN C AS irelsince
ON ipext1.BID = irelsince.PERS
AND ipext1EXT = 1
AND irelsince.DEF = 2
LEFT OUTER JOIN B AS isameaddr
ON ipext1.BID = isameaddr.PERS
AND ipext1EXT = 1
AND isameaddr.DEF = 1


>>> [login to unmask email] 12/19/01 11:27PM >>>
Hi List , here is simplification to the folowing query I need. I wrote the
query below. Could any body say why it is taking so long to execute ? Is
there any simplification can be done.
-------------
SELECT
relation.CID AS CID
,relation.VALUE AS RELATION
,relsince.VALUE AS RELATIONSHIPSINCE
,sameaddr.VALUE AS SAMEADDRESS
FROM
(
SELECT
ipext1.BID AS PERS
,ipext1.CID AS CID
,irelation.VALUE AS VALUE
FROM
A AS ipext1 LEFT OUTER JOIN
B AS irelation
ON ipext1.BID = irelation.PERS
AND ipext1EXT = 1
AND irelation.DEF = 3
) AS relation
,(
SELECT
ipext2.BID AS PERS
,ipext2.CID AS CID
,irelsince.VALUE AS VALUE
FROM
A AS ipext2 LEFT OUTER JOIN
B AS irelsince
ON ipext2.BID = irelsince.PERS
AND ipext2EXT = 1
AND irelsince.DEF = 2
) AS relsince
,(
SELECT
ipext3.BID AS PERS
,ipext3.CID AS CID
,isameaddr.VALUE AS VALUE
FROM
A AS ipext3 LEFT OUTER JOIN
B AS isameaddr
ON ipext3.BID = isameaddr.PERS
AND ipext3EXT = 1
AND isameaddr.DEF = 1
) AS sameaddr
WHERE
relation.PERS = relsince.PERS
AND relation.CID = relsince.CID
AND relation.PERS = sameaddr.PERS
AND relation.CID = sameaddr.CID;

---------------------
Thanking you all in advance.
Jennifer
;






_________________________________________________________________
Join the world's largest e-mail service with MSN Hotmail.
http://www.hotmail.com



This message, including any attachments, is intended solely for the use of the named
recipient(s) and may contain confidential and/or privileged information. Any
unauthorized review, use, disclosure or distribution of this communications is expressly
prohibited. If you are not the intended recipient, please contact the sender by reply e-mail
and destroy any and all copies of the original message. Thank you.



jennifer jennifer

Re: SQL SIMPLIFICATION
(in response to Dave Nance)
Good Day David, I m trying out your optins and thanks for giving me new
ideas. I would check the result and send the details.I again give you
thanks.

Jennifer


>From: David Nance <[login to unmask email]>
>Reply-To: DB2 Data Base Discussion List <[login to unmask email]>
>To: [login to unmask email]
>Subject: Re: SQL SIMPLIFICATION
>Date: Thu, 20 Dec 2001 09:37:07 -0500
>
>Jennifer,
> I can't answer directly as to why its taking so long. Have you done an
>explain, to ensure you have good index access to the tables? How much data
>is being retireved in each of these in-line views? If its quite a bit that
>could very well be the answer. As far as simplification goes it seems that
>from your example below that the A table is actually the same table in each
>nested expression and the B tables are actually different tables? If that
>is the case how about the following?
> SELECT
> relation.CID AS CID
> ,irelation.VALUE AS RELATION
> ,irelsince.VALUE AS RELATIONSHIPSINCE
> ,isameaddr.VALUE AS SAMEADDRESS
> FROM FROM A AS ipext1
> LEFT OUTER JOIN B AS irelation
> ON ipext1.BID = irelation.PERS
> AND ipext1EXT = 1
> AND irelation.DEF = 3
> LEFT OUTER JOIN C AS irelsince
> ON ipext1.BID = irelsince.PERS
> AND ipext1EXT = 1
> AND irelsince.DEF = 2
> LEFT OUTER JOIN B AS isameaddr
> ON ipext1.BID = isameaddr.PERS
> AND ipext1EXT = 1
> AND isameaddr.DEF = 1
>
>
> >>> [login to unmask email] 12/19/01 11:27PM >>>
>Hi List , here is simplification to the folowing query I need. I wrote the
>query below. Could any body say why it is taking so long to execute ? Is
>there any simplification can be done.
>-------------
> SELECT
> relation.CID AS CID
> ,relation.VALUE AS RELATION
> ,relsince.VALUE AS RELATIONSHIPSINCE
> ,sameaddr.VALUE AS SAMEADDRESS
> FROM
> (
> SELECT
> ipext1.BID AS PERS
> ,ipext1.CID AS CID
> ,irelation.VALUE AS VALUE
> FROM
> A AS ipext1 LEFT OUTER JOIN
> B AS irelation
> ON ipext1.BID = irelation.PERS
> AND ipext1EXT = 1
> AND irelation.DEF = 3
> ) AS relation
> ,(
> SELECT
> ipext2.BID AS PERS
> ,ipext2.CID AS CID
> ,irelsince.VALUE AS VALUE
> FROM
> A AS ipext2 LEFT OUTER JOIN
> B AS irelsince
> ON ipext2.BID = irelsince.PERS
> AND ipext2EXT = 1
> AND irelsince.DEF = 2
> ) AS relsince
> ,(
> SELECT
> ipext3.BID AS PERS
> ,ipext3.CID AS CID
> ,isameaddr.VALUE AS VALUE
> FROM
> A AS ipext3 LEFT OUTER JOIN
> B AS isameaddr
> ON ipext3.BID = isameaddr.PERS
> AND ipext3EXT = 1
> AND isameaddr.DEF = 1
> ) AS sameaddr
> WHERE
> relation.PERS = relsince.PERS
> AND relation.CID = relsince.CID
> AND relation.PERS = sameaddr.PERS
> AND relation.CID = sameaddr.CID;
>
>---------------------
>Thanking you all in advance.
>Jennifer
> ;
>
>
>
>
>
>
>_________________________________________________________________
>Join the world's largest e-mail service with MSN Hotmail.
>http://www.hotmail.com
>
>
>
>
>
>This message, including any attachments, is intended solely for the use of
>the named
>recipient(s) and may contain confidential and/or privileged information.
>Any
>unauthorized review, use, disclosure or distribution of this communications
>is expressly
>prohibited. If you are not the intended recipient, please contact the
>sender by reply e-mail
>and destroy any and all copies of the original message. Thank you.
>
>
>
>http://www.ryci.com/db2-l. The owners of the list can be reached at
>[login to unmask email]


_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp.