DB2 Cube Views: A Primer

© Copyright IBM Corp. 2003. All rights reserved. 661

Appendix B. Hybrid Analysis query

performance results

Table B-1 shows the query results of each of the individual queries that were

generated as a result of the DB2 OLAP Server Hybrid Analysis workloads. A

single query in a Hybrid Analysis environment will cause two or more SQL

queries to be generated. It is interesting to see the number of queries generated

for each of the Hybrid Analysis queries and to see where the MQT comes into

play.

Table B-1 lists all of the queries without the use of an MQT and when there was a

drill through query type MQT available.

Table B-1 Detailed query performance results

B

Query workload Query ID Elapsed time

(without MQT)

Elapsed time

(with MQT)

Re-routed

to MQT?

H1 Query 1 35801 0.242 0.322 N

35802 16.699 2.344 Y

H1 Query 2 35803 0.128 0.144 N

35804 0.137 0.139 N

35805 0.137 0.131 N

662 DB2 Cube Views: A Primer

35806 0.139 0.166 N

35807 0.140 0.133 N

35808 0.133 0.151 N

35809 2.913 4.166 Y

H2 Query 1a 35821 0.116 0.13 N

35822 161.839 5.139 Y

H2 Query 1b 35823 0.131 0.12 N

35824 11.811 0.365 Y

35825 0.474 0.342 Y

H2_Query 2a 35826 0.127 0.138 N

35827 0.139 0.141 N

35828 13.971 1.36 Y

H2_Query 2b 35829 0.119 0.127 N

35830 0.147 0.131 N

35831 0.131 0.131 N

35832 0.124 0.133 N

35833 0.132 0.132 N

35834 0.126 0.128 N

35835 0.314 1.466 Y

H3_Query 1a 35836 0.107 0.13 N

35837 0.404 0.344 Y

H3_Query 1b 35838 0.125 0.116 N

35839 0.423 0.206 Y

35840 0.353 0.204 Y

H3_Query 2 35841 0.089 0.098 N

35842 0.114 0.107 N

Query workload Query ID Elapsed time

(without MQT)

Elapsed time

(with MQT)

Re-routed

to MQT?

Appendix B. Hybrid Analysis query performance results 663

35843 0.125 0.128 N

35844 0.094 0.119 N

35845 0.105 0.137 N

35846 0.090 0.106 N

35847 0.121 0.142 N

35848 0.081 0.109 N

35849 0.132 0.143 N

35850 0.098 0.101 N

35851 0.125 0.154 N

35852 0.095 0.114 N

35853 0.106 0.132 N

35854 0.097 0.125 N

35855 0.106 0.085 N

35856 0.091 0.095 N

35857 0.110 0.112 N

35858 0.313 1.442 Y

H4_Query 1a 35859 0.140 0.126 N

35860 161.016 1.017 Y

H4_Query 1b 35861 0.122 0.147 N

35862 11.23 0.342 Y

35863 0.418 0.378 Y

H4_Query 1c 35864 0.088 0.111 N

35865 0.369 0.251 Y

35866 0.358 0.238 Y

H4_Query 2a 35867 0.102 0.106 N

35868 0.096 0.109 N

Query workload Query ID Elapsed time

(without MQT)

Elapsed time

(with MQT)

Re-routed

to MQT?

664 DB2 Cube Views: A Primer

35869 0.149 0.161 N

35870 0.090 0.103 N

35871 0.141 0.156 N

35872 0.086 0.107 N

35873 0.098 0.124 N

35874 0.098 0.102 N

35875 0.116 0.095 N

35876 13.783 1.44 Y

H4_Query 2b 35877 0.086 0.098 N

35878 0.092 0.111 N

35879 0.137 0.154 N

35880 0.115 0.113 N

35881 0.135 0.156 N

35882 0.101 0.115 N

35883 0.138 0.15 N

35884 0.142 0.108 N

35885 0.152 0.13 N

35886 0.111 0.122 N

35887 0.156 0.131 N

35888 0.113 0.092 N

35889 0.143 0.141 N

35890 0.138 0.094 N

35891 0.116 0.096 N

35892 0.112 0.114 N

35893 0.103 0.108 N

35894 0.334 1.429 Y

Query workload Query ID Elapsed time

(without MQT)

Elapsed time

(with MQT)

Re-routed

to MQT?

Appendix B. Hybrid Analysis query performance results 665

H5_Query 1a 35895 0.150 0.138 N

35896 162.485 1.885 Y

H5_Query 1b 35897 0.143 0.134 N

35898 11.428 0.379 Y

35899 0.452 0.426 Y

H5_Query 1c 35900 0.116 0.107 N

35901 0.371 0.262 Y

35902 0.407 0.241 Y

H5_Query 1d 35903 0.112 0.114 N

35904 0.361 0.208 Y

35905 0.368 0.198 Y

H5_Query 2a 35906 0.116 0.125 N

35907 0.112 0.109 N

35908 0.164 0.185 N

35909 0.107 0.117 N

35910 0.171 0.17 N

35911 0.097 0.121 N

35912 0.148 0.15 N

35913 0.167 0.161 N

35914 0.120 0.111 N

35915 0.155 0.145 N

35916 0.114 0.117 N

35917 0.168 0.152 N

35918 0.120 0.112 N

35919 0.155 0.148 N

35920 0.115 0.126 N

Query workload Query ID Elapsed time

(without MQT)

Elapsed time

(with MQT)

Re-routed

to MQT?