Em 04/09/2015 17:09, "Domingo Alvarez Duarte" <sqlite-mail at dev.dadbiz.es> escreveu: > > 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 ?
Just for information Is json_extract from https://github.com/groner/sqlite-json ? Luiz > > 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(); > > ________ > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
