🚀 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