Query over JDBC much slower than over SQL*Plus
Tony Murphy
now, and was wondering if anyone could help explain whats going on.
When I run the following query using sqlplus (having hardcoded the
variables), its takes about 100ms to process, but when I run the same
query in java using jdbc, its takes upwards on a minute to process.
any help appreciated
String sql = "SELECT x, " +
" y, " +
" COUNT(*), " +
" AVG(z) " +
" FROM table_a " +
" WHERE date1 >= " +
" TO_DATE(?,'YYYY-MM-DD HH24:MI:SS') " +
" AND date2 < " +
" TO_DATE(?,'YYYY-MM-DD HH24:MI:SS') " +
" AND g = ? " +
" GROUP BY x,y " +
" ORDER BY y " ;
pstmt = connection.prepareStatement(sql);
pstmt.setString(1,date1);
pstmt.setString(2,date2);
pstmt.setString(3,g);
rs = pstmt.executeQuery();
while(rs.next()) {
x = rs.getInt(1);
.....
}
rs.close();
pstmt.close();
Jon Skeet
> I've never had any noticable problems with queries over jdbc until
> now, and was wondering if anyone could help explain whats going on.
> When I run the following query using sqlplus (having hardcoded the
> variables), its takes about 100ms to process, but when I run the same
> query in java using jdbc, its takes upwards on a minute to process.
> any help appreciated
<snip>
> while(rs.next()) {
> x = rs.getInt(1);
> .....
> }
Where is the time being taken? If it's in the "....." then we'll need to
see the *contents* of that ..... before we've any idea whether it's your
code or not.
In particular, what happens if you just remove all of the ....., leaving
just a single getInt for each row?
(btw, your close() statements should almost certainly be in finally
blocks. Perhaps they are in your real code.)
--
Jon Skeet - <sk...@pobox.com>
http://www.pobox.com/~skeet/
If replying to the group, please do not mail me too
Tony Murphy
> Tony Murphy <tony_...@yahoo.com> wrote:
> > I've never had any noticable problems with queries over jdbc until
> > now, and was wondering if anyone could help explain whats going on.
> > When I run the following query using sqlplus (having hardcoded the
> > variables), its takes about 100ms to process, but when I run the same
> > query in java using jdbc, its takes upwards on a minute to process.
> > any help appreciated
>
> <snip>
>
> > while(rs.next()) {
> > x = rs.getInt(1);
> > .....
> > }
>
> Where is the time being taken? If it's in the "....." then we'll need to
> see the *contents* of that ..... before we've any idea whether it's your
> code or not.
>
> In particular, what happens if you just remove all of the ....., leaving
> just a single getInt for each row?
>
> (btw, your close() statements should almost certainly be in finally
> blocks. Perhaps they are in your real code.)
time been taken in the 'rs = pstmt.executeQuery(sql)' statement
Jon Skeet
> time been taken in the 'rs = pstmt.executeQuery(sql)' statement
How have you measured that? Bear in mind that output to the console may
be buffered.
Have you tried different drivers?
AV
times. If the difference is real, than ask your DBA check
"execution plan" of (i) plain sqlplus query and (ii) of
query produced by jdbc driver. Your query heavily
uses aggregate functions, and non-optimized execution
plan can make such a difference. What about
timing (i) simple Statement with fixed variables,
(ii) sqlplus and bind variables?
AlexV.
"Tony Murphy" <tony_...@yahoo.com> wrote in message
news:aa9724e3.02061...@posting.google.com...
Tony Murphy
If I replace the bind variables and hard code values in the where
clause, then performance is no longer a problem i.e.
String sql = "SELECT x, " +
" y, " +
" COUNT(*), " +
" AVG(z) " +
" FROM table_a " +
" WHERE date1 >= " +
" TO_DATE('2002-06-06 00:00:00','YYYY-MM-DD HH24:MI:SS') " +
" AND date1 < " +
" TO_DATE('2002-06-06 23:59:59','YYYY-MM-DD HH24:MI:SS') " +
" AND g = '07092209002' " +
" GROUP BY x,y " +
" ORDER BY y " ;
its like indexes aren't been used when I use bind variables. anyone
know why?
crappy
fact? (i.e., have you tried explain plan for the query with and
without the bind vars?)
it is in fact possible for the optimizer to use a less-than-perfect
query plan when you use bind variables as opposed to literals. what
version of oracle is it? and what is the parameter cursor_sharing set
to? and have the tables been analyzed recently?
tony_...@yahoo.com (Tony Murphy) wrote in message news:<aa9724e3.02061...@posting.google.com>...
Tony Murphy
thanks for help. using oracle 8.1.7, driver = jdbc:oracle:thin
have checked explain plans and the query with bind variables doesn't
use the index on the 'date1' column, but it uses the index on the 'g'
column. The query without bind variables uses the index on the 'date1'
column but doesn't use the index on the 'g' column.
Explain Plan #1
===============
OPERATION OPTIONS OBJECT_NAME POSITION
---------------- ----------------- ----------------- ----------
SELECT STATEMENT 94
SORT GROUP BY 1
TABLE ACCESS BY INDEX ROWID TABLE_A 1
INDEX RANGE SCAN idx_date1 1
Explain Plan #1
===============
OPERATION OPTIONS OBJECT_NAME POSITION
---------------- ----------------- ----------------- ----------
SELECT STATEMENT 21
SORT GROUP BY 1
TABLE ACCESS BY INDEX ROWID TABLE_A 1
INDEX RANGE SCAN idx_g 1
cursor_sharing
================
NAME TYPE VALUE ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ
DESCRIPTION
cursor_sharing 2 EXACT TRUE TRUE IMMEDIATE FALSE FALSE 'cursor
sharing mode
tables haven't been analyzed for some time.
will hassle my dba, thanks for the help in identifying this problem,
much appreciated
Gary Gapinski
how it will occasionally choose strange paths - perhaps it should be
regarded as a pessimizer); the TO_DATE function should have sufficed to
allow use of the associated index.
What might work is to convert the date strings to java.sql.Timestamps
(using java.util.DateFormat or somesuch) and use pstmt.setTimestamp to
bind.
crappy
that might help. in any case you should have your tables analyzed all
the time.
if it doesn't help in this particular situation, you might want to
think about using an optimizer hint to urge oracle to use the index on
the date column.
tony_...@yahoo.com (Tony Murphy) wrote in message news:<aa9724e3.02061...@posting.google.com>...
Jason Simpson
On 13 Jun 2002 at 03:30, Tony Murphy so generously added:
> thanks for help. using oracle 8.1.7, driver = jdbc:oracle:thin
> have checked explain plans and the query with bind variables doesn't
> use the index on the 'date1' column, but it uses the index on the 'g'
> column. The query without bind variables uses the index on the 'date1'
> column but doesn't use the index on the 'g' column.
I have been reading this thread, but not posting. Can someone please
explain how this is "fixed"? How does one tell it which index to use???
--
Jason Simpson
'A computer with an "intel inside" sticker is about as attractive as a
woman with an "HIV positive" tattoo' -- Volker Herminghaus-Shirai
Ruud de Koter
Gary Gapinski wrote:
>
> The optimizer behavior is unexpected (though I am continually surprised
> how it will occasionally choose strange paths - perhaps it should be
> regarded as a pessimizer); the TO_DATE function should have sufficed to
> allow use of the associated index.
I wouldn't call this unexpected. The cost based optimizer (that 's what we 're
talking about, not) makes its choices based on the availability of indexes
(among other objects), and the distribution of values in the indexes (how
selective the index will be for a given value). Obviuosly, when working with
bind variables, the suitability of the index from a distribution point of view
is harder to determine. The optimizer has no way to determine beforehand to what
value matches will be sought. This might (should) lead to another execution
plan. No surprise here, as far as I am concerned.
Regards,
Ruud de Koter.
>
> What might work is to convert the date strings to java.sql.Timestamps
> (using java.util.DateFormat or somesuch) and use pstmt.setTimestamp to
> bind.
--
--------------------------------------------------------------------------------------
Ruud de Koter HP OpenView Software Business Unit
Senior Software Engineer IT Service Management Operation
Telephone: +31 (20) 514 15 89 Van Diemenstraat 200
Telefax : +31 (20) 514 15 90 PO Box 831
Telnet : 547 - 1589 1000 AV Amsterdam, the Netherlands
Email : ruud_d...@hp.com
internet: http://www.openview.hp.com/products/servicedesk
intranet: http://ovweb.bbn.hp.com/itservicemanager
--------------------------------------------------------------------------------------
cel
One driver works faster than another one ...
"Tony Murphy" <tony_...@yahoo.com> wrote in message
news:aa9724e3.02061...@posting.google.com...