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

Sjoerd Mullender sjoerd at acm.org
Wed Oct 6 15:40:00 CEST 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 at 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 at lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/monetdb-developers


-- 
Sjoerd Mullender

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 371 bytes
Desc: OpenPGP digital signature
URL: <http://www.monetdb.org/pipermail/developers-list/attachments/20101006/2085891f/attachment.sig>


More information about the developers-list mailing list