How to make this query more efficient?

[jsql12]

Hi everybody,

The following query is very costly in terms of IO:

SELECT distinct m.memberID, [mem number] from members m left join discounts d on m.memberID = d.[id]

I'd like to avoid using distinct. So, I'm thinking of using a subquery with where exists. So is there a way of rewriting the above query with WHERE EXISTS to make it less costly in terms of IO.

Any help is greatly appreciated.

Thank you!

 

[danvlas]

SELECT m.memberID, [mem number] from members m left join discounts d on m.memberID = d.[id] Group By m.memberid, [mem number]

Where exists...what? There is a left join without any condition in your Select...

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[jsql12]

Thanks danvlas. I appreciate your help

 

[ESquared]

It's not really clear what it is you're looking for.

I'll assume that you just want to know if any discount exists for the member (but not any other information from the discounts table, or the exact number of discounts).

First, if you did want other information from the discounts table you'd move the distinct/group by to a derived table:

SELECT
   m.memberID,
   m.[mem number],
   hasdiscounts = case when d.id is not null then 1 else 0 end
from
   members m
   left join (
      select distinct id from discounts -- could do a group by instead to get other information such as max/min/count
   ) d on m.memberID = d.[id]

This way the distinct/group by is applied as early as possible and the other table is joined only after the work is done, which is more efficient.

A way to use WHERE EXISTS is:

SELECT
   m.memberID,
   m.[mem number],
   hasdiscounts = case when exists (select * from discounts d where d.id = m.memberid) then 1 else 0 end
from
   members m

That is a correlated subquery. The thing about them is that they can perform magnificently, and they can be utter dogs, all depending on the exact layout and contents of each table and on the conditions you provide on the subquery and the main query. So testing is in order to see what's the most efficient.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]