Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

For a MySQL server, would it be more efficient writing a 'prepare' for every column name, eg: 
$dbq1 = $dbh->prepare(q{SELECT name FROM mytable WHERE id = ?}q);
$dbq2 = $dbh->prepare(q{SELECT age FROM mytable WHERE id = ?});
$dbq3...

[download]
and then executing the appropriate one, OR 
$dbq = $dbh->prepare(q{SELECT * FROM mytable WHERE id = ?});

[download]
and then using: 
(undef, undef, name, undef) = $dbh->fetchrow_array();

[download]
or 
(undef, undef, undef, age) = $dbh->fetchrow_array();

[download]
etc. for whichever value I actually want.

If I do the first option, I will need a -lot- of seperate prepares, which is why I ask.

Thanks in advance,
--
A frustrated llama.

Replies are listed 'Best First'.
Re: DBI Efficiency
by chromatic (Archbishop) on Jan 16, 2003 at 21:43 UTC

    Depends. I'd go with the first, though. Why ask for data you don't need? Why rely on potentially indeterminate positioning of data?

    Efficiency wouldn't be my first concern. I'd use bound variables and prepare_cached() if it were, and if the bottleneck were these database calls, and if I'd already added indexes.

[reply]
[d/l]
Re: DBI Efficiency
by mpeppler (Vicar) on Jan 16, 2003 at 22:04 UTC
    As chromatic mentioned - it depends, and without knowing more about your application it's really hard to say. Off-hand I'd wager that you are trying to optimize something that will only have very little effect on the actual run-time of your program - the difference between fetching one column and fetching four isn't likely to be hugely significant. If you have run-time problems they are likely to be elsewhere (such as incorrect indexes on the tables, or incorrect algorithms in your perl code)

    Michael

[reply]
      Thanks for the replies!

      Yeah sorry I should have gone into more detail :P I'm making a MUD, and the DB will contain all of the information about players, rooms, mobs, text, helps, objects....So I'll be using it a lot. Especially since for example during fighting hp can change by the second for multiple users. Just thought it would be a good idea to take the most efficient approach I could.

      On a completely unrelated topic, does anyone know how to make it not echo the password characters. All I can find out about is how to do it locally. Does the user's telnet client recognise a password line, or is there some special signal I need to send them or what?

      Thanks again,
      --
      A frustrated llama.

[reply]
        I'm making a MUD, and the DB will contain all of the information about players, rooms, mobs, text, helps, objects....during fighting hp can change by the second for multiple users.
        I see. I still think you're starting your optimization at the wrong end. First make sure that your database design is efficient - it's the updates to the database that are likely to take most time and are likely to most affect the speed of the app (or at least that's been my experience...)

        Michael

[reply]
Re: DBI Efficiency
by runrig (Abbot) on Jan 16, 2003 at 21:59 UTC
    I'd either use 'select *' and fetch with fetchrow_hashref (my $value = $sth->fetchrow_hashref->{field_name}) so you don't need those ugly array assignments, or code the first option with prepare_cached in a subroutine, depending on how efficient this really needs to be:
    sub get_value {
  my ($dbh, $field_name) = @_;
  my $sth = $dbh->prepare_cached("select $field from table where id = 
+?");
  my $value = $sth->fetchrow_array;
  $sth->finish;
  $value;
}

    [download]
[reply]
[d/l]
[select]
Re: DBI Efficiency
by rir (Vicar) on Jan 17, 2003 at 20:07 UTC
    Being nameless monk & indistinguishable from a mass of monks it is not surprising that you see no danger in grabbing an anonymous group of fields in a select statement. :-)

    Do not ever use select * from table except interactively. Sometime the order of the fields will be changed under you.

    You might be interested in DBI::fetchrow_hashref though I'd expect it to be a slower query method.

    As far as optimization goes. Write the thing. Then optimize it.

[reply]
[d/l]
Re: DBI Efficiency
by dws (Chancellor) on Jan 18, 2003 at 22:00 UTC
    It is a really, really bad habit to use '*' in queries, unless you're playing around with a command-line tool. Things will break, possibly in strange ways, if someone restructures the schema underneath you.

    Instead of *, name the fields explicitly. This has the added benifit of yielding useful errors if a schema change affects field names. (With *, both the order of fields and field names could changes, and you might never know it, other than noting that you're getting corrupted results from your script.)

    In your case, this means 

    SELECT name, age FROM mytable WHERE id=?

    [download]
    And for heavens sake, arrange to detect or trap errors from DBI.

[reply]
[d/l]
Re: DBI Efficiency
by Anonymous Monk on Jan 17, 2003 at 00:20 UTC
    What about
    $sth = $dbh->prepare("select ? from databasename");
    $sth->execute( age );

    For your password question:
    I have no idea what a MUD is ( Yes, I live under a rock ),
    but assuming that it uses a telnet interface you can just
    not echo the password back to the user ( if local echoing
    is on for them its inevitable that the pw will be seen)

    -bl0rf

[reply]
      Be very careful with 
      $dbh->prepare("select ? from ...");

      [download]
      While this may work with MySQL, it is completely non-standard. Placeholders should not be used to pass column or table names to the query - only to pass column values that will affect the query.

      Michael

[reply]
[d/l]
Re: DBI Efficiency
by herveus (Prior) on Jan 17, 2003 at 12:40 UTC
    Howdy!

    Public Service gripe:

    Please do NOT wrap your entire message in PRE tags. Use the CODE tag around code, and ordinary P tags to mark paragraphs. As presented, your messages are excessively wide, forcing horizontal scrolling. That is annoying.

    yours,
    Michael

[reply]
Re: DBI Efficiency
by OM_Zen (Scribe) on Jan 18, 2003 at 20:02 UTC
    Hi ,

    select * from my_table

    has one server activity,database's activity and have an array to get the values returned from the fetchrow_array() and then see if it is defined

           my $my_table = $dbh->prepare("select * from my_table where id =
+ ?");
       $my_table->execute($nm);
       while(@data = $my_table->fetchrow_array){

            map{print "$_\n";}@data;
     
       }

    [download]
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom