Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2007
    Posts
    27

    Unanswered: improve performance of query

    Hi Guys,

    i have a query running against a datawarehouse database which is on V8.2 fp5(solaris).

    the query is taking some long time i need to tune it. the indexes and other stuff is ok.but primarly i have to modify the query in a better way.actually the tables involved in this query are from a federated database(nicknames).i hve updated the runstats on the nicknames also.
    here is the query

    select power.item_code,M.customer_id,M.sales_method,order _wt,prod_wt,power.crid,
    income,delivery_time,power.nov,deal_no,prod_no,sum (ltk_wt) as swd_wt

    from overweight_pos power

    inner join sales M on power.item_code = M.item_code

    LEFT JOIN prod_no pn ON M.item_code = pn.item_code

    and M.customer_id = pn.customer_id

    where M.sales_method = 'direct'

    group by power.item_code,M.customer_id,M.sales_method,order _wt,prod_wt,power.crid,

    income,delivery_time,power.nov,deal_no,prod_no

    UNION all

    select power.item_code,J.customer_id,J.sales_method,order _wt,prod_wt,power.crid,

    income,delivery_time,power.nov,deal_no,prod_no,sum (ltk_wt) as swd_wt

    from overweight_pos power

    inner join sales J on power.item_code = J.item_code

    LEFT JOIN prod_no pn ON J.item_code = pn.item_code

    and J.customer_id = pn.customer_id

    where J.sales_method = 'door'

    group by power.item_code,J.customer_id,J.sales_method,order _wt,prod_wt,power.crid,
    income,delivery_time,power.nov,deal_no,prod_no


    please help me guys, thanks.....

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Did you have a look at the access plan to see which portions of the query are shipped to the remote data suorce(s)?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Nov 2007
    Posts
    27

    improve performance of the query

    actually this is a part of a large complex query. the query is of type

    with temp1 as ( ......),
    temp2 as ( selct from temp1....),
    temp3 as (select from temp2...),
    ..
    ..
    ..
    ..
    and likewise temp8 is the query whichi posted
    select x,y,z from temp8;

    here when i try to get the visual explain for the query above the temp5 table i was able to get it.but when it comes to taking the explain of the query with the temp5 table (i.e, the query which i posted) i was not able to get the explain plan.i am getting the following error.

    DBA3065E Snapshot cannot be processed due to a CLI error.

    Explanation:

    A CLI error has occurred during snapshot processing. An access plan
    cannot be displayed.

    User response:

    Check the CLI configuration by issuing the following command:

    db2 get cli cfg
    for section <db-name>

    If LONGDATACOMPAT is set to 1, try cataloging the database with a
    different alias:

    catalog db <db-name> as <db-alias-name>

    Set LONGDATACOMPAT=0 for the database alias:

    db2 update cli cfg for section <db-alias-name> using
    longdatacompat 0

    Submit the query for EXPLAIN in the alias database.

    If LONGDATACOMPAT is not set to 1, or the parameter is not being set in
    the CLI configuration, contact IBM Service.

    but when i checked with the command db2 get cli cfg for section <databasename> i could just see the alias name of the database.
    i donno where this LONGDATACOMPAT is present.

    so i could not get the explain for this part of the query. i think because of the complexity of the query i could not get the explain output.so thought of modifying the query into less complex query so that i may get explain.

    actually i was able to get the output with a warning which is
    SQL0437W Performance of this complex query may be sub-optimal. Reason code: "3". SQLSTATE=01602

    0 Rows selected
    so can u plz give any suggestions in this regard.

    Thanks!!!!

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Something else you could do is to capture a CLI trace on the remote data source and, thus, figure out which part of the query is sent to the source. If no filter predicates or occur in the ship section, it would be an explanation for the performance issues.

    I also had a more detailed look at your query. I reformatted it a bit so that I can better see what the query tries to accomplish:
    Code:
    SELECT power.item_code, m.customer_id, m.sales_method,
           order _wt, prod_wt, power.crid, income, delivery_time,
           power.nov, deal_no, prod_no, SUM(ltk_wt) AS swd_wt
    FROM   overweight_pos AS power
              INNER JOIN
           sales AS m ON ( power.item_code = m.item_code )
              LEFT OUTER JOIN 
           prod_no AS pn ON ( m.item_code = pn.item_code AND
                               m.customer_id = pn.customer_id )
    WHERE  m.sales_method = 'direct'
    GROUP BY power.item_code, m.customer_id, m.sales_method, order _wt, prod_wt, power.crid,
             income, delivery_time, power.nov, deal_no, prod_no
    
    UNION ALL
    
    SELECT power.item_code, j.customer_id, j.sales_method,
           order _wt, prod_wt, power.crid, income, delivery_time,
           power.nov, deal_no, prod_no, SUM(ltk_wt) AS swd_wt
    FROM   overweight_pos AS power
              INNER JOIN
           sales AS j ON ( power.item_code = j.item_code )
              LEFT OUTER JOIN
           prod_no AS pn ON ( j.item_code = pn.item_code AND
                               j.customer_id = pn.customer_id )
    WHERE  j.sales_method = 'door'
    GROUP BY power.item_code, j.customer_id, j.sales_method, order _wt, prod_wt, power.crid,
             income, delivery_time, power.nov, deal_no, prod_no
    Please correct me if I'm wrong, but I see absolutely no differences between the two branches of the UNION ALL, except that one subselect uses a predicate "sales_method = 'direct'" and the other "sales_method = 'door'". Further, column sales_method is also in the GROUP BY list. So why do you use the UNION ALL in the first place? The following rewrite is much simpler and will return the same results:
    Code:
    SELECT power.item_code, m.customer_id, m.sales_method,
           order _wt, prod_wt, power.crid, income, delivery_time,
           power.nov, deal_no, prod_no, SUM(ltk_wt) AS swd_wt
    FROM   overweight_pos AS power
              INNER JOIN
           sales AS m ON ( power.item_code = m.item_code )
              LEFT OUTER JOIN 
           prod_no AS pn ON ( m.item_code = pn.item_code AND
                               m.customer_id = pn.customer_id )
    WHERE  m.sales_method IN ( 'direct', 'door' )
    GROUP BY power.item_code, m.customer_id, m.sales_method, order _wt, prod_wt, power.crid,
             income, delivery_time, power.nov, deal_no, prod_no
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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