Re: [sqlite] the speed of embedded database engines, sqlite3 vs berkeley db, I'm confused

[Giacomo Mussati]

"Matthew L. Creech"  wrote
in message
news:5ee96a840904271946o315df05dxb45024d5c0474...@mail.gmail.com...
> On Mon, Apr 27, 2009 at 10:25 PM, liubin liu
> <7101227-k+ct0dcb...@public.gmane.org> wrote:
>>
>> thanks
>>
>> I'm not sure of the real reason.
>> It's true that the speed of inserting with transaction is very fast. But
>> my
>> project is using SQLite mainly in selecting something. I don't know how
>> to
>> use transaction in the situation. May some friends give me some codes on
>> that?
>>
>
> As someone else mentioned, you probably don't want to open/close the
> database every time you do a single SELECT.  In the example you're
> doing 100,000 iterations of open/exec/close, which will give pretty
> terrible performance.
>
> Instead, try creating an initialization call, which opens the
> database, prepare()s your SELECT stmt (with a '?' placeholder for the
> ID in this example), and returns a handle to the caller.  The caller
> can then loop 100,000 times calling getdata() with this handle.
> getdata() can just do sqlite3_bind_XXX() followed by sqlite3_step()
> (and sqlite3_reset()), which will be _much_ faster than
> sqlite3_exec().
>
> -- 
> Matthew L. Creech
>

If he wants go faster he can use BEGIN TRANSACTION (EXCLUSIVE or other)
before the 100,000 iterations.
In this way sqlite doesn't lock the database file 100.000 times, but only 1
I don't know if he can use this trick, but maybe he can group 10, 50 or 100
select into a TRANSACTION.

Giacomo Mussati






 ___
> sqlite-users mailing list
> sqlite-users-czdrofg0bjidnm+yrof...@public.gmane.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

Re: [sqlite] the speed of embedded database engines, sqlite3 vs berkeley db, I'm confused

[Skipper Informatica]

"Matthew L. Creech"  wrote
in message
news:5ee96a840904271946o315df05dxb45024d5c0474...@mail.gmail.com...
> On Mon, Apr 27, 2009 at 10:25 PM, liubin liu
> <7101227-k+ct0dcb...@public.gmane.org> wrote:
>>
>> thanks
>>
>> I'm not sure of the real reason.
>> It's true that the speed of inserting with transaction is very fast. But
>> my
>> project is using SQLite mainly in selecting something. I don't know how
>> to
>> use transaction in the situation. May some friends give me some codes on
>> that?
>>
>
> As someone else mentioned, you probably don't want to open/close the
> database every time you do a single SELECT.  In the example you're
> doing 100,000 iterations of open/exec/close, which will give pretty
> terrible performance.
>
> Instead, try creating an initialization call, which opens the
> database, prepare()s your SELECT stmt (with a '?' placeholder for the
> ID in this example), and returns a handle to the caller.  The caller
> can then loop 100,000 times calling getdata() with this handle.
> getdata() can just do sqlite3_bind_XXX() followed by sqlite3_step()
> (and sqlite3_reset()), which will be _much_ faster than
> sqlite3_exec().
>
> -- 
> Matthew L. Creech
>

If he wants go faster he can use BEGIN TRANSACTION (EXCLUSIVE or other)
before the 100,000 iterations.
In this way sqlite doesn't lock the database file 100.000 times, but only 1
I don't know if he can use this trick, but maybe he can group 10, 50 or 100
select into a TRANSACTION.

Giacomo Mussati






 ___
> sqlite-users mailing list
> sqlite-users-czdrofg0bjidnm+yrof...@public.gmane.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

Re: [sqlite] the speed of embedded database engines, sqlite3 vs berkeley db, I'm confused

[Giacomo Mussati]

"Matthew L. Creech"  wrote
in message
news:5ee96a840904271946o315df05dxb45024d5c0474...@mail.gmail.com...
> On Mon, Apr 27, 2009 at 10:25 PM, liubin liu
> <7101227-k+ct0dcb...@public.gmane.org> wrote:
>>
>> thanks
>>
>> I'm not sure of the real reason.
>> It's true that the speed of inserting with transaction is very fast. But
>> my
>> project is using SQLite mainly in selecting something. I don't know how
>> to
>> use transaction in the situation. May some friends give me some codes on
>> that?
>>
>
> As someone else mentioned, you probably don't want to open/close the
> database every time you do a single SELECT.  In the example you're
> doing 100,000 iterations of open/exec/close, which will give pretty
> terrible performance.
>
> Instead, try creating an initialization call, which opens the
> database, prepare()s your SELECT stmt (with a '?' placeholder for the
> ID in this example), and returns a handle to the caller.  The caller
> can then loop 100,000 times calling getdata() with this handle.
> getdata() can just do sqlite3_bind_XXX() followed by sqlite3_step()
> (and sqlite3_reset()), which will be _much_ faster than
> sqlite3_exec().
>
> -- 
> Matthew L. Creech
>

If he wants go faster he can use BEGIN TRANSACTION (EXCLUSIVE or other)
before the 100,000 iterations.
In this way sqlite doesn't lock the database file 100.000 times, but only 1
I don't know if he can use this trick, but maybe he can group 10, 50 or 100
select into a TRANSACTION.

Giacomo Mussati






 ___
> sqlite-users mailing list
> sqlite-users-czdrofg0bjidnm+yrof...@public.gmane.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

Re: [sqlite] the speed of embedded database engines, sqlite3 vs berkeley db, I'm confused

[Giacomo Mussati]

"Matthew L. Creech"  wrote
in message
news:5ee96a840904271946o315df05dxb45024d5c0474...@mail.gmail.com...
> On Mon, Apr 27, 2009 at 10:25 PM, liubin liu
> <7101227-k+ct0dcb...@public.gmane.org> wrote:
>>
>> thanks
>>
>> I'm not sure of the real reason.
>> It's true that the speed of inserting with transaction is very fast. But
>> my
>> project is using SQLite mainly in selecting something. I don't know how
>> to
>> use transaction in the situation. May some friends give me some codes on
>> that?
>>
>
> As someone else mentioned, you probably don't want to open/close the
> database every time you do a single SELECT.  In the example you're
> doing 100,000 iterations of open/exec/close, which will give pretty
> terrible performance.
>
> Instead, try creating an initialization call, which opens the
> database, prepare()s your SELECT stmt (with a '?' placeholder for the
> ID in this example), and returns a handle to the caller.  The caller
> can then loop 100,000 times calling getdata() with this handle.
> getdata() can just do sqlite3_bind_XXX() followed by sqlite3_step()
> (and sqlite3_reset()), which will be _much_ faster than
> sqlite3_exec().
>
> -- 
> Matthew L. Creech
>

If he wants go faster he can use BEGIN TRANSACTION (EXCLUSIVE or other)
before the 100,000 iterations.
In this way sqlite doesn't lock the database file 100.000 times, but only 1
I don't know if he can use this trick, but maybe he can group 10, 50 or 100
select into a TRANSACTION.

Giacomo Mussati






 ___
> sqlite-users mailing list
> sqlite-users-czdrofg0bjidnm+yrof...@public.gmane.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

Re: [sqlite] the speed of embedded database engines, sqlite3 vs berkeley db, I'm confused

[Matthew L. Creech]

On Tue, Apr 28, 2009 at 3:25 AM, liubin liu <7101...@sina.com> wrote:
>
> Thanks!
>
> It sounds pretty good. But I don't understand it exactly. Could you write
> down the sample codes?
>

Please see the documentation here:

http://sqlite.org/c3ref/funclist.html

You'll have something along the lines of (just a sketch, obviously):

typedef struct {
sqlite3 *db;
sqlite3_stmt *stmt;
} my_handle_t;

my_handle_t my_init() {
sqlite3_open();
sqlite3_prepare();
return handle;
}

void my_exec(my_handle_t handle, int id) {
sqlite3_bind_int();
/* Put in a loop or whatever: */
sqlite3_step();
/* After you've gotten all the result rows: */
sqlite3_reset();
}


Your caller would then call my_init() once to get a handle, then call
my_exec() a bunch of times using that handle.  The way your example is
doing it, _every_ time through the loop it does an exec(), which
re-compiles the same SQL code (which is not a fast operation).

There are plenty of other examples floating around on this mailing
list, I'm sure - just do some digging.

-- 
Matthew L. Creech
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] the speed of embedded database engines, sqlite3 vs berkeley db, I'm confused

[liubin liu]

Thanks!

It sounds pretty good. But I don't understand it exactly. Could you write
down the sample codes?



Matthew L. Creech wrote:
> 
> On Mon, Apr 27, 2009 at 10:25 PM, liubin liu <7101...@sina.com> wrote:
>>
>> thanks
>>
>> I'm not sure of the real reason.
>> It's true that the speed of inserting with transaction is very fast. But
>> my
>> project is using SQLite mainly in selecting something. I don't know how
>> to
>> use transaction in the situation. May some friends give me some codes on
>> that?
>>
> As someone else mentioned, you probably don't want to open/close the
> database every time you do a single SELECT.  In the example you're
> doing 100,000 iterations of open/exec/close, which will give pretty
> terrible performance.
> 
> Instead, try creating an initialization call, which opens the
> database, prepare()s your SELECT stmt (with a '?' placeholder for the
> ID in this example), and returns a handle to the caller.  The caller
> can then loop 100,000 times calling getdata() with this handle.
> getdata() can just do sqlite3_bind_XXX() followed by sqlite3_step()
> (and sqlite3_reset()), which will be _much_ faster than
> sqlite3_exec().
> 
> -- 
> Matthew L. Creech
> ___
> 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-tp23209208p23271593.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

Re: [sqlite] the speed of embedded database engines, sqlite3 vs berkeley db, I'm confused

[Matthew L. Creech]

On Mon, Apr 27, 2009 at 10:25 PM, liubin liu <7101...@sina.com> wrote:
>
> thanks
>
> I'm not sure of the real reason.
> It's true that the speed of inserting with transaction is very fast. But my
> project is using SQLite mainly in selecting something. I don't know how to
> use transaction in the situation. May some friends give me some codes on
> that?
>

As someone else mentioned, you probably don't want to open/close the
database every time you do a single SELECT.  In the example you're
doing 100,000 iterations of open/exec/close, which will give pretty
terrible performance.

Instead, try creating an initialization call, which opens the
database, prepare()s your SELECT stmt (with a '?' placeholder for the
ID in this example), and returns a handle to the caller.  The caller
can then loop 100,000 times calling getdata() with this handle.
getdata() can just do sqlite3_bind_XXX() followed by sqlite3_step()
(and sqlite3_reset()), which will be _much_ faster than
sqlite3_exec().

-- 
Matthew L. Creech
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] the speed of embedded database engines, sqlite3 vs berkeley db, I'm confused

[liubin liu]

thanks

I'm not sure of the real reason.
It's true that the speed of inserting with transaction is very fast. But my
project is using SQLite mainly in selecting something. I don't know how to
use transaction in the situation. May some friends give me some codes on
that?

My application needs some funcs that serve as the database API, so I must
encapsulate SQLite func in my func. It includes the basic routine like this:
1.) sqlite3_open();
2.) sqlite3_get_table(),  sqlite3_exec()+callback, 
sqlite3_prepare_v2()+sqlite3_step()+sqlite3_column*+sqlite3_finalize()
3.) sqlite3_close();

I found that every routine running in the object board (Atmel 9260 (200MHz)
+ 128M RAM + ...) spend 20 millisecond. It's too slow...

And I have tried some PRAGMA settings for example: cache_size/page_size, but
the effect is very few.



John Stanton-3 wrote:
> 
> P Kishor wrote:
>> On Fri, Apr 24, 2009 at 5:27 AM, John Stanton 
>> 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.
>>
>>   
> Guess what make BDB run faster - no transactions. If you are not using
> transactions BDB mght be for you. Personally as a long time user of BDB
> in various ways I have a very poor opinion of it. It has a very
> different application to Sqlite.
> 
> 
> ___
> 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-tp23209208p23269006.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

Re: [sqlite] the speed of embedded database engines, sqlite3 vs berkeley db, I'm confused

[John Stanton]

P Kishor wrote:
> On Fri, Apr 24, 2009 at 5:27 AM, John Stanton  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.
>
>   
Guess what make BDB run faster - no transactions. If you are not using
transactions BDB mght be for you. Personally as a long time user of BDB
in various ways I have a very poor opinion of it. It has a very
different application to Sqlite.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] the speed of embedded database engines, sqlite3 vs berkeley db, I'm confused

[P Kishor]

On Fri, Apr 24, 2009 at 5:27 AM, John Stanton  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 
>> #include  // for system
>> #include  // for memset strcpy
>> #include  // for time
>>
>> #include  // for Sqlite3
>> #include  // 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,  );
>>   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 (  );
>>   for ( i=0; i>   {
>>   ret = sqlite3_open ( DB_FILE_SQLITE,  );
>>   ret = getdata_sqlite ( db, _data );
>>   sqlite3_close (db);
>>   }
>>   time (  );
>>   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> //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 

Re: [sqlite] the speed of embedded database engines, sqlite3 vs berkeley db, I'm confused

[John Stanton]

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 
> #include  // for system
> #include  // for memset strcpy
> #include  // for time
>
> #include  // for Sqlite3
> #include  // 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,  );
>   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 (  );
>   for ( i=0; i   {
>   ret = sqlite3_open ( DB_FILE_SQLITE,  );
>   ret = getdata_sqlite ( db, _data );
>   sqlite3_close (db);
>   }
>   time (  );
>   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 //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 (  );
>   sqlite3_exec ( db, "BEGIN", NULL, NULL, NULL );
>   for (i=0;i   {
>   sql = sqlite3_mprintf ( qf, i, "javer", "chengdu", 32*i );
>   ret = sqlite3_exec(db, sql, NULL, NULL, NULL);
>   sqlite3_free (sql);
>   }
>   

Re: [sqlite] the speed of embedded database engines, sqlite3 vs berkeley db, I'm confused

[liubin liu]

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 
>> #include  // for system
>> #include  // for memset strcpy
>> #include  // for time
>>
>> #include  // for Sqlite3
>> #include  // 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,  );
>>  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 (  );
>>  for ( i=0; i>  {
>>  ret = sqlite3_open ( DB_FILE_SQLITE,  );
>>  ret = getdata_sqlite ( db, _data );
>>  sqlite3_close (db);
>>  }
>>  time (  );
>>  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> //   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 

Re: [sqlite] the speed of embedded database engines, sqlite3 vs berkeley db, I'm confused

[Marcus Grimm]

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 
> #include  // for system
> #include  // for memset strcpy
> #include  // for time
>
> #include  // for Sqlite3
> #include  // 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,  );
>   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 (  );
>   for ( i=0; i   {
>   ret = sqlite3_open ( DB_FILE_SQLITE,  );
>   ret = getdata_sqlite ( db, _data );
>   sqlite3_close (db);
>   }
>   time (  );
>   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 //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 (  );
>   sqlite3_exec ( db, "BEGIN", NULL, NULL, NULL );
>   for (i=0;i   {
>   sql = sqlite3_mprintf ( qf, i, "javer", "chengdu", 32*i );
>   ret = 

Re: [sqlite] the speed of embedded database engines, sqlite3 vs berkeley db, I'm confused

[Eugene Wee]

Hi,

On Fri, Apr 24, 2009 at 11:35 AM, liubin liu <7101...@sina.com> 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?

Instead of using sqlite3_mprintf() and sqlite3_exec() for your insert
statements, try using prepared statements.

Regards,
Eugene Wee
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] the speed of embedded database engines, sqlite3 vs berkeley db, I'm confused

[RaghavendraK 70574]

sqlite has edge over BDB:
1) code readability is not as clean and clear as sqlite
2) forums not sure if they are responsive as sqlite
3) sqlite is stable and does not crash on-restarts, bdb make uses of
   mmap and its behaviour is undefined under diskful condition.
   get sigbus sometimes,sometimes throws error.
4) sqlite supports vfs,hence u can write your own layer to meet performance
5) too many parameters to configure in bdb. sqlite is zero config system.
6) easy to extend for later inclusions of alter table, bdb is key-value system
7) footprint is very small of sqlite compared with bdb.
8) sqlite does not support row or table level concurrency but bdb supports it 
well.
9) DB file is os independednt.

My opinion is sqlite is painfree system.

regards
ragha

**
 This email and its attachments contain confidential information from HUAWEI, 
which is intended only for the person or entity whose address is listed above. 
Any use of the information contained here in any way (including, but not 
limited to, total or partial disclosure, reproduction, or dissemination) by 
persons other than the intended recipient(s) is prohibited. If you receive this 
email in error, please notify the sender by phone or email immediately and 
delete it!
 
*

- Original Message -
From: liubin liu <7101...@sina.com>
Date: Friday, April 24, 2009 9:05 am
Subject: [sqlite]  the speed of embedded database engines, sqlite3 vs berkeley 
db, I'm confused
To: sqlite-users@sqlite.org

> 
> 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 
> #include  // for system
> #include  // for memset strcpy
> #include  // for time
> 
> #include  // for Sqlite3
> #include  // 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,  );
>   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;
>   

[sqlite] the speed of embedded database engines, sqlite3 vs berkeley db, I'm confused

[liubin liu]

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 
#include  // for system
#include  // for memset strcpy
#include  // for time

#include  // for Sqlite3
#include  // 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,  );
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 (  );
for ( i=0; iopen(dbp, NULL, DB_FILE_BDB, NULL, DB_BTREE, DB_CREATE, 
0664);

time (  );