0

Is it possible to write a 'simple' SQLite query that will take more than 5 seconds to run.

I am writing some unit-tests in my application to log slow queries, to that end, I would like a slow query that I know will take more than 5 or 10 seconds to run, (and create a warning to be raised/logged).

I was thinking of creating 10 tables with 10 entries in each table and doing a join select on all the table.
But I am not sure if that is a good way of testing a slow select, (rather than a large return set).

1

You should create a user-defined function that sleeps for the desired amount of time.

If you are restricted to build-in mechanisms, try using a recursive common table expression:

WITH RECURSIVE r(i) AS (
  VALUES(0)
  UNION ALL
  SELECT i FROM r
  LIMIT 1000000
)
SELECT i FROM r WHERE i = 1;

But the number of iterations needed for 5 s is strongly dependent on the CPU speed.

  • I wanted to use the built-in mechanisms rather than a user-defined function, the one you gave works great. Thanks – Simon Goodman Apr 11 '18 at 14:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.