Обсуждение: How to speeed up the query performance
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 affectedthe performance?.<br /><br /> select ts.transportid,ts.transporttype,ts.transportcapacity,ts.transportstatus,ts.routecoverage,ts.transportregnumber<br /> from transportsetup ts<br /> where ts.bizid = 'B126' <br /> and ts.deletestatus = 0 <br /> and ts.transportid<u><big>not in</big></u> ( select t.transportid <br /> f rom transportsetup t,servicedetail s,logisticservice l <br /> wheret.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 /> Any help pretty much appreciated. Thanks<br />
>
> 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
-----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 ?" thread. If you want more details, check the pgsql-performance archives. - -- 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= =Sg0n -----END PGP SIGNATURE-----
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 cite="mid3F3A501A.FDF96A90@rodos.fzk.de" type="cite"><blockquote type="cite"><pre wrap="">How do
Ispeed 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
in7.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><blockquotetype="cite"><pre wrap=""> and
</pre></blockquote><prewrap="">l.serviceid=s.serviceid </pre><blockquote type="cite"><pre wrap="">
and </pre></blockquote><pre wrap="">t.transportid=s.transportid </pre><blockquote
type="cite"><prewrap=""> and t.bizid = </pre></blockquote><pre
wrap="">'B126'</pre><blockquote type="cite"><pre wrap=""> and
</pre></blockquote><prewrap="">l.status='Pending' </pre><blockquote type="cite"><pre wrap="">
or t.bizid=l.bizid and
</pre></blockquote><prewrap="">l.serviceid=s.serviceid </pre><blockquote type="cite"><pre wrap="">
and </pre></blockquote><pre wrap="">t.transportid=s.transportid </pre><blockquote
type="cite"><prewrap=""> 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 />
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;
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 />
andt.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 cite="mid20030813201031.D67777-100000@megazone.bigpanda.com"
type="cite"><prewrap="">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="">>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 />
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 />