Hi,
I had run the optimization test and am attaching the report with 10.3 beta.
Looks like 10.3 performs better than 10.2 in query optimization. Please have
a look
--
Thanks,
Manjula.
Timings for Query type: Select from single view
QueryName Using PreparedStatment Using Statement Using PreparedStatment
Using Statement Rows Expected
(Version 10.3) (Version 10.3) (Version 10.2)
(Version 10.2)
---------------------------------------------------------------------------------------------------------------
Query size: 4
QUERY # 1 0.016 secs 0.166 secs 0.167 secs
0.515 secs 3
QUERY # 1 0.016 secs 0.017 secs 0.148 secs
0.174 secs 3
*****************************************************************************************************************
QUERY # 2 0.032 secs 0.270 secs 0.304 secs
0.763 secs 6
QUERY # 2 0.057 secs 0.020 secs 0.316 secs
0.358 secs 6
*****************************************************************************************************************
QUERY # 3 0.062 secs 0.310 secs 0.711 secs
1.326 secs 10
QUERY # 3 0.060 secs 0.042 secs 0.641 secs
0.835 secs 10
*****************************************************************************************************************
QUERY # 4 0.080 secs 0.409 secs 0.862 secs
1.524 secs 10
QUERY # 4 0.080 secs 0.090 secs 0.859 secs
0.951 secs 10
*****************************************************************************************************************
_________________________________________________________________________________________________________________
_________________________________________________________________________________________________________________
Timings for Query type: Select from multiple views using joins
QueryName Using PreparedStatment Using Statement Using PreparedStatment
Using Statement Rows Expected
(Version 10.3) (Version 10.3) (Version 10.2)
(Version 10.2)
--------------------------------------------------------------------------------------------------------------
Query size: 4
QUERY # 1 0.011 secs 0.356 secs 0.179 secs
0.383 secs 3
QUERY # 1 0.010 secs 0.020 secs 0.139 secs
0.160 secs 3
***************************************************************************************************************
QUERY # 2 0.063 secs 0.329 secs 0.368 secs
0.653 secs 6
QUERY # 2 0.025 secs 0.113 secs 0.297 secs
0.298 secs 6
***************************************************************************************************************
QUERY # 3 0.055 secs 0.735 secs 0.684 secs
1.522 secs 10
QUERY # 3 0.054 secs 0.092 secs 0.662 secs
0.750 secs 10
***************************************************************************************************************
QUERY # 4 0.072 secs 1.243 secs 0.910 secs
2.314 secs 10
QUERY # 4 0.104 secs 0.098 secs 0.915 secs
0.938 secs 10
***************************************************************************************************************
________________________________________________________________________________________________________________
________________________________________________________________________________________________________________
Timings for Query type: Select from multiple views with joins on columns having
indexes
QueryName Using PreparedStatment Using Statement Using PreparedStatment
Using Statement Rows Expected
(Version 10.3) (Version 10.3) (Version 10.2)
(Version 10.2)
----------------------------------------------------------------------------------------------------------------
Query size: 4
QUERY # 1 0.051 secs 0.317 secs 0.054 secs
0.223 secs 144
QUERY # 1 0.050 secs 0.058 secs 0.053 secs
0.057 secs 144
****************************************************************************************************************
QUERY # 2 0.219 secs 0.714 secs 0.240 secs
0.647 secs 288
QUERY # 2 0.189 secs 0.273 secs 0.230 secs
0.233 secs 288
****************************************************************************************************************
QUERY # 3 0.819 secs 2.216 secs 0.870 secs
2.134 secs 576
QUERY # 3 0.805 secs 0.853 secs 0.902 secs
0.908 secs 576
****************************************************************************************************************
QUERY # 4 1.422 secs 3.427 secs 1.560 secs
3.673 secs 756
QUERY # 4 1.373 secs 1.454 secs 1.524 secs
1.560 secs 756
****************************************************************************************************************
________________________________________________________________________________________________________________
________________________________________________________________________________________________________________
Timings for Query type: Select from multiple views with joins on columns having
no indexes
QueryName Using PreparedStatment Using Statement Using PreparedStatment
Using Statement Rows Expected
(Version 10.3) (Version 10.3) (Version 10.2)
(Version 10.2)
----------------------------------------------------------------------------------------------------------------
Query size: 4
QUERY # 1 3.561 secs 4.203 secs 3.124 secs
3.286 secs 144
QUERY # 1 3.662 secs 3.960 secs 3.136 secs
3.321 secs 144
****************************************************************************************************************
QUERY # 2 24.480 secs 25.472 secs 24.356 secs
26.162 secs 288
QUERY # 2 24.371 secs 25.264 secs 24.366 secs
25.377 secs 288
****************************************************************************************************************
QUERY # 3 1 min 39.031 secs 1 min 39.143 secs 1 min 38.197 secs
1 min 39.177 secs 576
QUERY # 3 1 min 38.742 secs 1 min 36.877 secs 1 min 38.625 secs
1 min 37.386 secs 576
******************************************************************************************************************
QUERY # 4 2 min 50.530 secs 2 min 51.701 secs 2 min 50.268 secs
2 min 50.618 secs 756
QUERY # 4 2 min 50.539 secs 2 min 50.588 secs 2 min 49.502 secs
2 min 49.814 secs 756
*******************************************************************************************************************
___________________________________________________________________________________________________________________
___________________________________________________________________________________________________________________
Timings for Query type: Select from multiple views with joins on columns, one
with index and one without index
QueryName Using PreparedStatment Using Statement Using PreparedStatment
Using Statement Rows Expected
(Version 10.3) (Version 10.3) (Version 10.2)
(Version 10.2)
-------------------------------------------------------------------------------------------------------------------
Query size: 4
QUERY # 1 1.884 secs 1.998 secs 1.628 secs
1.720 secs 144
QUERY # 1 1.899 secs 1.839 secs 1.601 secs
1.583 secs 144
********************************************************************************************************************
QUERY # 2 12.325 secs 11.147 secs 11.417 secs
11.039 secs 288
QUERY # 2 12.602 secs 12.342 secs 12.185 secs
12.450 secs 288
********************************************************************************************************************
QUERY # 3 51.049 secs 51.452 secs 49.354 secs
51.912 secs 576
QUERY # 3 51.069 secs 50.513 secs 49.577 secs
50.445 secs 576
*********************************************************************************************************************
QUERY # 4 1 min 28.063 secs 1 min 29.557 secs 1 min
25.513 secs 1 min 27.019 secs 756
QUERY # 4 1 min 28.475 secs 1 min 28.051 secs 1 min
25.488 secs 1 min 25.237 secs 756
*********************************************************************************************************************
______________________________________________________________________________________________________________________
______________________________________________________________________________________________________________________
Timings for Query type: Select from multiple views with combination of nested
views and aggregate views
QueryName Using PreparedStatment Using Statement Using PreparedStatment
Using Statement Rows Expected
(Version 10.3) (Version 10.3) (Version 10.2)
(Version 10.2)
----------------------------------------------------------------------------------------------------------------------
Query size: 24
QUERY # 1 0.734 secs 0.984 secs 0.809 secs
1.114 secs 56
QUERY # 1 0.715 secs 0.814 secs 0.845 secs
0.841 secs 56
***********************************************************************************************************************
QUERY # 2 0.748 secs 0.896 secs 0.824 secs
1.016 secs 56
QUERY # 2 0.715 secs 0.836 secs 0.858 secs
0.834 secs 56
***********************************************************************************************************************
QUERY # 3 0.716 secs 0.890 secs 0.835 secs
1.006 secs 56
QUERY # 3 0.711 secs 0.840 secs 0.860 secs
0.844 secs 56
***********************************************************************************************************************
QUERY # 4 0.711 secs 0.768 secs 0.808 secs
0.840 secs 56
QUERY # 4 0.709 secs 0.779 secs 0.850 secs
0.847 secs 56
***********************************************************************************************************************
QUERY # 5 0.711 secs 0.874 secs 0.819 secs
0.932 secs 56
QUERY # 5 0.708 secs 0.825 secs 0.854 secs
0.842 secs 56
***********************************************************************************************************************
QUERY # 6 0.712 secs 0.892 secs 0.805 secs
0.976 secs 56
QUERY # 6 0.711 secs 0.825 secs 0.843 secs
0.837 secs 56
***********************************************************************************************************************
QUERY # 7 1.353 secs 1.590 secs 1.519 secs
1.705 secs 112
QUERY # 7 1.323 secs 1.471 secs 1.508 secs
1.640 secs 112
***********************************************************************************************************************
QUERY # 8 0.736 secs 0.891 secs 0.841 secs
0.950 secs 56
QUERY # 8 0.770 secs 0.844 secs 0.810 secs
0.883 secs 56
***********************************************************************************************************************
QUERY # 9 1.316 secs 1.541 secs 1.486 secs
1.617 secs 112
QUERY # 9 1.303 secs 1.421 secs 1.488 secs
1.472 secs 112
***********************************************************************************************************************
QUERY # 10 1.254 secs 1.504 secs 1.458 secs
1.601 secs 112
QUERY # 10 1.242 secs 1.388 secs 1.462 secs
1.514 secs 112
***********************************************************************************************************************
QUERY # 11 1.281 secs 1.531 secs 1.494 secs
1.657 secs 504
QUERY # 11 1.284 secs 1.401 secs 1.491 secs
1.523 secs 504
***********************************************************************************************************************
QUERY # 12 10.814 secs 11.789 secs 12.183 secs
12.721 secs 512
QUERY # 12 10.588 secs 11.421 secs 11.911 secs
12.121 secs 512
***********************************************************************************************************************
QUERY # 13 5.862 secs 6.724 secs 6.754 secs
6.987 secs 1
QUERY # 13 5.900 secs 6.505 secs 6.710 secs
6.668 secs 1
***********************************************************************************************************************
QUERY # 14 0.137 secs 0.363 secs 0.155 secs
0.344 secs 0
QUERY # 14 0.171 secs 0.177 secs 0.156 secs
0.179 secs 0
***********************************************************************************************************************
QUERY # 15 10.373 secs 11.720 secs 11.889 secs
12.347 secs 512
QUERY # 15 10.460 secs 11.379 secs 11.925 secs
11.873 secs 512
***********************************************************************************************************************
QUERY # 16 2.244 secs 2.493 secs 2.434 secs
2.456 secs 112000
QUERY # 16 2.339 secs 2.305 secs 2.389 secs
2.284 secs 112000
***********************************************************************************************************************
QUERY # 17 0.143 secs 0.311 secs 0.197 secs
0.374 secs 0
QUERY # 17 0.142 secs 0.213 secs 0.161 secs
0.180 secs 0
***********************************************************************************************************************
QUERY # 18 1.138 secs 1.336 secs 1.223 secs
1.342 secs 8
QUERY # 18 1.211 secs 1.093 secs 1.274 secs
1.316 secs 8
***********************************************************************************************************************
QUERY # 19 0.238 secs 0.344 secs 0.289 secs
0.352 secs 1000
QUERY # 19 0.278 secs 0.234 secs 0.258 secs
0.244 secs 1000
***********************************************************************************************************************
QUERY # 20 0.312 secs 0.409 secs 0.321 secs
0.392 secs 999
QUERY # 20 0.290 secs 0.271 secs 0.321 secs
0.347 secs 999
***********************************************************************************************************************
QUERY # 21 0.617 secs 0.733 secs 0.608 secs
0.754 secs 1998
QUERY # 21 0.583 secs 0.613 secs 0.606 secs
0.651 secs 1998
***********************************************************************************************************************
QUERY # 22 0.852 secs 1.128 secs 0.876 secs
1.223 secs 2097
QUERY # 22 0.848 secs 0.822 secs 0.876 secs
0.867 secs 2097
***********************************************************************************************************************
QUERY # 23 1.114 secs 1.427 secs 1.159 secs
1.530 secs 2196
QUERY # 23 1.114 secs 1.115 secs 1.124 secs
1.158 secs 2196
***********************************************************************************************************************
QUERY # 24 0.759 secs 0.963 secs 0.792 secs
1.034 secs 1098
QUERY # 24 0.723 secs 0.746 secs 0.768 secs
0.829 secs 1098
***********************************************************************************************************************
_______________________________________________________________________________________________________________________
