Optimization for faster select...

From: Daniel Rozengurtel (Daniel.Rozengurtel_at_bofasecurities.com)
Date: 12/11/03 

To: "'dbi-users@perl.org'" <dbi-users@perl.org>
Date: Thu, 11 Dec 2003 13:56:10 -0500

Hello All,

I am trying to optimize my code to work faster in selecting about 30 columns
from a denormolized table. The result set of 165,000 records is put to a
file on Unix in about 35-40 minutes. I have tried to follow the guide lines
from Tim's recent presentation on DBI (DBI_AdvancedTalk_200307.ppt) to
achieve fast results. The code is working absolutely fine utilizing an index
on that table (INST_MNEM), however I was wondering if anyone can suggest a
faster and better approach to do the same thing. I know selecting 30 columns
can and will affect performance but still....
Does anyone know if a specific function in select stmt affects the
performance and how? (i.e. SUBSTR, INSTR) Is it better to parse it in Perl?

Thanx much for your help,

Regards,

Daniel

$pfd_sql="SELECT
CUSIP, ISIN, SUBSTR(FXTICKER, 1, INSTR(FXTICKER, ' ', 1) -1) AS TICKER,
SECURITY_DESC, ' ' AS IS_DOLLAR_PFD,
...
...
...
FROM GOVT_TABLE WHERE INST_MNEM='GRDCOMP' AND TRADE_STATUS='ACTV' ";

my %row;
# first get all the Preferreds into a file
my $sth = $dbh->prepare_cached($pfd_sql) || die $dbh->errstr;
        $sth->execute; $sth->bind_columns( \( @row{@ClnFldsArray} ));

while($sth->fetch) {
        print OUTFILE map "$row{$_}|", @ClnFldsArray; # print each row with
| as delimiter
        print OUTFILE "\n";
}#while

____________________________________________________________________

IMPORTANT NOTICES:
          This message is intended only for the addressee. Please notify the
sender by e-mail if you are not the intended recipient. If you are not the
intended recipient, you may not copy, disclose, or distribute this message
or its contents to any other person and any such actions may be unlawful.
 
         Banc of America Securities LLC("BAS") does not accept time
sensitive, action-oriented messages or transaction orders, including orders
to purchase or sell securities, via e-mail.

         BAS reserves the right to monitor and review the content of all
messages sent to or from this e-mail address. Messages sent to or from this
e-mail address may be stored on the BAS e-mail system.
__________________________________________________________________