Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2008
    Posts
    4

    Exclamation Unanswered: poor performance

    Hi,
    If any one provide better query for than below query

    SELECT DISTINCT SCANOSEC.I_LOAN_ACCOUNT.I_CLOSING_DATE,SCANOSEC.I_ APP_RELATIONSHIP.I_APP_ID

    FROM SCANOSEC.I_APPLICATION,SCANOSEC.I_APP_RELATIONSHIP ,SCANOSEC.I_LOAN_ACCOUNT

    WHERE SCANOSEC.I_APPLICATION.I_JOINT_STATUS = 200

    AND SCANOSEC.I_APPLICATION.I_APP_ID=SCANOSEC.I_APP_REL ATIONSHIP.I_LINKED_APP_ID

    AND SCANOSEC.I_APPLICATION.I_APP_ID=SCANOSEC.I_LOAN_AC COUNT.I_APP_ID

    AND SCANOSEC.I_APP_RELATIONSHIP.I_LINK_TYPE=2
    AND SCANOSEC.I_LOAN_ACCOUNT.I_CLOSING_DATE <='2009-11-25 00:00:00';

  2. #2
    Join Date
    Nov 2007
    Posts
    36
    use the AND conditions on smaller table first and then on bigger table.

  3. #3
    Join Date
    Nov 2007
    Posts
    36
    and try to see the JOINs on the below conditions

    AND SCANOSEC.I_APPLICATION.I_APP_ID=SCANOSEC.I_APP_REL ATIONSHIP.I_LINKED_APP_ID

    AND SCANOSEC.I_APPLICATION.I_APP_ID=SCANOSEC.I_LOAN_AC COUNT.I_APP_ID

    JOINS will improve the performance.

  4. #4
    Join Date
    Jan 2008
    Posts
    4

    Question poort performance

    thanks for your response,

    can you send joins for the below statements

    Quote Originally Posted by ani_dbforum
    and try to see the JOINs on the below conditions

    AND SCANOSEC.I_APPLICATION.I_APP_ID=SCANOSEC.I_APP_REL ATIONSHIP.I_LINKED_APP_ID

    AND SCANOSEC.I_APPLICATION.I_APP_ID=SCANOSEC.I_LOAN_AC COUNT.I_APP_ID

    JOINS will improve the performance.

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The order of conditions is completely irrelevant. The DB2 optimizer will choose the execution order for predicates as it seems best. Also, it is completely irrelevant for the optimizer whether join conditions are written in the ON clause of the explicit join syntax or in the WHERE clause with implicit joins. (Personally, I prefer the explicit join syntax because it is easier to read and to separate join conditions from other filter predicates.) So you can ignore ani_dbforum's statements right away.

    What should be done here is:
    (a) make sure that the query is exactly what the application needs and not too much (e.g. is the DISTINCT necessary?)
    (b) verify that the necessary indexes exist
    (c) ensure that statistics were collected on tables and indexes

    You could also run this query as workload through the DB2 design advisor. Maybe there are some other objects like MQTs that could improve the situation.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Jan 2008
    Posts
    4

    poort performance

    thanks for your response, can you send me using JOINS, for the below query

    SELECT DISTINCT SCANOSEC.I_LOAN_ACCOUNT.I_CLOSING_DATE,SCANOSEC.I_ APP_RELATIONSHIP.I_APP_ID
    FROM SCANOSEC.I_APPLICATION,SCANOSEC.I_APP_RELATIONSHIP ,SCANOSEC.I_LOAN_ACCOUNT
    WHERE SCANOSEC.I_APPLICATION.I_JOINT_STATUS = 200
    AND SCANOSEC.I_APPLICATION.I_APP_ID=SCANOSEC.I_APP_REL ATIONSHIP.I_LINKED_APP_ID
    AND SCANOSEC.I_APPLICATION.I_APP_ID=SCANOSEC.I_LOAN_AC COUNT.I_APP_ID
    AND SCANOSEC.I_APP_RELATIONSHIP.I_LINK_TYPE=2
    AND SCANOSEC.I_LOAN_ACCOUNT.I_CLOSING_DATE = '2009-11-25 00:00:00';

    -----------------------------------------------------------------------------
    Quote Originally Posted by stolze
    The order of conditions is completely irrelevant. The DB2 optimizer will choose the execution order for predicates as it seems best. Also, it is completely irrelevant for the optimizer whether join conditions are written in the ON clause of the explicit join syntax or in the WHERE clause with implicit joins. (Personally, I prefer the explicit join syntax because it is easier to read and to separate join conditions from other filter predicates.) So you can ignore ani_dbforum's statements right away.

    What should be done here is:
    (a) make sure that the query is exactly what the application needs and not too much (e.g. is the DISTINCT necessary?)
    (b) verify that the necessary indexes exist
    (c) ensure that statistics were collected on tables and indexes

    You could also run this query as workload through the DB2 design advisor. Maybe there are some other objects like MQTs that could improve the situation.

  7. #7
    Join Date
    Nov 2007
    Posts
    36
    Thanks stolze for correcting me,

    I've one more question:

    A have 1 million records, B has 40 million records.
    Can A inner join B with On condition faster or B inner join A faster?
    or both are same?

  8. #8
    Join Date
    Jan 2008
    Posts
    4

    Question poor performance

    thanks, i am expecting for the above query using Joins.

    Quote Originally Posted by ani_dbforum
    Thanks stolze for correcting me,

    I've one more question:

    A have 1 million records, B has 40 million records.
    Can A inner join B with On condition faster or B inner join A faster?
    or both are same?

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by ani_dbforum
    Thanks stolze for correcting me,

    I've one more question:

    A have 1 million records, B has 40 million records.
    Can A inner join B with On condition faster or B inner join A faster?
    or both are same?
    DB2 has a cost-based optimizer. It will evaluate both join orders (also with different join strategies and index evaluations) and choose the plan that has the lowest costs. So writing "a JOIN b ON ..." is exactly the same as "b JOIN a ON ...".
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Jan 2003
    Posts
    1,605
    Quote Originally Posted by stolze
    DB2 has a cost-based optimizer. It will evaluate both join orders (also with different join strategies and index evaluations) and choose the plan that has the lowest costs. So writing "a JOIN b ON ..." is exactly the same as "b JOIN a ON ...".
    Hi,
    this is easy to test. Run db2 explain...
    Hope this helps,
    Grofaty

  11. #11
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Your SQL code
    Code:
    SELECT DISTINCT SCANOSEC.I_LOAN_ACCOUNT.I_CLOSING_DATE,SCANOSEC.I_ APP_RELATIONSHIP.I_APP_ID
    FROM SCANOSEC.I_APPLICATION,SCANOSEC.I_APP_RELATIONSHIP ,SCANOSEC.I_LOAN_ACCOUNT
    WHERE SCANOSEC.I_APPLICATION.I_JOINT_STATUS = 200
    AND SCANOSEC.I_APPLICATION.I_APP_ID=SCANOSEC.I_APP_REL ATIONSHIP.I_LINKED_APP_ID
    AND SCANOSEC.I_APPLICATION.I_APP_ID=SCANOSEC.I_LOAN_AC COUNT.I_APP_ID
    AND SCANOSEC.I_APP_RELATIONSHIP.I_LINK_TYPE=2
    AND SCANOSEC.I_LOAN_ACCOUNT.I_CLOSING_DATE <='2009-11-25 00:00:00'
    ;
    can be rewritten as:
    Code:
    SELECT DISTINCT SCANOSEC.I_LOAN_ACCOUNT.I_CLOSING_DATE,
    		SCANOSEC.I_ APP_RELATIONSHIP.I_APP_ID
    FROM SCANOSEC.I_APPLICATION
      INNER JOIN SCANOSEC.I_APP_RELATIONSHIP ON
        SCANOSEC.I_APPLICATION.I_APP_ID=SCANOSEC.I_APP_REL ATIONSHIP.I_LINKED_APP_ID
      INNER JOIN SCANOSEC.I_LOAN_ACCOUNT ON
        SCANOSEC.I_APPLICATION.I_APP_ID=SCANOSEC.I_LOAN_AC COUNT.I_APP_ID
    WHERE SCANOSEC.I_APPLICATION.I_JOINT_STATUS = 200
        AND SCANOSEC.I_APP_RELATIONSHIP.I_LINK_TYPE=2
        AND SCANOSEC.I_LOAN_ACCOUNT.I_CLOSING_DATE <='2009-11-25 00:00:00'
    ;
    The INNER JOIN statements make it clear what the relations between the tables are. The WHERE statement now only contains business logic.

    Both SQL statemenst will give the same results, in the same amount of processing time. The second is just easier to understand (once you're used to it) by separating the join conditions from the business logic conditions.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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