DBI Update counter

[Glodalec]

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.

 

[gnari]

[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

 

[ctcgag]

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

 

[gnari]

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

 

[Glodalec]

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.