Path: utzoo!utgpu!jarvis.csri.toronto.edu!clyde.concordia.ca!uunet!dev!jkrueger
From: jkrueger@dev.dtic.dla.mil (Jonathan Krueger)
Newsgroups: comp.databases
Subject: Re: optimising sql
Message-ID: <3601@dev.dtic.dla.mil>
Date: 5 Feb 90 03:13:32 GMT
References: <1990Jan31.010510.15242@welch.jhu.edu>
Organization: Defense Technical Information Center (DTIC), Alexandria VA
Lines: 32

francois@welch.jhu.edu (Francois Schiettecatte) writes:

>Does anyone out there know of any books which might help
>me to optimise sql statements which are to be sent to a 
>database. What I really need are a set of guidelines, of
>does and donts, of what type of query to use, etc.

It isn't this simple.

First design your database.  There are books on this.  If you do it
right, you'll find most commonly executed queries can be made to go
fast without special attention from the application programmer.

Next get the right performance analysis tools.  At a minimum, you need
a way to measure how expensive your queries are and a way to find out
how your engine is executing them.  Use these tools.  You'll likely
find that the queries you thought were problems can indeed be made to
go faster, but that it won't help your application nearly as much as
optimizing others you never considered.  This may lead you back to
database design again.

Then, and only then, is it worth playing games with "exists" versus
"in".  Yes, certain families of queries will be poorly optimized across
broad classes of vendor products and versions.  You're unlikely to
learn which ones in advertiser rags like "Database Programming and
Design".

-- Jon
-- 
Jonathan Krueger    jkrueger.dtic.dla.mil   uunet!dgis!jkrueger
Isn't it interesting that the first thing you do with your
color bitmapped window system on a network is emulate an ASR33?