Link to home
Create AccountLog in
Avatar of raheis

asked on

MS Access NOT IN Subquery is Slow, ideas on improving performance


The following query is taking a long time to return, any ways to speed it up wuld be appreciated.

SELECT dbo_resource.last_name & ', ' & dbo_resource.first_name AS portfolio_mgr, dbo_portfolio.portfolio, dbo_resource_1.last_name & ', ' & dbo_resource_1.first_name AS team_lead, dbo_role.role, RES.last_name & ', ' & RES.first_name AS resource, RES.resource_code, dbo_resource_provider.resource_provider, dbo_location.location, dbo_skill.skill_name, dbo_resource_skill.years_experience
FROM (((((((dbo_resource AS RES INNER JOIN dbo_portfolio ON RES.portfolio_ID = dbo_portfolio.portfolio_ID) INNER JOIN dbo_resource ON dbo_portfolio.portfolio_mgr_ID = dbo_resource.resource_ID) INNER JOIN dbo_resource AS dbo_resource_1 ON RES.team_lead_ID = dbo_resource_1.resource_ID) INNER JOIN dbo_role ON RES.primary_role_ID = dbo_role.role_ID) INNER JOIN dbo_resource_provider ON RES.resource_provider_ID = dbo_resource_provider.resource_provider_ID) INNER JOIN dbo_location ON RES.location_ID = dbo_location.location_ID) INNER JOIN dbo_resource_skill ON RES.resource_ID = dbo_resource_skill.resource_ID) INNER JOIN dbo_skill ON dbo_resource_skill.skill_ID = dbo_skill.skill_ID

WHERE (((dbo_portfolio.portfolio) Like '*Functional*') AND ((RES.resource_ID) Not In (SELECT DISTINCT R.resource_ID
FROM (((((((dbo_demand_allocation AS da INNER JOIN (dbo_demand AS d INNER JOIN (dbo_resource AS R INNER JOIN dbo_demand_resource AS dr ON R.resource_ID = dr.resource_ID) ON d.demand_ID = dr.demand_ID) ON da.demand_resource_ID = dr.demand_resource_ID) INNER JOIN dbo_portfolio AS P ON d.portfolio_ID = P.portfolio_ID) INNER JOIN dbo_resource AS R1 ON P.portfolio_mgr_ID = R1.resource_ID) INNER JOIN dbo_role AS ROLE ON dr.role_ID = ROLE.role_ID) INNER JOIN dbo_resource AS R2 ON R.team_lead_ID = R2.resource_ID) LEFT JOIN dbo_resource AS R3 ON d.project_mgr_ID = R3.resource_ID) INNER JOIN dbo_portfolio AS P1 ON R.portfolio_ID = P1.portfolio_ID) INNER JOIN dbo_resource AS R4 ON P1.portfolio_mgr_ID = R4.resource_ID
WHERE (((P1.portfolio) Like '*Functional*') AND ((DateAdd("m",[DA.demand_month_offset],[D.start_date]))>Now() And (DateAdd("m",[DA.demand_month_offset],[D.start_date]))<DateAdd("m",12,Now())))
)) AND ((RES.Active)=-1))
ORDER BY dbo_resource.last_name & ', ' & dbo_resource.first_name, dbo_portfolio.portfolio, dbo_resource_1.last_name & ', ' & dbo_resource_1.first_name, dbo_role.role, RES.last_name & ', ' & RES.first_name, RES.resource_code, dbo_resource_provider.resource_provider, dbo_location.location, dbo_skill.skill_name;
Avatar of skullnobrains

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
You're doing several things here that harm performance:

1) Not In is a performance dog.  Using a LEFT JOIN as skullnobrains suggests above is much better, and I would also suggest Not Exists would do a better job as well

2) Your criterion of (dbo_portfolio.portfolio Like '*Functional*') will force a full table scan.  If there is no way around that, so be it

3) Based on the table names, it looks like you may be using tables linked from an external server.  If so, and if all of the data you need for the query reside on that server, you are better off redoing this as a pass-through query
Avatar of raheis


I need help with the syntax, can you amend the sql I have attached; sql.txt.

Many Thanks.
it will be easier to help you knowing
- what you tried to change
- what error message you received
- what a human-readable version of the query looks like : it is easier to write, to debug, and the error messages usually are much easier to understand as well
And given the complexity of this query, a sample database is likely necessary as well.
Avatar of raheis


I did have some trouble getting the syntax right, but this has fixed the perforamance problem.

Many Thanks, R.