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. liubin liu wrote: > 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)); > } > > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
