speeding up UPDATE and INSERT

am 20.04.2004 22:23:53 von Matthias Eireiner

Hi there,

I'm currently writing a Data Mining program in Perl, my problem is that =
the
writing to the database takes awful long.
Are there any ideas how to speed up the inserting and the updating, =
besides
using the bind operator?!=09

the SQLstatement looks like

"INSERT INTO `Counts` (`id`, `date`, `count`, `avg)VALUES ('', =
CURDATE(),
'$count', '$avg')"

or=20

"UPDATE `Counts` SET `count` =3D '$count', `avg` =3D '$avg' WHERE `id` =
=3D '$id'"

so if anybody has an idea how to get this done faster (dumping the data =
to a
csv file first and updating all at once, or something like this) I would
love to hear it. thanks a lot in advance.

regards

Matthias

_________________________________________

Matthias Eireiner

email: matthias.eireiner@ieee.org

www.bvcapital.com
_________________________________________

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org

Re: speeding up UPDATE and INSERT

am 21.04.2004 00:57:02 von rasikaa1

Hi,

I also do a similar thing with my data pre-processing
scripts. I use a multiple insert command with INSERT
DELAYED. It has improved the performance by heaps. The
format is
INSERT DELAYED INTO table VALUES (c11, c12, c13),
(c21, c22, c23)....;

The INSERT DELAYED will delay the updates so that they
get buffered for the updations, but will be done
later. Also turning off indexing before you do the
insertions will help, but you will need to have them
on for updates.

Rasika
--- Matthias Eireiner
wrote: > Hi there,
>
> I'm currently writing a Data Mining program in Perl,
> my problem is that the
> writing to the database takes awful long.
> Are there any ideas how to speed up the inserting
> and the updating, besides
> using the bind operator?!
>
> the SQLstatement looks like
>
> "INSERT INTO `Counts` (`id`, `date`, `count`,
> `avg)VALUES ('', CURDATE(),
> '$count', '$avg')"
>
> or
>
> "UPDATE `Counts` SET `count` = '$count', `avg` =
> '$avg' WHERE `id` = '$id'"
>
> so if anybody has an idea how to get this done
> faster (dumping the data to a
> csv file first and updating all at once, or
> something like this) I would
> love to hear it. thanks a lot in advance.
>
> regards
>
> Matthias
>
>
> _________________________________________
>
> Matthias Eireiner
>
> email: matthias.eireiner@ieee.org
>
> www.bvcapital.com
> _________________________________________
>
>
> --
> MySQL Perl Mailing List
> For list archives: http://lists.mysql.com/perl
> To unsubscribe:
>
http://lists.mysql.com/perl?unsub=rasikaa@cse.mrt.ac.lk
>

____________________________________________________________
Yahoo! Messenger - Communicate instantly..."Ping"
your friends today! Download Messenger Now
http://uk.messenger.yahoo.com/download/index.html

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: speeding up UPDATE and INSERT

am 21.04.2004 00:57:02 von rasikaa1

Re: speeding up UPDATE and INSERT

am 21.04.2004 00:57:02 von rasikaa1

Hi,

I also do a similar thing with my data pre-processing
scripts. I use a multiple insert command with INSERT
DELAYED. It has improved the performance by heaps. The
format is
INSERT DELAYED INTO table VALUES (c11, c12, c13),
(c21, c22, c23)....;

The INSERT DELAYED will delay the updates so that they
get buffered for the updations, but will be done
later. Also turning off indexing before you do the
insertions will help, but you will need to have them
on for updates.

Rasika
--- Matthias Eireiner
wrote: > Hi there,
>
> I'm currently writing a Data Mining program in Perl,
> my problem is that the
> writing to the database takes awful long.
> Are there any ideas how to speed up the inserting
> and the updating, besides
> using the bind operator?!
>
> the SQLstatement looks like
>
> "INSERT INTO `Counts` (`id`, `date`, `count`,
> `avg)VALUES ('', CURDATE(),
> '$count', '$avg')"
>
> or
>
> "UPDATE `Counts` SET `count` = '$count', `avg` =
> '$avg' WHERE `id` = '$id'"
>
> so if anybody has an idea how to get this done
> faster (dumping the data to a
> csv file first and updating all at once, or
> something like this) I would
> love to hear it. thanks a lot in advance.
>
> regards
>
> Matthias
>
>
> _________________________________________
>
> Matthias Eireiner
>
> email: matthias.eireiner@ieee.org
>
> www.bvcapital.com
> _________________________________________
>
>
> --
> MySQL Perl Mailing List
> For list archives: http://lists.mysql.com/perl
> To unsubscribe:
>
http://lists.mysql.com/perl?unsub=rasikaa@cse.mrt.ac.lk
>

____________________________________________________________
Yahoo! Messenger - Communicate instantly..."Ping"
your friends today! Download Messenger Now
http://uk.messenger.yahoo.com/download/index.html

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org