Hi,

    I'm currently processing a 3GB xml file. Below is the Query that is being executed and the corresponding output

 

count(db:open("ASEPXML", "A_SEPXML")/descendant::*:PmtInf[15]/*:CdtTrfTxInf/*:CdtrAgt/*:FinInstnId/*:BIC/text())

Output: 100000

 

           The total time taken to evaluate the query is 210 secs. Below is the query plan.

 

Compiling:
- pre-evaluating db:open("ASEPXML", "A_SEPXML")
Optimized Query:
fn:count(document-node { "A_SEPXML" }/descendant::*:PmtInf[15]/*:CdtTrfTxInf/*:CdtrAgt/*:FinInstnId/*:BIC/text())
Result:
- Hit(s): 1 Item
- Updated: 0 Items
- Printed: 6 Bytes
- Locking: local [ASEPXML]
Timing:
- Parsing: 0.0 ms
- Compiling: 0.28 ms
- Evaluating: 210818.96 ms
- Printing: 0.08 ms
- Total Time: 210819.34 ms
Query plan:
<QueryPlan>
  <FNAggr name="count(item)">
    <CachedPath>
      <DBNode name="ASEPXML" pre="0"/>
      <IterPosStep axis="descendant" test="*:PmtInf">
        <Pos min="15" max="15"/>
      </IterPosStep>
      <IterStep axis="child" test="*:CdtTrfTxInf"/>
      <IterStep axis="child" test="*:CdtrAgt"/>
      <IterStep axis="child" test="*:FinInstnId"/>
      <IterStep axis="child" test="*:BIC"/>
      <IterStep axis="child" test="text()"/>
    </CachedPath>
  </FNAggr>
</QueryPlan>

      The database ASEPXML has text index created. The structure of the xml is as follows:

 

<root>

       <PmtInf>

              <CdtTrfTxInf>

                   <CdtrAgt>

                             <FinInstnId>

                                     <BIC>XXXXXX1A</BIC>

                             </FinInstnId>

               <CdtTrfTxInf> ... repeating node (100000)

       </PmtInf>

       <PmtInf>

   

        </PmtInf>

        ... repeating node (40)

</root>

 

Are there possibilities of reducing the response times and how do I detect if an index is being used or not ?

 

Regards

Nesh