Topic
1 reply Latest Post - ‏2012-08-30T16:13:25Z by MatthiasNicola
Sigmazen
Sigmazen
19 Posts
ACCEPTED ANSWER

Pinned topic path performance

‏2012-08-30T11:47:41Z |
Hi
Assume the following document:


<Rule> <Targeting><Profile> <attr1><min>650</min><max>750</max></attr1> <attr2><min>650</min><max>750</max></attr2> <attr3><min>650</min><max>750</max></attr3> <attr4><min>650</min><max>750</max></attr4> <attr5><min>650</min><max>750</max></attr5> <attr6><min>650</min><max>750</max></attr6> <attr7><min>650</min><max>750</max></attr7> </Profile></Targeting> <Capping> <MaxCt>999</MaxCt> </Capping> </Rule>


Which option (or other) is going to be most efficient:

SELECT T2.* FROM MY_XML  AS E , XMLTABLE(
'$xml/Rule' passing E.COL_XML as 
"xml" COLUMNS ATTR1_MIN  INTEGER  PATH 
'Targeting/Profile/attr1/min' , ATTR1_MAX  INTEGER  PATH 
'Targeting/Profile/attr1/max' . . . , ATTR7_MIN  INTEGER  PATH 
'Targeting/Profile/attr7/min' , ATTR7_MAX  INTEGER  PATH 
'Targeting/Profile/attr7/max' , EXT_CAP_CT INTEGER  PATH 
'Capping/MaxCt' ) AS T2

or

SELECT T2.* FROM MY_XML  AS E , XMLTABLE(
'$xml/Rule/Targeting/Profile' passing E.COL_XML as 
"xml" COLUMNS ATTR1_MIN  INTEGER  PATH 
'attr1/min' , ATTR1_MAX  INTEGER  PATH 
'attr1/max' . . . , ATTR7_MIN  INTEGER  PATH 
'attr7/min' , ATTR7_MAX  INTEGER  PATH 
'attr7/max' , EXT_CAP_CT INTEGER  PATH 
'../../Capping/MaxCt' ) AS T2

or

SELECT T2.*, T3.* FROM MY_XML  AS E , XMLTABLE(
'$xml/Rule/Targeting/Profile' passing E.COL_XML as 
"xml" COLUMNS ATTR1_MIN  INTEGER  PATH 
'attr1/min' , ATTR1_MAX  INTEGER  PATH 
'attr1/max' . . . , ATTR7_MIN  INTEGER  PATH 
'attr7/min' , ATTR7_MAX  INTEGER  PATH 
'attr7/max' ) AS T2 , XMLTABLE(
'$xml/Rule/Capping' passing E.COL_XML as 
"xml" COLUMNS EXT_CAP_CT INTEGER  PATH 
'MaxCt' ) AS T3

Thanks in advance
Cheers
Simon
Updated on 2012-08-30T16:13:25Z at 2012-08-30T16:13:25Z by MatthiasNicola
  • MatthiasNicola
    MatthiasNicola
    321 Posts
    ACCEPTED ANSWER

    Re: path performance

    ‏2012-08-30T16:13:25Z  in response to Sigmazen
    Hi Simon,

    great question.

    I think the performance difference between these three queries should be very small. I think it is typically best to make the row-generating expression in the XMLTABLE function as long and specific as possible. Therefore I like your 2nd and 3rd option better than the 1st.

    I'm not sure that using 2 XMLTABLE functions (as in the 3rd option) to avoid the parent steps for Capping/MaxCt will make a huge performance difference. I would do that only if there are many columns for which the parent steps can be avoided.

    My suggestion: create a table with single sample document like the one that you posted below. Then use the tool db2batch to execute each of the three queries 10,000 or 100,000 times. In the SQL input file to db2batch, declare each query as a separate block with the desired repeat count:

    
    --#BGBLK 10000 SELECT...   --#EOBLK
    

    The measurement results produced by db2batch will give you the most precise answer to your question. Let us know what you find!

    Thanks,

    Matthias


    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/