2011/6/1 Dominique Pellé <dominique.pe...@gmail.com>:
> 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.
I used this structure as it is more representative of what the queries
the real application will do...
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
