Re: [Exist-open] Exist hangs on large collection query

[Michael W. <wes...@ja...>]

Dmitriy,

If you haven't yet done so, please read the Writing Queries section of the
database tuning documentation.

http://exist-db.org/exist/apps/doc/tuning.xml#D2.2.6

More interspersed below.

I didn't set any indexes yet. I guess, it is the key perfomance
> reason.
>

Setting indexes will help a great deal so far as performance is concerned.
 The first part of the above document deals with setting indexes.


> declare function local:index-of-node($sequence as node()*, $srch as
> node()) as xs:integer
> {
>   for $n at $i in $sequence where ($n is $srch) return $i
> };
>

Perhaps using the xutil extension module would be more efficient?

http://exist-db.org/xquery/xpath-util<http://exist-db.org/exist/apps/fundocs/view.html?uri=http://exist-db.org/xquery/xpath-util&location=/db/apps/matumi/modules/path.xql>
/db/apps/matumi/modules/path.xql<http://exist-db.org/exist/apps/eXide/index.html?open=/db/apps/matumi/modules/path.xql>

xutil:index-of-node
xutil:index-of-node($nodes as node()*, $nodeToFind as node()) as xs:integer*

Parameters:$nodes*$nodes*$nodeToFind$nodeToFindReturns:xs:integer*

(:getting Row element of a node table header:)
> let $row := doc("
> http://localhost:8080/exist/rest//db/excel/dv_capacity_2.xml
> ")//ss:Worksheet[1]//ss:Row[3]
>

This does not appear to be a very efficient way of accessing the document.
 It's pulling in the document from outside of eXist, so there will be no
indexes to work with even if you did set indexes.

Give this a try and see if it doesn't cut a big chunk off of the timing:

let $row := doc("/db/excel/dv_capacity_2.xml<http://localhost:8080/exist/rest//db/excel/dv_capacity_2.xml>
")//ss:Worksheet[1]//ss:Row[3]


> (: getting the position of a Region column in nodes table :)
> let $cell := $cells[ss:Data[./text()='Region']]
>

In general (as explained in the performance tuning documentation), the
following will run faster:

let $cell := $cells/ss:Data[./text()='Region']/..


> (: getting table rows wich node name contains '$val':)
> let $rows :=
> //ss:Worksheet[1]//ss:Row[ss:Cell[3]/ss:Data[contains(text(),$val)]]
>

This appears to be querying your entire database from the location that the
script is running from.

Perhaps you should start with saving the document to a variable, then using
it from there.

Back to the top, let's initialize like this:

let $worksheet :=
doc("/db/excel/dv_capacity_2.xml<http://localhost:8080/exist/rest//db/excel/dv_capacity_2.xml>
")//ss:Worksheet[1]
let $row := $worksheet//ss:Row[3]

....

(: Now get the rows where the node name contains "$val" :)
let $rows := $worksheet//ss:Row[ss:Cell[3]/ss:Data[contains(text(),$val)]]

I hope some of these hints help.

-- 
Michael Westbay
Writer/System Administrator
http://www.japanesebaseball.com/