Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!feeder.erje.net!eu.feeder.erje.net!eternal-september.org!feeder.eternal-september.org!mx05.eternal-september.org!.POSTED!not-for-mail From: Martin Gregorie Newsgroups: comp.lang.java.databases Subject: Re: Query performance Date: Mon, 15 Apr 2013 22:05:08 +0000 (UTC) Organization: A noiseless patient Spider Lines: 44 Message-ID: References: <02ee34f7-b22a-4704-b504-4b24920092a3@googlegroups.com> Mime-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Injection-Date: Mon, 15 Apr 2013 22:05:08 +0000 (UTC) Injection-Info: mx05.eternal-september.org; posting-host="023384c1aa284d1305afa0f48d0dff5a"; logging-data="6364"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1+/5L+YjZkj1Uz/6Uv6fd0UBOfRPh+kUZw=" User-Agent: Pan/0.139 (Sexual Chocolate; GIT bf56508 git://git.gnome.org/pan2) Cancel-Lock: sha1:PROYEvYXF++k6RDzhYbxBclu8Ks= Xref: csiph.com comp.lang.java.databases:594 On Mon, 15 Apr 2013 11:58:59 -0700, Roedy Green wrote: > On Fri, 5 Apr 2013 04:16:56 -0700 (PDT), thomas.lehmann@adtech.com > wrote, quoted or indirectly quoted someone who said : > > >>Does somebody has an idea? > > If you have a performance problem, first thing is to use a prepared > statement. > > Also give it a chance to "warm up" fill caches, glean data to optimise > etc. > > Next, analyse it the way we did back in the 1970s, figuring out how the > head moves on the typical transaction. > That isn't usually the gotcha in a modern database: the usual problem is that the DBA designed the database physical schema (by that I mean the way each table is stored and the indexes used to support prime keys and other access paths) from the overall system design but the developers forgot to tell him about the access paths they actually use. They then compound the problem by using very small datasets during development with the result that during development the DB runs like greased lightning because the entire database fits in RAM. Then, when the system goes live and volumes get closer to the design volumes they wonder why everything starts to run like molasses. It gets even worse if an SQL neophyte uses an automatic SQL generator to produce the SQL and never looks at the result: some of the code these things can spit out is braindead stuff. One case I was asked to look at one Friday evening was taking 25 seconds to insert new account details with a mere 25,000 accounts in the system. PowerBuilder SQL on a Sybase database was the culprit: a 5 year old could have written better SQL. Once I'd seen the query and schema it was a straight forward job to manually rewrite and retest the query. This put the performance back to the expected few milliseconds per transaction. And I still got to the pub on time. -- martin@ | Martin Gregorie gregorie. | Essex, UK org |