Xquery on 2 DBs taking more execution time !!
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/DocThis 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 !!!
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 >
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...
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 >
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
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.