Path: utzoo!attcan!uunet!cs.utexas.edu!sdd.hp.com!wuarchive!rex!ukma!seismo!beno!cooper
From: cooper@beno.CSS.GOV (Dale Cooper)
Newsgroups: comp.databases
Subject: Re: Sybase query
Message-ID: <49175@seismo.CSS.GOV>
Date: 29 Oct 90 15:30:52 GMT
References: <1453@beaudin.UUCP>
Sender: usenet@seismo.CSS.GOV
Organization: Center for Seismic Studies, Arlington, VA
Lines: 67

In article <1453@beaudin.UUCP> john@beaudin.UUCP (John Beaudin) writes:
>A friend familiar with another database told me a tale recently (I'm
>considering Sybase as a database). Here's what she said:
>
>"At my shop we had a query which took 3 weeks to finish...

	[stuff deleted]

Ouch.

>...database admins took a look at it and the files involved. They determined,
>from statistics available from the database server, that the files needed
>to be tuned... 

>...some of the joins were very complex and resource consuming. Therefore
>the DBA's merged some relations and partitioned others. The query now runs
>in an hour...

>Make sure that any database you get also has these stats available so that 
>database tuning is feasible and more scientific, as compared to making 
>intelligent guesses. 

Good advice...

>Otherwise, you run the risk of not knowing where bottlenecks are and 
>eventually having to rebuild the whole database, which could take a few days, 
>if things really get sloppy."

>Of course I'm paraphrasing her...

	[Sybase specific questions deleted]

But...

While I don't intend to pass judgement on your friend's database environment
without fair examination, some of her remarks indicate to me that "they" 
didn't really spend a lot of time designing the database and it's primary
relations.  In many shops, it is unimaginable to stop production to merge
some relations or repartition others.  In my shop, that would mean serious
problems since the tasks we are involved with are realtime in nature.

While statistics are nice, as they say in the medical world...prevention is 
the best medicine.  Since I work in an Oracle environment where optimization
statistics are a thing of the future, we have to be very conscientious in our 
database design and query development.  We have to isolate problems quickly in 
query performance and create better solutions either by the implementation 
of indices, usage of temporary tables and the like...but in most cases 
development of smarter queries.  While Oracle claims that they have an 
optimizer of sorts, it falls far short of the statistical optimizers found 
with Ingres and Sybase.   Therefore, design plays a very heavy role in our
site databases.

Query optimization is a tough task - with or without statistics - and I
look forward to the day when Oracle makes it a standard on our platform,
but for now, building a finely structured database and developing smart
queries are our key to success.


                          /      /\_
        |               _//\####/ 
      | | | |              _^ _^_
     |||||||||| |         (o)( O)                    |Dale Cooper
--|||||||||||||||||----- _-_<>-_- -------------------|Center for Seismic Studies
     |||| || | |        {::(::::}                    |Arlington, VA
      | |  |             \_@@\__/     ACK-PHFT!!!
        |                  @@|
                           @@|