June 30, 2003 at 8:47 am
Please could someone explain to me why the following query is so slow (1.5 Mins):
select distinct
f.fund, f.fund_id
from
contributions c
inner join investments i on i.investment_id = c.investment_id
inner join funds f on i.fund_id = f.fund_id
inner join member_filter on member_filter.member_id = c.member_id
yet if I include the member_id in the select query in a count statement it runs in 2 seconds:
select
f.fund, f.fund_id, count(c.member_id)
from
contributions c
inner join investments i on i.investment_id = c.investment_id
inner join funds f on i.fund_id = f.fund_id
inner join member_filter on member_filter.member_id = c.member_id
group by
f.fund, f.fund_id
I really don't unserstand the difference in speed!
Many thanks.
Edited by - ruairidh on 06/30/2003 08:59:50 AM
June 30, 2003 at 9:12 am
is member_id a primary key or a part of the primary key?
From may exp. when I call the primary keys in the select statement, SQL engine uses the index fully;
Try not use distinct, but group by;makes it faster
regards lmt
June 30, 2003 at 9:22 am
The difference originates from the use of the GROUP BY clause.
In your first query, SQL Server will build the complete result set, gathering all data for each and every f.fund and f.fund_id.
Once it has the complete set, it will start removing all duplicates from it.
In the second one, SQL Server will use a more intelligent approach, that limits the initial result set.
Try using SET SHOWPLAN_TEXT ON before your query. You can see the execution plan, which will give you detailed information what is going on under the hood.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply