dbTalk Databases Forums  

How to speed UPDATES

Go Back dbTalk Databases Forums Databases mailing.database.msql-mysql-modules How to speed UPDATES

mailing.database.msql-mysql-modules mailing.database.msql-mysql-modules


Discuss How to speed UPDATES in the mailing.database.msql-mysql-modules forum.



Reply
 
Thread Tools Display Modes
  #1  
Old   
Etienne Orliac
 
Posts: n/a

Default How to speed UPDATES - 04-01-2004 , 02:31 PM





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=3D...ie.nctu.edu.tw


Reply With Quote
  #2  
Old   
Ulrich Borchers
 
Posts: n/a

Default Re: How to speed UPDATES - 04-01-2004 , 02:51 PM





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:

Quote:
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=3D...) gmx (DOT) de

--
Ulrich Borchers
Brandenberger Str.18
41065 M=F6nchengladbach
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=3D...ie.nctu.edu.tw



Reply With Quote
  #3  
Old   
lshen
 
Posts: n/a

Default Re: How to speed UPDATES - 04-01-2004 , 03:00 PM


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

Brian
----- Original Message -----
From: "Ulrich Borchers" <borchers (AT) megabit (DOT) net>
To: <perl (AT) lists (DOT) mysql.com>; "Etienne Orliac" <isxejo (AT) nottingham (DOT) ac.uk>
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:

Quote:
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.
Quote:
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=ub...) gmx (DOT) 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=ls... gsfc.nasa.gov



--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=ms...ie.nctu.edu.tw



Reply With Quote
  #4  
Old   
Etienne Orliac
 
Posts: n/a

Default Re: How to speed UPDATES - 04-01-2004 , 03:15 PM


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
");
}

+-----------------------+---------------+------+-----+---------+-------+
Quote:
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 (AT) nottingham (DOT) 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
Quote:
"Ulrich Borchers" <borchers (AT) megabit (DOT) net> 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:

Quote:
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.
Quote:
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=3D...) gmx (DOT) de

--
Ulrich Borchers
Brandenberger Str.18
41065 M=F6nchengladbach
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=3D...o.Then@infotr=
onik.com



--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3D...ie.nctu.edu.tw



Reply With Quote
  #5  
Old   
Ulrich Borchers
 
Posts: n/a

Default Re: How to speed UPDATES - 04-01-2004 , 03:58 PM


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

[snip]
Quote:
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=F6nchengladbach
Tel. (0 21 61) 17 58 83
Mobil (0 179) 72 66 112



--Alt-Boundary-25055.23157528--


Reply With Quote
  #6  
Old   
Etienne Orliac
 
Posts: n/a

Default Re: How to speed UPDATES - 04-01-2004 , 05:40 PM


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 (AT) nottingham (DOT) 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
Quote:
"Ulrich Borchers" <borchers (AT) megabit (DOT) net> 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=F6nchengladbach
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=3D...ie.nctu.edu.tw



Reply With Quote
  #7  
Old   
Christopher Pryce
 
Posts: n/a

Default Re: How to speed UPDATES - 04-01-2004 , 07:24 PM



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

Quote:
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

Quote:
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=ms...ie.nctu.edu.tw



Reply With Quote
Reply

« Previous Thread | Next Thread »



Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.


Contact Us - dbTalk Databases Forums - Archive - Top