Re: How can I optimize this SQL

"Ryan Gaffuri" <rgaffuri_at_cox.net> wrote in message news:1efdad5b.0310060844.32263cc8_at_posting.google.com...
> "Randy Harris" <randy_at_SpamFree.com> wrote in message
news:<F2Pfb.5806$Hd6.3986630_at_newssvr28.news.prodigy.com>...
> > I'm looking for advice, errr, again...
> >
> > I have an SQL that gets executed repeatedly in an application that is
quite
> > slow. (It works, but slows down the app).
> >
> > There are two tables involved:
> > Events and Certs
> > In Events, EventID is the PK, the table contains primary events as well
as
> > Cert events.
> > Certs contains just two columns - Certification events and MainEvent,
which
> > is a link back to Events
> >
> > This SQL gets the needed count:
> >
> > SELECT Count(C.CertEvent)
> > FROM Events E, Certs C, Eventsl TE
> > WHERE E.EventID=C.MainEvent(+)
> > AND C.CertEvent=TE.EventID(+)
> > AND TE.Task_ <> 'Audit'
> > AND E.EventID=3158
> Index CertEvents if its not indexed. Or else you are doing a full
> table scan. If this table is non-transaction, use a bitmap index, its
> the fastest way to do a count. Do NOT use it if you are doing
> insert,update, and deletes on the table.
>
> TE.Task <> 'Audit' forces a full table scan. Is this appropriate? If
> not, try to replace it with an 'IN' and use an index.
>
> Outer joins also hurt. Why do you need it? Is it essential?
>
> I need the explain plan.

Thanks Ryan (and others who offered suggestions). I was unaware that I was forcing the full table scan, no wonder it was slow. (I know, I know, I need to learn to use the explain plan). That query was run repeatedly and really killing application responsiveness. I've been restructuring the data to simply avoid needing to use it. Preliminary tests look like I can cut the form refresh from an average of about 13.5 seconds to about 2.5. Should make management happy. :-)

> > As you can see, it gets a count of all Cert events, linked to a specific
> > primary event which are not of type Audit. There are indexes on all
> > involved fields (I'm using 8.1.7.4). The Events table is medium size,
Certs
> > is quite small.
> >
> > Is there a way to get that count more efficient by restructuring the
SQL?
> > Or.. Perhaps I simply have an inefficient design and the CertEvent ID
should
> > simply be kept in the Events table? I think if I added the Task type in
the
> > Certs table, it will violate normalization (same data in two places),
but
> > would probably make the query faster. I do have the option of changing
the
> > table structure if I can improve performance significantly.
> >
> who cares about normalization? performance is more important. However,
> keep in mind maintenance. How often do you run this query? Is it
> important to de-normalize this?
>
> > I'd be grateful for any suggestions.
Received on Mon Oct 06 2003 - 15:13:34 CDT