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