Article View: borland.public.interbase.sql
Article #11253Re: query runs slow with IN and GROUP BY HAVING
From: Brent Rose
Date: Sat, 30 Nov 2002 03:48
Date: Sat, 30 Nov 2002 03:48
19 lines
1892 bytes
1892 bytes
Sub-queries can be a problem sometimes - your one could be pretty hefty on a large table!! You may find the sub-query is being re-executed for each record considered, even if it is not returned in the end...a huge waste of time. Consider using a stored procedure instead - even if above is not quite the case, it may perform significantly better by breaking up the query....... Try using a for/select/do loop to cycle the Master/Detail records in the from/to date range. Only for these records run the sub-query, but optimised for the SINGLE Mast_Key record only ie just query Sum(Amount) in the date range for the specific Mast_Key (only a relative few records I assume), there is no need to use a more demanding "group by" query on the full detail table. If the sum > 2000, then "suspend".... -- Brent Rose brentrose@paradise.net.nz
Technical Details
Message-ID:
<3de83466@newsgroups.borland.com>Path:
arc1-ca.pugleaf.net!archiver1.google.com!news1.google.com!sn-xit-02!sn-xit-03!sn-xit-01!sn-xit-04!supernews.com!newshosting.com!news-xfer1.atl.newshosting.com!uunet!dca.uu.net!ash.uu.net!newsfeeds.ihug.co.nz!ihug.co.nz!newsgroups.borland.com!not-for-mailReferences:
<3de6f872@newsgroups.borland.com>Show Full Headers
Complete Article Headers
X-Google-Language: ENGLISH,ASCII-7-bit X-Google-Thread: 114a0e,ba43de26dfc40e54 X-Google-Attributes: gid114a0e,public X-Google-ArrivalTime: 2002-11-29 19:49:14 PST Reply-To: "Brent Rose" <brentrose@paradise.net.nz> From: "Brent Rose" <brentrose@paradise.net.nz> Newsgroups: borland.public.interbase.sql References: <3de6f872@newsgroups.borland.com> Subject: Re: query runs slow with IN and GROUP BY HAVING Date: Sat, 30 Nov 2002 16:48:40 +1300 Lines: 23 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 NNTP-Posting-Host: 203.79.102.234 Message-ID: <3de83466@newsgroups.borland.com> X-Trace: newsgroups.borland.com 1038627942 203.79.102.234 (29 Nov 2002 19:45:42 -0800) Path: arc1-ca.pugleaf.net!archiver1.google.com!news1.google.com!sn-xit-02!sn-xit-03!sn-xit-01!sn-xit-04!supernews.com!newshosting.com!news-xfer1.atl.newshosting.com!uunet!dca.uu.net!ash.uu.net!newsfeeds.ihug.co.nz!ihug.co.nz!newsgroups.borland.com!not-for-mail