Saiku Execution Speed Optimization Two

The previous article described adding a filter to speed up queries. Continue the analysis below:

The following MDX statement:

WithSet [~FILTER] as {[create_date].[ Create_date]. [ -- on- on]}set [~Rows_dimpartner_dimpartner] as {[dimpartner].[ Dimpartner]. [Name]. Members}set [~Rows_sight_sight] As Hierarchize ({{[sight].[ Sight]. [Country]. Members}, {[sight].[ Sight]. [Area]. Members}}) Selectnon EMPTY {[measures].[ Money], [Measures]. [Quantity], [Measures]. [Qunar_income], [Measures]. [Order_num]} On Columns,non EMPTY Order (NonEmptyCrossjoin ([~rows_dimpartner_dimpartner], [~Rows_sight_sight]), [Measures]. [Money], Bdesc) on Rowsfrom [Com_order_detail_cube]where [~FILTER] .-Geneva- the  -: About: to,176DEBUG [Org.saiku.service.olap.ThinQueryService] Query End .-Geneva- the  -: About: to,177INFO [Org.saiku.service.olap.ThinQueryService] run#:94Size:7/8Execute:444501ms format:1ms totals:0ms total:444502ms

Look at the data query statement, this should be very quick, execute a SQL can

Select"Com_order_detail_view". " Create_date " as"C0", "Dim_partner". " Name as"C1", "Com_order_detail_view". " Area as"C2",sum("Com_order_detail_view". " Money") as"M0",sum("Com_order_detail_view". " Quantity ") as"M1",sum("Com_order_detail_view". " Qunar_income ") as"M2",Count(distinct"Com_order_detail_view". " display_id ") as"M3" from"Com_order_detail_view" as"Com_order_detail_view", "Dim_partner" as"Dim_partner"where"Com_order_detail_view". " Create_date "=DATE'2013-01-01'  and"Com_order_detail_view". " Partner="Dim_partner". " CodeGroup  by"Com_order_detail_view". " Create_date "," Dim_partner "." Name "," Com_order_detail_view "." Area ";

The result is that this statement needs to be executed for the longest time before this is done:

Select Count (distinct from "Com_order_detail_view"

The reason for this should be that saiku the execution of the results of the format, so the execution of this statement, and this field should be a dimension table, so guess the use of dimension table or can improve efficiency. It was tested and found to be true, so use the latitude table as much as possible to optimize the use of data.

Saiku Execution Speed Optimization Two