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. > > 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 > -- Puneet Kishor http://www.punkish.org/ Carbon Model http://carbonmodel.org/ Charter Member, Open Source Geospatial Foundation http://www.osgeo.org/ Science Commons Fellow, Geospatial Data http://sciencecommons.org Nelson Institute, UW-Madison http://www.nelson.wisc.edu/ ----------------------------------------------------------------------- collaborate, communicate, compete ======================================================================= _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
