Re: [MonetDB-users] Strange performance behavior on bulk import with / without sequences

11 Mar 2009


      ...
Hi Martin,
I understand that it makes somoe more queries, but it doesn't explain 
the performance degradation.
I have 100 000 queries to create my entries, if I add 100 000 queries to 
get the next sequence number, it makes 2 * 100 000 queries, and if I 
apply a stupid rule, it makes 2 * 30 seconds = 1 minute, not 7:30 minutes !
Cimballi wrote:
that assumes that each query has the same amount of work to do,
which is not true. Only in special cases the system could perform a fast
query to derive the next value.
...
And, well, myabe I should not use a sequence, but so, what do you 
recommend to use instead of it ? The application generated id I used is 
just a hack for the tests...
Cimballi
On Wed, Mar 11, 2009 at 5:37 PM, Martin Kersten mailto:Martin.Kersten@cwi.nl> wrote:
Cimballi wrote:
     > Hi,
     >
     > I have a strange behavior with MonetDB and the JDBC driver, maybe you
     > have an explanation.
     >
     > Here is what I do :
     > I import about 100 000 rows in MonetDB using the JDBC driver and
    simple
     > SQL statements (not prepared) with Spring JDBC API.
    so you make 100.000 extra sql queries, that are parsed, optimized and
    executed. And indeed, this may call for a max() operation.
    This becomes expensive if you use auto-commit after each insert as well.
>
     > I made the test a first time using sequences, so before inserting the
     > data I query for the next sequence number and then I insert the data.
     > The second time, I generate the sequence number on the
    application side,
     > using a simple increment, so I directly insert the data.
     >
     > And the strange result :
     > In the first test, it took 7:30 minutes for the whole insert.
     > In the second test, it took only 30 seconds for the whole insert.
     >
     > So, it seems there is a problem with the select performance, and
    in fact
     > I saw this problem for another select operation.
     >
     > Do you have any idea about where it can come from ? Is there any
     > configuration option or the way I use the JDBC API ?
     >
     > Thanks,
     >
     > Cimballi
     >
     >
     >
    ------------------------------------------------------------------------
     >
     >
    ------------------------------------------------------------------------------
     > Apps built with the Adobe(R) Flex(R) framework and Flex
    Builder(TM) are
     > powering Web 2.0 with engaging, cross-platform capabilities.
    Quickly and
     > easily build your RIAs with Flex Builder, the Eclipse(TM)based
    development
     > software that enables intelligent coding and step-through debugging.
     > Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com
     >
     >
     >
    ------------------------------------------------------------------------
     >
     > _______________________________________________
     > MonetDB-users mailing list
     > MonetDB-users@lists.sourceforge.net
    mailto:MonetDB-users@lists.sourceforge.net
     > https://lists.sourceforge.net/lists/listinfo/monetdb-users
------------------------------------------------------------------------------
    Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are
    powering Web 2.0 with engaging, cross-platform capabilities. Quickly and
    easily build your RIAs with Flex Builder, the Eclipse(TM)based
    development
    software that enables intelligent coding and step-through debugging.
    Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com
    _______________________________________________
    MonetDB-users mailing list
    MonetDB-users@lists.sourceforge.net
    mailto:MonetDB-users@lists.sourceforge.net
    https://lists.sourceforge.net/lists/listinfo/monetdb-users
------------------------------------------------------------------------
------------------------------------------------------------------------------
Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are
powering Web 2.0 with engaging, cross-platform capabilities. Quickly and
easily build your RIAs with Flex Builder, the Eclipse(TM)based development
software that enables intelligent coding and step-through debugging.
Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com
------------------------------------------------------------------------
_______________________________________________
MonetDB-users mailing list
MonetDB-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/monetdb-users