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

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

Re: Query performance

Show all headers | View raw

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       |

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