Re: How can I optimize this SQL

"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.

>
> 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 - 11:44:30 CDT