Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
1 reply Latest Post - ‏2012-08-30T15:58:25Z by MatthiasNicola
Sigmazen
Sigmazen
19 Posts
ACCEPTED ANSWER

Pinned topic query performance

‏2012-08-30T11:33:09Z |
Hi there
I'd like to know if there are any performance differences between the following XMLTABLE options.
My real query requires the use of a UNION ALL due to grouping reasons, and would have around 30 xmltable columns.


SELECT 1,2 FROM table_A as A , XMLTABLE(
'$xml/Test' passing A.COL_XML as 
"xml" COLUMNS 1 INTEGER PATH 
'val1' , 2 INTEGER PATH 
'val2' , 3 INTEGER PATH 
'val3' , 4 INTEGER PATH 
'val4') UNION ALL SELECT 3,4 FROM table_A as A , XMLTABLE(
'$xml/Test' passing A.COL_XML as 
"xml" COLUMNS 1 INTEGER PATH 
'val1' , 2 INTEGER PATH 
'val2' , 3 INTEGER PATH 
'val3' , 4 INTEGER PATH 
'val4')


or


SELECT 1,2 FROM table_A as A , XMLTABLE(
'$xml/Test' passing A.COL_XML as 
"xml" COLUMNS 1 INTEGER PATH 
'val1' , 2 INTEGER PATH 
'val2') UNION ALL SELECT 3,4 FROM table_A as A , XMLTABLE(
'$xml/Test' passing A.COL_XML as 
"xml" COLUMNS 3 INTEGER PATH 
'val3' , 4 INTEGER PATH 
'val4')

I would think that the first option would have parsed the document once for the first query and reused the resultant xmltable for the second query.
Whereas the second option would have to parse the document twice to create two xmltables which would take a little longer?

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

    Re: query performance

    ‏2012-08-30T15:58:25Z  in response to Sigmazen
    Hi Simon,

    for questions like this it's a good idea to look at and compare the execution plans of the queries. Additionally, running some measurements is also a good idea.

    I think your second query is preferable over the first one. In the first query, I don't think that DB2 will reuse the result from the first XMLTABLE function to avoid evaluating the second XMLTABLE function. I think the two XMLTABLE functions are simply doing redundant work, which should be avoided.

    If you reeeally cannot avoid the UNION (which is not clear to me), go with the 2nd option.

    Matthias


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