Prédicat non poussé dans la jointure gauche sur MSSQL

J'essaie d'optimiser une vue complexe, qui a été réduite à un problème simple.

MSSQL se joint à deux tables, en partie sur un prédicat de requête principale. Le problème est que ce server n'utilise pas ce prédicat pour la table jointe tant qu'il n'est pas réellement joint et que le résultat est plus de données lues dans la table et que la requête est plus lente.

Exemple de données

Pour montrer ce problème, j'ai créé un exemple simple qui représente une partie des données de vue:

create table A ( ID numeric not null identity, D date not null, ); create table B ( ID numeric not null identity, A_ID numeric not null, DATE_FROM date not null, DATE_TO date not null ) declare @i int = 0 declare @j int declare @k int declare @batch int = 1000 declare @a_id int declare @month date begin transaction while @i < 2000 begin set @j = 0 set @month = dateadd(mm, @i, '1950-01-01') while @j < 20 begin insert into a (d) values (@month); select @a_id = scope_identity() set @k = 0 while @k < 30 begin insert into b ( a_id, date_from, date_to ) values ( @a_id, @month, dateadd(dd, round(rand() * 100, 0), @month) ); set @k = @k + 1; if (@batch = 0) begin set @batch = 1000 commit; begin transaction end set @batch = @batch - 1; end set @j = @j + 1; end set @i = @i + 1; end commit alter table A add constraint A_PK primary key (ID); alter table B add constraint B_PK primary key (ID); alter table B add constraint A_FK foreign key (A_ID) references A(ID); create index AI on A(D); create index BI on B(A_ID, DATE_FROM, DATE_TO) include (ID); 

Exemple de requête (lente)

La requête que je suis en train d'essayer d'optimiser est très simple:

• Sélectionnez les lignes où la valeur a été modifiée dans la colonne
• Si une table n'a pas de valeur, l'get d'une autre table
• Fusionner deux colonnes à partir d'une table SQL Server différente avec des lignes vides
• problème d'agrégation mssql avec le niveau de jointure et parent-enfant
• Valeur manquante après la jointure de deux requêtes

 select A.id , B.id , B.DATE_FROM , B.DATE_TO from A left join B on B.A_ID = A.ID and AD between B.DATE_FROM and B.DATE_TO where AD = '2000-01-01' 

Et le résultat prend environ 80ms avec un plan de requête comme celui-ci:

Fondamentalement même question (rapide)

Si j'utilise la date du prédicat juste dans la jointure gauche:

 select A.id , B.id , B.DATE_FROM , B.DATE_TO from A left join B on B.A_ID = A.ID and '2000-01-01' between B.DATE_FROM and B.DATE_TO where AD = '2000-01-01' 

Soudain, MSSQL peut réellement l'utiliser et accélère jusqu'à 0ms :

Question

Si j'ai dû supprimer / modifier les index IA ou IB ou la quantité de données, les deux plans semblent différents, mais la même chose persiste: la table jointe est lue sans prédicat et la requête est plus lente.

La question est pourquoi MSSQL crée des plans différents pour ces requêtes et comment puis-je join plus efficacement le premier échantillon? Notez que je ne peux pas utiliser la deuxième requête, car elle fait partie d'une vue dans laquelle le prédicat n'est pas connu.

Modifier 1

En ce qui concerne la réponse d'Allan, il y a un autre test. Si j'utilise uniquement ID et DATE_FROM dans le prédicat, l'optimiseur filter également B sur le prédicat:

Notez que cette modification returnne un résultat généralement différent, mais ici, elle renvoie la même chose (sans importance ici, je suppose).

Modifier 2

En ce qui concerne le commentaire de TT (et la réponse d'Allan aussi) j'ai changé datatables de test pour get plus de randomnes, donc l' Ad ne mach pas toujours l'intervalle commençant en B J'ai seulement changé l' insert into A :

 insert into a (d) values (dateadd(dd, round(rand() * @j, 0), @month)); 

Et que l'optimiseur a commencé à fonctionner comme prévu:

• Comment merge plusieurs lignes dans une seule ligne
• Rejoindre deux tables sur SQL Server
• Travailler sur Adventureworks2014, ne pas se connecter correctement
• SQL: défi de requête simple mais délicat
• Rejoindre deux tables