CODEWITHSUNDEEP
sqlperformancems-access
otemek
otemek

Reputation: 77

performance in MS Access query

Got a quick question about query performance. For last few days i've been struggling with a query performance. In MS Access I have a query without any conditions (with ~15 joins) which returns me ~50 columns(id, name, etc...) and ~1200 records (all records). And the execution time of this query is about 15 min. But when I add one simple criteria: WHERE ID < 10000(or any other number larger than my last id) (my max ID is 1139) all the query is executing in the blink of an eye. Could someone explain me why such simple condition is extremly speeding-up my query?

Here is query (generated by Access in SQLView):

SELECT MasterTableT.ID
,[General:CompanyProgrammeNameT].[Company Programme  Abbreviation] AS [Company Programme]
,[General:BlockNameT].[Block Name]
,[General:Position/JobTitleT].[Position/Job Title]
,[General:DeliveryStreamT].[Delivery Stream]
,[General:AgileTeamT].[Agile Team name]
,MasterTableT.[Modification Date]
,[General:ProgrammePeopleT].[Full Name] AS [Functional Manager]
,MasterTableT.[Expected: StartDate]
,MasterTableT.[Expected: EndDate]
,DateDiff("m", [MasterTableT] ! [Expected: StartDate], [MasterTableT] ! [Expected: EndDate]) AS [Expected Duration of involvement (months)]
,[General:LocationsT].Locations AS [Expected location]
,[General:TravelT].Travel AS [Expected Travel]
,[General:ProfileTypeT].[Profile type] AS [Expected Profile Type]
,[General:HireTypeT].[Hire Type] AS [Expected Type of Hire]
,[General:BA/GF and UnitT].[BA/GF and Unit Name] AS [If allocated, coming from BA/GF]
,MasterTableT.[Request Comments]
,[General:RecruitmentStatusT].[Recruitment Status]
,MasterTableT.[Employee Name]
,MasterTableT.[Employee User ID Number]
,MasterTableT.[Employee SOFT ID]
,[General:LocationsT_1].Locations AS [Actual Location]
,MasterTableT.[Actual: Allocation (%)]
,MasterTableT.[Actual: Start date in the Programme]
,MasterTableT.[Agreed: End Date in the programme]
,DateDiff("m", MasterTableT ! [Actual: Start Date in the programme], MasterTableT ! [Agreed: End Date in the programme]) AS [Time remaining in the Programme]
,[General:ProfileTypeT_1].[Profile type] AS [Actual Profile Type]
,[General:HireTypeT_1].[Hire Type] AS [Actual Type of Hire]
,[General:BA/GF and UnitT_1].[BA/GF and Unit Name] AS [Actual BA/GF and Unit]
,[General:ProgrammePeopleT_1].[Full Name] AS [Recruitment Driver]
,[General:ProgrammePeopleT_2].[Full Name] AS [Recruitment Manager]
,[General:ProgrammePeopleT_2].[Company ID] AS [Recruitment Manager ID]
,[General:ProgrammePeopleT_2].[Cost Center] AS [Recruitment Manager Cost Center]
,MasterTableT.[HR Comments]
,MasterTableT.[NIIDS number]
,IIf(IsNull([Actual: Start date in the Programme]), [Expected: StartDate], [Actual: Start date in the Programme]) AS [Expected/Actual StartDate]
,IIf(IsNull([Agreed: End Date in the programme]), [Expected: EndDate], [Agreed: End Date in the programme]) AS [Expected/Agreed End Date]
,MasterTableT.[Expected Allocation %]
,MasterTableT.[Training: GSU Intro]
,[General:PmoOrgLevel3].OrgL3Name
,[General:PmoOrgLevel4].OrgL4Name
,MasterTableT.[PMO: Org Level 5]
,MasterTableT.[Prioritized Recruitment]
FROM (
(
    (
        (
            (
                (
                    (
                        (
                            (
                                (
                                    (
                                        (
                                            (
                                                (
                                                    (
                                                        (
                                                            (
                                                                (
                                                                    (
                                                                           MasterTableT LEFT JOIN [General:AgileTeamT] ON MasterTableT.[Team Name ID] = [General:AgileTeamT].[ID Agile Team]
                                                                        ) LEFT JOIN [General:BA/GF and UnitT] ON MasterTableT.[Coming from: BA/GF/External and unit ID] = [General:BA/GF and UnitT].[ID BA/GF and Unit]
                                                                    ) LEFT JOIN [General:BlockNameT] ON MasterTableT.[Block Name ID] = [General:BlockNameT].[ID Block]
                                                                ) LEFT JOIN [General:DeliveryStreamT] ON MasterTableT.[Delivery Stream ID] = [General:DeliveryStreamT].[ID Delivery Stream]
                                                            ) LEFT JOIN [General:TravelT] ON MasterTableT.[Expected: Travel ID] = [General:TravelT].[ID Travel]
                                                        ) LEFT JOIN [General:HireTypeT] ON MasterTableT.[Expected: Hire Type ID] = [General:HireTypeT].[ID Hire Type]
                                                    ) LEFT JOIN [General:LocationsT] ON MasterTableT.[Expected: Location ID] = [General:LocationsT].[ID Location]
                                                ) LEFT JOIN [General:CompanyProgrammeNameT] ON MasterTableT.[Company Programmes ID] = [General:CompanyProgrammeNameT].[ID Company Programmes]
                                            ) LEFT JOIN [General:Position/JobTitleT] ON MasterTableT.[Position/Job Title ID] = [General:Position/JobTitleT].[Position/Job title ID]
                                        ) LEFT JOIN [General:ProfileTypeT] ON MasterTableT.[Expected: Profile Type ID] = [General:ProfileTypeT].[ID Profile type]
                                    ) LEFT JOIN [General:ProgrammePeopleT] ON MasterTableT.[Functional Owner ID] = [General:ProgrammePeopleT].[ID Programme People]
                                ) LEFT JOIN [General:RecruitmentStatusT] ON MasterTableT.[Recruitment Status ID] = [General:RecruitmentStatusT].[ID Recruitment Status]
                            ) LEFT JOIN [General:LocationsT] AS [General:LocationsT_1] ON MasterTableT.[Actual: Location ID] = [General:LocationsT_1].[ID Location]
                        ) LEFT JOIN [General:ProfileTypeT] AS [General:ProfileTypeT_1] ON MasterTableT.[Actual: Profile type ID] = [General:ProfileTypeT_1].[ID Profile type]
                    ) LEFT JOIN [General:HireTypeT] AS [General:HireTypeT_1] ON MasterTableT.[Actual: type of Hire ID] = [General:HireTypeT_1].[ID Hire Type]
                ) LEFT JOIN [General:BA/GF and UnitT] AS [General:BA/GF and UnitT_1] ON MasterTableT.[Actual: BA/GF/External and Unit ID] = [General:BA/GF and UnitT_1].[ID BA/GF and Unit]
            ) LEFT JOIN [General:ProgrammePeopleT] AS [General:ProgrammePeopleT_1] ON MasterTableT.[Recruitment Driver ID] = [General:ProgrammePeopleT_1].[ID Programme People]
        ) LEFT JOIN [General:ProgrammePeopleT] AS [General:ProgrammePeopleT_2] ON MasterTableT.[Recruitment Manager ID] = [General:ProgrammePeopleT_2].[ID Programme People]
    ) LEFT JOIN [General:PmoOrgLevel3] ON MasterTableT.[PMO: Org Level 3] = [General:PmoOrgLevel3].OrgL3ID
)
LEFT JOIN [General:PmoOrgLevel4] ON MasterTableT.[PMO: Org Level 4] =    [General:PmoOrgLevel4].OrgL4ID
WHERE (((MasterTableT.ID) < 10000)); // <-- and this condition is my a query booster

This query is not mine, i've just inherited from someone else :) There are no any MSSQL Server related tables...all are in the MS Access database file.

Thanks in advance for explaination.

Upvotes: 2

Views: 77

Answers (1)

Peter
Peter

Reputation: 27944

It probably gets a different execution path when you add the extra condition. To see the execution plans you can enable it. Without knowing the query I guess add id will change the order on how tables are joined, instead of first joining some tables and then join it to the table with the ids, the process gets done in a different order where the fist joins only deliver a few records instead of the whole tables.

Upvotes: 2

Related Questions