# 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
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
FROM
A AS ipext3 LEFT OUTER JOIN
AND ipext3EXT = 1
WHERE
relation.PERS = relsince.PERS
AND relation.CID = relsince.CID

---------------------
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
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
AND ipext1EXT = 1

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
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
FROM
A AS ipext3 LEFT OUTER JOIN
AND ipext3EXT = 1
WHERE
relation.PERS = relsince.PERS
AND relation.CID = relsince.CID

---------------------
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
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

>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
> 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
> AND ipext1EXT = 1
>
>
>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
> 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
> FROM
> A AS ipext3 LEFT OUTER JOIN
> AND ipext3EXT = 1
> WHERE
> relation.PERS = relsince.PERS
> AND relation.CID = relsince.CID
>
>---------------------
>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