[vhffs-dev] [662] Replaced selectall by a per row processing, perfs are roughly the same but should be more memory efficient.

[subversion@xxxxxxxxx]

Revision: 662
Author:   beuss
Date:     2007-07-05 07:49:46 +0000 (Thu, 05 Jul 2007)

Log Message:
-----------
Replaced selectall by a per row processing, perfs are roughly the same but should be more memory efficient.

Modified Paths:
--------------
    trunk/vhffs-backend/src/mirror/nss-mirror.pl


Modified: trunk/vhffs-backend/src/mirror/nss-mirror.pl
===================================================================
--- trunk/vhffs-backend/src/mirror/nss-mirror.pl	2007-07-04 22:42:35 UTC (rev 661)
+++ trunk/vhffs-backend/src/mirror/nss-mirror.pl	2007-07-05 07:49:46 UTC (rev 662)
@@ -63,35 +63,39 @@
 $st_dbh->do(q{CREATE TEMP TABLE tmp_user_group(uid INTEGER, gid INTEGER, CONSTRAINT pk_user_groups PRIMARY KEY(uid, gid))})
     or die("Unable to create temporary user_group table\n");
 
-my $users = $pg_dbh->selectall_arrayref(q{SELECT u.uid, u.gid, u.username, u.passwd, u.shell, u.homedir FROM vhffs_users u INNER JOIN vhffs_object o ON o.object_id = u.object_id WHERE o.state = 6},
-    { Slice => {} });
+my $ssth = $pg_dbh->prepare(q{SELECT u.uid, u.gid, u.username, u.passwd, u.shell, u.homedir FROM vhffs_users u INNER JOIN vhffs_object o ON o.object_id = u.object_id WHERE o.state = 6}) or die("Unable to prepare users SELECT statement\n");
 my $sth = $st_dbh->prepare(q{INSERT INTO tmp_shadow(uid, gid, username, passwd, shell, homedir) VALUES(?, ?, ?, ?, ?, ?)})
     or die("Unable to prepare shadow insert statement\n");;
 
-foreach(@$users) {
-    $sth->execute($_->{uid}, $_->{gid}, $_->{username}, $_->{passwd}, $_->{shell}, $_->{homedir})
+$ssth->execute() or die("Unable to execute users SELECT statement\n");
+
+while(my $row = $ssth->fetchrow_hashref()) {
+    $sth->execute($row->{uid}, $row->{gid}, $row->{username}, $row->{passwd}, $row->{shell}, $row->{homedir})
         or die("Unable to insert shadow\n");
 }
 $sth->finish();
 
-my $groups = $pg_dbh->selectall_arrayref(q{SELECT g.gid, g.groupname, g.passwd FROM vhffs_groups g INNER JOIN vhffs_object o ON o.object_id = g.object_id WHERE o.state = 6}, { Slice => {} })
-    or die("Unable to select groups\n");
+$ssth = $pg_dbh->prepare(q{SELECT g.gid, g.groupname, g.passwd FROM vhffs_groups g INNER JOIN vhffs_object o ON o.object_id = g.object_id WHERE o.state = 6}) or die("Unable to prepare groups SELECT statement\n");
 $sth = $st_dbh->prepare(q{INSERT INTO tmp_groups(gid, groupname, passwd) VALUES(?, ?, ?)})
     or die("Unable to prepare groups insert statement\n");
 
-foreach(@$groups) {
-    $sth->execute($_->{gid}, $_->{groupname}, ($_->{passwd} or ''))
+$ssth->execute() or die("Unable to execute groups SELECT statement\n");
+
+while(my $row = $ssth->fetchrow_hashref()) {
+    $sth->execute($row->{gid}, $row->{groupname}, ($row->{passwd} or ''))
         or die("unable to insert groups\n");
 }
 $sth->finish();
 
-my $user_group = $pg_dbh->selectall_arrayref(q{SELECT uid, gid FROM vhffs_user_group WHERE state = 6},{ Slice =>{} })
-    or die("Unable to select user_group association\n");
+$ssth = $pg_dbh->prepare(q{SELECT uid, gid FROM vhffs_user_group WHERE state = 6})
+    or die("Unable to prepare user_group SELECT statement\n");
 $sth = $st_dbh->prepare(q{INSERT INTO tmp_user_group(uid, gid) VALUES(?, ?)})
     or die("Unable to prepare user_group insert statement\n");
 
-foreach(@$user_group) {
-    $sth->execute($_->{uid}, $_->{gid})
+$ssth->execute();
+
+while(my $row = $ssth->fetchrow_hashref()) {
+    $sth->execute($row->{uid}, $row->{gid})
         or die("Unable to insert user_group\n");
 }
 $sth->finish();