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, NULL, 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, %Q, %d)"; char *sql = NULL; time ( &tick1 ); sqlite3_exec ( db, "BEGIN", NULL, NULL, NULL ); for (i=0;i<num;i++) { sql = sqlite3_mprintf ( qf, i, "javer", "chengdu", 32*i ); ret = sqlite3_exec(db, sql, NULL, NULL, NULL); sqlite3_free (sql); } sqlite3_exec(db,"COMMIT",NULL,NULL,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, NULL, 0); ret = dbp->open(dbp, NULL, DB_FILE_BDB, NULL, DB_BTREE, DB_CREATE, 0664); time ( &tick1 ); for ( i=0; i<num; i++ ) { cust.c_id = i; strncpy(cust.name, "javer", 9); strncpy(cust.address, "chengdu", 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, NULL, &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, 0, sizeof(struct customer)); data.data = &cust; data.ulen = sizeof(struct customer); data.flags = DB_DBT_USERMEM; ret = dbp->get(dbp, NULL, &key, &data, 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 次, 时间为: %4ld s\n", num, tick2 - tick1 ); if(dbp != NULL) dbp->close(dbp, 0); return 0; } // 数据结构DBT在使用前，应首先初始化，否则编译可通过但运行时报参数错误 void init_dbt( DBT *key, DBT *data ) { memset(key, 0, sizeof(DBT)); memset(data, 0, sizeof(DBT)); } // DB的函数执行完成后，返回0代表成功，否则失败 void print_error(int r) { if(r != 0) printf("ERROR: %s\n",db_strerror(r)); } -- View this message in context: http://www.nabble.com/the-speed-of-embedded-database-engines%2C-sqlite3-vs-berkeley-db%2C-I%27m-confused-tp23209208p23209208.html Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
