How to speed UPDATES

am 01.04.2004 21:32:16 von Etienne Orliac

Dear all,

I have to update 1500 lines on a daily basis in a single table. My =
problem is that it takes a while to execute. All the updates are siimilar.

I've read that you can delay the updates and just execute once or =
something like that. However, I wasn't able to find any documentation =
about it.

So I would greatly appreciate some help.

Thanks a lot,

Etienne.

--
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: How to speed UPDATES

am 01.04.2004 21:52:52 von Ulrich Borchers

Hi Etienne,

can you please post the query and the table structure (create table, index=
es).
Maybe you can tweak this by creating indexes on the right
columns if you have not already done so. Even dropping an
index might help. I doubt that 1500 update queries take
so long even on large tables if you have the right indexes
created and data types selected.

Uli

On 1 Apr 2004 at 20:32, Etienne Orliac wrote:

> Dear all,
>
> I have to update 1500 lines on a daily basis in a single table. My prob=
lem is that it takes a while to execute. All the updates are siimilar.
>
> I've read that you can delay the updates and just execute once or somet=
hing like that. However, I wasn't able to find any documentation about it.
>
> So I would greatly appreciate some help.
>
> Thanks a lot,
>
> Etienne.
>
>
> --
> MySQL Perl Mailing List
> For list archives: http://lists.mysql.com/perl
> To unsubscribe: http://lists.mysql.com/perl?unsub=3Dubo1@gmx.de
>

--
Ulrich Borchers
Brandenberger Str.18
41065 Mönchengladbach
Tel. (0 21 61) 17 58 83
Mobil (0 179) 72 66 112

--
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: How to speed UPDATES

am 01.04.2004 21:52:52 von Ulrich Borchers

Re: How to speed UPDATES

am 01.04.2004 22:04:03 von lshen

Hi Etienne, also you may clean up your transaction log . This also may help.

Brian
----- Original Message -----
From: "Ulrich Borchers"
To: ; "Etienne Orliac"
Sent: Thursday, April 01, 2004 2:52 PM
Subject: Re: How to speed UPDATES

Hi Etienne,

can you please post the query and the table structure (create table,
indexes).
Maybe you can tweak this by creating indexes on the right
columns if you have not already done so. Even dropping an
index might help. I doubt that 1500 update queries take
so long even on large tables if you have the right indexes
created and data types selected.

Uli

On 1 Apr 2004 at 20:32, Etienne Orliac wrote:

> Dear all,
>
> I have to update 1500 lines on a daily basis in a single table. My
problem is that it takes a while to execute. All the updates are siimilar.
>
> I've read that you can delay the updates and just execute once or
something like that. However, I wasn't able to find any documentation about
it.
>
> So I would greatly appreciate some help.
>
> Thanks a lot,
>
> Etienne.
>
>
> --
> MySQL Perl Mailing List
> For list archives: http://lists.mysql.com/perl
> To unsubscribe: http://lists.mysql.com/perl?unsub=ubo1@gmx.de
>

--
Ulrich Borchers
Brandenberger Str.18
41065 Mönchengladbach
Tel. (0 21 61) 17 58 83
Mobil (0 179) 72 66 112

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe:
http://lists.mysql.com/perl?unsub=lshen@pop900.gsfc.nasa.gov

--
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: How to speed UPDATES

am 01.04.2004 22:04:03 von lshen

Re: How to speed UPDATES

am 01.04.2004 22:16:01 von Etienne Orliac

Dear all,

here is a description of the table.It contains 70.000 lines on 66 columns =
and 1500 lines should be added everyday.

The piece of code for the update is ():

foreach my $h (@$Selec)
{
my $epoch =3D ${%$h}{'EPOCH'};
my $site =3D ${%$h}{'SITE'};

my $corsite =3D ("'"."$site"."'"); # Corresponds to the =
station field
my $corep =3D ("'"."$epoch"."'"); # Corresponds to the epoch =
field

my $sth =3D $dbh->do( "
UPDATE $table
SET $SET
WHERE ( epoch LIKE $corep AND station =3D =
$corsite)
LIMIT 1
");
}

+-----------------------+---------------+------+-----+------ ---+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+------ ---+-------+
| epoch | varchar(12) | YES | | NULL | |
| station | varchar(4) | YES | | NULL | |
| ztd_nrt | decimal(6,1) | YES | | NULL | |
| std_ztd_nrt | decimal(6,1) | YES | | NULL | |
| flag_ztd_nrt | varchar(50) | YES | | NULL | |
| iwv_nrt | decimal(6,1) | YES | | NULL | |
| std_iwv_nrt | decimal(6,1) | YES | | NULL | |
| flag_iwv_nrt | varchar(50) | YES | | NULL | |
| ztd_igu12 | decimal(6,1) | YES | | NULL | |
| std_ztd_igu12 | decimal(6,1) | YES | | NULL | |
| flag_ztd_igu12 | varchar(50) | YES | | NULL | |
| iwv_igu12 | decimal(6,1) | YES | | NULL | |
| std_iwv_igu12 | decimal(6,1) | YES | | NULL | |
| flag_iwv_igu12 | varchar(50) | YES | | NULL | |
| ztd_igu24 | decimal(6,1) | YES | | NULL | |
| std_ztd_igu24 | decimal(6,1) | YES | | NULL | |
| flag_ztd_igu24 | varchar(50) | YES | | NULL | |
| iwv_igu24 | decimal(6,1) | YES | | NULL | |
| std_iwv_igu24 | decimal(6,1) | YES | | NULL | |
| flag_iwv_igu24 | varchar(50) | YES | | NULL | |
| ztd_igrdd | decimal(6,1) | YES | | NULL | |
| std_ztd_igrdd | decimal(6,1) | YES | | NULL | |
| flag_ztd_igrdd | varchar(50) | YES | | NULL | |
| iwv_igrdd | decimal(6,1) | YES | | NULL | |
| std_iwv_igrdd | decimal(6,1) | YES | | NULL | |
| flag_iwv_igrdd | varchar(50) | YES | | NULL | |
| ztd_igrppp | decimal(6,1) | YES | | NULL | |
| std_ztd_igrppp | decimal(6,1) | YES | | NULL | |
| flag_ztd_igrppp | varchar(50) | YES | | NULL | |
| iwv_igrppp | decimal(6,1) | YES | | NULL | |
| std_iwv_igrppp | decimal(6,1) | YES | | NULL | |
| flag_iwv_igrppp | varchar(50) | YES | | NULL | |
| ztd_igsdd | decimal(6,1) | YES | | NULL | |
| std_ztd_igsdd | decimal(6,1) | YES | | NULL | |
| flag_ztd_igsdd | varchar(50) | YES | | NULL | |
| iwv_igsdd | decimal(6,1) | YES | | NULL | |
| std_iwv_igsdd | decimal(6,1) | YES | | NULL | |
| flag_iwv_igsdd | varchar(60) | YES | | NULL | |
| ztd_igsppp | decimal(6,1) | YES | | NULL | |
| std_ztd_igsppp | decimal(6,1) | YES | | NULL | |
| flag_ztd_igsppp | varchar(60) | YES | | NULL | |
| iwv_igsppp | decimal(6,1) | YES | | NULL | |
| std_iwv_igsppp | decimal(6,1) | YES | | NULL | |
| flag_iwv_igsppp | varchar(60) | YES | | NULL | |
| ztd_daily_igsppp | decimal(6,1) | YES | | NULL | |
| std_ztd_daily_igsppp | decimal(6,1) | YES | | NULL | |
| flag_ztd_daily_igsppp | varchar(60) | YES | | NULL | |
| iwv_daily_igsppp | decimal(6,1) | YES | | NULL | |
| std_iwv_daily_igsppp | decimal(6,1) | YES | | NULL | |
| flag_iwv_daily_igsppp | varchar(60) | YES | | NULL | |
| ps | decimal(7,2) | YES | | NULL | |
| ts | decimal(6,2) | YES | | NULL | |
| rh | decimal(6,3) | YES | | NULL | |
| q | decimal(6,5) | YES | | NULL | |
| pt | char(2) | YES | | NULL | |
| soln | varchar(4) | YES | | NULL | |
| t | char(1) | YES | | NULL | |
| sta_x | decimal(12,3) | YES | | NULL | |
| sta_y | decimal(12,3) | YES | | NULL | |
| sta_z | decimal(12,3) | YES | | NULL | |
| system | varchar(6) | YES | | NULL | |
| remrk | varchar(5) | YES | | NULL | |
| samp_int | int(5) | YES | | NULL | |
| samp_trop | int(5) | YES | | NULL | |
| trop_mf | varchar(22) | YES | | NULL | |
| el_cut_ang | decimal(5,2) | YES | | NULL | |
+-----------------------+---------------+------+-----+------ ---+-------+
66 rows in set (0.00 sec)

__________________________________________________

Etienne J. Orliac
Ph.D student
Institute of Engineering Surveying and Space Geodesy (IESSG) =20
School of Civil Engineering
University of Nottingham
University Park
Nottingham NG7 2RD, UK

e-mail: isxejo@nottingham.ac.uk=20

Tel : +44 (0) 115 951 5151 & ext: 18508
Mob : +44 (0) 781 785 5496 =20
Fax : +44 (0) 115 951 3881 =20
>>> "Ulrich Borchers" 04/01/04 8:52 PM >>>
Hi Etienne,

can you please post the query and the table structure (create table, =
indexes).
Maybe you can tweak this by creating indexes on the right
columns if you have not already done so. Even dropping an
index might help. I doubt that 1500 update queries take
so long even on large tables if you have the right indexes
created and data types selected.

Uli

On 1 Apr 2004 at 20:32, Etienne Orliac wrote:

> Dear all,
>
> I have to update 1500 lines on a daily basis in a single table. My =
problem is that it takes a while to execute. All the updates are siimilar.
>
> I've read that you can delay the updates and just execute once or =
something like that. However, I wasn't able to find any documentation =
about it.
>
> So I would greatly appreciate some help.
>
> Thanks a lot,
>
> Etienne.
>
>
> --
> MySQL Perl Mailing List
> For list archives: http://lists.mysql.com/perl
> To unsubscribe: http://lists.mysql.com/perl?unsub=3Dubo1@gmx.de
>

--
Ulrich Borchers
Brandenberger Str.18
41065 Mönchengladbach
Tel. (0 21 61) 17 58 83
Mobil (0 179) 72 66 112

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dsugiarto.Then@infotr=
onik.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: How to speed UPDATES

am 01.04.2004 22:16:01 von Etienne Orliac

Re: How to speed UPDATES

am 01.04.2004 23:00:03 von Ulrich Borchers

--Alt-Boundary-25055.23157528
Content-type: text/plain; charset=ISO-8859-1
Content-transfer-encoding: Quoted-printable
Content-description: Mail message body

[snip]
>
> my $sth =3D $dbh->do( "
> UPDATE $table
> SET $SET
> WHERE ( epoch LIKE $corep AND station =3D=
$corsite)
> LIMIT 1
> ");
> }
>
>
>
>
> +-----------------------+---------------+------+-----+------ ---+-------+
> | Field | Type | Null | Key | Default | Extra |
> +-----------------------+---------------+------+-----+------ ---+-------+
> | epoch | varchar(12) | YES | | NULL | |
> | station | varchar(4) | YES | | NULL | |
> | ztd_nrt | decimal(6,1) | YES | | NULL | |
> | std_ztd_nrt | decimal(6,1) | YES | | NULL | |
> | flag_ztd_nrt | varchar(50) | YES | | NULL | |
> | iwv_nrt | decimal(6,1) | YES | | NULL | |

You don't have any keys ----------------------------^

Try
create index idx_epoch on table (epoch)
create index idx_station on table (station)

or
create a primary key on these two columns (must not be null)

Avoid keys on other columns. Creating the indexes will very likely take a =
while :-)
If you can change the two columns to INT, maybe as foreign keys, do so.
That might gain even more speed. But the missing keys cause you the most t=
rouble I
think.
I don't know, if a very long $SET string will also slow things down...

Uli

--
Ulrich Borchers
Brandenberger Str.18
41065 Mönchengladbach
Tel. (0 21 61) 17 58 83
Mobil (0 179) 72 66 112

--Alt-Boundary-25055.23157528--

Re: How to speed UPDATES

am 01.04.2004 23:00:03 von Ulrich Borchers

Re: How to speed UPDATES

am 02.04.2004 00:40:52 von Etienne Orliac

Thanks for the solution. I put a key on the two columns and now the =
process is incredibly much faster.

Thanks a lot,

Etienne.

__________________________________________________

Etienne J. Orliac
Ph.D student
Institute of Engineering Surveying and Space Geodesy (IESSG) =20
School of Civil Engineering
University of Nottingham
University Park
Nottingham NG7 2RD, UK

e-mail: isxejo@nottingham.ac.uk=20

Tel : +44 (0) 115 951 5151 & ext: 18508
Mob : +44 (0) 781 785 5496 =20
Fax : +44 (0) 115 951 3881 =20
>>> "Ulrich Borchers" 04/01/04 10:00 PM >>>
[snip]
>
> my $sth =3D $dbh->do( "
> UPDATE $table
> SET $SET
> WHERE ( epoch LIKE $corep AND station =3D =
$corsite)
> LIMIT 1
> ");
> }
>
>
>
>
> +-----------------------+---------------+------+-----+------ ---+-------+
> | Field | Type | Null | Key | Default | Extra |
> +-----------------------+---------------+------+-----+------ ---+-------+
> | epoch | varchar(12) | YES | | NULL | |
> | station | varchar(4) | YES | | NULL | |
> | ztd_nrt | decimal(6,1) | YES | | NULL | |
> | std_ztd_nrt | decimal(6,1) | YES | | NULL | |
> | flag_ztd_nrt | varchar(50) | YES | | NULL | |
> | iwv_nrt | decimal(6,1) | YES | | NULL | |

You don't have any keys ----------------------------^

Try
create index idx_epoch on table (epoch)
create index idx_station on table (station)

or
create a primary key on these two columns (must not be null)

Avoid keys on other columns. Creating the indexes will very likely take a =
while :-)
If you can change the two columns to INT, maybe as foreign keys, do so.
That might gain even more speed. But the missing keys cause you the most =
trouble I
think.
I don't know, if a very long $SET string will also slow things down...

Uli

--
Ulrich Borchers
Brandenberger Str.18
41065 Mönchengladbach
Tel. (0 21 61) 17 58 83
Mobil (0 179) 72 66 112

--
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: How to speed UPDATES

am 02.04.2004 00:40:52 von Etienne Orliac

Re: How to speed UPDATES

am 02.04.2004 02:25:26 von Christopher Pryce

On Thursday, April 1, 2004, at 02:16 PM, Etienne Orliac wrote:

> The piece of code for the update is ():
>
> foreach my $h (@$Selec)
> {
> my $epoch = ${%$h}{'EPOCH'};
> my $site = ${%$h}{'SITE'};
> my $corsite = ("'"."$site"."'"); # Corresponds to the
> station field
> my $corep = ("'"."$epoch"."'"); # Corresponds to the epoch
> field

consider calling $dbh->quote() or use placeholders

> my $sth = $dbh->do( "
> UPDATE $table
> SET $SET
> WHERE ( epoch LIKE $corep AND station =
> $corsite)
> LIMIT 1
> ");
> }

Ideally, you should prepare a statement outside of a foreach loop, and
execute it inside, something along the lines of (untested and normal
error checking omitted):

my $sql = qq( UPDATE $table SET $SET WHERE ( epoch = ? AND station = ?
) );
my $sth = $dbh->prepare( $sql );

foreach my $h (@$Selec) {
$sth->execute( $h->{'EPOCH'}, $h->{'SITE'} ) ;
}

Note that using placeholders eliminates the need for quoting char
values. The Driver does that for you. For a batch of 1500 inserts, you
eliminate 1500 prepare statements, which will be a great savings.

cp

--
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: How to speed UPDATES

am 02.04.2004 02:25:26 von Christopher Pryce