IDS Forum

Fw: Optimizer

Posted By: Peter_Logan@spartanstores.com
Date: Wednesday, 28 March 2012, at 3:59 p.m.

Aix 6.1
IDS 11.70.fc3

The below query was executing ok, and then recently it started taking a
lot longer... In looking at the query plans, the order of the tables
changed a few weeks ago... Amount of data and stats in the tables haven't
changed dramatically ...

The question I have, is ... Is the optimizer smart enough to execute the
(select weekid(today, -1) from systables where tabid = 1 ... I can
duplicate if I take that select out and hard code the fiscal week id ..
runs about a minute .. then runs about 4 minutes. The query plan changes
.. If I do run the query with the directive ... then I can consistently
get it to run around a minute .. The problem is that this is a Micro
Strategies report, so that optimizer directive can't be included ....
Any help would be appreciated ...

Peter Logan
Senior Database Administrator
Phone: 616/878-8309
----- Forwarded by Peter Logan/Corporate/Spartan on 03/28/2012 03:53 PM
-----

From: plisad0 (spartan02) <plisad0@spartanstores.com>
To: Peter_Logan@spartanstores.com
Date: 03/28/2012 03:52 PM
Subject: Optimizer

-- select --+ORDERED
select
a18.mdse_grp_key mdse_grp_key,

a12.fiscal_week_id fiscal_week_id,

a13.catgy_manager_key catgy_manager_key,

(sum(a11.total_sales_amt) * 0.001) WJXBFS1,

sum(a11.ext_profit_amt) WJXBFS2,

sum(a11.total_sales_amt) WJXBFS3,

sum(a11.ext_profit_amt) WJXBFS4
from

week_to_year_lkp a12,

channel a16,

chain a15,

line a14,
efin_wk_line_cls a11,

mdse_class_manager a13,

mdse_class a17,

mdse_category a18,

mdse_group a19,

department a110,

business_dept a111
where a11.fiscal_week_id = a12.fiscal_wty_id and

a11.mdse_class_key = a13.mdse_class_key and

a11.sales_line_id = a14.sales_line_id and

a14.sales_chain_id = a15.sales_chain_id and

a15.sales_channel_id = a16.sales_channel_id and

a11.mdse_class_key = a17.mdse_class_key and

a17.mdse_catgy_key = a18.mdse_catgy_key and

a18.mdse_grp_key = a19.mdse_grp_key and

a19.dept_key = a110.dept_key and

a110.bus_dept_key = a111.bus_dept_key

and (a16.enterprise_id in (18)

and a13.catgy_manager_key in (70)

and a110.dept_grp_key not in (525, 9)

and a14.format_type_id in ('SUPERMKT ')

and a12.fiscal_week_id in (201253)

and a111.dept_grp_type_key in (1))
group by a18.mdse_grp_key,

a12.fiscal_week_id,

a13.catgy_manager_key
into temp ZZMD03 with no log

Messages In This Thread

• Fw: Optimizer
  Peter_Logan@spartanstores.com -- Wednesday, 28 March 2012, at 3:59 p.m.
  • Re: Fw: Optimizer
    Fernando Nunes -- Wednesday, 28 March 2012, at 5:11 p.m.
    • Re: Fw: Optimizer
      Peter_Logan@spartanstores.com -- Thursday, 29 March 2012, at 8:27 a.m.
      • Re: Fw: Optimizer
        Fernando Nunes -- Thursday, 29 March 2012, at 9:51 a.m.
        • Re: Fw: Optimizer
          Peter_Logan@spartanstores.com -- Thursday, 29 March 2012, at 10:06 a.m.
          • Re: Fw: Optimizer
            Art Kagel -- Thursday, 29 March 2012, at 10:23 a.m.
            • Re: Fw: Optimizer
              Peter_Logan@spartanstores.com -- Thursday, 29 March 2012, at 10:43 a.m.
              • Re: Fw: Optimizer
                Art Kagel -- Thursday, 29 March 2012, at 10:53 a.m.
                • Re: Fw: Optimizer
                  Peter_Logan@spartanstores.com -- Thursday, 29 March 2012, at 10:57 a.m.
                  • Re: Fw: Optimizer
                    Art Kagel -- Thursday, 29 March 2012, at 11:43 a.m.
                    • Re: Fw: Optimizer
                      Peter_Logan@spartanstores.com -- Thursday, 29 March 2012, at 11:50 a.m.
                      • Re: Fw: Optimizer
                        Art Kagel -- Thursday, 29 March 2012, at 12:42 p.m.
                        • Re: Fw: Optimizer
                          Peter_Logan@spartanstores.com -- Thursday, 29 March 2012, at 1:24 p.m.
                          • Re: Fw: Optimizer
                            Art Kagel -- Thursday, 29 March 2012, at 1:59 p.m.
                            • Re: Fw: Optimizer
                              Peter_Logan@spartanstores.com -- Thursday, 29 March 2012, at 2:04 p.m.
                      • Re: Fw: Optimizer
                        Fernando Nunes -- Thursday, 29 March 2012, at 1:52 p.m.
          • Re: Fw: Optimizer
            Fernando Nunes -- Thursday, 29 March 2012, at 1:49 p.m.
            • Re: Fw: Optimizer
              Peter_Logan@spartanstores.com -- Thursday, 29 March 2012, at 2:02 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.