Here's the top of the output from DBIx::Profile. DBIx::Profile is claiming
that my single SQL statement took 0.30sec to run! that's bad because I
have to run this query 40000 times and I dont want to wait 3 hours..
=================================================================
10098 "SELECT a.TotalAmount, a.PRNum, a.ORNum
FROM CollectionNG a, ClientInfo b WHERE
(b.UserName = ? OR b.MainAcct = ?)
AND a.Verified='Y' AND a.CollectionDate BETWEEN
TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS') AND
TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS') AND
a.ClientCode = b.ClientCode
" Total wall clock time: 0.291668s
execute ---------------------------------------
Total
Count : 1
Wall Clock : 0.2915810 s 0.2915810 s
Cpu Time : 0.0000000 s 0.0000000 s
returned 0E0
Count : 1
Wall Clock : 0.2915810 s 0.2915810 s
Cpu Time : 0.0000000 s 0.0000000 s
fetchrow_array ---------------------------------------
Total
Count : 1
Wall Clock : 0.0000870 s 0.0000870 s
Cpu Time : 0.0000000 s 0.0000000 s
first
Count : 1
Wall Clock : 0.0000870 s 0.0000870 s
Cpu Time : 0.0000000 s 0.0000000 s
=================================================================
There are two things odd with this reading:
1) my EXPLAIN PLAN seems to show that I can't optimize the query any
more..
Query Plan
----------
SELECT STATEMENT Cost = 10
NESTED LOOPS
TABLE ACCESS BY INDEX ROWID COLLECTIONNG
INDEX RANGE SCAN COLLECTIONNG_K4
TABLE ACCESS BY INDEX ROWID CLIENTINFO
INDEX RANGE SCAN CLIENTINFO_K5
2) I tried doing the EXACT SAME QUERY using SQL*Plus with SET TIMING ON,
and it executed in under 0.10sec. What gives? (I used another set of
search variables to avoid Oracle's caching)
Or am I misreading the output of DBIx::Profile? is the time elapsed shown
there the TOTAL elapsed time (since it's at the top of the output file)?
Cheers,
orly
--
Orlando Andico <[EMAIL PROTECTED]>
Mosaic Communications, Inc.
-----BEGIN GEEK CODE BLOCK-----
Version: 3.12
GE d(-) s: a-25 C++++ UBLSI++++$ P+++ L+++>++++ E- W++ N(+)
o K? w O-- M- !V PS(++) PE- Y PGP-- t(+)@ 5(+) X++@ R(+) tv@
b++ DI++ G e++@ h--(*) r% y+
------END GEEK CODE BLOCK------
_
Philippine Linux Users Group. Web site and archives at http://plug.linux.org.ph
To leave: send "unsubscribe" in the body to [EMAIL PROTECTED]
To subscribe to the Linux Newbies' List: send "subscribe" in the body to
[EMAIL PROTECTED]
