Daniel, You haven't said what RDBMS you are using, but it looks to me like you need to do the tuning there, and not in the DBI code.
If it was Oracle, I would not expect using functions like substr would cause a performamce problem, but I would look at the query plan, and maybe try a hint, eg change the SELECT to SELECT /* INDEX(GOVT_TABLE) */ Michael Fox -----Original Message----- From: Rozengurtel, Daniel [mailto:[EMAIL PROTECTED] Sent: Friday, 12 December 2003 5:56 AM To: '[EMAIL PROTECTED]' Subject: Optimization for faster select... 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. __________________________________________________________________ Australia Post is committed to providing our customers with excellent service. If we can assist you in any way please telephone 13 13 18 or visit our website www.auspost.com.au. CAUTION This e-mail and any files transmitted with it are privileged and confidential information intended for the use of the addressee. The confidentiality and/or privilege in this e-mail is not waived, lost or destroyed if it has been transmitted to you in error. If you have received this e-mail in error you must (a) not disseminate, copy or take any action in reliance on it; (b) please notify Australia Post immediately by return e-mail to the sender; and (c) please delete the original e-mail.
