DB2 - Problembeschreibung
Problem IC89702 | Status: Geschlossen |
DB2 XQUERY SHOWS POOR PERFORMANCE DUE TO EXPENSIVE USE OF LARGE XML SEQUENCES. | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / A10 - DB2 | |
Problembeschreibung: | |
Using large global sequences in XML queries can result in suboptimal performance due to intermediate result processing required to manage these large sequences during query processing. Queries susceptible to this type of performance typically start with a "let" clause and assign the sequence of all XML documents in a given table to some variable. This variable which is bound to the large sequence is then used to extract sequence item values, perform some kind of document construction or used to do predicate evaluation which applies to every item in the sequence. For any of these operations which perform some type of item extraction or evaluation on sequence items there is an underlying amount of intermediate processing required to process the entire xml sequence object. The larger the sequence, the more work required. The fix for this APAR helps improve performance for processing intermediate results from XML sequences. Example query which results in sub-optimal performance: xquery let $allitems := ( for $i in db2-fn:xmlcolumn("PURCHASEORDER.PORDER") return $i/PurchaseOrder/item ) for $pid in distinct-values($allitems/partid) order by $pid return <prod_info product = "{$pid}"> <name>{distinct-values($allitems[partid = $pid]/name)} </name> <price>{distinct-values($allitems[partid = $pid]/price)} </price> </prod_info>; In this example the query starts with a "let" clause and assigns sequence of all purchase order items from the table to $allitems. Unless the table is small, the sequence in $allitems is typically very large. Using "let" to combine items from all documents in the entire table often results in suboptimal performance. Next step we iterate for every distinct part value of all the item elements in the sequence $allitems. For each distinct "partid" it returns a constructed xml document "prod_info". Note that for each partid $pid, the query probes back in the sequence $allitems to find all items with matching partid. Same is done for other element values like "price". This type of coding is not straightforward and complex and can produce large amounts of intermediate results that need to be later processed for duplicate elimination. Same results can be achieved by using a much simpler query like the sql/xml query using XMLTABLE below: select XMLELEMENT ( NAME "prod_info", XMLATTRIBUTES( T2.pid as "product"), XMLFOREST(T2.name as "name", T2.price as "price")) FROM ( SELECT distinct T.pid, T.name, T.price FROM purchaseorder, XMLTABLE('$PODER/PurchaseOrder/item' passing PORDER as "PODER" COLUMNS pid VARCHAR(10) PATH 'partid', name VARCHAR(40) PATH 'name', price VARCHAR(10) PATH 'price') as T ) as T2 ; Writing queries in this manner avoids all the overhead of processing large intermediate results which comes from working with large XML sequences. Not only is it much more simple and easier to understand, but when compared to previous example this query performs several orders of magnitude faster. | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * DB2 UDB Version 10.1 * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to Version 10.1 FixPack 3. * **************************************************************** | |
Local-Fix: | |
Use SQL/XML to rewrite bad performing XQuery. See Error Description for more details. | |
verfügbare FixPacks: | |
DB2 Version 10.1 Fix Pack 3 for Linux, UNIX, and Windows | |
Lösung | |
Problem was first fixed in DB2 UDB Version 10.1 FixPack 3. | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 22.01.2013 01.10.2013 01.10.2013 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
Problem behoben lt. FixList in der Version | |
10.1.0.3 | |
10.1.0.3 |