Slow XQueries on a quite small database but great number of docs
Monday, 25 January 2016 · 2 messages
A
ansel.jm25 Jan 2016 15:13 #
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: 0Quoted 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}
} }
CG
Christian Grün25 Jan 2016 15:18 #
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.
⚡Generated with XQuery