Re: [sqlite] performance degradation from 3.6.1 to 3.6.2

[Steve Friedman]

Enrique Ramirez wrote:
> List stripped the attachment.
> 
> On Mon, Sep 15, 2008 at 12:27 PM, Steve Friedman <[EMAIL PROTECTED]> wrote:
>> Attached is an example program that demonstrates a significant performance
>> degradation when migrating from 3.6.1 to 3.6.2.  It can be compiled with
>>
>> gcc -Wall -O3 trial.c sqlite3.o -o trial -lpthread
>>
>> Steve Friedman
>>

I should have expected that.  Anyway, here it is...

#include <sqlite3.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sys/time.h>

static const char * const CREATE_TABLE =
   "CREATE TABLE IF NOT EXISTS data "
   "( a integer)";

static const char * const CREATE_INDEX =
   "CREATE INDEX data_index ON data (a)";

static const char * const DEL_SQL =
   "DELETE FROM data WHERE rowid in ("
   " SELECT rowid FROM data ORDER BY a limit 1)";

static const char * const ADD_SQL =
   "INSERT INTO data (a) VALUES (?1)";

void die(const char* msg)
{   printf("%s\n", msg);
     exit(1);
}

const int TABLE_COUNT = 50000;
const int ITERATIONS = 10000;

int main(int argc, char* argv[])
{   int i;
     sqlite3* db;
     sqlite3_stmt* del_stmt;
     sqlite3_stmt* add_stmt;
     if (sqlite3_open(":memory:", &db) != SQLITE_OK) 
die(sqlite3_errmsg(db));
     if (sqlite3_exec(db, CREATE_TABLE, NULL, NULL, NULL) != SQLITE_OK) 
die(sqlite3_errmsg(db));
     if (sqlite3_exec(db, CREATE_INDEX, NULL, NULL, NULL) != SQLITE_OK) 
die(sqlite3_errmsg(db));
     if (sqlite3_prepare(db, DEL_SQL, strlen(DEL_SQL), &del_stmt, NULL) 
!= SQLITE_OK) die(sqlite3_errmsg(db));
     if (sqlite3_prepare(db, ADD_SQL, strlen(ADD_SQL), &add_stmt, NULL) 
!= SQLITE_OK) die(sqlite3_errmsg(db));
     printf("initializing\n");
     for (i = 0; i < TABLE_COUNT; ++i)
     {   if (sqlite3_bind_int(add_stmt, 1, rand()) != SQLITE_OK) 
die(sqlite3_errmsg(db));
         if (sqlite3_step(add_stmt) != SQLITE_DONE) die(sqlite3_errmsg(db));
         if (sqlite3_reset(add_stmt) != SQLITE_OK) die(sqlite3_errmsg(db));
     }
     printf("timing\n");
     while (1)
     {   struct timeval start, end, diff;
         gettimeofday(&start, NULL);
         for (i = 0; i < ITERATIONS; ++i)
         {   if (sqlite3_step(del_stmt) != SQLITE_DONE) 
die(sqlite3_errmsg(db));
             if (sqlite3_reset(del_stmt) != SQLITE_OK) 
die(sqlite3_errmsg(db));
             if (sqlite3_bind_int(add_stmt, 1, rand()) != SQLITE_OK) 
die(sqlite3_errmsg(db));
             if (sqlite3_step(add_stmt) != SQLITE_DONE) 
die(sqlite3_errmsg(db));
             if (sqlite3_reset(add_stmt) != SQLITE_OK) 
die(sqlite3_errmsg(db));
         }
         gettimeofday(&end, NULL);
         timersub(&end,&start,&diff);
         double sec = (double)diff.tv_sec + (double)diff.tv_usec/1000000.;
         printf("%8d per second\n", (int)(1000000 / sec));
     }
}

_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users