Article View: borland.public.interbase.sql
Article #265Re: Extreamly cpu intensive query
From: Craig Stuntz TeamB
Date: Tue, 29 Jan 2008 13:53
Date: Tue, 29 Jan 2008 13:53
31 lines
2238 bytes
2238 bytes
Mikael wrote: > > Check the fetch statistics. The subqueries are correlated. Hence, > > lots of fetching, lots of sorting. > > I always thougt this conserned I/O not Cpu. But sorting in memory > maybe does? Sorting in memory uses CPU, but I interpreted "25%" as meaning "25% of one processor." If it's "one processor at 100%" then you're possibly sorting way too much. > > > > > Use GROUP BY / HAVING instead, if possible, a stored proc if not. > > Hmm intresting. can you please develop that? In a HAVING you can do thing like: HAVING o.Status = MAX(i.Status) ...note that you can't put MAX in a WHERE, but you can in a HAVING, provided that you get all of the info you need into the FROM. This can eliminate the need for a subquery in some cases. -- Craig Stuntz [TeamB] � Vertex Systems Corp. � Columbus, OH Delphi/InterBase Weblog : http://blogs.teamb.com/craigstuntz All the great TeamB service you've come to expect plus (New!) Irish Tin Whistle tips: http://learningtowhistle.blogspot.com
Technical Details
Message-ID:
<479f3ddf$1@newsgroups.borland.com>Path:
arc1-ca.pugleaf.net!g2news1.google.com!news4.google.com!feeder3.cambrium.nl!feed.tweaknews.nl!217.73.144.44.MISMATCH!ecngs!feeder.ecngs.de!news.germany.com!newsfeed01.sul.t-online.de!t-online.de!newsgroups.borland.com!not-for-mailReferences:
<479f1acb@newsgroups.borland.com> <479f37d1@newsgroups.borland.com> <479f3cda$1@newsgroups.borland.com>Show Full Headers
Complete Article Headers
X-Google-Thread: 114a0e,5debdf074bd6e927 X-Google-Attributes: gid114a0e,public,usenet X-Google-Language: ENGLISH,ASCII From: "Craig Stuntz [TeamB]" <craig_stuntz@nospam.please [a.k.a. acm.org]> Subject: Re: Extreamly cpu intensive query Newsgroups: borland.public.interbase.sql References: <479f1acb@newsgroups.borland.com> <479f37d1@newsgroups.borland.com> <479f3cda$1@newsgroups.borland.com> Organization: Vertex Systems Corporation User-Agent: XanaNews/1.18.1.9 X-Face: "huzh55Xi]!ZNni78>a*5+).`SK?<^q2MlHk-C]Z2M9T@\J5ZCB$95Ey+s"XQuhGFA[eutZ:0^AV!Q;t>gE`1-=8d;HZ6~*o1dH<X9\b$!|(}+I'!Xq)@3Op}%0ArW&nf'Qh*EwR MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit NNTP-Posting-Host: 64.199.116.234 Message-ID: <479f3ddf$1@newsgroups.borland.com> Date: 29 Jan 2008 06:53:19 -0700 X-Trace: newsgroups.borland.com 1201618399 64.199.116.234 (29 Jan 2008 06:53:19 -0700) Lines: 31 X-Authenticated-User: cstuntz Path: arc1-ca.pugleaf.net!g2news1.google.com!news4.google.com!feeder3.cambrium.nl!feed.tweaknews.nl!217.73.144.44.MISMATCH!ecngs!feeder.ecngs.de!news.germany.com!newsfeed01.sul.t-online.de!t-online.de!newsgroups.borland.com!not-for-mail