Better way of accessing fields

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

I keep doing stuff like this when I have to get a whole bunch of fields from a database and I want to not use hashref since I know array ref is faster.

Basically I make a select query and then get the array back and then dump each element from the array into the variable I need.

I thought about making an array of the fields and looping though the result array, but there probably is some magic answer I can't figure out.

thanks in advance

    my $sqlQuery = "SELECT Object_ID,
                                    ID,
                                    Script_Name,
                                    Sql_Name,
                                    Min_Length,
                                    Max_Length,
                                    Char_Regex,
                                    Char_Allowed,
                                    Key_Test_Type,
                                    Key_Test_Table,
                                    Key_Test_Field,
                                    Update_Allowed,
                                    Display_Transform,
                                    Update_Transform,
                                    FieldType,
                                    FieldDataSource,
                                    Display_Order
                      FROM OM_ObjectMetaData
                     WHERE OM_ObjectMetaData.Object_ID = ?";

                     
    #print "<!-- $sqlQuery -->\n\n\n\n\n";
                     
    my $query = $dbh->prepare( $sqlQuery );

    $query->execute( $self->{ID} ) || die $dbh->errstr;
   
      
   
   while( @result_array = $query->fetchrow_array )
    {
        $self->{fields}{$result_array[2]}{Object_ID}         = $result
+_array[0]; 
        $self->{fields}{$result_array[2]}{ID}                = $result
+_array[1];
        $self->{fields}{$result_array[2]}{Script_Name}       = $result
+_array[2];
        $self->{fields}{$result_array[2]}{Sql_Name}          = $result
+_array[3];
        $self->{fields}{$result_array[2]}{Min_Length}            = $re
+sult_array[4];
        $self->{fields}{$result_array[2]}{Max_Length}           = $res
+ult_array[5];
        $self->{fields}{$result_array[2]}{Char_Regex}           = $res
+ult_array[6];
        $self->{fields}{$result_array[2]}{Char_Allowed}       = $resul
+t_array[7];
        $self->{fields}{$result_array[2]}{Key_Test_Type}        = $res
+ult_array[8];
        $self->{fields}{$result_array[2]}{Key_Test_Table}    = $result
+_array[9];
        $self->{fields}{$result_array[2]}{Key_Test_Field}    = $result
+_array[10];
        $self->{fields}{$result_array[2]}{Update_Allowed}    = $result
+_array[11];
        $self->{fields}{$result_array[2]}{Display_Transform} = $result
+_array[12];
        $self->{fields}{$result_array[2]}{Update_Transform}  = $result
+_array[13];
        $self->{fields}{$result_array[2]}{FieldType}         = $result
+_array[14];
        $self->{fields}{$result_array[2]}{FieldDataSource}   = $result
+_array[15];
        $self->{fields}{$result_array[2]}{DisplayOrder}      = $result
+_array[16];
        
        $self->{fields}{$result_array[2]}{Value}  = "";
 
        $self->{display_order}{$result_array[16]} = $result_array[2];
        
      }
[download]

Comment on Better way of accessing fields Download Code

Replies are listed 'Best First'.
Re: Better way of accessing fields by matija (Priest) on Mar 18, 2004 at 19:28 UTC
First of all: you say you know arrayref is faster than hashref - but is the combination of taking an arrayref and then stuffing it into an array by hand really faster than hashref? You have to consider the possibility that the time you saved by using the arrayref is subsequently wasted by that long raft of assignment statements. Second, how often is this loop going to execute? If this loop happens 10 times per execution of your program, you probably wasted more time typing all those assignments than you saved by using arrayref. If the loop executes 10_000_000 then it is probably worth your while to benchmark both solutions, and judge for yourself which is really faster.	[reply]
Re: Better way of accessing fields by pbeckingham (Parson) on Mar 18, 2004 at 19:36 UTC
The access via array versus hashref would be faster, but only by a very small amount. I realize that those small amounts multiply up into larger delays, but we are talking very, very small improvements to some code that is paired up with a database query. Such small improvements do add up, but it loses the significance when paired with a database query, which is at least two orders of magnitude slower than a hash lookup, and maybe 4. It is like packing one less T-shirt in your suitcase to save weight, when there's also a bowling ball in there. Then again, even when it may not be necessary, optimization can be a fun pursuit, and good practice.	[reply]
Re: Better way of accessing fields by jZed (Prior) on Mar 18, 2004 at 19:10 UTC
See the DBI docs on bind_col and bind_columns	[reply]
Re: Better way of accessing fields by kvale (Monsignor) on Mar 18, 2004 at 19:22 UTC
One way you can reduce typing and speed up assignment is to concentrate on the hash you are assigning to: `while( @result_array = $query->fetchrow_array ) { my $col = $self->{fields}{$result_array[2]}; $col->{Object_ID} = $result_array[0]; # and so on ... }` [download] You could create an array that maps array numbers to column names: `my @name = qw\|Object_ID ID ...\|; while( @result_array = $query->fetchrow_array ) { my $col = $self->{fields}{$result_array[2]}; for my $pos(@name) { $col->{$name($pos)} = $result_array[$pos]; } $self->{fields}{$result_array[2]}{Value} = ""; $self->{display_order}{$result_array[16]} = $result_array[2]; }` [download] -Mark	[reply] [d/l] [select]