Re: [MonetDB-users] Insert slowdown

Guillaume Theoret wrote:

For now a batch load would do but when I actually put this to use I'm going to be inserting (or updating if the key is already present) every 15 minutes by cron job which is why the growing insert time is a You should just be careful with setting transaction boundaries to avoid most of the overhead, e.g. autocommit, but this is standard db application writing.

little worrisome. I can see how maybe some months down the road the inserts could become so slow that they can't even complete before the next cron job starts overlapping. One of the tables I planned to use MonetDB with already has 23.3MM rows. It would be preferable to

Wrt to growth, it increase with about 20% over the old one each time, which means you won't see it happen that often. the number is not the issue, it is the way you interact with a database, and the number of columns, all this in the context of your overall system resources.

rewrite the cron job so that it wrote all the data into a csv file and bulk loaded it when done? bulk loading is fast. And you may consider to dump batches into their own table, e.g. by week/month and use a union view to glue the cheaply together.

regards, Martin

On Wed, Oct 7, 2009 at 10:15 AM, Martin Kersten wrote:

...

Guillaume Theoret wrote:

...

Hi, I have a question about insert performance.

At first, with an empty table, it took a little over 1 second (around 1.5) to insert 1000 rows. Now, with 5.2MM rows, it's taking ~65s.

Is this to be expected?

Yes, the table space grows over time, which calls for a re-alloc operation. This may involve writing/reading. The fastest way to insert 5.2 M records is to use the SQL query COPY 5200000 RECORDS INTO tt FROM....

...

Thanks, Guillaume

------------------------------------------------------------------------------ Come build with us! The BlackBerry(R) Developer Conference in SF, CA is the only developer event you need to attend this year. Jumpstart your developing skills, take BlackBerry mobile applications to market and stay ahead of the curve. Join us from November 9 - 12, 2009. Register now! http://p.sf.net/sfu/devconference _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ Come build with us! The BlackBerry(R) Developer Conference in SF, CA is the only developer event you need to attend this year. Jumpstart your developing skills, take BlackBerry mobile applications to market and stay ahead of the curve. Join us from November 9 - 12, 2009. Register now! http://p.sf.net/sfu/devconference _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ Come build with us! The BlackBerry(R) Developer Conference in SF, CA is the only developer event you need to attend this year. Jumpstart your developing skills, take BlackBerry mobile applications to market and stay ahead of the curve. Join us from November 9 - 12, 2009. Register now! http://p.sf.net/sfu/devconference _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users