Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2008
    Posts
    7

    Unanswered: Performance problem with "in" operator

    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.

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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.
      when the "account" table has the 1lack+ records
    • Using IN is always inefficient but lets find out what you want before we improve things.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    lakh is Indian for 100,000
    see Lakh - Wikipedia, the free encyclopedia for details
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Apr 2008
    Posts
    7
    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.

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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
    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?

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

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    How's this for you?
    Code:
    SELECT a.username
         , Count(a.acctidentifier
         , Sum(a.time)
         , Sum(a.input)
         , Sum(a.output)
    FROM   account As a
     INNER
      JOIN client As c
        ON a.clientid = c.clientid
     INNER
      JOIN (
            SELECT acctidentifier
                 , Max(acctid) As max_acctid
            FROM   account
            GROUP
                BY acctidentifier
           ) As x
        ON a.acctidentifier = x.acctidentifier
       AND a.acctid = x.max_acctid
    WHERE  Date(a.acctrecdate) = '2009-02-24'
    George
    Home | Blog

  7. #7
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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' ).

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Agreed Mike - the SQL I posted is only part of the solution addressing the IN and no other part.
    George
    Home | Blog

  9. #9
    Join Date
    Apr 2008
    Posts
    7
    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
    Code:
    Would it be possible to calculate these values before doing the query and then just joining to this new data?
    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.

    Code:
    What are the indexes on the account table and roughly how many rows?
    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.

  10. #10
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Does the query provided by George run fast enough for what you need?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •