Re: Performance on 7.30

herbert.yates@cibavision.Novartis.com wrote: > > Please give me your input on this problem. > > Problem: > I have an sql that runs in 5.34 minutes on an AIX and 18.30 on an HP. > This is with no user PDQPRIORITY set. If I set PDQPRIORITY=2 (or > greater), the HP is faster, 1.07 vs 3.03. I expected the HP to be faster > in both cases. I can think of three things to look at: 1) Are the disk farms comparable on both machines? 2) The value of OPTCOMPIND the same on both? 3) The difference in the reporting of the HASH JOINs. That '(Build Outer)' clause looks like a new 7.30 feature. You should talk to tech support about that and find out a) what is the diff from the 7.24 report and b) can you disable it somehow. 4) What do you mean by stating that you have updated statistics? To what level? What sequence of commands did you use? Can we see the schema for this table? Is there an index on act_cd or/and sls_rep_id? What is the fragmentation schema/expression? > o Sqexplain.out for AIX: [SNIP] > DYNAMIC HASH JOIN > Dynamic Hash Filters: yateshe1.c.cust_id = yateshe1.a.cust_id > > o Sqexplain.out for HP: [SNIP] > Filters: (root.c.sls_rep_id < 140000 AND root.c.act_cd = 'A' ) > DYNAMIC HASH JOIN (Build Outer) Art S. Kagel