J'ai une petite question sur les performances des requêtes. Depuis quelques jours, je me débats avec les performances d'une requête. Dans MS Access, j'ai une requête sans aucune condition (avec ~15 jointures) qui me renvoie ~50 colonnes (id, nom, etc...) et ~1200 enregistrements (tous les enregistrements). Et le temps d'exécution de cette requête est d'environ 15 minutes. Mais lorsque j'ajoute un critère simple : WHERE ID < 10000(ou tout autre nombre plus grand que mon dernier ID) (mon ID maximum est 1139) toute la requête s'exécute en un clin d'oeil. Quelqu'un pourrait-il m'expliquer pourquoi une condition aussi simple accélère énormément ma requête ?
Voici la requête (générée par Access dans 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 boosterCette requête n'est pas la mienne, j'ai juste hérité de quelqu'un d'autre :) Il n'y a pas de tables liées à MSSQL Server... toutes sont dans le fichier de base de données MS Access.
Merci d'avance pour l'explication.
