in reply to How many Select Querie is considered too much?

To give more of an idea, I jotted down some scripting on how my code will look. 
        my ($id, $username);
    my $sth = $dbh->prepare_cached(<<SQL);
Select id, username From members
SQL
        $sth->execute();
        $sth->bind_columns(undef, \$id, \$username);



#category queries
        my ($count);
    my $sthx = $dbh->prepare_cached(<<SQL);
SELECT COUNT(*) From Results WHERE user_id = ? AND read = '2'
SQL
        my ($count_v);
    my $sthv = $dbh->prepare_cached(<<SQL);
SELECT COUNT(*) From Results WHERE user_id = ? AND read = '3'
SQL

        my ($count_c);
    my $sthc = $dbh->prepare_cached(<<SQL);
SELECT COUNT(*) From Results WHERE user_id = ? AND read = '4'
SQL

        my ($count_e);
    my $sthe = $dbh->prepare_cached(<<SQL);
SELECT COUNT(*) From Results WHERE user_id = ? AND pending = 'p'
SQL
        my ($count_m);
    my $sthm = $dbh->prepare_cached(<<SQL);
SELECT COUNT(*) From Results WHERE user_id = ? AND requests > 5
SQL

        my ($count_w);
    my $sthw = $dbh->prepare_cached(<<SQL);
SELECT COUNT(*) From Results WHERE user_id = ? AND votes > 0
SQL


#

        while $sth->fetch() {
$sthx->execute($id);
$sthx->bind_columns(undef, \$count);
$sthx->fetch();
    print "<td>$count</td>";

$sthv->execute($id);
$sthv->bind_columns(undef, \$count_v);
$sthv->fetch();
    print "<td>$count_v</td>";

#etc...etc.. until all sth's are executed.
#going to add a limit to the # of members to fetch, perhaps #100 membe
+rs a page....if thats the case:
#
#1 select query to fetch all 100 member id's.
#6 selects for each member, x 100 members so 600 queries to #print out
+ the table full of values.
#total to 601 select queries
       }


<br><br>
Thanks, Bobby

[download]

Replies are listed 'Best First'.
Re^2: How many Select Querie is considered too much?
by imp (Priest) on Aug 10, 2006 at 03:11 UTC
    This set of queries: 
    SELECT COUNT(*) From Results WHERE user_id = ? AND read = '2'
SELECT COUNT(*) From Results WHERE user_id = ? AND read = '3'
SELECT COUNT(*) From Results WHERE user_id = ? AND read = '4'

    [download]
    Could be replaced with something like this: 
    SELECT read, COUNT(*) From Results WHERE user_id = ? AND read in (2,3,
+4) GROUP BY read

    [download]
    Assuming there are entries for those three values you would get back 3 rows, where column 0 is the 'read' value, and column 1 is the number that had the value.

    UPDATE Fixed the query, as TrekNoid pointed out that I omitted the group by clause. Apparently I neglected to select that portion before hitting copy.

[reply]
[d/l]
[select]
      Wouldn't you need a 'group by' in there? 
      SELECT read, COUNT(*)
  FROM Results
 WHERE user_id = ?
   AND read in ('2','3','4')
 GROUP BY read

      [download]
      I know Oracle really well, so it's possible that I'm just being Oracle-centric :)

      Trek

[reply]
[d/l]
Re^2: How many Select Querie is considered too much?
by Anonymous Monk on Aug 10, 2006 at 02:09 UTC
    Try to avoid nested queries by storing the results from the first query in a hash or array. Then loop over that structure to do more queries. You can also reuse those statement handles and variables. This example avoids the nesting and might be further improved with joins, or a more normalized database. 
    my $sth = $dbh->prepare_cached(<<SQL);
Select id, username From members
SQL

$sth->execute();
$sth->bind_columns(\my($id,$username));

my $user;

while ($sth->fetch) {
  $user->{$id} = $username
}

for my $id (keys %$user) {
  $sth = $dbh->prepare_cached(q{SELECT COUNT(*) From Results WHERE use
+r_id = ? AND read = ?});
  $sth->execute($id,2);
  $sth->bind_columns(\my($count));
  $sth->fetch;
  print $count;

  $sth = $dbh->prepare_cached(q{SELECT COUNT(*) From Results WHERE use
+r_id = ? AND read = ?});
  $sth->execute($id,3);
  $sth->bind_columns(\$count);
  $sth->fetch;
  print $count;
}

    [download]
[reply]
[d/l]

In Section Seekers of Perl Wisdom