Histogramming a large dataset · Mailing List

MH

Martin Honnenin reply to Ron Katriel, 10 Mar 2021, 22:3710 Mar 2021 21:44 #

Quoted text · Ron Katriel · 33 lines

On 10.03.2021 21:37, Ron Katriel wrote: > Hi, > > I would appreciate your advice on optimizing a query against a large > BaseX (9.2.4) database. It is loaded with data from the FDA’s Adverse > Event Reporting System (FAERS). Currently this is just the 2020 dataset > which comprises 12 documents stored as 308,870,597 nodes (6,565 MB). > > The queries below effectively - though not necessarily efficiently - > implement a histogram. The first, which is applied to patient gender > (sex), returns the results (3 items) in 52 seconds: > > 2 893694 > 1 583999 > 0 198 > > The second does this for patient weight - rounded to the closest 10 lbs > increment. It takes 580 seconds to place the data into 67 bins. > Initially I tried running it on the rounded weights but aborted the run > as it was a taking an inordinate amount of time (there are 217 distinct > weights in the dataset). > > Is there a way to improve the performance of this type of query? > > Thanks, > Ron > > > (: 3 items - 52 sec :) > let $safetyreport := db:open('FAERS')/ichicsr/safetyreport > for $value in distinct-values($safetyreport/patient/patientsex) > return concat($value, " ", > count(index-of($safetyreport/patient/patientsex, $value)))

You could always try whether grouping performs better e.g.

   for $ps in db:open('FAERS')/ichicsr/safetyreport/patientsex
   group by $s := $ps
   return $s || " " || count($ps)

Quoted text · Ron Katriel · 8 lines

> > (: 67 items - 580 sec :) > let $safetyreport := db:open('FAERS')/ichicsr/safetyreport > for $value in distinct-values($safetyreport/patient/patientweight ! (. > div 10.0) ! round(.) ! (. * 10)) > return concat($value, " ", > count(index-of($safetyreport/patient/patientweight ! (. div 10.0) ! > round(.) ! (. * 10), $value)))

   for $pw in db:open('FAERS')/ichicsr/safetyreport/patientweight
   group by $w := $pw ! (. div 10.0) ! round(.) ! (. * 10))
   return $w || " " || count($pw)

MH

Martin Honnenin reply to Ron Katriel, 10 Mar 2021, 22:5610 Mar 2021 22:00 #

Quoted text · Ron Katriel · 4 lines

On 10.03.2021 21:55, Ron Katriel wrote: > The queries you shared terminate quickly and don’t return anything. The > one provided by Graydon does and completes significantly faster. I’m not > sure what accounts for the difference.

I think I missed a step in the paths e.g.

Quoted text · Ron Katriel · 11 lines

> On March 10, 2021 at 3:44:28 PM, Martin Honnen (martin.honnen at gmx.de > <mailto:martin.honnen at gmx.de>) wrote: >> > (: 3 items - 52 sec :) >> > let $safetyreport := db:open('FAERS')/ichicsr/safetyreport >> > for $value in distinct-values($safetyreport/patient/patientsex) >> > return concat($value, " ", >> > count(index-of($safetyreport/patient/patientsex, $value))) >> >> You could always try whether grouping performs better e.g. >> >> for $ps in db:open('FAERS')/ichicsr/safetyreport/patientsex

should have been

   for $ps in db:open('FAERS')/ichicsr/safetyreport/patient/patientsex

Quoted text · Ron Katriel · 11 lines

>> group by $s := $ps >> return $s || " " || count($ps) >> >> > >> > (: 67 items - 580 sec :) >> > let $safetyreport := db:open('FAERS')/ichicsr/safetyreport >> > for $value in distinct-values($safetyreport/patient/patientweight ! (. >> > div 10.0) ! round(.) ! (. * 10)) >> > return concat($value, " ", >> > count(index-of($safetyreport/patient/patientweight ! (. div 10.0) ! >> > round(.) ! (. * 10), $value)))

and

Quoted text · Ron Katriel · 1 line

>> for $pw in db:open('FAERS')/ichicsr/safetyreport/patientweight

   for $pw in db:open('FAERS')/ichicsr/safetyreport/patient/patientweight

Quoted text · Ron Katriel · 2 lines

>> group by $w := $pw ! (. div 10.0) ! round(.) ! (. * 10)) >> return $w || " " || count($pw)

RK

Ron Katriel10 Mar 2021 22:37 #

Hi, I would appreciate your advice on optimizing a query against a large BaseX (9.2.4) database. It is loaded with data from the FDA’s Adverse Event Reporting System (FAERS). Currently this is just the 2020 dataset which comprises 12 documents stored as 308,870,597 nodes (6,565 MB). The queries below effectively - though not necessarily efficiently - implement a histogram. The first, which is applied to patient gender (sex), returns the results (3 items) in 52 seconds: 2 893694 1 583999 0 198 The second does this for patient weight - rounded to the closest 10 lbs increment. It takes 580 seconds to place the data into 67 bins. Initially I tried running it on the rounded weights but aborted the run as it was a taking an inordinate amount of time (there are 217 distinct weights in the dataset). Is there a way to improve the performance of this type of query? Thanks, Ron

(: 3 items - 52 sec :)
let $safetyreport := db:open('FAERS')/ichicsr/safetyreport
for $value in distinct-values($safetyreport/patient/patientsex)
return concat($value, " ", count(index-of($safetyreport/patient/patientsex,
$value)))

(: 67 items - 580 sec :)
let $safetyreport := db:open('FAERS')/ichicsr/safetyreport
for $value in distinct-values($safetyreport/patient/patientweight ! (. div
10.0) ! round(.) ! (. * 10))
return concat($value, " ",
count(index-of($safetyreport/patient/patientweight ! (. div 10.0) !
round(.) ! (. * 10), $value)))

-- The information in this email and any attachments are intended solely for the recipient(s) to whom it is addressed, and may be confidential and/or privileged. Any unauthorized distribution or copying of this transmittal or its attachments is prohibited. If you are not a named recipient or have received this email in error: (i) you should not read, disclose, or copy it, (ii) please notify the sender of your receipt by reply email and delete this email and all attachments.

GS

Graydon Saunders10 Mar 2021 22:50 #

I am sure someone more knowledgable about BaseX specifics will answer, too, but in general, you have to try not to step on the optimizer's feet. 1. don't call db:open() twice; assign it to a variable,

let $sourceData as document-node()+ := db:open('FAERS')

and then use the variable. 2. don't do math in the binding expression of a for clause! Also, don't do math twice. Stick things in variables. 3. grouping is more efficient than distinct-values So maybe

for $weight in $sourceData/ichicsr/safetyreport/patient/patientweight
   let $numeric as xs:integer := (($weight div 10.0) => round()) * 10
   group by $numeric
  return concat($numeric, count($weight))

Not tested; I just typed that in, and I might not have understood what you were trying to do. But take advantage of the tuple stream processing whenever you can. -- Graydon

Quoted text · Ron Katriel · 53 lines

On Wed, Mar 10, 2021 at 3:37 PM Ron Katriel <rkatriel at mdsol.com> wrote: > Hi, > > I would appreciate your advice on optimizing a query against a large BaseX > (9.2.4) database. It is loaded with data from the FDA’s Adverse Event > Reporting System (FAERS). Currently this is just the 2020 dataset which > comprises 12 documents stored as 308,870,597 nodes (6,565 MB). > > The queries below effectively - though not necessarily efficiently - > implement a histogram. The first, which is applied to patient gender (sex), > returns the results (3 items) in 52 seconds: > > 2 893694 > 1 583999 > 0 198 > > The second does this for patient weight - rounded to the closest 10 lbs > increment. It takes 580 seconds to place the data into 67 bins. Initially I > tried running it on the rounded weights but aborted the run as it was a > taking an inordinate amount of time (there are 217 distinct weights in the > dataset). > > Is there a way to improve the performance of this type of query? > > Thanks, > Ron > > > (: 3 items - 52 sec :) > let $safetyreport := db:open('FAERS')/ichicsr/safetyreport > for $value in distinct-values($safetyreport/patient/patientsex) > return concat($value, " ", > count(index-of($safetyreport/patient/patientsex, $value))) > > (: 67 items - 580 sec :) > let $safetyreport := db:open('FAERS')/ichicsr/safetyreport > for $value in distinct-values($safetyreport/patient/patientweight ! (. div > 10.0) ! round(.) ! (. * 10)) > return concat($value, " ", > count(index-of($safetyreport/patient/patientweight ! (. div 10.0) ! > round(.) ! (. * 10), $value))) > > > > ------------------------------ > The information in this email and any attachments are intended solely for > the recipient(s) to whom it is addressed, and may be confidential and/or > privileged. Any unauthorized distribution or copying of this transmittal or > its attachments is prohibited. If you are not a named recipient or have > received this email in error: (i) you should not read, disclose, or copy > it, (ii) please notify the sender of your receipt by reply email and delete > this email and all attachments. >

RK

Ron Katrielin reply to Martin Honnen, 10 Mar 2021, 21:4410 Mar 2021 22:56 #

Hi Martin, The queries you shared terminate quickly and don’t return anything. The one provided by Graydon does and completes significantly faster. I’m not sure what accounts for the difference. Thanks, Ron

Quoted text · Ron Katriel · 34 lines

On March 10, 2021 at 3:44:28 PM, Martin Honnen (martin.honnen at gmx.de) wrote: On 10.03.2021 21:37, Ron Katriel wrote: > Hi, > > I would appreciate your advice on optimizing a query against a large > BaseX (9.2.4) database. It is loaded with data from the FDA’s Adverse > Event Reporting System (FAERS). Currently this is just the 2020 dataset > which comprises 12 documents stored as 308,870,597 nodes (6,565 MB). > > The queries below effectively - though not necessarily efficiently - > implement a histogram. The first, which is applied to patient gender > (sex), returns the results (3 items) in 52 seconds: > > 2 893694 > 1 583999 > 0 198 > > The second does this for patient weight - rounded to the closest 10 lbs > increment. It takes 580 seconds to place the data into 67 bins. > Initially I tried running it on the rounded weights but aborted the run > as it was a taking an inordinate amount of time (there are 217 distinct > weights in the dataset). > > Is there a way to improve the performance of this type of query? > > Thanks, > Ron > > > (: 3 items - 52 sec :) > let $safetyreport := db:open('FAERS')/ichicsr/safetyreport > for $value in distinct-values($safetyreport/patient/patientsex) > return concat($value, " ", > count(index-of($safetyreport/patient/patientsex, $value)))

You could always try whether grouping performs better e.g.

for $ps in db:open('FAERS')/ichicsr/safetyreport/patientsex
group by $s := $ps
return $s || " " || count($ps)

Quoted text · Martin Honnen · 8 lines

> > (: 67 items - 580 sec :) > let $safetyreport := db:open('FAERS')/ichicsr/safetyreport > for $value in distinct-values($safetyreport/patient/patientweight ! (. > div 10.0) ! round(.) ! (. * 10)) > return concat($value, " ", > count(index-of($safetyreport/patient/patientweight ! (. div 10.0) ! > round(.) ! (. * 10), $value)))

for $pw in db:open('FAERS')/ichicsr/safetyreport/patientweight
group by $w := $pw ! (. div 10.0) ! round(.) ! (. * 10))
return $w || " " || count($pw)

-- The information in this email and any attachments are intended solely for the recipient(s) to whom it is addressed, and may be confidential and/or privileged. Any unauthorized distribution or copying of this transmittal or its attachments is prohibited. If you are not a named recipient or have received this email in error: (i) you should not read, disclose, or copy it, (ii) please notify the sender of your receipt by reply email and delete this email and all attachments.

RK

Ron Katrielin reply to Graydon Saunders, 10 Mar 2021, 22:5010 Mar 2021 22:57 #

Hi Graydon, The query for the patient weight you shared works correctly and completes significantly faster (in 15 seconds). Thanks! Ron

Quoted text · Graydon Saunders (graydonish at gmail.com) · 2 lines

On March 10, 2021 at 3:50:17 PM, Graydon Saunders (graydonish at gmail.com) wrote:

I am sure someone more knowledgable about BaseX specifics will answer, too, but in general, you have to try not to step on the optimizer's feet. 1. don't call db:open() twice; assign it to a variable,

let $sourceData as document-node()+ := db:open('FAERS')

and then use the variable. 2. don't do math in the binding expression of a for clause! Also, don't do math twice. Stick things in variables. 3. grouping is more efficient than distinct-values So maybe

for $weight in $sourceData/ichicsr/safetyreport/patient/patientweight
   let $numeric as xs:integer := (($weight div 10.0) => round()) * 10
   group by $numeric
  return concat($numeric, count($weight))

Not tested; I just typed that in, and I might not have understood what you were trying to do. But take advantage of the tuple stream processing whenever you can. -- Graydon

Quoted text · Ron Katriel · 53 lines

On Wed, Mar 10, 2021 at 3:37 PM Ron Katriel <rkatriel at mdsol.com> wrote: > Hi, > > I would appreciate your advice on optimizing a query against a large BaseX > (9.2.4) database. It is loaded with data from the FDA’s Adverse Event > Reporting System (FAERS). Currently this is just the 2020 dataset which > comprises 12 documents stored as 308,870,597 nodes (6,565 MB). > > The queries below effectively - though not necessarily efficiently - > implement a histogram. The first, which is applied to patient gender (sex), > returns the results (3 items) in 52 seconds: > > 2 893694 > 1 583999 > 0 198 > > The second does this for patient weight - rounded to the closest 10 lbs > increment. It takes 580 seconds to place the data into 67 bins. Initially I > tried running it on the rounded weights but aborted the run as it was a > taking an inordinate amount of time (there are 217 distinct weights in the > dataset). > > Is there a way to improve the performance of this type of query? > > Thanks, > Ron > > > (: 3 items - 52 sec :) > let $safetyreport := db:open('FAERS')/ichicsr/safetyreport > for $value in distinct-values($safetyreport/patient/patientsex) > return concat($value, " ", > count(index-of($safetyreport/patient/patientsex, $value))) > > (: 67 items - 580 sec :) > let $safetyreport := db:open('FAERS')/ichicsr/safetyreport > for $value in distinct-values($safetyreport/patient/patientweight ! (. div > 10.0) ! round(.) ! (. * 10)) > return concat($value, " ", > count(index-of($safetyreport/patient/patientweight ! (. div 10.0) ! > round(.) ! (. * 10), $value))) > > > > ------------------------------ > The information in this email and any attachments are intended solely for > the recipient(s) to whom it is addressed, and may be confidential and/or > privileged. Any unauthorized distribution or copying of this transmittal or > its attachments is prohibited. If you are not a named recipient or have > received this email in error: (i) you should not read, disclose, or copy > it, (ii) please notify the sender of your receipt by reply email and delete > this email and all attachments. >

-- The information in this email and any attachments are intended solely for the recipient(s) to whom it is addressed, and may be confidential and/or privileged. Any unauthorized distribution or copying of this transmittal or its attachments is prohibited. If you are not a named recipient or have received this email in error: (i) you should not read, disclose, or copy it, (ii) please notify the sender of your receipt by reply email and delete this email and all attachments.

RK

Ron Katrielin reply to Martin Honnen, 10 Mar 2021, 22:0010 Mar 2021 23:07 #

Yes, that was it. Thanks! Best, Ron

Quoted text · Ron Katriel · 5 lines

On March 10, 2021 at 4:00:33 PM, Martin Honnen (martin.honnen at gmx.de) wrote: On 10.03.2021 21:55, Ron Katriel wrote: > The queries you shared terminate quickly and don’t return anything. The > one provided by Graydon does and completes significantly faster. I’m not > sure what accounts for the difference.

I think I missed a step in the paths e.g.

Quoted text · Martin Honnen · 11 lines

> On March 10, 2021 at 3:44:28 PM, Martin Honnen (martin.honnen at gmx.de > <mailto:martin.honnen at gmx.de>) wrote: >> > (: 3 items - 52 sec :) >> > let $safetyreport := db:open('FAERS')/ichicsr/safetyreport >> > for $value in distinct-values($safetyreport/patient/patientsex) >> > return concat($value, " ", >> > count(index-of($safetyreport/patient/patientsex, $value))) >> >> You could always try whether grouping performs better e.g. >> >> for $ps in db:open('FAERS')/ichicsr/safetyreport/patientsex

should have been

for $ps in db:open('FAERS')/ichicsr/safetyreport/patient/patientsex

Quoted text · Martin Honnen · 11 lines

>> group by $s := $ps >> return $s || " " || count($ps) >> >> > >> > (: 67 items - 580 sec :) >> > let $safetyreport := db:open('FAERS')/ichicsr/safetyreport >> > for $value in distinct-values($safetyreport/patient/patientweight ! (. >> > div 10.0) ! round(.) ! (. * 10)) >> > return concat($value, " ", >> > count(index-of($safetyreport/patient/patientweight ! (. div 10.0) ! >> > round(.) ! (. * 10), $value)))

and

Quoted text · Martin Honnen · 1 line

>> for $pw in db:open('FAERS')/ichicsr/safetyreport/patientweight

for $pw in db:open('FAERS')/ichicsr/safetyreport/patient/patientweight

Quoted text · Martin Honnen · 2 lines

>> group by $w := $pw ! (. div 10.0) ! round(.) ! (. * 10)) >> return $w || " " || count($pw)

-- The information in this email and any attachments are intended solely for the recipient(s) to whom it is addressed, and may be confidential and/or privileged. Any unauthorized distribution or copying of this transmittal or its attachments is prohibited. If you are not a named recipient or have received this email in error: (i) you should not read, disclose, or copy it, (ii) please notify the sender of your receipt by reply email and delete this email and all attachments.

`J`	Select the next message
`K`	Select the previous message
`U`	Go up one level
`/`	Jump to the search field