Re: [Monetdb-developers] Why the Speed of Mapi Lib is so lower?

6 Oct 2010

      On 2010-10-06 15:13, peter qin wrote:
...
Dear all,
       Yesterday,I had done a test about the speed of MonetDB.I used
the example code make a test.the red part is my adding code.
I don't see any red, but I'm guessing you added the for loop inserting
10000 records.
There are two main reasons this is slow:
- There are 10000 interactions with the server.  This takes time.
Normally the best way to enter data into MonetDB/SQL is to use the COPY
INTO command which can read from a CSV file.
- The server uses autocommit by default.  That means, every query is a
separate transaction.  You'll get a speed up if you first (before the
CREATE TABLE) call
    update(dbh, "START TRANSACTION");
and after the for loop call
    update(dbh, "COMMIT");
In this way you tell the server that the whole table creation and
filling is a single transaction, so you'll only get that overhead once.
...
#include <mapilib/Mapi.h>
     #include <stdio.h>
     #include <stdlib.h>
 void die(Mapi dbh, MapiHdl hdl)
 {
  if (hdl != NULL) {
   mapi_explain_query(hdl, stderr);
   do {
    if (mapi_result_error(hdl) != NULL)
     mapi_explain_result(hdl, stderr);
   } while (mapi_next_result(hdl) == 1);
   mapi_close_handle(hdl);
   mapi_destroy(dbh);
  } else if (dbh != NULL) {
   mapi_explain(dbh, stderr);
   mapi_destroy(dbh);
  } else {
   fprintf(stderr, "command failed\n");
  }
  exit(-1);
 }

 MapiHdl query(Mapi dbh, char *q)
 {
  MapiHdl ret = NULL;
  if ((ret = mapi_query(dbh, q)) == NULL || mapi_error(dbh) != MOK)
   die(dbh, ret);
  return(ret);
 }

 void update(Mapi dbh, char *q)
 {
  MapiHdl ret = query(dbh, q);
  if (mapi_close_handle(ret) != MOK)
   die(dbh, ret);
 }

 int main(int argc, char *argv[])
 {
     Mapi dbh;
     MapiHdl hdl = NULL;
  char *name;
  char *age;

     dbh = mapi_connect("localhost", 50000, "monetdb", "monetdb",

"sql", "demo");
         if (mapi_error(dbh))
             die(dbh, hdl);
  update(dbh, "CREATE TABLE emp (name VARCHAR(20), age INT)");

  for(int i = 0;i<10000;i++)
  {
  update(dbh, "INSERT INTO emp VALUES ('John', 23)");
  }
 //why this cast more then serveral minutes .can you help me?

  hdl = query(dbh, "SELECT * FROM emp");

     while (mapi_fetch_row(hdl)) {
         name = mapi_fetch_field(hdl, 0);
         age = mapi_fetch_field(hdl, 1);
         printf("%s is %s\n", name, age);
     }

     mapi_close_handle(hdl);
     mapi_destroy(dbh);

     return(0);
 }

Peter Qin
 peterqin1980@gmail.com

Beautiful is writing same markup. Internet Explorer 9 supports
standards for HTML5, CSS3, SVG 1.1,  ECMAScript5, and DOM L2 & L3.
Spend less time writing and  rewriting code and more time creating great
experiences on the web. Be a part of the beta today.
http://p.sf.net/sfu/beautyoftheweb
_______________________________________________
Monetdb-developers mailing list
Monetdb-developers@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/monetdb-developers
-- 
Sjoerd Mullender