Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Posts
    2

    Unanswered: DB2 Query Optimization

    I've written the following query which just clocks and clocks. Is there a better way to write the where clause?

    Secondly, is there a free sql optimization tool that will work for DB2 v5 on OS/390? I've found many that look promising but do not support my environment. Thanks in advance.

    SELECT DISTINCT a.ID_NBR_PERS FROM
    VISDBA.VPER a,
    VISDBA.PERX b,
    VISDBA.TRSS c,
    VISDBA.GRSC d,
    VISDBA.UGSC e,
    WHERE
    a.ID_NBR_PERS=b.ID_NBR
    AND
    (a.VISA_CD = 'F2' OR a.VISA_CD = 'F1' OR a.CITIZEN = 'FR')
    AND
    ((a.ID_NBR_PERS = c.ID_NBR_TRSS AND (c.SEM_TRSS = '2003FA' OR c.SEM_TRSS = '2003S2' OR c.SEM_TRSS = '2003S1' OR c.SEM_TRSS = '2003SP'))
    OR
    (b.STDNT_COLLEGE = 'GR' AND b.GR_TYP_CD = '' AND (d.GR_ADM_ACTN_SEM = '2003FA' OR d.GR_ADM_ACTN_SEM = '2003S2' OR d.GR_ADM_ACTN_SEM = '2003S1' OR d.GR_ADM_ACTN_SEM = '2003SP'))
    OR
    (b.STDNT_COLLEGE <> 'GR' AND b.UG_TYP_CD = '' AND (e.UG_ADM_ACTN_SEM = '2003FA' OR e.UG_ADM_ACTN_SEM = '2003S2' OR e.UG_ADM_ACTN_SEM = '2003S1' OR e.UG_ADM_ACTN_SEM = '2003SP')))

    ============

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: DB2 Query Optimization

    The first thing I see in your query is that it is not joined properly ... It is very close to a cartesian product ... ie, if each table in the from clause has 10 rows , you may end up with 10*10*10*10*10 rows against which you all your criteria has to be applied and then sorted to get 'distinct' ...
    The only join criteria I see is a.ID_NBR_PERS = c.ID_NBR_TRSS and that too am not sure how it will be interpreted by the optimizer


    If 10 rows in each table will mean a 100,000 rows, then you can understand ...

    To be short :

    1) Join the tables based on a key and have an index on the key
    2) Do Runstats and Rebind your program
    3) Avoid the distinct, if possible ...(becuase I have seen many people write distinct whether it really matters or not)

    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Aug 2003
    Posts
    2
    I took the advice of Sathyaram and changed the initial query to look like the one below. It is quite a bit faster but is still too slow... I'll continue to find ways to tweak.

    SELECT DISTINCT a.ID_NBR_PERS
    FROM
    VISDBA.VPER a
    INNER JOIN VISDBA.PERX b ON a.ID_NBR_PERS=b.ID_NBR
    LEFT OUTER JOIN VISDBA.TRSS c ON a.ID_NBR_PERS = c.ID_NBR_TRSS
    LEFT OUTER JOIN VISDBA.GRSC d ON a.ID_NBR_PERS = d.ID_NBR_GRSC
    LEFT OUTER JOIN VISDBA.UGSC e ON a.ID_NBR_PERS = e.ID_NBR_UGSC

    WHERE

    (a.VISA_CD = 'F2' OR a.VISA_CD = 'F1' OR a.CITIZEN = 'FR')

    AND

    (
    (c.SEM_TRSS = '2003FA' OR c.SEM_TRSS = '2003S2' OR c.SEM_TRSS = '2003S1' OR c.SEM_TRSS = '2003SP')

    OR

    (b.STDNT_COLLEGE = 'GR' AND b.GR_TYP_CD = '' AND (d.GR_ADM_ACTN_SEM = '2003FA' OR d.GR_ADM_ACTN_SEM = '2003S2' OR d.GR_ADM_ACTN_SEM = '2003S1' OR d.GR_ADM_ACTN_SEM = '2003SP'))

    OR

    (b.STDNT_COLLEGE <> 'GR' AND b.UG_TYP_CD = '' AND (e.UG_ADM_ACTN_SEM = '2003FA' OR e.UG_ADM_ACTN_SEM = '2003S2' OR e.UG_ADM_ACTN_SEM = '2003S1' OR e.UG_ADM_ACTN_SEM = '2003SP'))

    )

    =========

  4. #4
    Join Date
    Jun 2003
    Location
    Canada
    Posts
    37
    If I recall correctly, in V5, DB2 had a fairly limited ability to push down WHERE clause predicates to before the join. This means your WHERE clause as shown in the code would only be executed after the join. If you have a huge number of rows in the tables involved, the row restricting predicates will only be applied too late.

    In V5, the way to "manually" push a WHERE clause to before-join is to code the predicate in a nested table expression. This, of course adds complexity and overhead (extra materialization) but in the case of effective predicates, this is something worth doing. Should definitely result in faster execution.

    Do you have plans to move to V6? The same code you have should execute much faster there.

    HTH

    Julius

Posting Permissions

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