db perf setup

if 0 {
-- Suppose a table: n char(30), v char(1).
CREATE TABLE t(n char(30), v char(1));
-- Create the table with appropriate indexes then fill 300,000 recs with
-- n=random 30 chars and v=random chars in range [A-Z].
INSERT INTO t(n, v)
WITH RECURSIVE tmp(n, v) AS (
SELECT 1, SUBSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZ', FLOOR((RANDOM() + 9223372036854775808) / 2.0 / 9223372036854775808 * 26) + 1, 1)
UNION ALL
SELECT n + 1, SUBSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZ', FLOOR((RANDOM() + 9223372036854775808) / 2.0 / 9223372036854775808 * 26) + 1, 1)
FROM tmp
LIMIT 300000 -- !!! Replace with the actual number of records !!!
)
SELECT (
WITH rand_string(n, v, chars) AS (
SELECT 1, '', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
UNION ALL
SELECT rand_string.n + 1, rand_string.v || SUBSTR(chars, FLOOR(( (0 * tmp.n) + RANDOM() + 9223372036854775808) / 2.0 / 9223372036854775808 * LENGTH(chars)) + 1, 1), chars
FROM rand_string
WHERE rand_string.n <= 30
)
SELECT rand_string.v
FROM rand_string
WHERE rand_string.n = 30
), tmp.v
FROM tmp;
CREATE INDEX tvn ON t(v, n);
ANALYZE t;
}

Recent Posts