Alessandro Marzocchi wrote:
> 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.
...snip...
> if(SQLITE_OK!=(ret=sqlite3_prepare_v2(db,
> "INSERT INTO helloworld VALUES (?,?,?)",
> -1,
> &db_stm,
> NULL
> )))
Since you're going to always insert 1 in the first
column, why not use:
INSERT INTO helloworld VALUES (1,?,?)
> {
> 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';
Since the data column also does not change,
you could bind it only once before entering
the for loop (rather than binding it at each loop
iteration)
> 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;
> }
Above bind is useless if you used:
INSERT INTO helloworld VALUES (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;
> }
above bind to blob can be moved outside the loop.
> 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);
Calling sqlite3_clear_binding(...) at each iteration
is not needed in your example. In fact, you should
not call it at all if you decide to move the bind to the blob
outside the loop.
That should make it faster.
-- Dominique
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
