john@beaudin.UUCP (John Beaudin) (10/28/90)
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. Some of our 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. The statistics mentioned that some columns were not referenced very frequently, while others requiring joins were accessed often. It seems that 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. 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, and I'm uncertain of the accuracy of the terms I used. Does Sybase have these stats available for database tuning? What kind of info can they tell you? What do other products tell you about the health of the database. Are these some or all of the bells & whistles I hear vaguely described? "...oh yeah, sure Sybase is the fastest in the industry, but add all the Big database bells & whistles and it'll be as slow as the others..." -- My .signature is awaiting apropriate display technology
cooper@beno.CSS.GOV (Dale Cooper) (10/29/90)
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!!! | @@| @@|