Hi Jeremy,
I've done some tests grouping the INSERTs in groups of 1000 and 10000, and
it got very faster, in my Pentium III 700 from job I could get less than 3s.
But I'm still doing concatenation to get the INSERT clause in the form
"values (x,x), (x,x) ...". Is there a way to make a template query in
mysql++ that could take 1000 or more parameters??
Please tell me the alternative you were thinking about.
Best regards,
Cesar
----- Original Message -----
From: "Jeremy Zawodny" <[EMAIL PROTECTED]>
To: "Cesar Mello - Axi" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Thursday, May 30, 2002 9:04 PM
Subject: Re: I need 50.000 inserts / second
> On Fri, May 31, 2002 at 01:49:11AM -0300, Cesar Mello - Axi wrote:
> > Hello,
> >
> > I intend to use MySQL in a data acquisition software. The actual
> > version stores the acquired data straight in files. The sample rate
> > can get up to 50 kHz. I would like to know if there is some way to
> > improve MySQL insert rate. The following C++ code with mysql++
> > takes 5 seconds to execute in my Athlon 1.33 machine:
>
> Is the process disk bound or cpu bound? Put another way, does the cpu
> max out during this process?
>
> > sql_create_2 (teste1, 1, 2, double, datahora, double, valor1)
> >
> > int main() {
> > try { // its in one big try block
> >
> > Connection con(use_exceptions);
> > con.connect("cesar");
> > Query query = con.query();
> >
> > teste1 row;
> > // create an empty stock object
> >
> > for (int i=1;i<50000;i++)
> > {
> > row.datahora = (double) i;
> > row.valor1 = i / 1000;
> >
> > query.insert(row);
> > query.execute();
> > }
> >
> > As you can see there are only two fields: a double timestamp and a
> > double value. In the real application there are some more double
> > values. I need to decrease this time to less than 1 second. Is there
> > any kind of buffered inserts or maybe a way that I could pass a
> > matrix?
>
> You can boost the performance by inserting several records at the same
> time in one querey using MySQL's extended insert syntax:
>
> INSERT INTO my_table VALUES (foo, foo), (bar, bar), (z, z)...
>
> Try batching them in groups of 100 or even 1000 to see how it
> performs.
>
> > I'm shocked with the performance of MySQL, a similar query to
> > compute 1 million records takes 1.17 seconds in MySQL and around 6
> > seconds in the current system. So if I can decrease the insert time
> > I'll definetly use MySQL!
>
> Welcome to MySQL. :-)
>
> There's an alternative that I have in mind, but it'd be good to see
> how much closer those get you before getting into it.
>
> Jeremy
> --
> Jeremy D. Zawodny, <[EMAIL PROTECTED]>
> Technical Yahoo - Yahoo Finance
> Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936
>
> MySQL 3.23.51: up 1 days, processed 28,531,290 queries (274/sec. avg)
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
