Insert mit Rückgabe der ID

[hotti]

hi Tom,

Hast Du es schon mit REPLACE und "On DUPLICATE KEY UPDATE" ausprobiert?

Proprietär und unperformant ;)

Überraschung:
Benchmark: timing 100000000 iterations of New Way, Old Way...
   New Way:  1 wallclock secs ( 0.30 usr +  0.00 sys =  0.30 CPU) @ 336700336.70/s (n=100000000)
            (warning: too few iterations for a reliable count)
   Old Way:  2 wallclock secs ( 1.77 usr +  0.00 sys =  1.77 CPU) @ 56657223.80/s (n=100000000)
               Rate Old Way New Way
Old Way  56657224/s      --    -83%
New Way 336700337/s    494%      --

Viele Grüße,
Horst ;)

PS: Perl-Code zum Nachvollziehen:

  
  
use strict;  
use warnings;  
use DBI;  
use Data::Dumper;  
use Benchmark qw(cmpthese);  
  
my $DBH = dbh() or die $@;  
  
  
#create('user') || drop('user');  
  
# Statements  
my $STH_IGNORE = $DBH->prepare("INSERT IGNORE INTO user(user_id, user_name)VALUES(?,?)");  
my $STH_INSERT = $DBH->prepare("INSERT INTO user(user_id, user_name)VALUES(?,?)");  
my $STH_TEST   = $DBH->prepare("SELECT id FROM user WHERE user_id=? AND user_name=?");  
  
my $user_id = 'hh10';  
my $user_name = 'Horst Haselhuhn';  
my $id = undef;  
  
#my $id = insert_ignore({user_id => $user_id, user_name => 'Horst Haselhuhn'}) or die $@;  
#print "Old Way: $id\n";  
  
#$id = insert({user_id => $user_id, user_name => $user_name}) or die $@;  
#print "New Way: $id\n";  
  
# Jetzt machen wir mal ein Benchmark  
cmpthese(100000000, {  
        'Old Way' => insert({user_id => $user_id, user_name => $user_name}),  
        'New Way' => insert_ignore({user_id => $user_id, user_name => $user_name}) ,  
});  
  
  
# Herkömmlicher Weg  
sub insert{  
	my $r = shift;  
	my @args = ($r->{user_id}, $r->{user_name});  
	my $id = '0E0';  
	eval{  
		$STH_TEST->execute(@args);  
		my $id_ist = $STH_TEST->fetchrow_array;  
		if($id_ist){  
			#print "DRIN: $id_ist\n";  
			$id = $id_ist;  
		}  
		else{  
			$STH_INSERT->execute(@args);  
			$id = $DBH->selectrow_array("SELECT LAST_INSERT_ID()");  
		}  
	};  
	return $@ ? undef : $id;	  
}  
  
  
# Neuer Weg mit INSERT IGNORE  
sub insert_ignore{  
	my $r = shift;  
	my @args = ($r->{user_id}, $r->{user_name});  
	my $id = '0E0';  
	eval{  
		my $rv = $STH_IGNORE->execute(@args); # IGNORE => 0E0, NEU => 1  
		if($rv == 0){  
			# 0E0, kein Fehler, Record schon drin  
			$id = $DBH->selectrow_array("SELECT id FROM user WHERE user_id=? AND user_name=?", {}, @args);  
			#print "Drin: $id\n";  
		}  
		else{  
			$id = $DBH->selectrow_array("SELECT LAST_INSERT_ID()");  
			#print "Neu: $id\n";  
		}  
	};  
	return $@ ? undef : $id;	  
}  
  
sub drop{  
	my $table_name = shift;  
	eval{ $DBH->do("DROP TABLE $table_name"); };  
	print "DROP\n";  
	return $@ ? undef : 1;  
}  
sub create{  
	my $table_name = shift;  
	my %qq = creates();  
	my $qtable = $qq{$table_name};  
	eval{ $DBH->do($qtable) };  
	print "CREATE\n";  
	return $@ ? undef : 1;  
}  
  
  
sub creates{  
	return (  
		user => q(  
			CREATE TABLE user(  
				id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,  
				user_name VARCHAR(50) NOT NULL DEFAULT '',  
				user_id VARCHAR(50) NOT NULL DEFAULT '',  
				PRIMARY KEY (id),  
				UNIQUE KEY ident(user_name, user_id)  
			)DEFAULT CHARSET=UTF8  
		),  
	);  
}  
###########################################################################  
sub dbh{  
	my %cfg = (  
		base => 'myweb',  
		host => 'localhost',  
		port => 3306,  
		user => '',  
		pass => '',  
  
	@_);  
	my $dbh = undef;  
	eval{  
		$dbh = DBI->connect("DBI:mysql:$cfg{base}:$cfg{host}:$cfg{port}", $cfg{user}, $cfg{pass},  
			{RaiseError => 1, PrintError => 0}  
		);  
	};  
	return $@ ? undef : $dbh;  
}