Spectactularly slow performance with db:open vs. db:text
Hello Basexers, I'm getting such a low performance on a relatively simple join between two databases that I feel there must be something going wrong here. I can provide the sources if necessary, but basically DB1 is 26 MB, about 80,000 small documents; DB2 is 47 MB, about 18,500 small documents. I'm using 8.4, by the way, haven't tested on other releases. Query 1 [returns in 144 minutes] ---------------------------------------- for $a in (db:open('DB1')/item/order-id) return if (db:open('DB2')//order-id[. = $a]) then $a else () 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.
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.
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.
Your second query executes in a fraction of a second - but only because the optimizer successfully rewrites it to use the TEXT index.
Exactly. A single context item reference (".") can be rewritten for index if the database statistics indicate that the addressed element – in your case order-id – has no other descendant elements. If it did have, you would get different results: see e.g.: <order-id>X<a/>Y</order-id> = 'XY' → true In your case, I assume that your database statistics have got outdated (e.g. due to updates), or there may be order-id elements with elements as children.
participants (2)
-
Christian Grün -
Hondros, Constantine (ELS-AMS)