Reg : Xquery performence
Wednesday, 28 November 2018 · 3 messages
CS
chandra Sekhar28 Nov 2018 10:45 #
Dear basex,
Please find attachment for sample xml,Like sample xml we are added multiple xml's to the collection .Below query is my XQuery ,It is taking huge time,Can we optimize this query.
My collection size is nearly 1.5 GB
let $entriesDisplay := 'Display All'
let $Rows :=
(collection("752150_Voice_OCTOBER-18_Billed")/SUBCUSTBRK[AccNo[@NO = (9800964)]][DOB >='2018-10-01' and DOB <='2018-10-31'][ServInfo!=0])
let $accNo := "9800964"
let $accWiseTags := $Rows[AccNo[@NO = $accNo]]
for $servNos in distinct-values($accWiseTags/CONN/@NO)
let $eachRow := $accWiseTags[CONN[@NO=$servNos]] return $eachRow
MH
Martin Honnen28 Nov 2018 10:56 #
Quoted text · chandra Sekhar · 22 lines
Am 28.11.2018 um 10:45 schrieb chandra Sekhar: > > Dear basex, > > Please find attachment for sample xml,Like sample xml we are added > multiple xml's to the collection .Below query is my XQuery ,It is > taking huge time,Can we optimize this query. > > > My collection size is nearly 1.5 GB > > > > let $entriesDisplay := 'Display All' > let $Rows := > (collection("752150_Voice_OCTOBER-18_Billed")/SUBCUSTBRK[AccNo[@NO = > (9800964)]][DOB >='2018-10-01' and DOB <='2018-10-31'][ServInfo!=0]) > let $accNo := "9800964" > let $accWiseTags := $Rows[AccNo[@NO = $accNo]] > for $servNos in distinct-values($accWiseTags/CONN/@NO) > let $eachRow := $accWiseTags[CONN[@NO=$servNos]] > return $eachRow
Have you tried to use
group-by e.g. for $r in $accWiseTags
group by $no := $r/CONN/@NO return $r
?
CG
Christian Grün28 Nov 2018 11:12 #
Martin’s idea of using group by is a good idea. You could as well get replace "for $servNos in" by "let $servNos :=", because you can compare multiple values in a predicate.
I am wondering, however, if your query isn’t doing the same thing multiple times. I guess the following query is equivalent?
collection("752150_Voice_OCTOBER-18_Billed")/SUBCUSTBRK
[AccNo/@NO = "9800964"]
[DOB >='2018-10-01' and DOB <='2018-10-31']
[ServInfo != '0']
Quoted text · Martin Honnen · 28 lines
> Dear basex, > > Please find attachment for sample xml,Like sample xml we are added multiple xml's to the collection .Below query is my XQuery ,It is taking huge time,Can we optimize this query. > > > My collection size is nearly 1.5 GB > > > > let $entriesDisplay := 'Display All' > let $Rows := (collection("752150_Voice_OCTOBER-18_Billed")/SUBCUSTBRK[AccNo[@NO = (9800964)]][DOB >='2018-10-01' and DOB <='2018-10-31'][ServInfo!=0]) > > let $accNo := "9800964" > let $accWiseTags := $Rows[AccNo[@NO = $accNo]] > for $servNos in distinct-values($accWiseTags/CONN/@NO) > let $eachRow := $accWiseTags[CONN[@NO=$servNos]] > > return $eachRow > > Have you tried to use `group-by` e.g. > > for $r in $accWiseTags > > group by $no := $r/CONN/@NO > > return $r > > ?
Written for BaseX 9.1. BaseX may have changed since: please check the current documentation.
⚡Generated with XQuery