OK...here's the benchmark...First...let's build the shell (I'm using 3.7.5)
cc -o shell -O -DHAVE_READLINE -DSQLITE_ENABLE_FTS4
-DSQLITE_ENABLE_FTS3_PARENTHESIS sqlite3.c shell.c -lpthread -ldl -lreadline
-lncurses
The one thing you can do with the table method is make the left-most character
an integer which speeds things up a notch.
Let's generate some sql data
#include "sqlite3.h"
int main(int argc,char *argv[])
{
char buf[4096];
char sql[8192];
int i;
for(i=0;i<4096;++i) {
if ((i+1)%6==0) buf[i]=' ';
else buf[i]='X';
}
buf[4095]=0;
for(i=0;i<1000000;++i) {
buf[0]='a'+(i%26);
if (argc == 1)
sprintf(sql,"insert into text values('%s',%d);",buf,buf[0]);
else
sprintf(sql,"insert into ftext values('%s');",buf);
printf("%s\n",sql);
}
}
cc -o mydata mydata.c
./mydata >data1.sql
./mydata arg >data2.sql
./shell text1.db
create table text (t text,left integer);
create virtual table ftext using fts3(t text);
begin;
.read data1.sql
commit;
create index left_index on text(left);
select count(*) from text where left=97;
38462
CPU Time: user 0.005999 sys 0.002999
select count(*) from text where left=98;
38462
CPU Time: user 0.007999 sys 0.001000
select count(*) from text where left=99;
38462
CPU Time: user 0.006999 sys 0.000000
create virtual table ftext using fts3(t text);
begin;
.read data2.sql
commit;
.timer on
sqlite> select count(*) from ftext where t match 'a*';
38462
CPU Time: user 0.008999 sys 0.000000
select count(*) from ftext where t match 'b*';
38462
CPU Time: user 0.007998 sys 0.001000
select count(*) from ftext where t match 'c*';
38462
CPU Time: user 0.008999 sys 0.000999
Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Petite Abeille [petite.abei...@gmail.com]
Sent: Sunday, October 09, 2011 10:20 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Can pre-sorted data help?
On Oct 9, 2011, at 5:07 PM, Black, Michael (IS) wrote:
> Your assumption is that it is.
Why are you assuming that I'm assuming? Is that an assumption? 8^)
In any case, looking forward for your benchmark :)
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
