Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2006
    Posts
    5

    Unanswered: Speed Up SQL Statement

    Hi there,

    I need some guidance on the sql statement that i have.
    Its running very slow and i think the Distinct and Group By play their part in it
    but not sure how to enhance this code.

    SELECT DISTINCT A, B, C, COUNT(*) D
    FROM TABLE_A, TABLE_B
    WHERE E = '01'
    AND TABLE_A.F = TABLE_B.H
    AND TABLE_A.J = TABLE_B.K
    AND TABLE_A.L <> TABLE_B.M
    GROUP BY A,B,C

    Hope someone can point me a right direction.
    Thanks in advance

  2. #2
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    Hi,

    you are right, the distinct/group by thing is one of the responsible actors, but maybe not the only one, therefore a number of further questions:
    0) set expectations: what do you call "very slow"? Can you please tell how long does this query take to complete, and what duration do you expect?
    1) Are your statistics up to date (i-e when has 'update statistics' been run for the last time)
    2) Is this the first time you run this query, or was it fast before and slow now ?
    3) If (1) has not been executed recently, please run it with dbaccess as informix or a dba role, then, before rerunning your query, add the set explain on statement, like this:
    Code:
    SET EXPLAIN ON;
    SELECT DISTINCT A, B, C, COUNT(*) D
    FROM TABLE_A, TABLE_B
    WHERE E = '01'
    AND TABLE_A.F = TABLE_B.H
    AND TABLE_A.J = TABLE_B.K
    AND TABLE_A.L <> TABLE_B.M
    GROUP BY A,B,C
    send me the sqexplain.out file that you will find i, the current directory.
    4) How many rows do table_a and table_b have?
    5) can you also post the schema of both tables, including indexes?

    We'll talk again after I have all this.
    Regards,
    Eric

Tags for this Thread

Posting Permissions

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