Re: [basex-talk] Spectactularly slow performance with db:open vs. db:text

Hello Christian,

Your second query executes in a fraction of a second - but only because the optimizer successfully rewrites it to use the TEXT index. When I remove the TEXT index so that the optimizer uses db:open-pre(), then the query takes some 88 minutes to run. (I did improve it to use a direct path).

On the other hand, I did some benchmarking against different sized lookups. I've discovered that I am averageing just over 10 lookups per second against DB2 using db:open-pre, which is independent of the number of lookups performed per query. Presumably this is just an expensive sort of query to repeat thousands of times, so the major learning for me is to double-check that queries are rewritten against the TEXT index where possible.

Thanks for helping me think this through.

C.

-----Original Message----- From: Christian Grün [mailto:christian.gruen@gmail.com] Sent: 15 February 2016 10:44 To: Hondros, Constantine (ELS-AMS) Cc: BaseX Subject: Re: [basex-talk] Spectactularly slow performance with db:open vs. db:text

Hi Constantine,

for $a in (db:open('DB1')/item/order-id) return if (db:open('DB2')//order-id[. = $a]) then $a else ()

Do some of the order-id elements contain descendant elements?

db:open('DB1')/item/order-id[*]

If yes, the following query might be faster:

for $a in (db:open('DB1')/item/order-id) return if (db:open('DB2')//order-id[text() = $a]) then $a else ()

Here is another way to rewrite the query:

for $a in (db:open('DB1')/item/order-id) where db:open('DB2')//order-id[text() = $a] return $a

Note that the optimized query uses db:open-pre to access DB2. When I re-write the query myself to use the TEXT index then performance is excellent. But why such a difference?

Query 2 [returns in 0.3 second]

---

for $a in (db:open('DB1')/item/order-id)

return

if (db:text('DB2', $a)/parent::order-id) then

$a

else

()

---

Elsevier B.V. Registered Office: Radarweg 29, 1043 NX Amsterdam, The Netherlands, Registration No. 33156677, Registered in The Netherlands.

________________________________

Elsevier B.V. Registered Office: Radarweg 29, 1043 NX Amsterdam, The Netherlands, Registration No. 33156677, Registered in The Netherlands.