0

I have two queries I need to execute, which are identical except for the limit which is offset slightly. They look something like

FOR u IN users
  FILTER u.id == "ID"
  SORT u.score DESC LIMIT 5
  RETURN u

only the second one is offset (e.g. LIMIT 20, 3) from the first. When I profile these queries separately, they're running at 0.2 ms. But when I try to combine them into one query using sub-queries, it slows down tenfold to 2 ms (one sub-query is 0.5 ms, other is still 0.2 ms). I've been doing something like

RETURN APPEND(subquery_1, subquery_2)

is there some problem with how I'm combining these or are sub-queries just slower?

ElJay
  • 347
  • 4
  • 17
  • I suggest to use 'EXPLAIN' on the individual and the combined queries to see how the DB handles each one; – Tom Regner Feb 27 '20 at 09:37
  • I believe I said I was profiling the queries already, which is a step above explaining them. Please fully read questions before commenting. – ElJay Feb 27 '20 at 10:14
  • 'Profiling' is ambiguous, the more if the only information you give is measured execution time with not the slightest hint that you looked at and compard the execution plans -- I'm sorry I tried to be helpful to you, it won't happen again, my bad. – Tom Regner Feb 27 '20 at 10:17
  • Personally profiling seemed relatively unambiguous to me, sorry if my wording caused any confusion. But yes I've seen the execution plans, and the same thing takes more time as a sub query, and there appears to be a one-time 0.5 ms overhead for sub-queries as well. – ElJay Feb 27 '20 at 10:32
  • 0 Is it really the subquery that takes longer, or is it the overhead that's to be expected (think of function call overhead in prog-languages)? 0.5 ms seems a bit much for that overhead (maybe an arango-dev can step in here (@stj can you shed a bit of light here?) -- do you have the possibility to test on other hardware? Is the arangodb version current? – Tom Regner Feb 27 '20 at 13:19
  • Yea it's arangodb version 3.6 on RocksDB. – ElJay Feb 27 '20 at 18:53
  • I tested a bit on my setup - wrapping a query in a subselect adds ~0.05-0.1ms to the execution time on my test machine -- not too bad IMHO; if your 0.5 is aberrant depends on the machine specs. – Tom Regner Feb 28 '20 at 06:38
  • This is really noticable only an very fast Queries with small result sets; A query taking ~110ms shows no significant difference in execution time. Given that the alternative is executing two queries and aggregating on the client, you're probably fine. Are your times measured on the production system, or will that be a beefier machine? – Tom Regner Feb 28 '20 at 06:50
  • The result set will always be small due to the nature of the queries and use of indexes and this is on an system equivalent in terms of resources as the production system. Thanks for the explanation. – ElJay Mar 06 '20 at 05:31

0 Answers0