How to tune this view for fast query ?

[former_member1108223]

Hi,

In my data warehouse project, I have a CUS_MSTR table which has the following columns: CUS_CIF_NBR, CUS_TYP, CUS_SUB_TYP, CUS_FULL_NM, CUS_TAX_CD... I am using Information Steward to validate this table. However the data in CUS_FULL_NM and CUS_TAX_CD need to be corrected before validation by Information Steward. So I create two database functions:

     valid_cus_full_name(varchar(100)) return varchar(100)

     valid_cus_tax_code(varchar(20)) return varchar(20)

These functions remove some special characters from CUS_FULL_NM and CUS_TAX_CD columns.

Then I create a database view:

create view VIW_CBSCIF18

as

select

     CUS_CIF_NBR,

     CUS_TYP,

     CUS_SUB_TYP,

     CUS_FULL_NM,

     CUS_TAX_CD,

     valid_cus_full_name(CUS_FULL_NM) VLD_CUS_FULL_NM,

     valid_cus_tax_code(CUS_TAX_CD) VLD_CUS_TAX_CD

from CUS_MSTR

And I create a Information Steward rule, which validate data of this view. The Information Steward rule task running speed is OK. But my client ask me to perform unit test by running the following query:

select VLD_CUS_FULL_NM, VLD_CUS_TAX_CD, count(*)

from VIW_CBSCIF18

where CUS_TYP=1 and CUS_SUB_TYP in ('11', '12')

group by VLD_CUS_FULL_NM, VLD_CUS_TAX_CD

having count(*) > 1

The query speed is very bad. The CUS_MSTR table has about 4 millions rows.

I also realize that IQ16.0 does not support materialized view based on IQ table.

So my question is: How do I tune the VIW_CBSCIF18 view and the CUS_MSTR table to improve query speed for the unit test ?

Thanks.

[markmumy]

The issue is that you've got SQL functions in the code. SQL functions are handled by the SA engine and are not the fastest. Likely every row has to go back and forth across the internal SA and IQ bridge resulting in high run times. Unfortunately, there's not much that can be done to tune , per se. The best tuning would be to eliminate the sql function and put that logic in the view.

Mark