query runs slow with IN and GROUP BY HAVING

Article View: borland.public.interbase.sql

Article #11247

Back to borland.public.interbase.sql Previous Next

#11247

From: amita
Date: Fri, 29 Nov 2002 05:22

29 lines
1741 bytes

I have a master and detail table
as
Master_Table ->  Mast_Key....
Detail_Table  ->   Det_Key, Mast_key , Col_Date , Amount.....

Now I have a query get the rows of the master
where the Date lies between given dates and the total amount
collected during that period exceeds say 2000

so i have the query as

select * from  Master_Table inner join Detail_Table
on(Master_Table.Mast_Key=Detail_Table.Mast_Key)
where Col_Date between :FromDate and :ToDate
and Mast_Key in
 (select Mast_key from Detail_Table
 where Col_Date between :FromDate and :ToDate
 group by Mast_Key having sum(Amount)>2000)

Now the problem is that both Master_Table and
Detail table have millions of records this
query runs very slow
I have indexes
on Mast_Key, Det_key and Amount

--
Amita
Agni Software Pvt Ltd
Bangalore, India

Technical Details

Message-ID: <3de6f872@newsgroups.borland.com>

Path:

arc1-ca.pugleaf.net!archiver1.google.com!news1.google.com!sn-xit-03!sn-xit-01!sn-xit-08!supernews.com!newsfeed.news2me.com!border1.nntp.aus1.giganews.com!nntp.giganews.com!newsfeeds.ihug.co.nz!ihug.co.nz!newsgroups.borland.com!not-for-mail

Show Full Headers

Complete Article Headers

X-Google-Language: ENGLISH,ASCII-7-bit
X-Google-Thread: 114a0e,ba43de26dfc40e54,start
X-Google-Attributes: gid114a0e,public
X-Google-ArrivalTime: 2002-11-28 21:36:03 PST
From: "amita" <amita@agnisoft.com>
Newsgroups: borland.public.interbase.sql
Subject: query runs slow with IN and GROUP BY HAVING
Date: Fri, 29 Nov 2002 10:52:06 +0530
Lines: 31
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.195.196.78
Message-ID: <3de6f872@newsgroups.borland.com>
X-Trace: newsgroups.borland.com 1038547058 203.195.196.78 (28 Nov 2002 21:17:38 -0800)
Path: arc1-ca.pugleaf.net!archiver1.google.com!news1.google.com!sn-xit-03!sn-xit-01!sn-xit-08!supernews.com!newsfeed.news2me.com!border1.nntp.aus1.giganews.com!nntp.giganews.com!newsfeeds.ihug.co.nz!ihug.co.nz!newsgroups.borland.com!not-for-mail