Re: query performance -- revised!

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:3ed5eea7$0$29716$ed9e5944_at_reading.news.pipex.net...
> I don't think sy had any issue with what you had suggested, it was
> suggestions 5 onwards that are suspect.

Any ide rewriting the query to a semantically equivalent form is a suspect, because that is what optimiser does automatically. Rewriting that makes sence is usually different: a user realizes that he doesn't need some column from that particular table, therefore the table can be omitted from the query altogether.

Let me clarify the issues with the original proposals.

> 1. change your Or to an 'IN'

I tested

select * from hz_parties
where party_id = 1 or party_id = 2

and the plan contains "inlist iterator" node. Optimiser knows about transforming Or to an 'IN' already!

2. if Address has less records than the other tables(try it anyway) make it a sub-query with an exists
where exists (select 1 from Address where blah...

I explained that nested form is almost always inferior to flat query in the other message.

3. pr.remove_from_mdlocator <> 1
try to chnage that to an 'IN' not equals can be slow

4. Possibly put Address and all its qualifiers in a sub-query with Exists.

Rewriting subqueries without realizing what is really going on is just as naive as my suggestion to change tables order in the where clause. Received on Thu May 29 2003 - 12:44:40 CDT