Slow XQueries on a quite small database but great number of docs
Hello,
I have a speed problem with a quite simple query (2 flwor nested) on the database below. (almost 20.000 ms !!!) The great number of docs (1200) seems to slow down the database.
But the resulting database is quite small (689kB) so I'm really disappointed...
If someone has an idea, it would be nice...
P.S. : I tried OPTIMIZE, CREATE INDEX... Nothing will work...
Thank you in advance.
JMA
Quoted text · 1 line
> info db
Database Properties Name: ect Size: 894 KB Nodes: 20656 Documents: 1210 Binaries: 0 Timestamp: 2016-01-25T13:34:28.000Z
Resource Properties Timestamp: 2016-01-21T16:45:12.938Z Encoding: UTF-8 CHOP: true
Indexes
Up-to-date: true
TEXTINDEX: true
ATTRINDEX: true
TOKENINDEX: true
FTINDEX: true
TEXTINCLUDE:
ATTRINCLUDE:
TOKENINCLUDE:
FTINCLUDE:
LANGUAGE: English
STEMMING: false
CASESENS: false
DIACRITICS: false
STOPWORDS:
UPDINDEX: false
AUTOOPTIMIZE: false
MAXCATS: 100
MAXLEN: 96
INDEXSPLITSIZE: 0
Quoted text · 2 lines
FTINDEXSPLITSIZE: 0 > exit
for $intervenant in ("AB","JMA","MM","SP")
return element {"planning"} {
attribute inter {$intervenant},
for $s in db:open("ect")/calendrier/s
let $lundiS12015 :=db:open("ect")/calendrier/mois[@annee = $s/@annee and @numero='01']/debut
let $nbjours :="P" || string(( $s - 1 ) * 7) || "D"
let $lundi := xs:date($lundiS12015) + xs:dayTimeDuration($nbjours)
let $dimanche := xs:date($lundi) + xs:dayTimeDuration("P6D")
return element {'semaine'} {
attribute no {$s}, attribute lundi {$lundi}, attribute dimanche {$dimanche},
for $tache in db:open("ect")/planning/tache[intervenant=$intervenant and (debut/text()) and (fin/text()) and (xs:date($lundi) lt xs:date(fin) ) and (xs:date(debut) lt xs:date($dimanche))]
let $res := tokenize(base-uri($tache),'/')
let $nodossier := substring($res[2],1,3)
let $typedossier := substring($res[2],4,2)
return element {'inter'} {$res[2] , db:open("ect")/dossier[numero=$nodossier and type=$typedossier] , $tache/libelle, $tache/debut, $tache/fin}
}}
Quoted text · ansel.jm · 1 line
> so I'm really disappointed...
This doesn’t sound good… ;) Could you possibly provide us with the data?
Quoted text · ansel.jm · 78 lines
> > If someone has an idea, it would be nice... > > P.S. : I tried OPTIMIZE, CREATE INDEX... Nothing will work... > > Thank you in advance. > > JMA > > >> info db > Database Properties > Name: ect > Size: 894 KB > Nodes: 20656 > Documents: 1210 > Binaries: 0 > Timestamp: 2016-01-25T13:34:28.000Z > > Resource Properties > Timestamp: 2016-01-21T16:45:12.938Z > Encoding: UTF-8 > CHOP: true > > Indexes > Up-to-date: true > TEXTINDEX: true > ATTRINDEX: true > TOKENINDEX: true > FTINDEX: true > TEXTINCLUDE: > ATTRINCLUDE: > TOKENINCLUDE: > FTINCLUDE: > LANGUAGE: English > STEMMING: false > CASESENS: false > DIACRITICS: false > STOPWORDS: > UPDINDEX: false > AUTOOPTIMIZE: false > MAXCATS: 100 > MAXLEN: 96 > INDEXSPLITSIZE: 0 > FTINDEXSPLITSIZE: 0 >> exit > > > > > > > for $intervenant in ("AB","JMA","MM","SP") > > > return element {"planning"} { > attribute inter {$intervenant}, > > for $s in db:open("ect")/calendrier/s > > let $lundiS12015 :=db:open("ect")/calendrier/mois[@annee = $s/@annee and @numero='01']/debut > let $nbjours :="P" || string(( $s - 1 ) * 7) || "D" > let $lundi := xs:date($lundiS12015) + xs:dayTimeDuration($nbjours) > let $dimanche := xs:date($lundi) + xs:dayTimeDuration("P6D") > > return element {'semaine'} { > > attribute no {$s}, attribute lundi {$lundi}, attribute dimanche {$dimanche}, > for $tache in db:open("ect")/planning/tache[intervenant=$intervenant and (debut/text()) and (fin/text()) and (xs:date($lundi) lt xs:date(fin) ) and (xs:date(debut) lt xs:date($dimanche))] > let $res := tokenize(base-uri($tache),'/') > let $nodossier := substring($res[2],1,3) > let $typedossier := substring($res[2],4,2) > return element {'inter'} {$res[2] , db:open("ect")/dossier[numero=$nodossier and type=$typedossier] , $tache/libelle, $tache/debut, $tache/fin} > } > > > } >
Written for BaseX 8.3. BaseX may have changed since: please check the current documentation.