From wahab@mimos.my Wed Aug  13 03:21:15 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP id 3CBEBD1B53A
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 13 Aug 2003 03:21:15 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024) with ESMTP id 59536-03
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 13 Aug 2003 00:19:39 -0300 (ADT)
Received: from filter.mimos.my (filter.mimos.my [192.228.137.70])
	by svr1.postgresql.org (Postfix) with ESMTP id 01FBFD1B58A
	for <pgsql-sql@postgresql.org>; Wed, 13 Aug 2003 00:21:03 -0300 (ADT)
Received: from ew.mimos.my (localhost.localdomain [127.0.0.1])
	by filter.mimos.my (8.11.6/8.11.6) with ESMTP id h7D2jkr31442
	for <pgsql-sql@postgresql.org>; Wed, 13 Aug 2003 10:45:46 +0800
Received: (from root@localhost)
	by ew.mimos.my (8.12.3/8.12.3) id h7D3KxSP035554;
	Wed, 13 Aug 2003 11:20:59 +0800 (MYT) (envelope-from wahab@mimos.my)
Received: from mimos.my (sol-wel197.nat.mimos.my [10.1.23.197])
	by ew.mimos.my (8.12.3/8.11.6) with ESMTP id h7D3Kstv035228;
	Wed, 13 Aug 2003 11:20:55 +0800 (MYT) (envelope-from wahab@mimos.my)
Message-ID: <3F39AE9A.5020901@mimos.my>
Date: Wed, 13 Aug 2003 11:20:58 +0800
From: Abdul Wahab Dahalan <wahab@mimos.my>
User-Agent: Mozilla/5.0 (Windows; U; Win98; en-US;
	rv:1.0.2) Gecko/20030208 Netscape/7.02
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: pgsql-sql@postgresql.org, wahab@mimos.my
Subject: How to speeed up the query performance
X-scanner: scanned by Inflex 1.0.10 - (http://pldaniels.com/inflex/)
Content-Type: multipart/alternative;
	boundary="------------060608040208050302060201"
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200308/124
X-Sequence-Number: 14740

--------------060608040208050302060201
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

How do I speed up the quey performance if I've a query like this :
Does 'not in' command will affected the performance?.

select      
ts.transportid,ts.transporttype,ts.transportcapacity,ts.transportstatus,ts.routecoverage,ts.transportregnumber 

from       transportsetup ts
where     ts.bizid = 'B126'
and         ts.deletestatus = 0
and         ts.transportid not in (    select t.transportid
                                                  f rom transportsetup 
t,servicedetail s,logisticservice l
                                                  where t.bizid=l.bizid
                                                      and 
l.serviceid=s.serviceid
                                                      and 
t.transportid=s.transportid
                                                      and t.bizid = 'B126'
                                                      and l.status='Pending'
                                                  or t.bizid=l.bizid
                                                      and 
l.serviceid=s.serviceid
                                                      and 
t.transportid=s.transportid
                                                      and t.bizid = 
'B126' and l.status='Reserved' )
order by ts.transporttype;


Any help pretty much appreciated. Thanks

--------------060608040208050302060201
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <title></title>
</head>
<body>
How do I speed up the quey performance if I've a query like this :<br>
Does '<u><big>not in</big></u>' command will affected the performance?.<br>
<br>
select&nbsp; &nbsp;&nbsp;&nbsp; ts.transportid,ts.transporttype,ts.transportcapacity,ts.transportstatus,ts.routecoverage,ts.transportregnumber
<br>
from&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; transportsetup ts<br>
where &nbsp;&nbsp;&nbsp; ts.bizid = 'B126' <br>
and &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; ts.deletestatus = 0 <br>
and &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; ts.transportid <u><big>not in</big></u> (&nbsp;&nbsp;&nbsp; select t.transportid
<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; f rom transportsetup t,servicedetail
s,logisticservice l <br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; where t.bizid=l.bizid <br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp;&nbsp;&nbsp; and l.serviceid=s.serviceid
<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp;&nbsp;&nbsp; and t.transportid=s.transportid
<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp;&nbsp;&nbsp; and t.bizid = 'B126'
<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp;&nbsp;&nbsp; and l.status='Pending'<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; or t.bizid=l.bizid <br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; and l.serviceid=s.serviceid
<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; and t.transportid=s.transportid
<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; and t.bizid = 'B126'
and l.status='Reserved' ) <br>
order by ts.transporttype;<br>
<br>
<br>
Any help pretty much appreciated. Thanks<br>
</body>
</html>

--------------060608040208050302060201--




From ch@rodos.fzk.de Wed Aug  13 14:50:13 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP id 3B54BD1C4C8
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 13 Aug 2003 14:50:13 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024) with ESMTP id 25597-09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 13 Aug 2003 11:48:41 -0300 (ADT)
Received: from fzkmail2.fzk.de (fzkmail2.fzk.de [141.52.27.52])
	by svr1.postgresql.org (Postfix) with SMTP id 01E6FD1B524
	for <pgsql-sql@postgresql.org>; Wed, 13 Aug 2003 11:50:01 -0300 (ADT)
Received: FROM resy5.fzk.de BY fzkmail2.fzk.de ;
 Wed Aug 13 16:50:04 2003 +0200
Received: œby rodos.fzk.de with ESMTP (8.8.6 (PHNE_17135)/8.7.1) id QAA25601; Wed, 13 Aug 2003 16:50:03 +0200 (METDST)
Message-ID: <3F3A501A.FDF96A90@rodos.fzk.de>
Date: Wed, 13 Aug 2003 16:50:03 +0200
From: Christoph Haller <ch@rodos.fzk.de>
X-Mailer: Mozilla 4.77 [en] (X11; U; HP-UX B.10.20 9000/780)
X-Accept-Language: en
MIME-Version: 1.0
To: pgsql-sql@postgresql.org, wahab@mimos.my
Subject: Re: How to speeed up the query performance
Content-Type: text/plain; charset=iso-8859-2
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Amavis-Alert: BAD HEADER Non-encoded 8-bit data (char 9C hex) in message
	header 'Received' Received: \234by rodos.fzk.de...            ^
X-Archive-Number: 200308/135
X-Sequence-Number: 14751

>
> How do I speed up the quey performance if I've a query like this :
> Does 'not in' command will affected the performance?.
Yes, it's well known to be slow in 7.3 and lower, should be fixed in 7.4
AFAIK.
>
> select
>
ts.transportid,ts.transporttype,ts.transportcapacity,ts.transportstatus,ts.routecoverage,ts.transportregnumber

> from       transportsetup ts
> where     ts.bizid = 'B126'
> and         ts.deletestatus = 0
> and         ts.transportid not in (    select t.transportid
>                                                   from transportsetup
t,servicedetail s,logisticservice l
>                                                   where
t.bizid=l.bizid
>                                                       and
l.serviceid=s.serviceid
>                                                       and
t.transportid=s.transportid
>                                                       and t.bizid =
'B126'
>                                                       and
l.status='Pending'
>                                                   or t.bizid=l.bizid
>                                                       and
l.serviceid=s.serviceid
>                                                       and
t.transportid=s.transportid
>                                                       and t.bizid =
'B126' and l.status='Reserved' )
> order by ts.transporttype;
>
As recently mentioned by Stephan Szabo on '[SQL] How to optimize this
query ?'
NOT EXISTS performs much better.

Try:
select
ts.transportid,ts.transporttype,ts.transportcapacity,ts.transportstatus,ts.routecoverage,ts.transportregnumber

from    transportsetup ts
where   ts.bizid = 'B126'
and     ts.deletestatus = 0
and     NOT EXISTS (    select t.transportid
        from transportsetup t,servicedetail s,logisticservice l
        where ts.transportid = t.transportid
            and t.bizid=l.bizid
            and l.serviceid=s.serviceid
            and t.transportid=s.transportid
            and t.bizid = 'B126'
            and l.status='Pending'
        or t.bizid=l.bizid
            and l.serviceid=s.serviceid
            and t.transportid=s.transportid
            and t.bizid = 'B126' and l.status='Reserved' )
order by ts.transporttype;

Regards, Christoph





From jgardner@jonathangardner.net Wed Aug  13 14:50:51 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP id 28E46D1C4B8
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 13 Aug 2003 14:50:51 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024) with ESMTP id 30120-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 13 Aug 2003 11:49:19 -0300 (ADT)
Received: from irko.smoothcorp.com (unknown [208.49.241.41])
	by svr1.postgresql.org (Postfix) with ESMTP id B3133D1B524
	for <pgsql-sql@postgresql.org>; Wed, 13 Aug 2003 11:50:39 -0300 (ADT)
Received: from [10.0.1.63] (helo=jgardner.smoothcorp.com)
	by irko.smoothcorp.com with esmtp (Exim 4.12)
	id 19mwxX-0003HU-00; Wed, 13 Aug 2003 07:50:43 -0700
Received: from jgardner.smoothcorp.com (localhost.localdomain [127.0.0.1])
	by jgardner.smoothcorp.com (8.12.8/8.12.8) with ESMTP id h7DEoisN032351;
	Wed, 13 Aug 2003 07:50:44 -0700
Received: from localhost (localhost [[UNIX: localhost]])
	by jgardner.smoothcorp.com (8.12.8/8.12.8/Submit) id h7DEog9T032349;
	Wed, 13 Aug 2003 07:50:42 -0700
From: Jonathan Gardner <jgardner@jonathangardner.net>
To: Abdul Wahab Dahalan <wahab@mimos.my>, pgsql-sql@postgresql.org
Subject: Re: How to speeed up the query performance
Date: Wed, 13 Aug 2003 07:50:41 -0700
User-Agent: KMail/1.5
References: <3F39AE9A.5020901@mimos.my>
In-Reply-To: <3F39AE9A.5020901@mimos.my>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Content-Description: clearsigned data
Content-Disposition: inline
Message-Id: <200308130750.42570.jgardner@jonathangardner.net>
X-SA-Exim-Rcpt-To: wahab@mimos.my, pgsql-sql@postgresql.org
X-SA-Exim-Scanned: No; SAEximRunCond expanded to false
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200308/136
X-Sequence-Number: 14752

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Tuesday 12 August 2003 20:20, Abdul Wahab Dahalan wrote:
> How do I speed up the quey performance if I've a query like this :
> Does 'not in' command will affected the performance?.
>

Yes. Severely. See the responses to the "How to optimize this query ?"=20
thread.

If you want more details, check the pgsql-performance archives.

- --=20
Jonathan Gardner <jgardner@jonathangardner.net>
Live Free, Use Linux!
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQE/OlBBWgwF3QvpWNwRAuzJAJ99iMmMbU/tiJhi077+8WCmAId76ACffL+5
biOZSLPbuhWZBL6MNlZE3V0=3D
=3DSg0n
-----END PGP SIGNATURE-----



From wahab@mimos.my Thu Aug  14 02:52:28 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP id 890F4D1C9DC
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 14 Aug 2003 02:52:28 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024) with ESMTP id 30491-03
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 13 Aug 2003 23:50:50 -0300 (ADT)
Received: from filter.mimos.my (filter.mimos.my [192.228.137.70])
	by svr1.postgresql.org (Postfix) with ESMTP id AD138D1C4E7
	for <pgsql-sql@postgresql.org>; Wed, 13 Aug 2003 23:51:40 -0300 (ADT)
Received: from ew.mimos.my (localhost.localdomain [127.0.0.1])
	by filter.mimos.my (8.11.6/8.11.6) with ESMTP id h7E2GMr06139;
	Thu, 14 Aug 2003 10:16:22 +0800
Received: (from root@localhost)
	by ew.mimos.my (8.12.3/8.12.3) id h7E2phnj092970;
	Thu, 14 Aug 2003 10:51:43 +0800 (MYT) (envelope-from wahab@mimos.my)
Received: from mimos.my (sol-wel197.nat.mimos.my [10.1.23.197])
	by ew.mimos.my (8.12.3/8.11.6) with ESMTP id h7E2pdtv092378;
	Thu, 14 Aug 2003 10:51:41 +0800 (MYT) (envelope-from wahab@mimos.my)
Message-ID: <3F3AF940.9000002@mimos.my>
Date: Thu, 14 Aug 2003 10:51:44 +0800
From: Abdul Wahab Dahalan <wahab@mimos.my>
User-Agent: Mozilla/5.0 (Windows; U; Win98; en-US;
	rv:1.0.2) Gecko/20030208 Netscape/7.02
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Christoph Haller <ch@rodos.fzk.de>
Cc: pgsql-sql@postgresql.org
Subject: Re: How to speeed up the query performance
References: <3F3A501A.FDF96A90@rodos.fzk.de>
X-scanner: scanned by Inflex 1.0.10 - (http://pldaniels.com/inflex/)
Content-Type: multipart/alternative;
	boundary="------------040005000106080806050100"
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200308/163
X-Sequence-Number: 14779

--------------040005000106080806050100
Content-Type: text/plain; charset=ISO-8859-2; format=flowed
Content-Transfer-Encoding: 7bit

Hai Chris!
Thanks for the solution but seem it doesnt work.
(0 rows) returned when I used NOT EXITS but (4 rows) returned
when NOT IN is used...................

FYI I used 7.2

Christoph Haller wrote:

>>How do I speed up the quey performance if I've a query like this :
>>Does 'not in' command will affected the performance?.
>>    
>>
>Yes, it's well known to be slow in 7.3 and lower, should be fixed in 7.4
>AFAIK.
>  
>
>>select
>>
>>    
>>
>ts.transportid,ts.transporttype,ts.transportcapacity,ts.transportstatus,ts.routecoverage,ts.transportregnumber
>
>  
>
>>from       transportsetup ts
>>where     ts.bizid = 'B126'
>>and         ts.deletestatus = 0
>>and         ts.transportid not in (    select t.transportid
>>                                                  from transportsetup
>>    
>>
>t,servicedetail s,logisticservice l
>  
>
>>                                                  where
>>    
>>
>t.bizid=l.bizid
>  
>
>>                                                      and
>>    
>>
>l.serviceid=s.serviceid
>  
>
>>                                                      and
>>    
>>
>t.transportid=s.transportid
>  
>
>>                                                      and t.bizid =
>>    
>>
>'B126'
>  
>
>>                                                      and
>>    
>>
>l.status='Pending'
>  
>
>>                                                  or t.bizid=l.bizid
>>                                                      and
>>    
>>
>l.serviceid=s.serviceid
>  
>
>>                                                      and
>>    
>>
>t.transportid=s.transportid
>  
>
>>                                                      and t.bizid =
>>    
>>
>'B126' and l.status='Reserved' )
>  
>
>>order by ts.transporttype;
>>
>>    
>>
>As recently mentioned by Stephan Szabo on '[SQL] How to optimize this
>query ?'
>NOT EXISTS performs much better.
>
>Try:
>select
>ts.transportid,ts.transporttype,ts.transportcapacity,ts.transportstatus,ts.routecoverage,ts.transportregnumber
>
>from    transportsetup ts
>where   ts.bizid = 'B126'
>and     ts.deletestatus = 0
>and     NOT EXISTS (    select t.transportid
>        from transportsetup t,servicedetail s,logisticservice l
>        where ts.transportid = t.transportid
>            and t.bizid=l.bizid
>            and l.serviceid=s.serviceid
>            and t.transportid=s.transportid
>            and t.bizid = 'B126'
>            and l.status='Pending'
>        or t.bizid=l.bizid
>            and l.serviceid=s.serviceid
>            and t.transportid=s.transportid
>            and t.bizid = 'B126' and l.status='Reserved' )
>order by ts.transporttype;
>
>Regards, Christoph
>
>
>
>  
>


--------------040005000106080806050100
Content-Type: text/html; charset=ISO-8859-2
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <title></title>
</head>
<body>
Hai Chris!<br>
Thanks for the solution but seem it doesnt work.<br>
(0 rows) returned when I used NOT EXITS but (4 rows) returned<br>
when NOT IN is used...................<br>
<br>
FYI I used 7.2<br>
<br>
Christoph Haller wrote:<br>
<blockquote type="cite" cite="mid3F3A501A.FDF96A90@rodos.fzk.de">
  <blockquote type="cite">
    <pre wrap="">How do I speed up the quey performance if I've a query like this :
Does 'not in' command will affected the performance?.
    </pre>
  </blockquote>
  <pre wrap=""><!---->Yes, it's well known to be slow in 7.3 and lower, should be fixed in 7.4
AFAIK.
  </pre>
  <blockquote type="cite">
    <pre wrap="">select

    </pre>
  </blockquote>
  <pre wrap=""><!---->ts.transportid,ts.transporttype,ts.transportcapacity,ts.transportstatus,ts.routecoverage,ts.transportregnumber

  </pre>
  <blockquote type="cite">
    <pre wrap="">from       transportsetup ts
where     ts.bizid = 'B126'
and         ts.deletestatus = 0
and         ts.transportid not in (    select t.transportid
                                                  from transportsetup
    </pre>
  </blockquote>
  <pre wrap=""><!---->t,servicedetail s,logisticservice l
  </pre>
  <blockquote type="cite">
    <pre wrap="">                                                  where
    </pre>
  </blockquote>
  <pre wrap=""><!---->t.bizid=l.bizid
  </pre>
  <blockquote type="cite">
    <pre wrap="">                                                      and
    </pre>
  </blockquote>
  <pre wrap=""><!---->l.serviceid=s.serviceid
  </pre>
  <blockquote type="cite">
    <pre wrap="">                                                      and
    </pre>
  </blockquote>
  <pre wrap=""><!---->t.transportid=s.transportid
  </pre>
  <blockquote type="cite">
    <pre wrap="">                                                      and t.bizid =
    </pre>
  </blockquote>
  <pre wrap=""><!---->'B126'
  </pre>
  <blockquote type="cite">
    <pre wrap="">                                                      and
    </pre>
  </blockquote>
  <pre wrap=""><!---->l.status='Pending'
  </pre>
  <blockquote type="cite">
    <pre wrap="">                                                  or t.bizid=l.bizid
                                                      and
    </pre>
  </blockquote>
  <pre wrap=""><!---->l.serviceid=s.serviceid
  </pre>
  <blockquote type="cite">
    <pre wrap="">                                                      and
    </pre>
  </blockquote>
  <pre wrap=""><!---->t.transportid=s.transportid
  </pre>
  <blockquote type="cite">
    <pre wrap="">                                                      and t.bizid =
    </pre>
  </blockquote>
  <pre wrap=""><!---->'B126' and l.status='Reserved' )
  </pre>
  <blockquote type="cite">
    <pre wrap="">order by ts.transporttype;

    </pre>
  </blockquote>
  <pre wrap=""><!---->As recently mentioned by Stephan Szabo on '[SQL] How to optimize this
query ?'
NOT EXISTS performs much better.

Try:
select
ts.transportid,ts.transporttype,ts.transportcapacity,ts.transportstatus,ts.routecoverage,ts.transportregnumber

from    transportsetup ts
where   ts.bizid = 'B126'
and     ts.deletestatus = 0
and     NOT EXISTS (    select t.transportid
        from transportsetup t,servicedetail s,logisticservice l
        where ts.transportid = t.transportid
            and t.bizid=l.bizid
            and l.serviceid=s.serviceid
            and t.transportid=s.transportid
            and t.bizid = 'B126'
            and l.status='Pending'
        or t.bizid=l.bizid
            and l.serviceid=s.serviceid
            and t.transportid=s.transportid
            and t.bizid = 'B126' and l.status='Reserved' )
order by ts.transporttype;

Regards, Christoph



  </pre>
</blockquote>
<br>
</body>
</html>

--------------040005000106080806050100--




From sszabo@megazone.bigpanda.com Thu Aug  14 03:12:36 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP id 70E89D1C510
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 14 Aug 2003 03:12:36 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024) with ESMTP id 32104-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 14 Aug 2003 00:10:57 -0300 (ADT)
Received: from megazone.bigpanda.com (45.mufb.snfc.sffca01r18.dsl.att.net
	[12.103.249.45])
	by svr1.postgresql.org (Postfix) with ESMTP id 179DBD1C4DC
	for <pgsql-sql@postgresql.org>; Thu, 14 Aug 2003 00:12:25 -0300 (ADT)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id 75936D649; Wed, 13 Aug 2003 20:12:24 -0700 (PDT)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id 672F65C0B; Wed, 13 Aug 2003 20:12:24 -0700 (PDT)
Date: Wed, 13 Aug 2003 20:12:24 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Abdul Wahab Dahalan <wahab@mimos.my>
Cc: Christoph Haller <ch@rodos.fzk.de>, <pgsql-sql@postgresql.org>
Subject: Re: How to speeed up the query performance
In-Reply-To: <3F3AF940.9000002@mimos.my>
Message-ID: <20030813201031.D67777-100000@megazone.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200308/164
X-Sequence-Number: 14780

On Thu, 14 Aug 2003, Abdul Wahab Dahalan wrote:

> Hai Chris!
> Thanks for the solution but seem it doesnt work.
> (0 rows) returned when I used NOT EXITS but (4 rows) returned
> when NOT IN is used...................

Maybe you need a set of parenthesis around the old conditions
because of the or.

> >Try:
> >select
> >ts.transportid,ts.transporttype,ts.transportcapacity,ts.transportstatus,ts.routecoverage,ts.transportregnumber
> >
> >from    transportsetup ts
> >where   ts.bizid = 'B126'
> >and     ts.deletestatus = 0
> >and     NOT EXISTS (    select t.transportid
> >        from transportsetup t,servicedetail s,logisticservice l
> >        where ts.transportid = t.transportid

> >            and t.bizid=l.bizid
Replace the above with:
	       and (t.bizid=l.bizid

> >            and l.serviceid=s.serviceid
> >            and t.transportid=s.transportid
> >            and t.bizid = 'B126'
> >            and l.status='Pending'
> >        or t.bizid=l.bizid
> >            and l.serviceid=s.serviceid
> >            and t.transportid=s.transportid
> >            and t.bizid = 'B126' and l.status='Reserved' )

Add:
		)

> >order by ts.transporttype;




From wahab@mimos.my Thu Aug  14 03:22:37 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP id 06CD4D1C943
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 14 Aug 2003 03:22:37 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024) with ESMTP id 43054-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 14 Aug 2003 00:20:58 -0300 (ADT)
Received: from filter.mimos.my (filter.mimos.my [192.228.137.70])
	by svr1.postgresql.org (Postfix) with ESMTP id D0F2DD1C50D
	for <pgsql-sql@postgresql.org>; Thu, 14 Aug 2003 00:22:24 -0300 (ADT)
Received: from ew.mimos.my (localhost.localdomain [127.0.0.1])
	by filter.mimos.my (8.11.6/8.11.6) with ESMTP id h7E2kxr13499;
	Thu, 14 Aug 2003 10:46:59 +0800
Received: (from root@localhost)
	by ew.mimos.my (8.12.3/8.12.3) id h7E3MKNR068706;
	Thu, 14 Aug 2003 11:22:20 +0800 (MYT) (envelope-from wahab@mimos.my)
Received: from mimos.my (sol-wel197.nat.mimos.my [10.1.23.197])
	by ew.mimos.my (8.12.3/8.11.6) with ESMTP id h7E3MHtv068335;
	Thu, 14 Aug 2003 11:22:19 +0800 (MYT) (envelope-from wahab@mimos.my)
Message-ID: <3F3B006D.4000708@mimos.my>
Date: Thu, 14 Aug 2003 11:22:21 +0800
From: Abdul Wahab Dahalan <wahab@mimos.my>
User-Agent: Mozilla/5.0 (Windows; U; Win98; en-US;
	rv:1.0.2) Gecko/20030208 Netscape/7.02
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Stephan Szabo <sszabo@megazone.bigpanda.com>
Cc: Christoph Haller <ch@rodos.fzk.de>, pgsql-sql@postgresql.org
Subject: Re: How to speeed up the query performance
References: <20030813201031.D67777-100000@megazone.bigpanda.com>
X-scanner: scanned by Inflex 1.0.10 - (http://pldaniels.com/inflex/)
Content-Type: multipart/alternative;
	boundary="------------040500040300090704030504"
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200308/166
X-Sequence-Number: 14782

--------------040500040300090704030504
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

I cant find where to put the parenthesis as u said Stephan.

Here the query :

select ts.transportid,ts.transporttype,ts.transportcapacity,
ts.transportstatus,ts.routecoverage,ts.transportregnumber
from transportsetup ts
where ts.bizid = 'B126'
and ts.deletestatus = 0
and NOT EXISTS (select t.transportid
from transportsetup t,servicedetail s,logisticservice l
where ts.transportid = t.transportid
and t.bizid=l.bizid
and l.serviceid=s.serviceid
and t.transportid=s.transportid
and t.bizid = 'B126'
and l.status='Pending'
or t.bizid=l.bizid
and l.serviceid=s.serviceid
and t.transportid=s.transportid
and t.bizid = 'B126' and l.status='Reserved')
order by ts.transporttype;



Stephan Szabo wrote:

>On Thu, 14 Aug 2003, Abdul Wahab Dahalan wrote:
>
>  
>
>>Hai Chris!
>>Thanks for the solution but seem it doesnt work.
>>(0 rows) returned when I used NOT EXITS but (4 rows) returned
>>when NOT IN is used...................
>>    
>>
>
>Maybe you need a set of parenthesis around the old conditions
>because of the or.
>
>  
>
>>>Try:
>>>select
>>>ts.transportid,ts.transporttype,ts.transportcapacity,ts.transportstatus,ts.routecoverage,ts.transportregnumber
>>>
>>>      
>>>
>>>from    transportsetup ts
>>    
>>
>>>where   ts.bizid = 'B126'
>>>and     ts.deletestatus = 0
>>>and     NOT EXISTS (    select t.transportid
>>>       from transportsetup t,servicedetail s,logisticservice l
>>>       where ts.transportid = t.transportid
>>>      
>>>
>
>  
>
>>>           and t.bizid=l.bizid
>>>      
>>>
>Replace the above with:
>	       and (t.bizid=l.bizid
>
>  
>
>>>           and l.serviceid=s.serviceid
>>>           and t.transportid=s.transportid
>>>           and t.bizid = 'B126'
>>>           and l.status='Pending'
>>>       or t.bizid=l.bizid
>>>           and l.serviceid=s.serviceid
>>>           and t.transportid=s.transportid
>>>           and t.bizid = 'B126' and l.status='Reserved' )
>>>      
>>>
>
>Add:
>		)
>
>  
>
>>>order by ts.transporttype;
>>>      
>>>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 7: don't forget to increase your free space map settings
>
>
>  
>


--------------040500040300090704030504
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
  <title></title>
</head>
<body>
I cant find where to put the parenthesis as u said Stephan.<br>
<br>
Here the query :<br>
<br>
select ts.transportid,ts.transporttype,ts.transportcapacity,<br>
ts.transportstatus,ts.routecoverage,ts.transportregnumber<br>
from transportsetup ts<br>
where ts.bizid = 'B126'<br>
and ts.deletestatus = 0<br>
and NOT EXISTS (select t.transportid<br>
from transportsetup t,servicedetail s,logisticservice l<br>
where ts.transportid = t.transportid<br>
and t.bizid=l.bizid<br>
and l.serviceid=s.serviceid<br>
and t.transportid=s.transportid<br>
and t.bizid = 'B126'<br>
and l.status='Pending'<br>
or t.bizid=l.bizid<br>
and l.serviceid=s.serviceid<br>
and t.transportid=s.transportid<br>
and t.bizid = 'B126' and l.status='Reserved')<br>
order by ts.transporttype;<br>
<br>
<br>
<br>
Stephan Szabo wrote:<br>
<blockquote type="cite"
 cite="mid20030813201031.D67777-100000@megazone.bigpanda.com">
  <pre wrap="">On Thu, 14 Aug 2003, Abdul Wahab Dahalan wrote:

  </pre>
  <blockquote type="cite">
    <pre wrap="">Hai Chris!
Thanks for the solution but seem it doesnt work.
(0 rows) returned when I used NOT EXITS but (4 rows) returned
when NOT IN is used...................
    </pre>
  </blockquote>
  <pre wrap=""><!---->
Maybe you need a set of parenthesis around the old conditions
because of the or.

  </pre>
  <blockquote type="cite">
    <blockquote type="cite">
      <pre wrap="">Try:
select
ts.transportid,ts.transporttype,ts.transportcapacity,ts.transportstatus,ts.routecoverage,ts.transportregnumber

      </pre>
    </blockquote>
    <pre wrap="">&gt;from    transportsetup ts
    </pre>
    <blockquote type="cite">
      <pre wrap="">where   ts.bizid = 'B126'
and     ts.deletestatus = 0
and     NOT EXISTS (    select t.transportid
       from transportsetup t,servicedetail s,logisticservice l
       where ts.transportid = t.transportid
      </pre>
    </blockquote>
  </blockquote>
  <pre wrap=""><!---->
  </pre>
  <blockquote type="cite">
    <blockquote type="cite">
      <pre wrap="">           and t.bizid=l.bizid
      </pre>
    </blockquote>
  </blockquote>
  <pre wrap=""><!---->Replace the above with:
	       and (t.bizid=l.bizid

  </pre>
  <blockquote type="cite">
    <blockquote type="cite">
      <pre wrap="">           and l.serviceid=s.serviceid
           and t.transportid=s.transportid
           and t.bizid = 'B126'
           and l.status='Pending'
       or t.bizid=l.bizid
           and l.serviceid=s.serviceid
           and t.transportid=s.transportid
           and t.bizid = 'B126' and l.status='Reserved' )
      </pre>
    </blockquote>
  </blockquote>
  <pre wrap=""><!---->
Add:
		)

  </pre>
  <blockquote type="cite">
    <blockquote type="cite">
      <pre wrap="">order by ts.transporttype;
      </pre>
    </blockquote>
  </blockquote>
  <pre wrap=""><!---->

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings


  </pre>
</blockquote>
<br>
</body>
</html>

--------------040500040300090704030504--




From wahab@mimos.my Thu Aug  14 03:35:43 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP id 2718FD1C7F3
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 14 Aug 2003 03:35:43 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024) with ESMTP id 46223-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 14 Aug 2003 00:34:04 -0300 (ADT)
Received: from filter.mimos.my (filter.mimos.my [192.228.137.70])
	by svr1.postgresql.org (Postfix) with ESMTP id 2F8C2D1C4E1
	for <pgsql-sql@postgresql.org>; Thu, 14 Aug 2003 00:35:30 -0300 (ADT)
Received: from ew.mimos.my (localhost.localdomain [127.0.0.1])
	by filter.mimos.my (8.11.6/8.11.6) with ESMTP id h7E302r17365;
	Thu, 14 Aug 2003 11:00:02 +0800
Received: (from root@localhost)
	by ew.mimos.my (8.12.3/8.12.3) id h7E3ZOMt000349;
	Thu, 14 Aug 2003 11:35:24 +0800 (MYT) (envelope-from wahab@mimos.my)
Received: from mimos.my (sol-wel197.nat.mimos.my [10.1.23.197])
	by ew.mimos.my (8.12.3/8.11.6) with ESMTP id h7E3ZGtv000125;
	Thu, 14 Aug 2003 11:35:16 +0800 (MYT) (envelope-from wahab@mimos.my)
Message-ID: <3F3B0379.9030602@mimos.my>
Date: Thu, 14 Aug 2003 11:35:21 +0800
From: Abdul Wahab Dahalan <wahab@mimos.my>
User-Agent: Mozilla/5.0 (Windows; U; Win98; en-US;
	rv:1.0.2) Gecko/20030208 Netscape/7.02
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Abdul Wahab Dahalan <wahab@mimos.my>
Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>,
	Christoph Haller <ch@rodos.fzk.de>, pgsql-sql@postgresql.org
Subject: Re: How to speeed up the query performance
References: <20030813201031.D67777-100000@megazone.bigpanda.com>
	<3F3B006D.4000708@mimos.my>
X-scanner: scanned by Inflex 1.0.10 - (http://pldaniels.com/inflex/)
Content-Type: multipart/alternative;
	boundary="------------060402000304010603040002"
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200308/167
X-Sequence-Number: 14783

--------------060402000304010603040002
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Thanks Chris and Stephan
the query is working now.

Abdul Wahab Dahalan wrote:

> I cant find where to put the parenthesis as u said Stephan.
>
> Here the query :
>
> select ts.transportid,ts.transporttype,ts.transportcapacity,
> ts.transportstatus,ts.routecoverage,ts.transportregnumber
> from transportsetup ts
> where ts.bizid = 'B126'
> and ts.deletestatus = 0
> and NOT EXISTS (select t.transportid
> from transportsetup t,servicedetail s,logisticservice l
> where ts.transportid = t.transportid
> and t.bizid=l.bizid
> and l.serviceid=s.serviceid
> and t.transportid=s.transportid
> and t.bizid = 'B126'
> and l.status='Pending'
> or t.bizid=l.bizid
> and l.serviceid=s.serviceid
> and t.transportid=s.transportid
> and t.bizid = 'B126' and l.status='Reserved')
> order by ts.transporttype;
>
>
>
> Stephan Szabo wrote:
>
>>On Thu, 14 Aug 2003, Abdul Wahab Dahalan wrote:
>>
>>  
>>
>>>Hai Chris!
>>>Thanks for the solution but seem it doesnt work.
>>>(0 rows) returned when I used NOT EXITS but (4 rows) returned
>>>when NOT IN is used...................
>>>    
>>>
>>
>>Maybe you need a set of parenthesis around the old conditions
>>because of the or.
>>
>>  
>>
>>>>Try:
>>>>select
>>>>ts.transportid,ts.transporttype,ts.transportcapacity,ts.transportstatus,ts.routecoverage,ts.transportregnumber
>>>>
>>>>      
>>>>
>>>>from    transportsetup ts
>>>    
>>>
>>>>where   ts.bizid = 'B126'
>>>>and     ts.deletestatus = 0
>>>>and     NOT EXISTS (    select t.transportid
>>>>       from transportsetup t,servicedetail s,logisticservice l
>>>>       where ts.transportid = t.transportid
>>>>      
>>>>
>>
>>  
>>
>>>>           and t.bizid=l.bizid
>>>>      
>>>>
>>Replace the above with:
>>	       and (t.bizid=l.bizid
>>
>>  
>>
>>>>           and l.serviceid=s.serviceid
>>>>           and t.transportid=s.transportid
>>>>           and t.bizid = 'B126'
>>>>           and l.status='Pending'
>>>>       or t.bizid=l.bizid
>>>>           and l.serviceid=s.serviceid
>>>>           and t.transportid=s.transportid
>>>>           and t.bizid = 'B126' and l.status='Reserved' )
>>>>      
>>>>
>>
>>Add:
>>		)
>>
>>  
>>
>>>>order by ts.transporttype;
>>>>      
>>>>
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 7: don't forget to increase your free space map settings
>>
>>
>>  
>>
>


--------------060402000304010603040002
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <title></title>
</head>
<body>
Thanks Chris and Stephan<br>
the query is working now. <br>
<br>
Abdul Wahab Dahalan wrote:<br>
<blockquote type="cite" cite="mid3F3B006D.4000708@mimos.my">     
  <meta http-equiv="Content-Type" content="text/html;">
  <title></title>
     I cant find where to put the parenthesis as u said Stephan.<br>
 <br>
 Here the query :<br>
 <br>
 select ts.transportid,ts.transporttype,ts.transportcapacity,<br>
 ts.transportstatus,ts.routecoverage,ts.transportregnumber<br>
 from transportsetup ts<br>
 where ts.bizid = 'B126'<br>
 and ts.deletestatus = 0<br>
 and NOT EXISTS (select t.transportid<br>
 from transportsetup t,servicedetail s,logisticservice l<br>
 where ts.transportid = t.transportid<br>
 and t.bizid=l.bizid<br>
 and l.serviceid=s.serviceid<br>
 and t.transportid=s.transportid<br>
 and t.bizid = 'B126'<br>
 and l.status='Pending'<br>
 or t.bizid=l.bizid<br>
 and l.serviceid=s.serviceid<br>
 and t.transportid=s.transportid<br>
 and t.bizid = 'B126' and l.status='Reserved')<br>
 order by ts.transporttype;<br>
 <br>
 <br>
 <br>
 Stephan Szabo wrote:<br>
 
  <blockquote type="cite"
 cite="mid20030813201031.D67777-100000@megazone.bigpanda.com">   
    <pre wrap="">On Thu, 14 Aug 2003, Abdul Wahab Dahalan wrote:

  </pre>
   
    <blockquote type="cite">     
      <pre wrap="">Hai Chris!
Thanks for the solution but seem it doesnt work.
(0 rows) returned when I used NOT EXITS but (4 rows) returned
when NOT IN is used...................
    </pre>
   </blockquote>
   
    <pre wrap=""><!---->
Maybe you need a set of parenthesis around the old conditions
because of the or.

  </pre>
   
    <blockquote type="cite">     
      <blockquote type="cite">       
        <pre wrap="">Try:
select
ts.transportid,ts.transporttype,ts.transportcapacity,ts.transportstatus,ts.routecoverage,ts.transportregnumber

      </pre>
     </blockquote>
     
      <pre wrap="">&gt;from    transportsetup ts
    </pre>
     
      <blockquote type="cite">       
        <pre wrap="">where   ts.bizid = 'B126'
and     ts.deletestatus = 0
and     NOT EXISTS (    select t.transportid
       from transportsetup t,servicedetail s,logisticservice l
       where ts.transportid = t.transportid
      </pre>
     </blockquote>
   </blockquote>
   
    <pre wrap=""><!---->
  </pre>
   
    <blockquote type="cite">     
      <blockquote type="cite">       
        <pre wrap="">           and t.bizid=l.bizid
      </pre>
     </blockquote>
   </blockquote>
   
    <pre wrap=""><!---->Replace the above with:
	       and (t.bizid=l.bizid

  </pre>
   
    <blockquote type="cite">     
      <blockquote type="cite">       
        <pre wrap="">           and l.serviceid=s.serviceid
           and t.transportid=s.transportid
           and t.bizid = 'B126'
           and l.status='Pending'
       or t.bizid=l.bizid
           and l.serviceid=s.serviceid
           and t.transportid=s.transportid
           and t.bizid = 'B126' and l.status='Reserved' )
      </pre>
     </blockquote>
   </blockquote>
   
    <pre wrap=""><!---->
Add:
		)

  </pre>
   
    <blockquote type="cite">     
      <blockquote type="cite">       
        <pre wrap="">order by ts.transporttype;
      </pre>
     </blockquote>
   </blockquote>
   
    <pre wrap=""><!---->

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings


  </pre>
 </blockquote>
 <br>
 </blockquote>
<br>
</body>
</html>

--------------060402000304010603040002--