Changeset 385 for trunk/DNSDB.pm

Timestamp:

09/06/12 15:58:43 (12 years ago)

Author:

Kris Deugau

Message:

/trunk

Fix performance blackhole with group management; time to retrieve
the list of groups scaled as O(n^{2) (or probably worse) due to
LEFT JOINs for user, domain, and revzone counts. Split out counts
into separate SQL calls.}

File:

• trunk/DNSDB.pm (modified) (2 diffs)

trunk/DNSDB.pm

@@ -2464 +2464 @@
   $args{sortby} = 'g2.group_name' if $args{sortby} eq 'parent';
 
-  my $sql = q(SELECT g.group_id AS groupid, g.group_name AS groupname, g2.group_name AS pgroup,
-        count(distinct(u.username)) AS nusers, count(distinct(d.domain)) AS ndomains,
-        count(distinct(r.revnet)) AS nrevzones
+  my $sql = q(SELECT g.group_id AS groupid, g.group_name AS groupname, g2.group_name AS pgroup
         FROM groups g
         INNER JOIN groups g2 ON g2.group_id=g.parent_group_id
-        LEFT OUTER JOIN users u ON u.group_id=g.group_id
-        LEFT OUTER JOIN domains d ON d.group_id=g.group_id
-        LEFT OUTER JOIN revzones r ON r.group_id=g.group_id
         ).
-        "WHERE g.parent_group_id IN ($args{curgroup}".($args{childlist} ? ",$args{childlist}" : '').")".
+        " WHERE g.parent_group_id IN ($args{curgroup}".($args{childlist} ? ",$args{childlist}" : '').")".
         ($args{startwith} ? " AND g.group_name ~* ?" : '').
         ($args{filter} ? " AND g.group_name ~* ?" : '').
@@ -2481 +2476 @@
   my $glist = $dbh->selectall_arrayref($sql, { Slice => {} }, (@filterargs) );
   $errstr = $dbh->errstr if !$glist;
+
+  # LEFT JOINs make the result set balloon beyond sanity just to include counts;
+  # this means there's lots of crunching needed to trim the result set back down.
+  # So instead we track the order of the groups, and push the counts into the
+  # arrayref result separately.
+##fixme:  put this whole sub in a transaction?  might be
+# needed for accurate results on very busy systems.
+  my %gref;
+  my $i = 0;
+  foreach (@{$glist}) {
+    $gref{$$_{groupid}} = $i++;
+  }
+  my $tmpchlist = $args{curgroup}.($args{childlist} ? ",$args{childlist}" : '');
+  foreach my $grp (split /,/, $tmpchlist) {
+    my ($ucnt) = $dbh->selectrow_array("SELECT count(*) FROM users WHERE group_id=?", undef, ($grp));
+    $glist->[$gref{$grp}]{nusers} = $ucnt;
+    my ($dcnt) = $dbh->selectrow_array("SELECT count(*) FROM domains WHERE group_id=?", undef, ($grp));
+    $glist->[$gref{$grp}]{ndomains} = $dcnt;
+    my ($rcnt) = $dbh->selectrow_array("SELECT count(*) FROM revzones WHERE group_id=?", undef, ($grp));
+    $glist->[$gref{$grp}]{nrevzones} = $rcnt;
+  }
+
   return $glist;
 } # end getGroupList