CODEWITHSUNDEEP
xpathxqueryexist-db
Den
Den

Reputation: 43

Optimizing XQuery interogation with XPath predicate

So, I was optimizing a query I carried over from SQL, and I ran into a bit of a performance issue when compared to how it used to work in sql.

Basically, my php script is sending between 2 and 5 sets of two (numeric) values.

These have to be compare against id and doc from my collection's elements. Of course, the fewer elements in the predicate, the faster the query My for with predicate looks like this right now:

for $p in collection("/db/col1")//set1/page[(id eq val1 and doc eq altval1) or (id eq val2 and doc eq altval2) or (id eq val3 and doc eq altval3) or (id eq val4 and doc eq altval4) or (id eq val5 and doc eq altval5)]

I need to somehow write a predicate that changes depending on the number of values. I tried writing a function that writes the conditions and calling it in the predicate, depending on how many values are passed, but that didn't seem to work.

I would really appreciate if someone knows a workaround for this.

Edit: Removed a typo in the code.

Upvotes: 3

Views: 90

Answers (3)

Den
Den

Reputation: 43

I upvoted both answers since they both get the job done and I could clearly see an improvement. I don't want to select one over the other as I really think it's more a matter of taste at this point.

For my part, I found a third one which, specifically for this case is even faster. On the down side, it's horribly tedious, inelegant and very context specific. Also, while your answers can be adapted to several similar problems, this one only works in the case when you are 'triggering' the XQuery scripts externally. So here it goes:

I actually made FIVE different xql scripts, one that deals with 1 pair of values, one that deals with the first two pairs, on with the first three pairs etcetera.

So script one would contain:

  for $p in collection("/db/col1")//set1/page[id eq val1 and doc1 eq altval1]

while in script five you would find something like the original:

 for $p in collection("/db/col1")//set1/page[(id eq val1 and doc eq altval1) or (id eq val2 and doc eq altval2) or (id eq val3 and doc eq altval3) or (id eq val4 and doc eq altval4) or (id eq val5 and doc eq altval5)]

I then call them from my PHP script, depending on the number of parameters I need to send. I wouldn't attempt to scale this to more than five pairs, but for the moment it gets the job done.

Upvotes: 0

adamretter
adamretter

Reputation: 3517

If you wanted to use a function in the predicate, then something like the following could possibly work for you:

xquery version "3.1";

declare variable $local:criteria := array {
  ("val1", "altval1"),
  ("val2", "altval2"),
  ("val3", "altval3"),
  ("val4", "altval4"),
  ("val5", "altval5")
};

declare function local:match($id, $doc) as xs:boolean {
  array:size(
    array:filter($local:criteria, function($x) {
      $id eq $x[1] and $doc eq $x[2]
    })
  ) eq 1  
};

collection("/db/col1")//set1//page[local:match(id, doc)]

Note - I have not tested the performance of the above.

Also maybe worth mentioning that ancestor lookup in eXist-db is very fast due to its DLN node numbering. So it may be worth testing if //set1//page is slower than say //page[ancestor::set1].

Upvotes: 4

Michael Kay
Michael Kay

Reputation: 163635

If $val and $altval are two sequences of values, then you can write the generic predicate

SOMETHING[some $i in 1 to count($val) satisfies (id=$val[$i] and doc=$altval[$i]]

But I've no idea how well it will perform.

Upvotes: 5

Related Questions