Sounds like a great test! Why don't you let us know which way is faster:
doing the substr's on the DB or in perl (my bet is perl). For that
matter, you could also test to see if join('|', @row) is faster. You
could certainly move the "\n" up to the first print line (via . or ,) and
cut out a whole perl statement. But then, there alway more than one way
to do it ;-)
I don't normally use the bind* calls. I typically just use fetchrow_array
or fetchrow_arrayref for large result sets. But, I just read page 127 of
our favorite book and your way is probably faster than mine.
For testing purposes, you could always insert a cut-off (say 10K rows) and
time it on a smaller scale. That way each test won't take 35min. You
could also take the print out of the loop and just see how long it takes
to pull the data from the DB. I imagine that's where your spending most
of your time. Maybe tuning the (Oracle?) client interface is the place to
look. As Michael points out, EXPLAIN PLAN might be beneficial on your
query.
Finally, would performance be improved if you used bind_columns on a
regular array instead of a hash? (see join(), above.) It would save the
overhead of computing the index (hash).
Just my $0.02.
Dave
On Dec 11, Rozengurtel, Daniel scribed:
> 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( \( @[EMAIL PROTECTED] ));
>
> 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.
> __________________________________________________________________
>
