Thanks for your quick answer. Query execution improves its timing a lot.
One more question, in a similar query but with attributes, it looks like the
index doesn´t get applied (more similar to our real situation):
XML is similar to:
<businessEvent>
<inputs>
<input name="data">
<input element="229388252" name="id" type="String"/>
<input element="2751 XTE" name="matriculation" type="String"/>
<input element="XTETVTTJKC" name="clientId" type="String"/>
<input name="driver">
<input element="driver name" name="name" type="String"/>
<input element="driver surname" name="surname" type="String"/>
<input element="21973887X" name="nif" type="String"/>
<input element="line 1" name="line1" type="String"/>
<input element="Line 2" name="line2" type="String"/>
<input element="City name" name="city" type="String"/>
<input element="28197" name="cp" type="String"/>
<input element="Region7" name="region" type="String"/>
<input element="Country" name="country" type="String"/>
</input>
<input name="accident">
<input name="date">
<input element="2012" name="year" type="String"/>
<input element="13" name="month" type="String"/>
<input element="22" name="day" type="String"/>
<input element="2" name="hour" type="String"/>
<input element="53" name="minute" type="String"/>
</input>
<input name="address">
<input element="Avenue 5" name="line1" type="String"/>
<input element="Line2" name="line2" type="String"/>
<input element="Madrid" name="city" type="String"/>
<input element="28224" name="cp" type="String"/>
<input element="Region3" name="region" type="String"/>
<input element="Spain" name="country" type="String"/>
</input>
<input element="Lorem ipsum dolor …." name="declaration" type="String"/>
</input>
</input>
</inputs>
</businessEvent>
Execution:
Query: <result>{ for $col1 in (db:open('bbdd_1')//businessEvent ), $col2 in
(db:open('bbdd2')//businessEvent) for $income in
distinct-values($col2//inputs/input[@name='data']/input[@name='accident']/input[@name='address']/input[@name='region']/data(@element))
where
$col1//inputs/input[@name='data']/input[@name='id']/data(@element)=$col2//inputs/input[@name='data']/input[@name='id']/data(@element)
group by $income return <line><label>{ $income }</label><data>{ count($col2)
}</data></line>}</result>
Compiling:
- rewriting where clause to predicate(s)
- pre-evaluating db:open("bbdd_1")
- simplifying descendant-or-self step(s)
- converting descendant::*:businessEvent to child steps
- pre-evaluating db:open("bbdd_2")
- simplifying descendant-or-self step(s)
- simplifying descendant-or-self step(s)
- swapping operands: descendant::*:inputs/*:input[@*:name =
"data"]/*:input[@*:name = "id"]/fn:data(@*:element) =
$col1/descendant::inputs/input[@name = "data"]/input[@name =
"id"]/fn:data(@element)
- simplifying descendant-or-self step(s)
- converting descendant::*:businessEvent[descendant::*:inputs/*:input[@*:name =
"data"]/*:input[@*:name = "id"]/fn:data(@*:element) =
$col1/descendant::inputs/input[@name = "data"]/input[@name =
"id"]/fn:data(@element)] to child steps
- simplifying descendant-or-self step(s)
Result: element result { for $col1 in (document-node { "1361200546116" },
...)/*:businessEvent for $col2 in (document-node { "1361200600857" },
...)/*:businessEvent[descendant::*:inputs/*:input[@*:name =
"data"]/*:input[@*:name = "id"]/fn:data(@*:element) =
$col1/descendant::inputs/input[@name = "data"]/input[@name =
"id"]/fn:data(@element)] for $income in
fn:distinct-values($col2/descendant::inputs/input[@name = "data"]/input[@name =
"accident"]/input[@name = "address"]/input[@name = "region"]/fn:data(@element))
group by $income := $income return element line { element label { $income },
element data { fn:count($col2) } } }
Timing:
- Parsing: 1.24 ms
- Compiling: 7.44 ms
- Evaluating: 36090.05 ms
- Printing: 0.64 ms
- Total Time: 36099.37 ms
Is it possible that the index doesn’t get applied?, how could I improve this
query?
> Date: Mon, 18 Feb 2013 13:35:56 +0100
> From: [email protected]
> To: [email protected]
> CC: [email protected]
> Subject: Re: [basex-talk] Performance problem
>
> Hi Nuria,
>
> Am 18.02.2013 12:10, schrieb Nuria Garcia:
> > Compiling:
> > - rewriting where clause to predicate(s)
> > - pre-evaluating db:open("Authors")
> > - simplifying descendant-or-self step(s)
> > - pre-evaluating db:open("Books")
> > - simplifying descendant-or-self step(s)
> > - simplifying descendant-or-self step(s)
> > - swapping operands: descendant::*:book/*:idauthor =
> > $Author/descendant::author/idauthor
> > - simplifying descendant-or-self step(s)
> > - simplifying descendant-or-self step(s)
>
> It seems that the predicate into which the `where` clause is rewritten
> is not converted into an index access. Nothing in the query should cause
> this, so the problem seems to be (one of) the databases.
>
> Are you sure that the text index of the "Books" database is up-to-date?
> You can check that in the GUI under "Database -> Open & Manage..." or
> with the command `INFO INDEX TEXT` [1]. If it is outdated, you have to
> update it via "Database -> Properties... -> Optimize..." in the GUI or
> the `OPTIMIZE` command [2]. You can also recreate the database with
> incremental updates activated via the `OPDINDEX` option [3], but this
> only affects the text and attribute indices.
>
> Hope this helps,
> cheers, Leo
>
> [1] http://docs.basex.org/wiki/Commands#INFO_INDEX
> [2] http://docs.basex.org/wiki/Commands#OPTIMIZE
> [3] http://docs.basex.org/wiki/Options#UPDINDEX
_______________________________________________
BaseX-Talk mailing list
[email protected]
https://mailman.uni-konstanz.de/mailman/listinfo/basex-talk
