[odb-users] how to use ODB for hight performance

[Herzl Shmuelian / Brilltech Int.]

Hi Boris,
thanks for your answer,
I rty to change my code for sending  from multiple threads like below

unsigned __stdcall silly( void *arg ){

database *pdb = (database *)arg;
 person jane ("222", "Doe", 32);
transaction t(pdb->begin());
for(unsigned int i = 0 ; i < 100000 ; i++){
pdb->persist (jane);
/*if (i % 100 == 0){
t.commit ();
t.reset (pdb->begin ());
  }*/
t.commit();
}
return 1;
}

int
main (int argc, char* argv[])
{
  {
  HANDLE hThread[10];
  unsigned threadID;

  database *pdb = (new odb::mysql::database
("odb_test","","odb_test","127.0.0.1"));

  time_t t1,t2;
  t1 = time(0);
  for(unsigned int i = 0 ; i < 10 ; i++)
  hThread[i] = (HANDLE)_beginthreadex( NULL, 0, &silly, pdb, 0, &threadID );

  for(unsigned int i = 0 ; i < 10 ; i++)
    WaitForSingleObject( hThread[i], INFINITE );

  t2 = time(0);
  cout<<"time:"<<t2-t1<<endl;
  return 0;
   }
}

this improve my performance this do the work in 35 sec
but when I add comment code "if (i % 100 == 0) etc" this decrease the
performace to 95 sec.
why this happend?

if what that I do is the right way for improve perfomace?

thanks Herzl.

On Tue, May 21, 2013 at 6:06 PM, Boris Kolpackov <boris at codesynthesis.com>wrote:

> Hi Herzl,
>
> Herzl Shmuelian / Brilltech Int. <hshmuelian at brilltech.com> writes:
>
> > I try to use it but I have Performance problem, and when I try to check
> the
> > performance by simple program like below:
> >
> > odb::mysql::database *pdb = new odb::mysql::database (
> >   "odb_test","","odb_test","127.0.0.1");
> > time_t t1;
> > time_t t2;
> > t1= time(0);
> > transaction t(pdb->begin());
> > person jane ("222", "Doe", 32);
> > for(unsigned int i = 0 ; i < 10000000 ; i++){
> >          pdb->persist (jane);
> >   }
> >                  t.comit();
> > t2= time(0);
> > cout<<"time:"t<<2-t1<<endl;
>
> One sure way to improve the performance of the above transaction is to
> split it into multiple batches. Persisting 1M of objects in a single
> transaction will definitely be slow.
>
> Here is an example (taken from the manual):
>
> transaction t(pdb->begin());
> person jane ("222", "Doe", 32);
>
> for(unsigned int i = 0 ; i < 10000000 ; i++)
> {
>   if (i % 100 == 0)
>   {
>     t.commit ();
>     t.reset (pdb->begin ());
>   }
>
>   pdb->persist (jane);
> }
>
> t.commit();
>
> Exactly how many objects to persist in each batch depends on your
> particular situation so it is a good idea to experiment with
> different sizes.
>
>
> > 1) open several connection to mysql
>
> ODB will do that if you request several connections at the same time.
> So if you can perform your persist() calls from multiple threads, then
> it may be worth a try. Before that, however, you may want to figure out
> what the bottleneck in your case is. For example, if the CPU utilization
> of your MySQL server is 100% then running persists from multiple threads
> won't do you any good.
>
>
> > 2) send several command together
>
> Are you referring to bulk INSERT here? If so, then MySQL doesn't support
> this feature. But if you use a database that supports it (e.g., Oracle or
> MS SQL), then, yes, that would definitely help. We plan to add ODB support
> for bulk INSERT for these databases in near future.
>
>
> > 3) unsync send commnad to mysql
>
> I maybe wrong, but I don't remember seeing any asynchronous statement
> execution support in the MySQL C API.
>
> Another option would be to switch to a different database. For example,
> I would expect SQLite, being an in-process database, to perform much
> better in this scenario.
>
> Boris
>