Hello,
I made some preliminary tests for an application storing big
chunks of data in a sqlite database. I did firsts tests with python
and they gave me quite impressive results. I then tried to make the
same test using C. I expected to get execution times to be the same of
those of python. However I was surprised as the performance got a lot
worse, with execution times being more than 3 times more. I tried
everything I could think of and also peeked at python module's source
but i couldn't find any way to get C program performance to match
python's one. Any suggestion of what could i be doing wrong? I include
both python and C source code's. The only thing that this program does
is creating a database, making a table where a pair of integer maps
8192-bytes blobs and writing 100k rows in it. Any suggestions of what
I could be doing wrong?
Note: These are the results i get for Windows/MinGW environment. I
have no Linux box at hand at the moment.
------ Python results
bash-3.1$ python --version
Python 2.7rc2
bash-3.1$ time python testsqlite.py
0
10000
20000
30000
40000
50000
60000
70000
80000
90000
30.8555624521
real 0m31.249s
user 0m0.015s
sys 0m0.015s
------ C results
bash-3.1$ gcc --version
gcc.exe (GCC) 4.5.0
Copyright (C) 2010 Free Software Foundation, Inc.
This is free software; see the source for copying conditions. There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
bash-3.1$ gcc -O2 test.c sqlite/sqlite3.c && time ./a.exe
0
10000
20000
30000
40000
50000
60000
70000
80000
90000
real 2m33.376s
user 0m0.015s
sys 0m0.015s
------ Python script
conn = sqlite3.connect('test1.sqlite')
c=conn.cursor()
c.executescript('''CREATE TABLE IF NOT EXISTS values_log(acquisition
INTEGER,chunk INTEGER, acq_data BLOB);
CREATE INDEX IF NOT EXISTS values_step ON
values_log(acquisition,chunk);
''' )
conn.commit()
def f():
data="01234567"*1024
with conn:
for i in range(0,100000):
conn.execute("INSERT INTO values_log VALUES (?,?,?)",(1,i,data))
if not i%10000: print i
conn.commit()
ret=timeit.timeit(f,'gc.enable()',number=1)
print ret
------- C source
#include "sqlite3.7.6.3/sqlite3.h"
sqlite3* db;
char* db_err;
int main(int argc,const char *argv)
{
int ret;
sqlite3_stmt *db_stm;
sqlite3_open("testDB.sql", &db);
if(SQLITE_OK!=(ret=sqlite3_exec(db, "create table if not exists
'helloworld' (acq integer, chunk integer, data blob);"
"CREATE INDEX IF NOT EXISTS
acq_index ON helloworld(acq,chunk);"
, NULL, 0, &db_err)))
{
fprintf(stderr,"sqlite error in sqlite3_exec() [%d]",ret);
return -1;
}
if(SQLITE_OK!=(ret=sqlite3_exec(db, "PRAGMA synchronous=OFF;", NULL,
0, &db_err)))
{
fprintf(stderr,"sqlite error in sqlite3_exec() [%d]",ret);
return -1;
}
if(SQLITE_OK!=(ret=sqlite3_exec(db, "begin transaction;", NULL, 0, &db_err)))
{
fprintf(stderr,"sqlite error in sqlite3_exec() [%d]",ret);
return -1;
}
if(SQLITE_OK!=(ret=sqlite3_prepare_v2(db,
"INSERT INTO helloworld VALUES (?,?,?)",
-1,
&db_stm,
NULL
)))
{
fprintf(stderr,"sqlite error in prepare() [%d]",ret);
return -1;
};
int i;
char data[1024*8+1];
for(i=0;i<1024*8;i++)data[i]='0';
data[1024*8]='\0';
for(i=0;i<100000;i++)
{
if(!(i%10000))printf("%d\n",i);
if(SQLITE_OK!=(ret=sqlite3_bind_int(db_stm, 1, 1)))
{
fprintf(stderr,"sqlite error in bind()");
return -1;
}
if(SQLITE_OK!=(ret=sqlite3_bind_int(db_stm, 2, i)))
{
fprintf(stderr,"sqlite error in bind()");
return -1;
}
//if(SQLITE_OK!=(ret=sqlite3_bind_blob(db_stm, 3, data,-1,
SQLITE_STATIC/*SQLITE_TRANSIENT*/)))
if(SQLITE_OK!=(ret=sqlite3_bind_blob(db_stm, 3, data,8192,
SQLITE_STATIC/*SQLITE_TRANSIENT*/)))
{
fprintf(stderr,"sqlite error in bind_blob() [%d] ok=%d",ret,SQLITE_OK);
return -1;
}
ret=sqlite3_step(db_stm);
if(ret!=SQLITE_DONE)
{
fprintf(stderr,"sqlite error in sqlite3_step() [%d]",ret);
return -1;
}
if(SQLITE_OK!=(ret=sqlite3_reset(db_stm)))
{
fprintf(stderr,"sqlite error in sqlite3_reset() [%d]",ret);
return -1;
}
sqlite3_clear_bindings(db_stm);
}
if(SQLITE_OK!=(ret=sqlite3_exec(db, "commit;", NULL, 0, &db_err)))
{
fprintf(stderr,"sqlite error in sqlite3_exec() [%d]",ret);
return -1;
}
sqlite3_close(db);
}
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
