improve performance of query

rajaryan4545 · #1 (**permalink**) 12-27-07, 23:25

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.....

stolze · #2 (**permalink**) 12-28-07, 02:49

Did you have a look at the access plan to see which portions of the query are shipped to the remote data suorce(s)?

rajaryan4545 · #3 (**permalink**) 12-29-07, 13:18

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!!!!

stolze · #4 (**permalink**) 01-01-08, 17:34

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

Internet Services

Web Development

Digital Marketing

Consumer Tech