Thanks I amend the code according to your message.
Marcus Grimm wrote: > > 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 > > -- View this message in context: http://www.nabble.com/speed-test%2C-Sqlite3-vs-BerkeleyDB%2C-I%27m-confused-tp23209208p23212195.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
