[sqlite] the speed of embedded database engines, sqlite3 vs

our project is using sqlite3,but found that the speed is too slow.
I just tested the BerkeleyDB,and found that the speed is very fast. But I
knew the sqlite3 is fast enough. And so I'm confused.
I may be using sqlite3 in wrong way?
anyway,next is my test code. I'm glad to receive your message.
______________________________________________________________________________
// http://www.ibm.com/developerworks/cn/linux/l-embdb/

////////////////////////////// head ///////////////////////////////
#include <stdio.h>
#include <stdlib.h> // for system
#include <string.h> // for memset strcpy
#include <time.h> // for time

#include <sqlite3.h> // for Sqlite3
#include <db.h> // for Berkeley DB



//////////////////////////// macro and struct /////////////////////////////
#define DB_FILE_SQLITE "test_sqlite_0.1.db"
#define DB_FILE_BDB "test_bdb_0.1.db"

struct customer
{
int c_id;
char name[10];
char address[20];
int age;
};


////////////////////////////// global variable ///////////////////////////////

sqlite3 *db = NULL;

int ret = -1; // 各函数返回值


////////////////////////////// func proto ///////////////////////////////

void way01(); // 打开、关闭的影响


///////// sqlite3 //////////

int sqlite_createtb(sqlite3 *db);
int sqlite_insertdb(sqlite3 *db);

int getdata_sqlite ( sqlite3 *db,struct customer *tb1 );


/////// berkeley db ////////

int bdb_createdb(); // 包含了插入数据

void print_error(int r);
void init_dbt( DBT *key,DBT *data );


/////////////////////////////// code ///////////////////////////////

int main ( void )
{
int c = 0;

system ( "rm -rf test_0.1.db" );
ret = sqlite3_open ( DB_FILE_SQLITE,&db );
ret = sqlite_createtb(db);
ret = sqlite_insertdb(db);
sqlite3_close (db);

printf ( "Sqlite3 / Berkeley DB,创建数据库 + 插入数据 ... 完成/n" );

printf ( "//////////////////////////////////////////////////n" );
printf ( "1 : 查询性能比较 - Berkeley DB 与 Sqlite3 ////n" );

while ( (c=getchar()) != 'q' )
{
switch (c)
{
case '1':
way01();
break;
default:
break;
}
}

system ( "rm -rf test_sqlite_0.1.db" );
system ( "rm -rf test_bdb_0.1.db" );

return 0;
}

///////////////////////////////////////////////////////////////
// 查询性能比较 - Berkeley DB 与 Sqlite3
void way01()
{
time_t tick1,tick2;

int i = 0;
int num = 1000*100;

struct customer tb_data;

///////////////////////////////////////////////////////////
time ( &tick1 );
for ( i=0; i<num; i++ )
{
ret = sqlite3_open ( DB_FILE_SQLITE,&db );
ret = getdata_sqlite ( db,&tb_data );
sqlite3_close (db);
}
time ( &tick2 );
printf("Sqlite3 : 打开、关闭并操作数据库文件 %d 次,时间为: %4ld s/n",num,tick2 -
tick1 );

///////////////////////////////////////////////////////////
bdb_createdb();
}

///////////////////////////////////////////////////////////////
void *callback(void *para,int col,char **value,char **colname )
{
// int i;
// for(i=0; i<col; i++){
// printf("%s,",(value[i] ? value[i] : "NULL") );
// }
// printf("col = %d/n",col);
return (void *) 0;
}
int getdata_sqlite ( sqlite3 *db,struct customer *tb1 )
{
char *sql = "SELECT * FROM table1 WHERE id=500;";
ret = sqlite3_exec ( db,sql,(void *)callback,NULL,NULL );
return 0;
}

///////////////////////////////////////////////////////////////
int sqlite_createtb( sqlite3 *db )
{
char *sql1 = "CREATE TABLE table1 (id INTEGER,name VARCHAR(10),address
VARCHAR(20),age INTEGER)";
ret = sqlite3_exec ( db,sql1,NULL );
return 0;
}
int sqlite_insertdb(sqlite3 *db)
{
time_t tick1,tick2;
int i = 0;
int num = 1000;

char *qf = "INSERT INTO table1 VALUES (%d,%Q,%d)";
char *sql = NULL;

time ( &tick1 );
sqlite3_exec ( db,"BEGIN",NULL );
for (i=0;i<num;i++)
{
sql = sqlite3_mprintf ( qf,i,"javer","chengdu",32*i );
ret = sqlite3_exec(db,NULL);
sqlite3_free (sql);
}
sqlite3_exec(db,"COMMIT",NULL);
time ( &tick2 );

return 0;
}



///////////////////////////////////////////////////////////////
int bdb_createdb()
{
time_t tick1,tick2;
int i = 0;
int num = 1000;
int key_cust_c_id = 500;

DB *dbp;
DBT key,data;
struct customer cust;


ret = db_create(&dbp,0);
ret = dbp->open(dbp,DB_FILE_BDB,DB_BTREE,DB_CREATE,
0664);

time ( &tick1 );
for ( i=0; i<num; i++ )
{
cust.c_id = i;
strncpy(cust.name,9);
strncpy(cust.address,19);
cust.age = 32*i;

init_dbt( &key,&data );

key.size = sizeof(int);
key.data = &(cust.c_id);

data.size = sizeof(struct customer);
data.data = &cust;

ret = dbp->put(dbp,&key,&data,DB_NOOVERWRITE);
print_error(ret);
}
time ( &tick2 );
//printf ( "Berkeley DB 打开+插入+关闭,耗时: %ld s/n",tick2-tick1 );


printf("Berkeley DB : 插入完成/n");
time ( &tick1 );
for ( i=0; i<num*10000; i++ )
{
init_dbt( &key,&data );

key.size = sizeof(int);
key.data = &key_cust_c_id;

memset(&cust,sizeof(struct customer));

data.data = &cust;
data.ulen = sizeof(struct customer);
data.flags = DB_DBT_USERMEM;

ret = dbp->get(dbp,0);
print_error(ret);

// printf("c_id = %d,name = %s,address = %s,age = %d./n",
// cust.c_id,cust.name,cust.address,cust.age);
}
time ( &tick2 );
printf("Berkeley DB : 打开、关闭并操作数据库文件 %d 次,tick2 -
tick1 );

if(dbp != NULL)
dbp->close(dbp,0);

return 0;
}
// 数据结构DBT在使用前，应首先初始化，否则编译可通过但运行时报参数错误
void init_dbt( DBT *key,DBT *data )
{
memset(key,sizeof(DBT));
memset(data,sizeof(DBT));
}
// DB的函数执行完成后，返回0代表成功，否则失败
void print_error(int r)
{
if(r != 0)
printf("ERROR: %s/n",db_strerror(r));
}

sqlite has edge over BDB:

1) code readability is not as clean and clear as sqlite
2) forums not sure if they are responsive as sqlite
3) sqlite is stable and does not crash on-restarts,bdb make uses of mmap and its behaviour is undefined under diskful condition. get sigbus sometimes,sometimes throws error.
4) sqlite supports vfs,hence u can write your own layer to meet performance
5) too many parameters to configure in bdb. sqlite is zero config system.
6) easy to extend for later inclusions of alter table,bdb is key-value system
7) footprint is very small of sqlite compared with bdb.
8) sqlite does not support row or table level concurrency but bdb supports it well.
9) DB file is os independednt.
My opinion is sqlite is painfree system.
regards
ragha

Instead of using sqlite3_mprintf() and sqlite3_exec() for your insert
statements,try using prepared statements.

Regards,
Eugene Wee

in your test code I see that you open the sqlite DB
each time you perform the single query.
I guess it would be fair to open the sqlite DB only once
outside your test loop.
2nd: You query against ID without an index,try either
define an index on ID or change your table definition
into "ID INTEGER PRIMARY KEY" which will have an index
on ID automatically,I think..

Sqlte provides COMMIT and ROLLBACK unlike Berkeley. If you can get by
without the advanced features of Sqlite,then use Berkely and take
advantage of its simplicity and faster execution.

Note that for best performance an Sqlite application should group
database inserts,deletes and updates into transactions.

On Fri,Apr 24,2009 at 5:27 AM,John Stanton <jo...@viacognis.com> wrote:
> Sqlte provides COMMIT and ROLLBACK unlike Berkeley. If you can get by
> without the advanced features of Sqlite,then use Berkely and take
> advantage of its simplicity and faster execution.

BDB does support transactions...

http://www.oracle.com/technology/documentation/berkeley-db/xml/gsg_xml_txn/cxx/usingtxns.html

In fact,BDB acts as the (a) transactional layer in MySQL (the
alternative is InnoDB).

Of course,other advantages of SQLite still apply. A key-value
metaphor can only be pushed so far.

Guess what make BDB run faster - no transactions. If you are not using
transactions BDB mght be for you. Personally as a long time user of BDB
in various ways I have a very poor opinion of it. It has a very
different application to Sqlite.

thanks

I'm not sure of the real reason.
It's true that the speed of inserting with transaction is very fast. But my
project is using SQLite mainly in selecting something. I don't know how to
use transaction in the situation. May some friends give me some codes on
that?

My application needs some funcs that serve as the database API,so I must
encapsulate SQLite func in my func. It includes the basic routine like this:
1.) sqlite3_open();
2.) sqlite3_get_table(),sqlite3_exec()+callback,
sqlite3_prepare_v2()+sqlite3_step()+sqlite3_column*+sqlite3_finalize()
3.) sqlite3_close();

I found that every routine running in the object board (Atmel 9260 (200MHz)
+ 128M RAM + ...) spend 20 millisecond. It's too slow...

And I have tried some PRAGMA settings for example: cache_size/page_size,but
the effect is very few.

As someone else mentioned,you probably don't want to open/close the
database every time you do a single SELECT. In the example you're
doing 100,000 iterations of open/exec/close,which will give pretty
terrible performance.

Instead,try creating an initialization call,which opens the
database,prepare()s your SELECT stmt (with a '?' placeholder for the
ID in this example),and returns a handle to the caller. The caller
can then loop 100,000 times calling getdata() with this handle.
getdata() can just do sqlite3_bind_XXX() followed by sqlite3_step()
(and sqlite3_reset()),which will be _much_ faster than
sqlite3_exec().

--
Matthew L. Creech

On Tue,Apr 28,2009 at 3:25 AM,liubin liu <7101...@sina.com> wrote:
>
> Thanks!
>
> It sounds pretty good. But I don't understand it exactly. Could you write
> down the sample codes?
>

Please see the documentation here:

http://sqlite.org/c3ref/funclist.html

You'll have something along the lines of (just a sketch,obviously):

typedef struct {
sqlite3 *db;
sqlite3_stmt *stmt;
} my_handle_t;

my_handle_t my_init() {
sqlite3_open();
sqlite3_prepare();
return handle;
}

void my_exec(my_handle_t handle,int id) {
sqlite3_bind_int();
/* Put in a loop or whatever: */
sqlite3_step();
/* After you've gotten all the result rows: */
sqlite3_reset();
}


Your caller would then call my_init() once to get a handle,then call
my_exec() a bunch of times using that handle. The way your example is
doing it,_every_ time through the loop it does an exec(),which
re-compiles the same SQL code (which is not a fast operation).

There are plenty of other examples floating around on this mailing
list,I'm sure - just do some digging.

--
Matthew L. Creech

If he wants go faster he can use BEGIN TRANSACTION (EXCLUSIVE or other) before the 100,000 iterations. In this way sqlite doesn't lock the database file 100.000 times,but only 1 I don't know if he can use this trick,but maybe he can group 10,50 or 100 select into a TRANSACTION. Giacomo Mussati

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!