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

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

Re: Query performance

Show all headers | View raw

Roedy Green wrote:
> If you have a performance problem, first thing is to use a prepared
> statement.

That in and of itself is not enough to guarantee performance improvement, but it 
has other significant benefits.

If it is a simple query that is run frequently within a single database transaction then 
there can be significant improvement with a 'PreparedStatement' and suitable tuning 
of the database control parameters.

> Also give it a chance to "warm up" fill caches, glean data to optimise
> etc. 

If the aim is to reflect possible real-world performance then the setup should 
mimic real-world conditions.

Roedy's advice about cache applies even more so to JVM considerations.

You haven't told us how you measured the times, nor if your methodology is rigorous, 
OP, and that can skew your results to the point of uselessness. Database connections 
tend to be part of longer-running server-side apps, for example, and should be 
measured in the context of a JVM that has been running for a while, not one that 
starts up as part of the time you measure.

> Next, analyse it the way we did back in the 1970s, figuring out how
> the head moves on the typical transaction.  Then you think about how

This is likely cargo-cult programming, especially in a hard-driving system like 
Oracle, and the more so with various secondary caches if they're there (like JPA).

Oracle has lots and lots of things it does to handle those low-level details for you, 
and their tuning is the purview of an accomplished Oracle DBA. It is good to know 
about such things as a Java programmer, but not to the point of believing one can 
outfox the system.

> to reduce the number of i/os, position the files, use multiple drives,
> SSDs etc.  You can sometimes change the order of the work of a
> transaction.

As a Java programmer I tend to black-box those layers in my thinking.

-- 
Lew

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