RE: improve performance on large insert numbers

[SOA Work <so...@web.de>]

Sorry for answering so late. 
I was on holiday. Thank you for your suggestions.

I know that file is a lot faster, but we were comparing different approaches. So I wanted to use the optimal settings.
Your suggestions are intresting but I was just testing. 
I got about 10 000 inserts in under 2 seconds which is pretty fast and ok.

Thank you for your help

> -----Ursprüngliche Nachricht-----
> Von: "Derby Discussion" <de...@db.apache.org>
> Gesendet: 22.08.06 19:41:51
> An:  <mi...@sbcglobal.net>
> Betreff: RE: improve performance on large insert numbers


> And why do you want to use a database for this task?
> 
> I was waiting for someone to ask this, but since no one did I guess I have
> to do it... ;-)
> 
> What you've said, is that you wanted to load a lot of records in to a table,
> really, really fast. 
> 
> No indexes. Nor have you identified any type of query or extract
> requirements.
> 
> So why use a database for this function?
> 
> A general purpose RDBMS will not be the fastest method of capturing and
> storing this data.
> 
> What you may want to consider is capturing the initial data in to a flat
> file, then using a separate process, load this data in to an indexed
> database table(s) for future use.
> 
> Since you really didn't go into detail, let me use a hypothetical example of
> a solution that may parallel your problem...
> 
> Let me give you an example application.
> 
> Suppose your application is set up to log any and all TCP/IP traffic on your
> server. Depending on your server, there can be a lot of traffic.
> So your application writes to a file or set of files (round robin or
> something similar) with the traffic information. You then have a secondary
> application that tracks when a file buffer is full and then writes that
> buffer to the database. After completion, the second program tells the first
> that the file buffer is cleared for deletion and then the app can delete and
> reuse that buffer's name.
> 
> This way you then have indexes on the tables for fast retrieval and you can
> use things like triggers to set up alerts based on the data being inserted.
> 
> In essence you're writing to a buffer pool prior to storage in the database.
> (Only that in the event of a server crash, the data is safe(r) in the files
> of the buffer pool, rather than in memory....)
> 
> Informix had an interesting take on this. The Financial Foundation which
> included its real time loader. Instead of files, it used memory as its
> buffer. Of course those who implemented solutions would tee off the data to
> also write to a file too but that's another story.
> 
> 
> HTH.
> 
> -G
> 
> > -----Original Message-----
> > From: Mike Matrigali [mailto:mikem_app@sbcglobal.net]
> > Sent: Tuesday, August 22, 2006 11:08 AM
> > To: Derby Discussion
> > Subject: Re: improve performance on large insert numbers
> > 
> > is your machine cpu bound during the insert?  If not having log
> > on separate disk may help.
> > 
> > Does your app always load a large number of entries into an empty table,
> > or does it also load a large number of entries into an existing table
> > with entries.  If loading into an empty table then using one of the
> > import system routines with a vti would avoid logging.
> > 
> > SOA Work wrote:
> > > Hi there,
> > >
> > > I'm currently using derby to log values applying in my application.
> > > One value consists of a string, a date object and a double value. Not
> > much data. But I want to insert  a huge amount of entries.
> > >
> > > At the moment inserting 10 000 entries takes about 2 seconds. Now I
> > would like to here your opinion about how to improve performance (if
> > possible).
> > >
> > > Here the ideas I'm currently using (comment if you want)
> > >
> > > 1.) I use a table without indexes, primary keys and so on.
> > > "create table log(id varchar(50), value double, timestamp date)"
> > >
> > > 2.) I use the embedded mode
> > >
> > > 3.) I use a prepared statement "insert into log values (?, ?, ?)"
> > >
> > > 4.) I disabled auto commit and use addBatch for all 10000 entries. at
> > the end I call executeBatch() and commit()
> > >
> > > I would like to here your suggestions or comments.
> > > Best regards
> > >
> > > ______________________________________________________________________
> > > XXL-Speicher, PC-Virenschutz, Spartarife & mehr: Nur im WEB.DE Club!
> > 
> > > Jetzt gratis testen! http://freemail.web.de/home/landingpad/?mc=021130
> > >
> > >
> > >
> 
> 
> 


_____________________________________________________________________
Der WEB.DE SmartSurfer hilft bis zu 70% Ihrer Onlinekosten zu sparen!
http://smartsurfer.web.de/?mc=100071&distributionid=000000000066