fn:count() performance

On Tue, Mar 29, 2022 at 5:13 PM Josselin Morvan <morvan.josselin at gmail.com> wrote: > > Hi everyone, > > We are experimenting a small issue with the count() function, and I wanted to know if you have any idea to reduce the response time of the server : > > We have a database containing almost 5.000 descriptions of expert reports from the 18th century. As a report can take place over several years, we want to list all the years mentioned in the reports and then count for each year how many reports we have. Our goal with this query is to produce a filter by year for our web application. > > But, if the first part of the query is quite fast, it is not the case for the second part… here is a simplified sample of our code : > > xquery version "3.1"; > declare default element namespace "xpr" ; > > (: > to create the xprDB ;) > db:create('xpr', 'https://raw.githubusercontent.com/anrExperts/data/master/db/xpr.xml') > :) > > let $years := fn:distinct-values(db:open('xpr')/xpr/expertises/expertise/description/sessions/date[@when castable as xs:date]/fn:year-from-date(@when)) > for $year in $years > return > $year || ' : ' (:this first part of the query is quite fast 0.09sec on my old computer:) || > fn:count(db:open('xpr')/xpr/expertises/expertise[description/sessions/date[fn:matches(@when, xs:string($year))]]) (:it takes 5sec to execute the second part of the query:) > > > Do you see anything we are doing wrong or we can improve to reduce the server response time ? > > We thank you in advance! > Best, > Josselin

> Le 29 mars 2022 à 18:33, Christian Grün <christian.gruen at gmail.com> a écrit : > > Dear Josselin, > > The group by clause can be used to speed up repeated value lookups: > > declare default element namespace 'xpr' ; > > for $group in db:open('xpr')/xpr/expertises/expertise > for $year in $group/description/sessions/ > date[@when castable as xs:date]/year-from-date(@when) > group by $year > return $year || ' : ' || count($group/self::node()) > > The self::node() step is used in the last line to remove duplicate > nodes in the resulting $group value. > > Salutations, > Christian > > > > > On Tue, Mar 29, 2022 at 5:13 PM Josselin Morvan > <morvan.josselin at gmail.com> wrote: >> >> Hi everyone, >> >> We are experimenting a small issue with the count() function, and I wanted to know if you have any idea to reduce the response time of the server : >> >> We have a database containing almost 5.000 descriptions of expert reports from the 18th century. As a report can take place over several years, we want to list all the years mentioned in the reports and then count for each year how many reports we have. Our goal with this query is to produce a filter by year for our web application. >> >> But, if the first part of the query is quite fast, it is not the case for the second part… here is a simplified sample of our code : >> >> xquery version "3.1"; >> declare default element namespace "xpr" ; >> >> (: >> to create the xprDB ;) >> db:create('xpr', 'https://raw.githubusercontent.com/anrExperts/data/master/db/xpr.xml') >> :) >> >> let $years := fn:distinct-values(db:open('xpr')/xpr/expertises/expertise/description/sessions/date[@when castable as xs:date]/fn:year-from-date(@when)) >> for $year in $years >> return >> $year || ' : ' (:this first part of the query is quite fast 0.09sec on my old computer:) || >> fn:count(db:open('xpr')/xpr/expertises/expertise[description/sessions/date[fn:matches(@when, xs:string($year))]]) (:it takes 5sec to execute the second part of the query:) >> >> >> Do you see anything we are doing wrong or we can improve to reduce the server response time ? >> >> We thank you in advance! >> Best, >> Josselin