Write a slow SQLITE query to test timeout

Question

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).

Answer 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.

• Simon Goodman: I wanted to use the built-in mechanisms rather than a user-defined function, the one you gave works great. Thanks