headers
Etienne Orliac | 1 Apr 2004 21:32
Picon
Picon
Favicon

How to speed UPDATES

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=gcdmp-msql-mysql-modules <at> m.gmane.org
Permalink | Reply | 2 comments |
headers
Ulrich Borchers | 1 Apr 2004 21:52
Favicon

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
(Continue reading)

Permalink | Reply | 1 comment |
headers
lshen | 1 Apr 2004 22:04
Picon

Re: How to speed UPDATES

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

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

> 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
(Continue reading)

Permalink | Reply | 0 comments |
headers
Etienne Orliac | 1 Apr 2004 22:16
Picon
Picon
Favicon

Re: How to speed UPDATES

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 ( <at> $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    |       |
(Continue reading)

Permalink | Reply | 668 comments |
headers
Rick Smith | 1 Apr 2004 22:18
Picon

Mac OS X 10.3.3, PERL 5.8.1 and MySQL 4

Hello,

I’ve installed Bundle::DBI sucessfully using CPAN, but I’m having problems installing
Bundle::DBD::mysql.  I’ve tried both CPAN and manually going through the “perl
Makefile.PL-make-make test-make install” sequence.  I can’t get past the make part.  The output of
commands is shown below.

OS: Mac OS X 10.3.3
Perl: 5.8.1
MySQL: 4.0.18-standard (installed in /usr/local/share/mysql)

Thanks,
Rick

[pbg3:share/mysql/bin] smithrd% uname -a
Darwin pbg3.loc 7.3.0 Darwin Kernel Version 7.3.0: Fri Mar  5 14:22:55 PST 2004;
root:xnu/xnu-517.3.15.obj~4/RELEASE_PPC  Power Macintosh powerpc
[pbg3:share/mysql/bin] smithrd% perl -V
Summary of my perl5 (revision 5.0 version 8 subversion 1 RC3) configuration:
  Platform:
    osname=darwin, osvers=7.0, archname=darwin-thread-multi-2level
    uname='darwin hampsten 7.0 darwin kernel version 6.0: fri jul 25 16:58:41 pdt 2003;
root:xnu-344.frankd.rootsxnu-344.frankd~objrelease_ppc power macintosh powerpc '
    config_args='-ds -e -Dprefix=/usr -Dccflags=-g  -pipe  -Dldflags=-Dman3ext=3pm -Duseithreads -Duseshrplib'
    hint=recommended, useposix=true, d_sigaction=define
    usethreads=define use5005threads=undef useithreads=define usemultiplicity=define
    useperlio=define d_sfio=undef uselargefiles=define usesocks=undef
    use64bitint=undef use64bitall=undef uselongdouble=undef
    usemymalloc=n, bincompat5005=undef
  Compiler:
(Continue reading)

Permalink | Reply | 1 comment |
headers
Rudy Lippan | 1 Apr 2004 22:23

Re: Mac OS X 10.3.3, PERL 5.8.1 and MySQL 4

On Thu, 1 Apr 2004, Rick Smith wrote:

> Hello,
> 
> I’ve installed Bundle::DBI sucessfully using CPAN, but I’m having problems installing
Bundle::DBD::mysql.  I’ve tried both CPAN and manually going through the “perl
Makefile.PL-make-make test-make install” sequence.  I can’t get past the make part.  The
output of commands is shown below.
> 
> OS: Mac OS X 10.3.3
> Perl: 5.8.1
> MySQL: 4.0.18-standard (installed in /usr/local/share/mysql)

Try building with --nocatchstderr

Rudy

--

-- 
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe:    http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules <at> m.gmane.org
Permalink | Reply | 0 comments |
headers
Ulrich Borchers | 1 Apr 2004 23:00
Favicon

Re: How to speed UPDATES

[snip]
> 
>         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    |       |

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

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

or
(Continue reading)

Permalink | Reply | 0 comments |
headers
Etienne Orliac | 2 Apr 2004 00:40
Picon
Picon
Favicon

Re: How to speed UPDATES

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)       
School of Civil Engineering
University of Nottingham
University Park
Nottingham NG7 2RD, UK

e-mail: isxejo <at> nottingham.ac.uk 

Tel   : +44 (0) 115 951 5151 & ext: 18508
Mob : +44 (0) 781 785 5496         
Fax  : +44 (0) 115 951 3881     
>>> "Ulrich Borchers" <borchers <at> megabit.net> 04/01/04 10:00 PM >>>
[snip]
>
>         my $sth = $dbh->do( "
>                               UPDATE $table
>                               SET $SET
>                               WHERE ( epoch LIKE $corep  AND station = $corsite)
>                               LIMIT 1
>                              ");
(Continue reading)

Permalink | Reply | 0 comments |
headers
Christopher Pryce | 2 Apr 2004 02:25

Re: How to speed UPDATES


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

>  The piece of code for the update is ():
>
> foreach my $h ( <at> $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 = ?
(Continue reading)

Permalink | Reply | 668 comments |
headers
James, Joby (Joby)** CTR ** | 2 Apr 2004 16:43
Picon
Favicon

Segmentation fault (core dumped)

hi all
	
 i am creating a database in solaris with  $rc = $drh->func('createdb',
$database, $host, $user, $password, 'admin'); API and it is working
perfectly in one  server.. And now i am testing the same script(instalation
script) in another server this gives an error Segmentation fault (core
dumped) and after this progaram exits ..

is any package or driver is missing in the new server(system) ????

please help me to resolve this problum...

******************programe code************************
print "\nStart creating the DB.........\n";
$rc = $drh->func('createdb', $database, $host, $user, $password, 'admin');
warn "Create failed ($rc)" unless $rc;

Joby James
 	Lucent Technologies India Ltd,
 	INS India Development center,
 	Salarpuria Towers 1,
 	22 Hosur Road, Koramangala Industrial Layout,
 	Bangalore-560095.
 	Ph: 080-51191413(Direct) Extn (2413)
 Email: jobyjames <at> lucent.com

--

-- 
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe:    http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules <at> m.gmane.org
(Continue reading)

Permalink | Reply | 1 comment |

Gmane