[bdbxml] Question: How to structure the XMLs to use BDB XML to max

[Itay Neeman]

So after more modifications than my last email showed, I've managed
to get it to do all the queries I want. With 3500 entries in the DB,
it works fine, and queries are more or less instantaneous.

However, when I increase it to 14000 (I have 1 directory with 10000
files, "file1" to file"10000"), most queries take around 2-3 seconds
to complete, and they return about 1000 results.
If I do a query on either the folder name or the filename that has
10000 matches, it takes up to 25 seconds to return the query. I'm
attaching the query and and the QueryPlan associated with it.

Also, to get it to match the indices properly, I have had to stop
lower-casing the results from BDBXML. Until now my query was on the
predicate [contains(lower-case(./albumref), 'myquerystring')], but to
get it to match the substring index I had to cancel the lower-case(./
albumref) part. Is there any way to do it or is that not possible?

<XQuery>
<FLWOR>
<ForBinding name="picture">
<Navigation>
<QueryPlanFunction result="collection" container="/Users/
itay/Raphael/.metadata/raphael.dbxml">
<OQPlan>V(node-element-substring-string,albumref,=,'pic')</
OQPlan>
</QueryPlanFunction>
<Step axis="child" name="picture" nodeType="element"/>
<DbXmlFilter>
<Navigation>
<ContextItem/>
<Step axis="child" name="albumref" nodeType="element"/>
<DbXmlContains>
<Sequence>
<AnyAtomicTypeConstructor value="pic"
typeuri="http://www.w3.org/2001/XMLSchema" typename="string"/>
</Sequence>
</DbXmlContains>
</Navigation>
</DbXmlFilter>
</Navigation>
</ForBinding>
<LetBinding name="album">
<Navigation>
<Variable name="picture"/>
<Step axis="child" name="albumref" nodeType="element"/>
<Function name="{http://www.w3.org/2005/04/xpath-
functions}:string"/>
</Navigation>
</LetBinding>
<LetBinding name="albumpath">
<Navigation>
<QueryPlanFunction result="collection" container="/Users/
itay/Raphael/.metadata/raphael.dbxml">
<RQPlan>V(name,=,'[to be calculated]')</RQPlan>
</QueryPlanFunction>
<Step axis="child" name="albums" nodeType="element"/>
<Step axis="child" name="album" nodeType="element"/>
<DbXmlFilter>
<Navigation>
<Step axis="child" name="name" nodeType="element"/>
<DbXmlCompare name="equal">
<Variable name="album"/>
</DbXmlCompare>
</Navigation>
</DbXmlFilter>
<Step axis="child" name="path" nodeType="element"/>
</Navigation>
</LetBinding>
<LetBinding name="pictureFile">
<Navigation>
<Variable name="picture"/>
<Step axis="child" name="filename" nodeType="element">
<OQPlan>P(node-element-equality-string,prefix,filename)</
OQPlan>
</Step>
<Function name="{http://www.w3.org/2005/04/xpath-
functions}:string"/>
</Navigation>
</LetBinding>
<Function name="{http://www.w3.org/2005/04/xpath-
functions}:string-join">
<Parenthesized>
<Function name="{http://www.w3.org/2005/04/xpath-
functions}:concat">
<Function name="{http://www.w3.org/2005/04/xpath-
functions}:concat">
<Variable name="albumpath"/>
<Sequence>
<AnyAtomicTypeConstructor value="/" typeuri="http://
www.w3.org/2001/XMLSchema" typename="string"/>
</Sequence>
</Function>
<Variable name="pictureFile"/>
</Function>
<Navigation>
<Variable name="picture"/>
<Step axis="child" name="id" nodeType="element">
<OQPlan>P(node-element-equality-string,prefix,id)</OQPlan>
</Step>
<Function name="{http://www.w3.org/2005/04/xpath-
functions}:string"/>
</Navigation>
</Parenthesized>
<Sequence>
<AnyAtomicTypeConstructor value="::" typeuri="http://
www.w3.org/2001/XMLSchema" typename="string"/>
</Sequence>
</Function>
</FLWOR>
</XQuery>

Found 10001 results for the query: for $picture in collection('/Users/
itay/Raphael/.metadata/raphael.dbxml')/picture[contains(./
albumref,"pic")]
let $album := $picture/albumref/string()
let $albumpath := collection('/Users/itay/Raphael/.metadata/
raphael.dbxml')/albums/album[name=$album]/path
let $pictureFile := $picture/filename/string()
return
string-join((concat(concat($albumpath,'/'),$pictureFile),$picture/id/
string()),'::')