Re: How to speed UPDATES

[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  = ${%$h}{'EPOCH'};
        my $site   = ${%$h}{'SITE'};


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

        my $sth = $dbh->do( "
                              UPDATE $table
                              SET $SET
                              WHERE ( epoch LIKE $corep  AND station = $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)       
School of Civil Engineering
University of Nottingham
University Park
Nottingham NG7 2RD, UK

e-mail: isxejo@stripped 

Tel   : [phone] & ext: 18508
Mob : [phone]         
Fax  : [phone]     
>>> "Ulrich Borchers" <borchers@stripped> 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=1
>


--
Ulrich Borchers
Brandenberger Str.18
41065 Mönchengladbach
Tel. [phone]
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=1