Hello Michael, Tom and Henri
First thanx much for your replies. I appreciate it.
I am using Oracle 8.1.7,perl 5.6.1 DBI 1.38, DBD 1.14.
Explain plan shows me the same results if I include the /* INDEX(GOVT_TABLE)
*/ . Also I thought optimizer doesn't need to be told what to use if its
already present in index space for that table. I might be mistaken though.
1. With respect to selectall_arrayref - I dont use it since first I
prepare my statements and then bind columns (according to docs - its the
fastest way to get the data out.) I have column names passed to perl as
params, thats why they are in array and it was easier and cleaner to do so.
However I will try to use it.
2. "How long does the SELECT take when run outside perl?" - I haven't
tested yet. Would a regular SPOOL command do it for me from SQLPLUS? If its
faster than its probably an indicator that there is something in Perl/DBI/my
code processing isnt it?
3. How do I figure out what my disk access is?
4. Compound index sounds interesting. I was going to try the 'bcp'
approach but then stumbled upon some weird functions that I did not have
enough experience with that perform just that. (some UTL_FILE or am I
mistaken?) Any help with File Handling from PL/SQL would be very much
appreciated.
5. Henri, when you say a stored procedure you still mean to pass a
cursor back to perl for printing, right? Otherwise I assume there is not
need in perl if a store proc can print the output itself.
Anyway here is output of what I did and THANX MUCH EVERYONE.
Regards,
Daniel
EXPLAIN PLAN set STATEMENT_ID = 'MITCHEL' for
SELECT /* INDEX(GOVT_TABLE) */
CUSIP,
ISIN,
SUBSTR(FXTICKER, 1, INSTR(FXTICKER, ' ', 1) -1) AS TICKER,
SECURITY_DESC,
SHORT_NAME,
...
...
CALC_TYP_DES
FROM GOVT_MONDAY WHERE INST_MNEM='GRDCOMP' AND TRADE_STATUS='ACTV'
SELECT LPAD(' ',2*(LEVEL-1))||operation operation, options, object_name,
position
FROM plan_table
START WITH id = 0 AND statement_id = 'MITCHEL'
CONNECT BY PRIOR id = parent_id and statement_id = 'MITCHEL'
Here is an output:
operation options object_name
position
SELECT STATEMENT
TABLE ACCESS BY INDEX ROWID GOVT_MONDAY 1
INDEX RANGE SCAN GOVT_MONDAY_INDX_2
1
-----Original Message-----
From: Fox, Michael [mailto:[EMAIL PROTECTED]
Sent: Thursday, December 11, 2003 4:12 PM
To: '[EMAIL PROTECTED]'
Subject: RE: Optimization for faster select...
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.
