Question about split group by in IQ16 SP08.

[Gisung]

Dear all,

I have a customer who encountered performance of "union all view".
As per our analysis, IQ optimizer does't split the Group By.

[Query]
select "EDPS_CSN","count"()
  from "adwown"."vw_adw_dpy111n_01"
  group by "edps_csn"

As far as I know, There are some restrictions on the situations and queries that benefit from the split GROUP BY.
But this view meets all restrictions.

Please refer to the below URL.

    [Impact on query performance of GROUP BY over a UNION ALL]
      - http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00169.1520/html/iqperf/iq...
 
  So I would like to know the following questions.
  1) How to enforce the split the group by.
  2) Any changes about split group by in IQ16 SP08?
 
  Any comments or advice will be greatly apprecaited.

Thanks,

Gi-Sung Jang

[Former Member]

Hi

Could you post a detailed query plan of your query (preferably HTML)?

Did you try to perform a "manual split group-by" by running queries directly from all tables participating in the view? Did you see a performance benefit in this case?

How many tables participate in the view? Does the optimizer choose the same query plan when you decrease the number of tables in the view?

[Gisung]

Hi

Thanks for your reply.

There are three tables in this view.

** Base Table Rows

    1) ADWOWN.TB_ADW_DPY119N : 23,259

    2) ADWOWN.TB_ADW_DPY111N : 398,017,348

    3) ADWOWN.TB_ADW_DPY117N : 16,160,487

I attached the query plan and DDL and checkoptions.

the html is not allowed, so I added the txt.

so please remove the "txt" extension.

Thanks

Gi-Sung Jang

sp_iqcheckoptions.txt.zip

union_all_view_performance_issue.html.txt.zip

DDL.sql.txt.zip

[Former Member]

Hi

I tried to reproduce the problem with 3 tables with EDPS_CSN column only - without success. In my case, GROUP BY is pushed down to branches of the UNION ALL view. I can provide more details about my test case if required.

I see some strange things in your query plan. While the query returns 10706 rows, we can see in Leaf #02 in the query plan that the number of distinct values is 14740483. Leaf #3 also shows quite a big number of distinct values. Do you restrict the number of returned rows from the client side?

I see that you have some internal and hardly documented options enabled (DML_Options, Core_Options). While these options may solve some specific problem, it may also change the optimizer behavior in other aspects. I would suggest testing the query without these options, probably on a separate test environment, and see what happens. Also, verify that you receive "Grouped Leaf" in your query plan when running the same query directly from tables participating in the view (from a single table).

I'm going to upgrade one of my customer's databases to IQ 16 SP08 in a number of weeks. If you have faced with other problems specific to this IQ version - please share if possible.

Hope it helps

Leonid Gvirtz

[Gisung]

Hi

Thanks for your help very much.

We have a plan to test without any core/dml options.

I will let you know the result about this.

Thanks

Gi-Sung Jang