Re: How to speeed up the query performance - Mailing list pgsql-sql

Thanks Chris and Stephan<br /> the query is working now. <br /><br /> Abdul Wahab Dahalan wrote:<br /><blockquote
cite="mid3F3B006D.4000708@mimos.my"type="cite"></blockquote> 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,logisticservicel<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'
andl.status='Reserved')<br /> order by ts.transporttype;<br /><br /><br /><br /> Stephan Szabo wrote:<br /><blockquote
cite="mid20030813201031.D67777-100000@megazone.bigpanda.com"type="cite"><pre wrap="">On Thu, 14 Aug 2003, Abdul Wahab
Dahalanwrote:
 
 </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="">>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"><blockquotetype="cite"><pre wrap="">           and t.bizid=l.bizid     </pre></blockquote></blockquote><pre
wrap="">Replacethe 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><prewrap="">
 

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

 </pre></blockquote><br /><br />