Hello ! Yes it is but I discover later some flaws on my assumptions look at further on this thread to see the end result.
Cheers ! > Sat Sep 05 2015 2:03:40 pm CEST CEST from "Luiz Am?rico" ><brandkarl at gmail.com> Subject: Re: [sqlite] First test of json and index >expressions, not so good > > 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 > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ?
