[MonetDB-users] Strange performance behavior on bulk import with / without sequences
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.
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
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 https://lists.sourceforge.net/lists/listinfo/monetdb-users
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 !
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 Martin.Kersten@cwi.nlwrote:
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 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
Cimballi wrote:
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 !
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 <Martin.Kersten@cwi.nl 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
participants (2)
-
Cimballi -
Martin Kersten