Main Page » Mailing List » September 2013

Xquery on 2 DBs taking more execution time !!

Monday, 2 September 2013 · 5 messages · last reply 5 Sep 2013

John Best2 Sep 2013 06:34 #

As the data is quite huge around 2GB, which creates problems while creating FT Index, I have divided it into 2 parts. For a better picture of what I have done, I am explaining it with a suitable example. Lets say I have articles on Physics in one single DB. So, I divided them into 2 DBs, viz; short description and long description. So, now I have 2 DBs - PhysicsSD PhysicsLD By doing so, I am not getting OOM now while creating FT Index. (Wow!!). But now, I am facing another issue... Say, I want to search for words "emf" and "waves" into the Physics DBs, I would do this - for $dbname in ('physicsSD', 'physicsLD') for $x in doc($dbname)//Doc[SD/Info/ text() contains text {"emf waves"} all words or LD/Info/Para/text() contains text {"emf waves"} all words]

order by xs:integer($x/Details/Year) descending
return $x/Doc

This query retrieves data in approx. 83000 ms (83 Sec) But when executed on INDIVIDUAL DBs, the total time is very very less compared to the above. Its only 4500 ms (4.5 sec) for BOTH !!!

-- Have a nice day JBest

Dirk Kirsten2 Sep 2013 21:27 #

Hello John, Sounds to me like the FT index isn't hit in the long-running query. Could you please check for both queries if the index is actually used? You can do so by looking at the query plan in the info view. Could you then please provide the misbehaving query plan? Also, it would be best to have at least some of your data, so that we can reproduce the problem. You can send the data to us directly, so we don't spam the list and if the data should not be public. Cheers, Dirk

Quoted text · John Best · 40 lines

On 09/02/2013 06:34 AM, John Best wrote: > As the data is quite huge around 2GB, which creates problems while > creating FT Index, I have divided it into 2 parts. For a better picture of > what I have done, I am explaining it with a suitable example. > > Lets say I have articles on Physics in one single DB. So, I divided them > into 2 DBs, viz; short description and long description. So, now I have 2 > DBs - > > PhysicsSD > PhysicsLD > > By doing so, I am not getting OOM now while creating FT Index. (Wow!!). > > But now, I am facing another issue... > > Say, I want to search for words "emf" and "waves" into the Physics > DBs, I would do this - > > for $dbname in ('physicsSD', 'physicsLD') > for $x in doc($dbname)//Doc[SD/Info/ > text() contains text {"emf waves"} all words or > LD/Info/Para/text() contains text {"emf waves"} all words] > > order by xs:integer($x/Details/Year) descending > return $x/Doc > > This query retrieves data in approx. 83000 ms (83 Sec) > > But when executed on INDIVIDUAL DBs, the total time is very very less > compared to the above. Its only 4500 ms (4.5 sec) for BOTH !!! > > > > > _______________________________________________ > BaseX-Talk mailing list > BaseX-Talk at mailman.uni-konstanz.de > https://mailman.uni-konstanz.de/mailman/listinfo/basex-talk >

-- Dirk Kirsten, BaseX GmbH, http://basex.org |-- Firmensitz: Blarerstrasse 56, 78462 Konstanz |-- Registergericht Freiburg, HRB: 708285, Geschäftsführer: | Dr. Christian Grün, Dr. Alexander Holupirek, Michael Seiferle `-- Phone: 0049 7531 28 28 676, Fax: 0049 7531 20 05 22

John Best4 Sep 2013 08:36 #

Dear Dirk, "Sounds to me like the FT index isn't hit in the long-running query. Could you please check for both queries if the index is actually used? You can do so by looking at the query plan in the info view." Both the DBs are Text, Attribute and FT Indexed. Please find the Timing and Query Plan output after running the query - *Timing:* - Parsing: 0.0 ms - Compiling: 11.08 ms - Evaluating: 48729.01 ms - Printing: 16.38 ms - Total Time: 48756.49 ms

*Query plan: *
<QueryPlan>
  <FLWR>
    <Let var="$items">
      <List>
        <IterPath>
          <DBNode name="physicsSD" pre="0"/>
          <IterStep axis="child" test="*:Docs"/>
          <IterStep axis="child" test="*:Doc"/>
        </IterPath>
        <IterPath>
          <DBNode name="physicsLD" pre="0"/>
          <IterStep axis="child" test="*:Docs"/>
          <IterStep axis="child" test="*:Doc"/>
        </IterPath>
      </List>
    </Let>
    <For var="$x">
      <IterFilter>
        <VarRef>
          <Var name="$items" id="0"/>
        </VarRef>
        <FTContains>
          <AxisPath>
            <IterStep axis="child" test="Info"/>
            <IterStep axis="child" test="Para"/>
            <IterStep axis="child" test="text()"/>
          </AxisPath>
          <FTWords>
            <Str value="emf waves" type="xs:string"/>
          </FTWords>
        </FTContains>
      </IterFilter>
    </For>
    <Return>
      <AxisPath>
        <VarRef>
          <Var name="$x" id="1"/>
        </VarRef>
        <IterStep axis="child" test="Doc"/>
      </AxisPath>
    </Return>
  </FLWR>
</QueryPlan>

"Also it would be best to have at least some of your data, so that we can reproduce the problem. You can send the data to us directly, so we don't spam the list and if the data should not be public." I can't provide the data publicly. I do have Christian's personal mail id. But I think he is enjoying vacations...

-- Have a nice day JBest

Dirk Kirsten4 Sep 2013 09:58 #

Hello John, As assumed, the index is not used in your query. The query plan should show some <FTIndexAccess /> Element, which it does not. The reasons for that can be numerous and it is difficult to guess without the data. Of course you can still send the data to Christian, but it will take same time until he is back in office and I am sure there is a pile of e-mails waiting for him when he returns, so it could be a while. In general, all people with a @basex.org mail address are employees of BaseX, so the same rules apply to all of them (if you didn't sign any special NDAs with Christian in person...) in terms of secrecy. However, of course Christian is our main architect and will always provide the fastest and most knowledgeable answers. But we try our best to be of some help as well :) Cheers, Dirk

Quoted text · John Best · 82 lines

On 09/04/2013 08:36 AM, John Best wrote: > Dear Dirk, > > > "Sounds to me like the FT index isn't hit in the long-running query. > Could you please check for both queries if the index is actually used? > You can do so by looking at the query plan in the info view." > > Both the DBs are Text, Attribute and FT Indexed. > > Please find the Timing and Query Plan output after running the query - > > *Timing:* > - Parsing: 0.0 ms > - Compiling: 11.08 ms > - Evaluating: 48729.01 ms > - Printing: 16.38 ms > - Total Time: 48756.49 ms > > > *Query plan: * > <QueryPlan> > <FLWR> > <Let var="$items"> > <List> > <IterPath> > <DBNode name="physicsSD" pre="0"/> > <IterStep axis="child" test="*:Docs"/> > <IterStep axis="child" test="*:Doc"/> > </IterPath> > <IterPath> > <DBNode name="physicsLD" pre="0"/> > <IterStep axis="child" test="*:Docs"/> > <IterStep axis="child" test="*:Doc"/> > </IterPath> > </List> > </Let> > <For var="$x"> > <IterFilter> > <VarRef> > <Var name="$items" id="0"/> > </VarRef> > <FTContains> > <AxisPath> > <IterStep axis="child" test="Info"/> > <IterStep axis="child" test="Para"/> > <IterStep axis="child" test="text()"/> > </AxisPath> > <FTWords> > <Str value="emf waves" type="xs:string"/> > </FTWords> > </FTContains> > </IterFilter> > </For> > <Return> > <AxisPath> > <VarRef> > <Var name="$x" id="1"/> > </VarRef> > <IterStep axis="child" test="Doc"/> > </AxisPath> > </Return> > </FLWR> > </QueryPlan> > > > "Also it would be best to have at least some of your data, so that we can > reproduce the problem. You can send the data to us directly, so we don't > spam the list and if the data should not be public." > > I can't provide the data publicly. I do have Christian's personal mail id. > But I think he is enjoying vacations... > > > > > > _______________________________________________ > BaseX-Talk mailing list > BaseX-Talk at mailman.uni-konstanz.de > https://mailman.uni-konstanz.de/mailman/listinfo/basex-talk >

Christian Grünin reply to John Best, 2 Sep 2013, 06:345 Sep 2013 16:10 #

Hi John, thanks Dirk,

Quoted text · John Best · 4 lines

> for $dbname in ('physicsSD', 'physicsLD') > for $x in doc($dbname)//Doc[SD/Info/ > text() contains text {"emf waves"} all words or > LD/Info/Para/text() contains text {"emf waves"} all words]

due to the dynamic choice of addressed databases, the full-text index will not be utilized. The ft:search function can be used instead [1]. Hope this helps, Christian [1] http://docs.basex.org/wiki/Full-Text_Module#ft:search

Quoted text · John Best · 21 lines

> > order by xs:integer($x/Details/Year) descending > return $x/Doc > > This query retrieves data in approx. 83000 ms (83 Sec) > > But when executed on INDIVIDUAL DBs, the total time is very very less > compared to the above. Its only 4500 ms (4.5 sec) for BOTH !!! > > > -- > Have a nice day > JBest > > > > _______________________________________________ > BaseX-Talk mailing list > BaseX-Talk at mailman.uni-konstanz.de > https://mailman.uni-konstanz.de/mailman/listinfo/basex-talk >

Written for BaseX 7.7. BaseX may have changed since: please check the current documentation.

⚡Generated with XQuery

`J`	Select the next message
`K`	Select the previous message
`N`	Next thread
`P`	Previous thread
`U`	Go up one level
`/`	Jump to the search field