Main Page » Mailing List » June 2025

Optimizing query that reads nodes from two databases

Tuesday, 3 June 2025 · 6 messages · last reply 4 Jun 2025
MD
Matt Dziuban3 Jun 2025 23:10 #

Hi all,

I'm working on a query that reads from two databases -- OriginalDB and UpdateDB. The structure of each database is the same:

<data>
  <item id="1">...</item>
  <item id="2">...</item>
  ...
</data>

OriginalDB has 2,305,570 items and UpdateDB has 307,019.

The query gets all distinct item ids from the two databases, then gets the item with a matching id (if there is one) from each database, and returns the id and both nodes in a JSON object. Here's the query:

let $originalDB := db:get('OriginalDB')/data/item
let $updateDB := db:get('UpdateDB')/data/item
for $id in distinct-values(($originalDB/@id, $updateDB/@id))
let $original := $originalDB[@id = $id]
let $update := $updateDB[@id = $id]
return json:serialize(map {
  'id': xs:long($id),
  'original': $original,
  'update': $update
}, map { 'indent': 'no' })

In its current state this query is very slow -- it's returned only 35 JSON objects in ~30 minutes. How can I go about optimizing it to best take advantage of each database's indices?

Thanks in advance, Matt

R
ron.vdbranden4 Jun 2025 09:06 #

Hi Matt,

Since I've started using BaseX, I've had great experiences with map performance. Would this version work better, where you first create a map version for all items in both databases, and next look up the corresponding keys?

   let $originalDB := db:get('OriginalDB')/data/item
   let $updateDB := db:get('UpdateDB')/data/item

   let $originalDB.map := map:merge(
     for $entry in $originalDB
     return map:entry($entry/@id, $entry)
   )
   let $updateDB.map := map:merge(
     for $entry in $updateDB
     return map:entry($entry/@id, $entry)
   )
   for $id in ($originalDB.map => map:keys(), $updateDB.map =>
map:keys()) => distinct-values()
   return json:serialize(map {
     'id': xs:long($id),
     'original': $originalDB.map($id),
     'update': $updateDB.map($id)
   }, map { 'indent': 'no' })

Best,

Ron

Quoted text · Matt Dziuban · 35 lines
On 3/06/2025 23:09, Matt Dziuban wrote: > Hi all, > > I'm working on a query that reads from two databases -- OriginalDB and > UpdateDB. The structure of each database is the same: > > <data> > <item id="1">...</item> > <item id="2">...</item> > ... > </data> > > OriginalDB has 2,305,570 items and UpdateDB has 307,019. > > The query gets all distinct item ids from the two databases, then gets > the item with a matching id (if there is one) from each database, and > returns the id and both nodes in a JSON object. Here's the query: > > let $originalDB := db:get('OriginalDB')/data/item > let $updateDB := db:get('UpdateDB')/data/item > for $id in distinct-values(($originalDB/@id, $updateDB/@id)) > let $original := $originalDB[@id = $id] > let $update := $updateDB[@id = $id] > return json:serialize(map { > 'id': xs:long($id), > 'original': $original, > 'update': $update > }, map { 'indent': 'no' }) > > In its current state this query is very slow -- it's returned only 35 > JSON objects in ~30 minutes. How can I go about optimizing it to best > take advantage of each database's indices? > > Thanks in advance, > Matt
CG
Christian Grünin reply to Matt Dziuban, 3 Jun 2025, 23:104 Jun 2025 09:27 #

Hi Matt,

Ron has already given you a perfect answer on how to utilize maps. Another slightly slower solution is to address the database twice, and let the compiler do its job:

let $originalDB := db:get('OriginalDB')/data/item
let $updateDB := db:get('UpdateDB')/data/item
for $id in distinct-values(($originalDB/@id, $updateDB/@id))
let $original := db:get('OriginalDB')/data/item[@id = $id]
let $update := db:get('UpdateDB')/data/item[@id = $id]
return json:serialize(map {
  'id': xs:long($id),
  'original': $original,
  'update': $update
})

This may seem counterintuitive at first glance, but the expression "db:get('OriginalDB')/data/item[@id = $id]" is something that the optimizer will rewrite for index access. It is equivalent to:

let $original := db:attribute("OriginalDB",
$id)/self::attribute(id)/parent::item
let $update := db:attribute("UpdateDB",
$id)/self::attribute(id)/parent::item

Hope this helps, Christian

Quoted text · Matt Dziuban · 36 lines
On Tue, Jun 3, 2025 at 11:10 PM Matt Dziuban <mrdziuban at gmail.com> wrote: > Hi all, > > I'm working on a query that reads from two databases -- OriginalDB and > UpdateDB. The structure of each database is the same: > > <data> > <item id="1">...</item> > <item id="2">...</item> > ... > </data> > > OriginalDB has 2,305,570 items and UpdateDB has 307,019. > > The query gets all distinct item ids from the two databases, then gets the > item with a matching id (if there is one) from each database, and returns > the id and both nodes in a JSON object. Here's the query: > > let $originalDB := db:get('OriginalDB')/data/item > let $updateDB := db:get('UpdateDB')/data/item > for $id in distinct-values(($originalDB/@id, $updateDB/@id)) > let $original := $originalDB[@id = $id] > let $update := $updateDB[@id = $id] > return json:serialize(map { > 'id': xs:long($id), > 'original': $original, > 'update': $update > }, map { 'indent': 'no' }) > > In its current state this query is very slow -- it's returned only 35 > JSON objects in ~30 minutes. How can I go about optimizing it to best take > advantage of each database's indices? > > Thanks in advance, > Matt >
MD
Matt Dziuban4 Jun 2025 13:17 #

Thank you Ron and Christian for the suggestions! I’ll give both a try. Will the map solution cause BaseX to hold every item from both databases in memory?

Best, Matt

Quoted text · Christian Grün · 71 lines
On Wed, Jun 4, 2025 at 3:27 AM Christian Grün <christian.gruen at gmail.com> wrote: > Hi Matt, > > Ron has already given you a perfect answer on how to utilize maps. Another > slightly slower solution is to address the database twice, and let the > compiler do its job: > > let $originalDB := db:get('OriginalDB')/data/item > let $updateDB := db:get('UpdateDB')/data/item > for $id in distinct-values(($originalDB/@id, $updateDB/@id)) > let $original := db:get('OriginalDB')/data/item[@id = $id] > let $update := db:get('UpdateDB')/data/item[@id = $id] > return json:serialize(map { > 'id': xs:long($id), > 'original': $original, > 'update': $update > }) > > This may seem counterintuitive at first glance, but the expression > "db:get('OriginalDB')/data/item[@id = $id]" is something that the optimizer > will rewrite for index access. It is equivalent to: > > let $original := db:attribute("OriginalDB", > $id)/self::attribute(id)/parent::item > let $update := db:attribute("UpdateDB", > $id)/self::attribute(id)/parent::item > > Hope this helps, > Christian > > > > On Tue, Jun 3, 2025 at 11:10 PM Matt Dziuban <mrdziuban at gmail.com> wrote: > >> Hi all, >> >> I'm working on a query that reads from two databases -- OriginalDB and >> UpdateDB. The structure of each database is the same: >> >> <data> >> <item id="1">...</item> >> <item id="2">...</item> >> ... >> </data> >> >> OriginalDB has 2,305,570 items and UpdateDB has 307,019. >> >> The query gets all distinct item ids from the two databases, then gets >> the item with a matching id (if there is one) from each database, and >> returns the id and both nodes in a JSON object. Here's the query: >> >> let $originalDB := db:get('OriginalDB')/data/item >> let $updateDB := db:get('UpdateDB')/data/item >> for $id in distinct-values(($originalDB/@id, $updateDB/@id)) >> let $original := $originalDB[@id = $id] >> let $update := $updateDB[@id = $id] >> return json:serialize(map { >> 'id': xs:long($id), >> 'original': $original, >> 'update': $update >> }, map { 'indent': 'no' }) >> >> In its current state this query is very slow -- it's returned only 35 >> JSON objects in ~30 minutes. How can I go about optimizing it to best take >> advantage of each database's indices? >> >> Thanks in advance, >> Matt >> >
CG
Christian Grün4 Jun 2025 13:27 #
Quoted text · Matt Dziuban · 5 lines
> > Thank you Ron and Christian for the suggestions! I’ll give both a try. > Will the map solution cause BaseX to hold every item from both databases in > memory? >

It’s just references to the database nodes. To learn more about the memory consumption of particular operations, you can attach a profile function call to map:merge and check out the output in the info view panel of the GUI (or see what the command line returns):

let $originalDB.map := map:merge(
  for $entry in $originalDB
  return map:entry($entry/@id, $entry)
) => prof:memory()
MD
Matt Dziuban4 Jun 2025 14:26 #

Good to know, thanks again!

Quoted text · Christian Grün · 18 lines
On Wed, Jun 4, 2025 at 7:27 AM Christian Grün <christian.gruen at gmail.com> wrote: > Thank you Ron and Christian for the suggestions! I’ll give both a try. >> Will the map solution cause BaseX to hold every item from both databases in >> memory? >> > > It’s just references to the database nodes. To learn more about the memory > consumption of particular operations, you can attach a profile function > call to map:merge and check out the output in the info view panel of the > GUI (or see what the command line returns): > > let $originalDB.map := map:merge( > for $entry in $originalDB > return map:entry($entry/@id, $entry) > ) => prof:memory() > >
-- Matthew R. Dziuban mattdziuban.com 703-973-6717 mrdziuban at gmail.com

Written for BaseX 11.9. BaseX may have changed since: please check the current documentation.

« Questions about xslt:transform-text and xslt:transform-reportResult too large with Full Text Index »
⚡Generated with XQuery