Hi all,

I have the following query for which I am unable to retrieve the data when the "account" table has the 1lack+ records. When the data is less I got the result. I checked the query by splitting it I found that the "in" operator is taking time. Can any body help me to tune this query. I need to execute this query from the screen and display the data.

select a.username, count(a.acctidentifier) , sum(a.time), sum(a.input), sum(a.output)
from account a, client c where a.clientid = c.clientid and
a.acctid in (select max(acctid) from account group by acctidentifier)
and date(a.acctrecdate) >= '2009-02-24' and date(a.acctrecdate) <= '2009-02-24' group by a.username

Thanks in Advance
Naresh.

Your query doesn't make sense to me:

• What are you trying to do in this part?
  
  Code:

           select max(acctid) 
           from account 
           group by acctidentifier

• Is this really what you want to put here?
  
  Code:

  and date(a.acctrecdate) >= '2009-02-24' 
  and date(a.acctrecdate) <= '2009-02-24'

• Can you explain this part more.
  
  Quote:

  when the "account" table has the 1lack+ records

• Using IN is always inefficient but lets find out what you want before we improve things.

lakh is Indian for 100,000
see Lakh - Wikipedia, the free encyclopedia for details

Thanks mike_bike_kite for your response,

Coming to your questions

1. In the account table for each "acctidentifier" value there will be multiple "acctid" values out of that I need the latest "acctid". For that I used the "select max(acctid) from account group by acctidentifier".
ex: acctid acctidentifier
----------------------------
1 acc1
2 acc1
3 acc1
4 acc2
5 acc2

In the above lines I need to consider only the Bolded rows.

2. The column "acctrecdate" datatype is timestamp. But I need to compare it with date part only. For that reason I used "and date(a.acctrecdate) >= '2009-02-24' ".

3. The Account table is having more than 100,000 records.

Thanks in Advance,
Naresh.

Would it be possible to calculate these values before doing the query and then just joining to this new data?

What are the indexes on the account table and roughly how many rows?

But your testing this date to be >= '2009-02-24' and <= '2009-02-24' . Do you just mean date(a.acctrecdate) = '2009-02-24'.

How's this for you?

I was getting there George but I just wanted to clear up the thought process in the original SQL first. It's still worth looking at the indexes on the account table though. Also if there is an index on the acctrecdate field then the query still won't use it as you're applying a function to the field ( Date(a.acctrecdate) = '2009-02-24' ).

Agreed Mike - the SQL I posted is only part of the solution addressing the IN and no other part.

Thanks gvee and mike_bike_kite,

gvee: The query which u posted is retrieving data. Now I need to validate the data with different scenarios whether that query is retreiving the valid data or not.

mike_bike_kite: For your questions
No, I need to give this query in a file. First I will retrieve the qurey string from the file and then I will retreive the data from database.

Currently I don't have any idexes, because this table will be used to insert data more times than retreiving data. But the "acctid" is the primary key in the "account" table.

Thanks,
Naresh.

Does the query provided by George run fast enough for what you need?