Parameters
| Setting | Value |
|---|
| Database | SQLite 3.50.4 (64-bit) |
| OS | Arch Linux x86_64 |
| CPU | AMD Ryzen 5 6600H |
| Table Rows | 208,561 |
Table Schema
CREATE TABLE "verse_text" (
"id" INTEGER,
"verse_id" INTEGER,
"version_id" INTEGER,
"text" TEXT
);
View Definition
CREATE VIEW v_passage AS
SELECT
ver.code,
b.book_name,
ba.abbreviation,
c.chapter_num,
v.verse_num,
vt.text,
vt.word_count,
vt.char_length
FROM verse v
JOIN verse_text vt ON vt.verse_id = v.id
JOIN version ver ON vt.version_id = ver.id
JOIN chapter c ON v.chapter_id = c.id
JOIN book b ON c.book_id = b.id
JOIN book_abbreviation ba ON ba.book_id = b.id
Experiment
10 warm-up runs followed by 100 timed runs for each query type.
Query Comparison
| Query Type |
Virtual Columns |
SELECT Calculated Column |
| Word Count |
SELECT
book_name,
AVG(word_count),
MAX(word_count),
MIN(word_count)
FROM v_passage
GROUP BY book_name;
|
SELECT
book_name,
AVG(LENGTH(text) - LENGTH(REPLACE(text, ' ', '')) + 1),
MAX(LENGTH(text) - LENGTH(REPLACE(text, ' ', '')) + 1),
MIN(LENGTH(text) - LENGTH(REPLACE(text, ' ', '')) + 1)
FROM v_passage
GROUP BY book_name;
|
| Character Count |
SELECT
book_name,
AVG(char_length),
MAX(char_length),
MIN(char_length)
FROM v_passage
GROUP BY book_name;
|
SELECT
book_name,
AVG(LENGTH(text)),
MAX(LENGTH(text)),
MIN(LENGTH(text))
FROM v_passage
GROUP BY book_name;
|
Results
Word Count - WHERE code = 1981
| Query | Avg (s) | Min (s) | Max (s) | StdDev (s) |
|---|
| Virtual Column | 0.0497 | 0.0489 | 0.0518 | 0.0005 |
| SELECT Calculation | 0.0917 | 0.0902 | 0.0956 | 0.0011 |
Word Count - WHERE code = 'NRSVUE'
| Query | Avg (s) | Min (s) | Max (s) | StdDev (s) |
|---|
| Virtual Column | 0.2119 | 0.2080 | 0.2350 | 0.0046 |
| SELECT Calculation | 0.3581 | 0.3515 | 0.3812 | 0.0059 |
Word Count - No WHERE Clause
| Query | Avg (s) | Min (s) | Max (s) | StdDev (s) |
|---|
| Virtual Column | 1.9352 | 1.9017 | 2.0299 | 0.0256 |
| SELECT Calculation | 1.7448 | 1.7168 | 1.8555 | 0.0267 |
Character Length - No WHERE Clause
| Query | Avg (s) | Min (s) | Max (s) | StdDev (s) |
|---|
| Virtual Column | 0.7181 | 0.7034 | 0.7447 | 0.0102 |
| SELECT Calculation | 0.7014 | 0.6857 | 0.7315 | 0.0110 |
Character Length - WHERE code = 'NRSVUE'
| Query | Avg (s) | Min (s) | Max (s) | StdDev (s) |
|---|
| Virtual Column | 0.1250 | 0.1234 | 0.1308 | 0.0013 |
| SELECT Calculation | 0.1663 | 0.1641 | 0.1753 | 0.0015 |
Character Length - WHERE code = 1981
| Query | Avg (s) | Min (s) | Max (s) | StdDev (s) |
|---|
| Virtual Column | 0.0279 | 0.0275 | 0.0313 | 0.0005 |
| SELECT Calculation | 0.0415 | 0.0409 | 0.0467 | 0.0006 |
Conclusion
When no predicates are applied, the normal SELECT calculation is generally faster. However, when predicates are applied over the version, the virtual column queries perform significantly faster.
When a column is declared as GENERATED ALWAYS AS (...), SQLite can internally index or cache some computation logic, even if the column is VIRTUAL. Even though virtual columns aren't physically stored, the query planner can optimize evaluation order more effectively when the expression is named in the schema versus dynamically written.
In other test cases, execution speed when using indexes appears to be identical. But in other cases, virtual columns provide an additional performance benefit over SELECT calculations, and vice-versa.
💡 Key Takeaway: Use virtual columns when queries include WHERE clauses on filtered datasets. Use SELECT calculations when querying the full table without filters.