List:General Discussion« Previous MessageNext Message »
From:Mark Date:May 31 2002 1:24pm
Subject:Re: I need 50.000 inserts / second
View as plain text  
Cesar, you really should consider using placeholders and bind_param (if
available). Without using placeholders, the insert statement will contain
the literal values to be inserted and has to be re-prepared and re-executed
for each row. With placeholders, the insert statement only needs to be
prepared once. The bind values for each row can be given to the execute
method each time it's called. By avoiding the need to re-prepare the
statement for each row, the application typically runs many times faster.

BEFORE your loop, you prepare the query only once! Like this (in Perl),

$sth = $dbh->prepare ("INSERT into mynames (my_num, my_id) values (?, ?)");

Now, within your loop,

while (($name, $message_id) = each %posts) {
    $sth->execute ($name, $message_id);
}

This should yield some positive effects.

- Mark

        System Administrator Asarian-host.org

---
"If you were supposed to understand it,
we wouldn't call it code." - FedEx


----- Original Message -----
From: "Cesar Mello - Axi" <cesar@stripped>
To: <mysql@stripped>
Sent: Friday, May 31, 2002 6:49 AM
Subject: I need 50.000 inserts / second


> 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:
>
> 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();
>  }

Thread
I need 50.000 inserts / secondCesar Mello - Axi31 May
  • Re: I need 50.000 inserts / secondJeremy Zawodny31 May
  • Re: I need 50.000 inserts / secondSteve Edberg31 May
  • Re: I need 50.000 inserts / secondHarald Fuchs31 May
  • Re: I need 50.000 inserts / secondMark31 May
    • Re: I need 50.000 inserts / secondBenjamin Pflugmann31 May
    • Re: I need 50.000 inserts / secondDan Nelson31 May
      • Re: I need 50.000 inserts / secondJeremy Zawodny1 Jun
  • Re: I need 50.000 inserts / secondMark31 May
  • Re: I need 50.000 inserts / secondCesar Mello - Axi31 May
    • Re: I need 50.000 inserts / secondmos2 Jun
  • Re: I need 50.000 inserts / secondRichard Clarke2 Jun
RE: I need 50.000 inserts / seconddomi31 May
  • Re: I need 50.000 inserts / secondCesar Mello - Axi31 May
    • Re: I need 50.000 inserts / secondT├Ánu Samuel31 May
    • Re: I need 50.000 inserts / secondBrent Baisley31 May