2

I have a large eXist-db database in which I am implementing some functions to manage the data inside.

The pertinent information here is:

There are 2000 customers.
Each customer has access to 400 documents.
Now each document has 20 languages.

So I have 2000 XMLs, each have something like this:

<customer name="foo">
    <document num="A01" subscribed="Y">
      <languages>
        <lang subscribed="N">Arabic</lang>
        <lang subscribed="Y">Polish</lang>
... and so on for 400 documents for 20 languages ...

Now I an trying to write an xQuery because I need to revoke a language. This means I need to essentially delete in 2000 files, for every document, the <lang> whose name is defined.

So something like this:

 for $langs in $g:collection.customers//lang[text()=$deletelang]
 return update delete $langs

But this is taking forever and actually requires a huge amount of memory. Certainly in looking at it ... $langs would be 2000*400 = 800,000 items.

I would note that a range index exists which contains:

<create qname="lang" type="xs:string" nested="no"/>

But still, is that delete query so inefficient that essentially it cannot execute with 800,000 items or is there some other way that should be written?

Update I

So I changed things a bit to just see.

1) Per the comment, I changed to "." from text()

2) I added subsequence() surrounding this to test various sizes>>

for $langs in subsequence($g:collection.customers//lang[.=$deletelang],1,30000)

1-30000 = 24s

so running the whole collection now = 110s

Kevin Brown
  • 8,805
  • 2
  • 20
  • 38

2 Answers2

1

You would need to profile the query to determine exactly where it's spending the most time, but it's possible that your use of text() is short-circuiting the engine's ability to use the range index and is therefore loading all those documents into memory.

text() selects text nodes, and there may be more that one text node in an element. i.e: element lang { text { 'Pol' }, text { 'ish' } } will result in an element that looks like <lang>Polish</lang>, but has two text nodes and will fail on a predicate that assumes there is only one: [text() = 'Polish'].

Try using dot in your predicate: [. = $deletelang].

It's also possible that there is additional overhead in the database to committing such a large update at once. If it is not critical that the delete be done atomically, you may improve performance by batching the updates.

wst
  • 11,681
  • 1
  • 24
  • 39
  • I will test "." and let you know. The same query handles adding a new language as well as deleting it. Adding a new language took 6 mins. Deleting what we just added has never successfully run because it runs out of memory and we have to recover. – Kevin Brown Feb 10 '19 at 23:07
  • Oh, I can also add that there is no way that there are additional text nodes as we create the node in code and not that way. – Kevin Brown Feb 10 '19 at 23:08
  • @KevinBrown Yes, but just because you can guarantee that the query is accurate in practice, does not mean that the database can. – wst Feb 10 '19 at 23:12
0

It's been a while since I've had to do mass deletions. But at least in eXist version 2.2 I could do:

let $langs := $g:collection.customers//lang[.=$deletelang]
return update delete $langs

There was no need to loop through them all.

On a separate note, I would do a complete backup before issuing such mass deletions, just to make sure that everything goes right.

Hope this helps.

westbaystars
  • 151
  • 1
  • 4