Hello !
I was not been fair with my raw select against json_extract now I'm doing it
one by one and the difference with json_extract is basically none.
But I still could not manage to use the index expression to speedup the
query.
__________Output
Time to insert??? 5000??? 0.03179??? records by second = ???
157282
count=??? 4999
json=??? the_value_1
??? 0??? 0??? 0??? SCAN TABLE json_tbl
Time to select raw??? 5000??? 0.002456??? records by second = ???
2.03583e+06
Time to select raw one by one??? 5000??? 7.40657??? records by
second = ??? 675.076
Time to select json_extrat no index??? 5000??? 7.68341??? records by
second = ??? 650.753
Time to create index??? 5000??? 0.006101??? records by second =
??? 819538
??? 0??? 0??? 0??? SCAN TABLE json_tbl
Time to select json_extrat indexed??? 5000??? 7.68529??? records by
second = ??? 650.593
__________
__________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 raw one by one", 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);
/*
local function unql_exec(db, sql)
{
??? local the_stmt = db.prepare(sql);
??? local rc = the_stmt.step();
??? the_stmt.finalize();
??? return rc;
}
start = os.clock();
local db_unql = xjd1(db);
unql_exec(db_unql, "CREATE COLLECTION unql_json;");
stmt = db_unql.prepare("INSERT INTO unql_json VALUE ?;");
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();
stmt = db_unql.prepare("SELECT FROM unql_json WHERE unql_json.value = ?;");
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 unql", max_count, time_spent, "records by second = ",
max_count/time_spent);
*/
//db.backup("json.db");
db.close();
__________
