Query Execution

[Former Member]

Hi,

I have several partitions of one table where data is loaded between different partitions based partition table limit.

My query is running on view which essentially does a sequential run across each partition of that view.

This is having an impact on performance.

Is there any way I can make my query run parallel?

Thanks.

[jong-kil_park]

Hi,

Please refer to a manual pages describing "Queries Referencing UNION ALL Views" when using UNION ALL views.

http://help.sap.com/saphelp_iq1610_iqperf/helpdata/en/a6/0f298e84f21015909a81591294e474/content.htm

It's very important to keep the constraints in the above page to get the optimal performance in querying against the view.

Also, take a look at the next item "5.12.2 UNION ALL View Performance" in that manual page.

Best Regards

Jerry

[Former Member]

Hi Jerry,

Thanks for the link.

Please check above SQL for my view creation.

Can it be optimized in any way?

[jong-kil_park]

Hi Vrushali,

I would like to add a couple of things in addition to the manual page.

1. Specify the column names directly in the select list instead of "*" in the view definition.

2. Also, don't use any expressions in the select list instead of the physical column name.

Best Regards

Jerry

[Former Member]

Thanks Jerry.

Will try that.

If I select only required columns in view creation.. does it mean that it may reduce number of rows fetched by aggregating them depending on my selection.

Also, as you mentioned about not using expressions. If i create a table with expressions and define that as a column in table. Will creating a view using that column have any impact on performance.

Thanks.

[jong-kil_park]

Hi Vrushali,

Yes, if you create a view with selective columns from the IQ base table, it must be positive for performance.

But, the number of rows being returned for queries against the view depends on the predicates specified.

And, as for the expressions I said, I meant this.

Create view sample_view

AS

Select c1, c2, c3 from t1

union all

Select c1, c2, min(c3, 0) from t2

union all

Select c1, c2, c2*0.3 from t3

;

The view like above style having those expressions prevents IQ optimizer from processing the query with the optimal performance. To my knowledge, it's impossible for the IQ optimizer to execute it with a split group by.

So, it would be better to avoid the view definition like above.

Best Regards

Jerry

[Former Member]

Thanks Jerry.

So does creating a table (instead of views) with expressions help?

[Former Member]

Appreciate any reply on this. Thanks

[markmumy]

Are you using a UNION?  UNION ALL?  Can you post some sample code?

Mark

[Former Member]

Hi Mark,

My query is as follows -

alter view <viewname> as

select * from <table_partition_1>

union all

select * from <table_partition_2>

union all

select * from <table_partition_3>

union all

select * from <table_partition_4>

and so on depending on the number of partitions for the table.