KB3859: How to use the sub query VLDB setting to optimize performance for attribute relationship filtering

• Strategy

When creating attribute relationship filters, the default Very Large Database (VLDB) property for sub query types causes EXISTS statements to appear in the sub queries. This syntax can be costly for most Relational Database Management System (RDBMS) platforms when the fact tables involved are large in size. There is a simple optimization that can be performed, which is to modify this VLDB setting so that temp tables are applied for this type of analysis. The following example from Strategy Tutorial serves to illustrate this point.

Users can define an attribute relationship filter to display those customers who have purchased Godzilla and not To Engineer Is Human:

Users must place the attribute 'Customer' on the template. The resulting SQL, by default, appears as follows:


select a11.CUSTOMER_ID  CUSTOMER_ID,
 max(a12.CUST_LAST_NAME)  CUST_LAST_NAME,
 max(a12.CUST_FIRST_NAME)  CUST_FIRST_NAME,
 sum((a11.QTY_SOLD * (a11.UNIT_PRICE - a11.DISCOUNT)))  WJXBFS1
from ORDER_DETAIL a11
 join LU_CUSTOMER a12
   on  (a11.CUSTOMER_ID = a12.CUSTOMER_ID)
where ((not ((a11.CUSTOMER_ID)
 in (select r11.CUSTOMER_ID
 from ORDER_DETAIL r11
 where r11.ITEM_ID in (74))))
 and ((a11.CUSTOMER_ID)
 in (select r11.CUSTOMER_ID
 from ORDER_DETAIL r11
 where r11.ITEM_ID in (184)))
 and a11.ITEM_ID in (184))
group by a11.CUSTOMER_ID

The first part of the WHERE clause applies '((not ((a11.CUSTOMER_ID) in (select...))…' to select those customers who have not purchased To Engineer Is Human. The second part of the SQL joins the first results set with the customers who have purchased Godzilla.

This SQL functions well because, in this case, the fact table (ORDER_DETAIL) is not very big. However, users can optimize this SQL for large data warehouses by changing the Sub Query Type VLDB property. The property may be set project wide in the database instance, or on a report-by-report basis.

Enter the VLDB property editor for the report, template or database instance.
Once inside the VLDB Properties editor for the warehouse instance, go to 'Query Optimizations' and 'Sub Query Type.'

Click on the 'Use default inherited value' checkbox and select the fifth radio button, 'Use Temporary Table...' The seventh radio button and is also valid for databases that support in (select...) syntax.

Click on 'Save and Close' and 'OK.'

The new SQL for this query is as follows:


select distinct r11.CUSTOMER_ID  CUSTOMER_ID
into #ZZRF00
from ORDER_DETAIL r11
where r11.ITEM_ID in (184) 

select distinct r11.CUSTOMER_ID  CUSTOMER_ID
into #ZZRF01
from ORDER_DETAIL r11
where r11.ITEM_ID in (74) 

select a13.QUARTER_ID  QUARTER_ID,
 max(a15.QUARTER_DESC)  QUARTER_DESC,
 a11.CUSTOMER_ID  CUSTOMER_ID,
 max(a14.CUST_LAST_NAME)  CUST_LAST_NAME,
 max(a14.CUST_FIRST_NAME)  CUST_FIRST_NAME,
 sum((a11.QTY_SOLD * (a11.UNIT_PRICE - a11.DISCOUNT)))  WJXBFS1
from ORDER_DETAIL a11
 join #ZZRF00 pa12
   on  (a11.CUSTOMER_ID = pa12.CUSTOMER_ID)
 join LU_DAY a13
   on  (a11.ORDER_DATE = a13.DAY_DATE)
 join LU_CUSTOMER a14
   on  (a11.CUSTOMER_ID = a14.CUSTOMER_ID)
 join LU_QUARTER a15
   on  (a13.QUARTER_ID = a15.QUARTER_ID)
where ((not (exists (select *
 from #ZZRF01 ps21
 where ps21.CUSTOMER_ID = a11.CUSTOMER_ID)))
 and a11.ITEM_ID in (184))
group by a13.QUARTER_ID,
 a11.CUSTOMER_ID


drop table #ZZRF00

drop table #ZZRF01

This SQL is different from the first SQL sample because now users create two intermediate tables (ZZRF00 and ZZRF01, in this case). The first sample contains all of the customers who bought Godzilla and the second contains all of the customers whobought To Engineer Is Human. The final pass of SQL then joins both table and uses '(not (exists' to exclude those clients who have To Engineer Is Human. However, this EXISTS clause is executed against a reduced data set, so the performance is greatly improved when compared to the first query.

Comment

0 comments

Details

Knowledge Article

Published:

May 5, 2017

Last Updated:

May 5, 2017

select a11.CUSTOMER_ID CUSTOMER_ID, max(a12.CUST_LAST_NAME) CUST_LAST_NAME, max(a12.CUST_FIRST_NAME) CUST_FIRST_NAME, sum((a11.QTY_SOLD * (a11.UNIT_PRICE - a11.DISCOUNT))) WJXBFS1 from ORDER_DETAIL a11 join LU_CUSTOMER a12 on (a11.CUSTOMER_ID = a12.CUSTOMER_ID) where ((not ((a11.CUSTOMER_ID) in (select r11.CUSTOMER_ID from ORDER_DETAIL r11 where r11.ITEM_ID in (74)))) and ((a11.CUSTOMER_ID) in (select r11.CUSTOMER_ID from ORDER_DETAIL r11 where r11.ITEM_ID in (184))) and a11.ITEM_ID in (184)) group by a11.CUSTOMER_ID

select distinct r11.CUSTOMER_ID CUSTOMER_ID into #ZZRF00 from ORDER_DETAIL r11 where r11.ITEM_ID in (184) select distinct r11.CUSTOMER_ID CUSTOMER_ID into #ZZRF01 from ORDER_DETAIL r11 where r11.ITEM_ID in (74) select a13.QUARTER_ID QUARTER_ID, max(a15.QUARTER_DESC) QUARTER_DESC, a11.CUSTOMER_ID CUSTOMER_ID, max(a14.CUST_LAST_NAME) CUST_LAST_NAME, max(a14.CUST_FIRST_NAME) CUST_FIRST_NAME, sum((a11.QTY_SOLD * (a11.UNIT_PRICE - a11.DISCOUNT))) WJXBFS1 from ORDER_DETAIL a11 join #ZZRF00 pa12 on (a11.CUSTOMER_ID = pa12.CUSTOMER_ID) join LU_DAY a13 on (a11.ORDER_DATE = a13.DAY_DATE) join LU_CUSTOMER a14 on (a11.CUSTOMER_ID = a14.CUSTOMER_ID) join LU_QUARTER a15 on (a13.QUARTER_ID = a15.QUARTER_ID) where ((not (exists (select * from #ZZRF01 ps21 where ps21.CUSTOMER_ID = a11.CUSTOMER_ID))) and a11.ITEM_ID in (184)) group by a13.QUARTER_ID, a11.CUSTOMER_ID drop table #ZZRF00 drop table #ZZRF01