DBI Update counter

Discussion in 'Perl Misc' started by Glodalec, Jan 9, 2004.

  1. Glodalec

    Glodalec Guest

    Hi !

    Is there any way to get proper number of rows affected during update ?

    The problem is, that I need to replace records if key exists, or insert
    records if there is no key.
    I was using REPLACE statement, but this is a performance problem. I am
    using round-robin keys, so after table holds specific keys, they are
    never more inserted, but only updated, which is in average 80%. REPLACE
    statement DELETES and then INSERTS the same key, which is nonsense.

    So I tried to do it manualy. If I would always SELECT first for specific
    key, that would be performance degrade too, so my idea is to try first
    to UPDATE and if it fails, it goes to INSERT.

    Let's say, I have to fields, MYKEY and MYFIELD, and the table is:

    mysql> select * from mytable;
    +-------+---------+
    | mykey | myfield |
    +-------+---------+
    | K1 | 10 |
    | K2 | 20 |
    | K3 | 30 |
    +-------+---------+
    3 rows in set (0.00 sec)

    Because of nature of my table processing, I am using binding.

    So here it is part of my code:

    sub DoIt
    {
    my ($KEY,$VALUE)=@_;
    my $UPDATE_SQL="UPDATE mytable SET myfield=? WHERE mykey=?" ;
    my $update_handle = $marvindb::dbh->prepare( $UPDATE_SQL );
    $update_handle->bind_param(1, $VALUE ) ;
    $update_handle->bind_param(2, $KEY, DBI::SQL_VARCHAR );
    my $RET=$update_handle->execute() ;
    print "Key=${KEY}, Value=${VALUE}, Ret=${RET}, rows=",
    $update_handle->rows, "\n";
    }
    DoIt("K1","50") ;
    DoIt("K1","20") ;
    DoIt("K2","20") ;
    DoIt("K4","50") ;
    DoIt("K4","60") ;
    DoIt("K4","60") ;


    The output for this is:
    Key=K1, Value=50, Ret=1, rows=1
    Key=K1, Value=20, Ret=1, rows=1
    Key=K2, Value=20, Ret=0E0, rows=0
    Key=K4, Value=50, Ret=0E0, rows=0
    Key=K4, Value=60, Ret=0E0, rows=0
    Key=K4, Value=60, Ret=0E0, rows=0


    So what I see is that there is no way using UPDATE statement, whether
    key exist or no, since if mysql detects, that no changes is needed,
    it returns 0.


    Any help would be appreciated.
    Glodalec, Jan 9, 2004
    #1
    1. Advertising

  2. Glodalec

    gnari Guest

    "Glodalec" <> wrote in message
    news:...

    [snipped dbi problem where (if i understand it properly),
    the count returned by UPDATE is the number of changed records,
    not the number of records selected by WHERE clause]

    > So what I see is that there is no way using UPDATE statement, whether
    > key exist or no, since if mysql detects, that no changes is needed,
    > it returns 0.
    >
    > Any help would be appreciated.


    in my opinion, this is a bug. I do not see this with my postgresql.

    if key reuse is much less likely than new keys, then you
    could just allways start by inserting, and in the less likely case,
    catch the key violation error, and then update.
    this results in 1 operation in the common case, 2 in the other

    if key reuse is much more likely than new key, and most likely to use a
    different value,
    you could start by update, and if the count returned is 0, do a insert.
    if the key existed but with a different value, (common case) you use 1
    operation.
    if the key existed but with same value, you use 2 operations (insert
    returning error)
    if the key is new you use 2 operations.

    if most common case is key reuse vith same value, you might look at adding
    one dummy integer column, and do
    update mytable set value=?, dummy=dummy+1 where key=?;
    this should force every found row to be counted. if return is 0,
    then do your insert (with dummy=0)

    I still think it is a bug.

    gnari
    gnari, Jan 9, 2004
    #2
    1. Advertising

  3. Glodalec

    Guest

    Glodalec <> wrote:
    > Hi !
    >
    > Is there any way to get proper number of rows affected during update ?


    turn on mysql_client_found_rows:


    my
    $dbh=DBI->connect("DBI:mysql:database=hts;host=localhost;mysql_client_found
    _rows=1", "nobody", "mypassword", { RaiseError => 1 }) ;


    Xho

    --
    -------------------- http://NewsReader.Com/ --------------------
    Usenet Newsgroup Service New Rate! $9.95/Month 50GB
    , Jan 9, 2004
    #3
  4. Glodalec

    gnari Guest

    <> wrote in message
    news:20040109184258.909$...
    > Glodalec <> wrote:
    > > Hi !
    > >
    > > Is there any way to get proper number of rows affected during update ?

    >
    > turn on mysql_client_found_rows:
    >
    >
    > my
    >

    $dbh=DBI->connect("DBI:mysql:database=hts;host=localhost;mysql_client_found
    > _rows=1", "nobody", "mypassword", { RaiseError => 1 }) ;


    OT:
    I wonder why this is not default. performance issue?

    gnari
    gnari, Jan 10, 2004
    #4
  5. Glodalec

    Glodalec Guest

    In article <20040109184258.909$>,
    says...
    > Glodalec <> wrote:
    > > Hi !
    > >
    > > Is there any way to get proper number of rows affected during update ?

    >
    > turn on mysql_client_found_rows:
    >
    >
    > my
    > $dbh=DBI->connect("DBI:mysql:database=hts;host=localhost;mysql_client_found
    > _rows=1", "nobody", "mypassword", { RaiseError => 1 }) ;
    >
    >
    > Xho
    >
    >

    Ths. I will try it in Mon. Although I had read DBI manual many times, I
    see DBD for mysql should as well.
    Glodalec, Jan 11, 2004
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. ulloa
    Replies:
    1
    Views:
    523
    Juha Laiho
    Jul 22, 2004
  2. The Eeediot
    Replies:
    3
    Views:
    2,225
    =?Utf-8?B?UnVsaW4gSG9uZw==?=
    Dec 22, 2004
  3. George2
    Replies:
    1
    Views:
    791
    Alf P. Steinbach
    Jan 31, 2008
  4. Jerome Hauss
    Replies:
    0
    Views:
    166
    Jerome Hauss
    Oct 13, 2004
  5. Asby

    Mason, DBI, and DBI::Pg

    Asby, Jul 24, 2003, in forum: Perl Misc
    Replies:
    0
    Views:
    173
Loading...

Share This Page