🚀 go-pugleaf

amita wrote:
> 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

Someone can help only if you say what is your server, post your methadata
definitions and plan that you receive from the server for this query and
what you expect as results.

On such large table you must ensure that everything is going by indexes.

Index on Amount is useless, is is used as Sum only.
Index on Col_Date is needed.

That I have understand from your query:
This is all  Mast_keys where in given period sum is more than 2000
(select Mast_key from Detail_Table
          where Col_Date between :FromDate and :ToDate
          group by Mast_Key having sum(Amount)>2000)

and what you do then - why is this here also - Col_Date between :FromDate
and :ToDate ?
Is Col_Date exists in Master_Table also or this is from detail?

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)

Something here I don't undertand... Please explain again what you expect as
result.
Also may be you can try to separate the qubquery in view and then join...


--
Bojidar Alexanrov
Kodar Ltd.
http://www.Kodar.net

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

Hi Amita!
I think your problem is the order of the fields in your detail primary key.
It should be mast_key, Det_Key, ....etc.
If you get it. This should work fine for you:
Select *
  From master_table a
 Where 2000< (Select Sum(amount)
                        From detail_table b
                       Where b.mast_key =a.mast_key
                           And b.col_date between :fromdate and :todate)

Bye
FBrador

"amita" <amita@agnisoft.com> wrote in message
news:3de6f872@newsgroups.borland.com...
> 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
>
>