Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.java.databases > #593

Re: Query performance

Show all headers | View raw

On Fri, 05 Apr 2013 04:16:56 -0700, thomas.lehmann wrote:

> Hi,
> 
> using a very simple Java application I've connected to a database that
> is Oracle. The code just uses standard JDBC handling like Connection,
> Statement and ResultSet.
> 
> Now ...
> 
> A very specific SQL statement takes about 250ms just providing an ID.
> Using "SQuirrel" or "SQL Plus" as clients exactly the same query takes
> 5ms.
> 
> SQuirrel is in Java and - so far I have seen - the same JDBC handling
> has been taken as I did. For Squirrel to say: I have been using the same
> Oracle (thin) driver and same connection parameter (for sure).
> 
> So what might be the reason for the performance problem?
> 
> Does somebody has an idea?
>
Here are a couple of things to look at:

1) Are the run-time environments identical, i.e. could SQuirrel be using
   prepared statements while your program is interpreting a String
   containing the query? The latter will almost certainly be slower,
   especially if SQuirrel prepares the query as part of its initialisation
   procedures.

2) Is your code running against exactly the same database being accessed
   by SQuirrel, i/e is SQuirrel using a production database while you're
   using a development database? If not, are you absolutely sure the two
   physical schemas are identical? 

   The reason I ask is that sometimes a schema gets optimised as part of
   a tuning exercise but the results don't get checked into the 'project
   standard' schema. Consequently, any database clone that's set up using
   the latter will not have the same performance characteristics.

3) If neither of the above are causing problems, I suggest you use
   Oracle's query analysis tools to see what this query is actually doing
   and what parts of the query are costing the most to run. Once you've
   done that you should know exactly what is using the most time and
   resources. That will let you optimise the schema and/or the query.

   I don't know what Oracle calls its query analyser or how its used. 
   Under Postgres its called EXPLAIN and you prefix the query with it
   which causes the execution plan to be displayed rather than running 
   the query. Its important that you run this against the target database
   because the indexes, table sizes etc can affect the way that the query 
   is executed.


-- 
martin@   | Martin Gregorie
gregorie. | Essex, UK
org       |

Back to comp.lang.java.databases | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Query performance thomas.lehmann@adtech.com - 2013-04-05 04:16 -0700
  Re: Query performance "John B. Matthews" <nospam@nospam.invalid> - 2013-04-05 09:30 -0400
  Re: Query performance David Kerber <dkerber@WarrenRogersAssociates.invalid> - 2013-04-11 08:18 -0400
  Re: Query performance Roedy Green <see_website@mindprod.com.invalid> - 2013-04-15 11:58 -0700
    Re: Query performance Martin Gregorie <martin@address-in-sig.invalid> - 2013-04-15 22:05 +0000
      Re: Query performance Arne Vajhøj <arne@vajhoej.dk> - 2013-04-15 21:14 -0400
    Re: Query performance Arne Vajhøj <arne@vajhoej.dk> - 2013-04-15 21:12 -0400
    Re: Query performance Lew <lewbloch@gmail.com> - 2013-04-15 19:01 -0700
  Re: Query performance Martin Gregorie <martin@address-in-sig.invalid> - 2013-04-15 21:39 +0000
  Re: Query performance Arne Vajhøj <arne@vajhoej.dk> - 2013-04-15 21:18 -0400
  Re: Query performance Harry Tuttle <SOZRBLNTLEEE@spammotel.com> - 2013-04-16 14:59 +0200

csiph-web