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..
Marcus > > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
