Re: Oracle JDBC Driver ps.setTimestamp() slows down query execution many times

From: Juha Laiho (Juha.Laiho_at_iki.fi)
Date: 01/20/05 

Date: Thu, 20 Jan 2005 16:53:53 +0000 (UTC)

"Janusz" <januszstu@wytnij.spam.wp.pl> said:
>I have the following problem. I perform simple sql query to Oracle database,
>there are 2 date variables in the query.
>
>final String sSql = "select count(*) from test where start_date > ? and
>start_date < ?";
>
>When I set the date variables using
> preparedStatement.setTimestamp(1, new Timestamp(startDate.getTime()));
> preparedStatement.setTimestamp(2, new Timestamp(endDate.getTime()));
>
>this simple query executes 10500 miliseconds [ms]
>
>but when the dates are hardcoded in SQL like this:
>final String sSql = "select count(*) from test where start_date >
>'2005-01-14' and start_date < '2005-01-18'";
>
>the query runs 50 [ms]
>
>In SQLPlus this simple query is also very fast - 30 [ms]

After seeing Joe's responses (about use of indexes etc), I'll try to add
something; check a couple of variations of your parametrized query.

What happens if you, instead of providing the timestamps with setTimestamp,
provide them as strings (as you do in your hardcoded SQL example)?

How about using an explicit TO_DATE() (hmm.. was it TO_DATE() or was there
something else for greater accuracy?) conversion around your ?-marks in
the query?

Also, check that the index makes sense; so, chek index statistics, and try
to get an idea whether the index is at least somewhat correctly balanced.
And remember, the analyze results will be discarded if they're too old,
so even just re-analyzing an index may help. 

-- 
Wolf  a.k.a.  Juha Laiho     Espoo, Finland
(GC 3.0) GIT d- s+: a C++ ULSH++++$ P++@ L+++ E- W+$@ N++ !K w !O !M V
         PS(+) PE Y+ PGP(+) t- 5 !X R !tv b+ !DI D G e+ h---- r+++ y++++
"...cancel my subscription to the resurrection!" (Jim Morrison)

Relevant Pages

  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)
  • How do I do Paging through a large dataset via Stored Procedures
    ... Paging by dynamically altering the SQL Query ... Create stored procedures ... SELECT * FROM STUDENTS ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Populating a list -- learning Access
    ... It is kinda funny -- I started using databases in the early 80's with dBase and, for years, never knew that I knew SQL! ... If you are on a form or report, the most important property is the NAME, because that is how you refer to it in code. ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)
  • Re: Populating a list -- table structure?
    ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... I have one report complete, ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)
Loading