X-Received: by 10.224.207.2 with SMTP id fw2mr560069qab.6.1366077702135; Mon, 15 Apr 2013 19:01:42 -0700 (PDT) X-Received: by 10.50.187.133 with SMTP id fs5mr28284igc.12.1366077702070; Mon, 15 Apr 2013 19:01:42 -0700 (PDT) Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!news.glorb.com!ca1no46415288qab.0!news-out.google.com!ef9ni42441qab.0!nntp.google.com!ca1no46415284qab.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail Newsgroups: comp.lang.java.databases Date: Mon, 15 Apr 2013 19:01:41 -0700 (PDT) In-Reply-To: Complaints-To: groups-abuse@google.com Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=69.28.149.29; posting-account=CP-lKQoAAAAGtB5diOuGlDQk0jIwmH0T NNTP-Posting-Host: 69.28.149.29 References: <02ee34f7-b22a-4704-b504-4b24920092a3@googlegroups.com> User-Agent: G2/1.0 MIME-Version: 1.0 Message-ID: Subject: Re: Query performance From: Lew Injection-Date: Tue, 16 Apr 2013 02:01:42 +0000 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Xref: csiph.com comp.lang.java.databases:598 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, b= ut it=20 has other significant benefits. If it is a simple query that is run frequently within a single database tra= nsaction then=20 there can be significant improvement with a 'PreparedStatement' and suitabl= e tuning=20 of the database control parameters. > Also give it a chance to "warm up" fill caches, glean data to optimise > etc.=20 If the aim is to reflect possible real-world performance then the setup sho= uld=20 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,=20 OP, and that can skew your results to the point of uselessness. Database co= nnections=20 tend to be part of longer-running server-side apps, for example,=A0and shou= ld be=20 measured in the context of a JVM that has been running for a while, not one= that=20 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=20 Oracle, and the more so with various secondary caches if they're there (lik= e JPA). Oracle has lots and lots of things it does to handle those low-level detail= s for you,=20 and their tuning is the purview of an accomplished Oracle DBA. It is good t= o know=20 about such things as a Java programmer, but not to the point of believing o= ne can=20 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. --=20 Lew