Changeset 385 for trunk


Ignore:
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(n2) (or probably worse) due to
LEFT JOINs for user, domain, and revzone counts. Split out counts
into separate SQL calls.

File:
1 edited

Legend:

Unmodified
Added
Removed
  • trunk/DNSDB.pm

    r384 r385  
    24642464  $args{sortby} = 'g2.group_name' if $args{sortby} eq 'parent';
    24652465
    2466   my $sql = q(SELECT g.group_id AS groupid, g.group_name AS groupname, g2.group_name AS pgroup,
    2467         count(distinct(u.username)) AS nusers, count(distinct(d.domain)) AS ndomains,
    2468         count(distinct(r.revnet)) AS nrevzones
     2466  my $sql = q(SELECT g.group_id AS groupid, g.group_name AS groupname, g2.group_name AS pgroup
    24692467        FROM groups g
    24702468        INNER JOIN groups g2 ON g2.group_id=g.parent_group_id
    2471         LEFT OUTER JOIN users u ON u.group_id=g.group_id
    2472         LEFT OUTER JOIN domains d ON d.group_id=g.group_id
    2473         LEFT OUTER JOIN revzones r ON r.group_id=g.group_id
    24742469        ).
    2475         "WHERE g.parent_group_id IN ($args{curgroup}".($args{childlist} ? ",$args{childlist}" : '').")".
     2470        " WHERE g.parent_group_id IN ($args{curgroup}".($args{childlist} ? ",$args{childlist}" : '').")".
    24762471        ($args{startwith} ? " AND g.group_name ~* ?" : '').
    24772472        ($args{filter} ? " AND g.group_name ~* ?" : '').
     
    24812476  my $glist = $dbh->selectall_arrayref($sql, { Slice => {} }, (@filterargs) );
    24822477  $errstr = $dbh->errstr if !$glist;
     2478
     2479  # LEFT JOINs make the result set balloon beyond sanity just to include counts;
     2480  # this means there's lots of crunching needed to trim the result set back down.
     2481  # So instead we track the order of the groups, and push the counts into the
     2482  # arrayref result separately.
     2483##fixme:  put this whole sub in a transaction?  might be
     2484# needed for accurate results on very busy systems.
     2485  my %gref;
     2486  my $i = 0;
     2487  foreach (@{$glist}) {
     2488    $gref{$$_{groupid}} = $i++;
     2489  }
     2490  my $tmpchlist = $args{curgroup}.($args{childlist} ? ",$args{childlist}" : '');
     2491  foreach my $grp (split /,/, $tmpchlist) {
     2492    my ($ucnt) = $dbh->selectrow_array("SELECT count(*) FROM users WHERE group_id=?", undef, ($grp));
     2493    $glist->[$gref{$grp}]{nusers} = $ucnt;
     2494    my ($dcnt) = $dbh->selectrow_array("SELECT count(*) FROM domains WHERE group_id=?", undef, ($grp));
     2495    $glist->[$gref{$grp}]{ndomains} = $dcnt;
     2496    my ($rcnt) = $dbh->selectrow_array("SELECT count(*) FROM revzones WHERE group_id=?", undef, ($grp));
     2497    $glist->[$gref{$grp}]{nrevzones} = $rcnt;
     2498  }
     2499
    24832500  return $glist;
    24842501} # end getGroupList
Note: See TracChangeset for help on using the changeset viewer.