Hello !
This is my first test with json and index expressions !
How to make usage of the expression index on queries ?
Why is json_extract so slow ?
Cheers !
________Output
Time to insert??? 5000??? 0.032227??? records by second = ???
155149
count=??? 4999
json=??? the_value_1
??? 0??? 0??? 0??? SCAN TABLE json_tbl
Time to select raw??? 5000??? 0.00244??? records by second = ???
2.04918e+06
Time to select json_extrat no index??? 5000??? 8.12196??? records by
second = ??? 615.615
Time to create index??? 5000??? 0.00605??? records by second =
??? 826446
??? 0??? 0??? 0??? SCAN TABLE json_tbl
Time to select json_extrat indexed??? 5000??? 7.38144??? records by
second = ??? 677.375
________
?
________Program
local max_count = 5000;
local sql = "CREATE TABLE IF NOT EXISTS json_tbl(id integer primary key, json
text collate nocase);";
local db = SQLite3(":memory:");
db.exec_dml(sql);
local stmt = db.prepare("insert into json_tbl(json) values(?);");
local start = os.clock();
db.exec_dml("begin;");
for(local i=1; i < max_count; ++i)
{
??? stmt.bind(1, format([==[{"id" : %d, "value" : "the_value_%d"}]==], i,
i));
??? stmt.step();
??? stmt.reset();
}
stmt.finalize();
db.exec_dml("commit;");
local time_spent = os.clock() -start;
print("Time to insert", max_count, time_spent, "records by second = ",
max_count/time_spent);
print("count=", db.exec_get_one("select count(*) from json_tbl"));
print("json=", db.exec_get_one("select json_extract(json, '$.value') AS val
from json_tbl? where val = 'the_value_1';"));
sql = "select? json_extract(json, '$.value') AS val? from json_tbl where
val = ?;";
local showPlan = function()
{
??? stmt = db.prepare("explain query plan " + sql);
??? while(stmt.next_row())
??? {
??? ??? local line = "";
??? ??? for(local i=0, len = stmt.col_count(); i < len; ++i)
??? ??? {
??? ??? ??? line += "\t" + stmt.col(i);
??? ??? }
??? ??? print(line);
??? }
??? stmt.finalize();
}
showPlan();
start = os.clock();
stmt = db.prepare("select * from json_tbl");
while(stmt.next_row())
{
}
stmt.finalize();
time_spent = os.clock() -start;
print("Time to select raw", max_count, time_spent, "records by second = ",
max_count/time_spent);
start = os.clock();
stmt = db.prepare(sql);
for(local i=1; i < max_count; ++i)
{
??? stmt.bind(1, format("the_value_%d", i));
??? stmt.step();
??? //print(stmt.col(0));
??? stmt.reset();
}
stmt.finalize();
time_spent = os.clock() -start;
print("Time to select json_extrat no index", max_count, time_spent, "records
by second = ", max_count/time_spent);
start = os.clock();
db.exec_dml("create index json_tbl_idx on json_tbl(json_extract(json,
'$.value'));");
time_spent = os.clock() -start;
print("Time to create index", max_count, time_spent, "records by second = ",
max_count/time_spent);
showPlan();
start = os.clock();
stmt = db.prepare(sql);
for(local i=1; i < max_count; ++i)
{
??? stmt.bind(1, format("the_value_%d", i));
??? stmt.step();
??? //print(stmt.col(0));
??? stmt.reset();
}
stmt.finalize();
time_spent = os.clock() -start;
print("Time to select json_extrat indexed", max_count, time_spent, "records
by second = ", max_count/time_spent);
db.close();
________
