MS SQL Query toute la table trop lente

Je rencontre un problème avec ma requête. Je suis en MS SQL et j'essaie d'get toute la table associée de mon patient. De là, tout va bien. Je construis ma requête d'abord puis je l'exécute. Quand je l'exécute, il faut plus de 2 minutes pour terminer. C'est trop long Et j'ai index sur toute ma table. Voici ce que j'essaie de réaliser:

Voici ma requête de construction:

select * from ( select (STUFF(( SELECT ' ' + AA.LEFTJOIN FROM (Select 'LEFT OUTER JOIN ' + A.name + ' WITH (NOLOCK) on (tbpatient.Id = ' + (A.name + '.' + B.Name) + ')' AS LEFTJOIN from sysobjects A Join SysColumns B on (A.id = B.id) Where B.name in ('IdPatient','ImageIdPatient') And not A.name in ('tbrv','tbPatient','tbRV_ToExport','tbRV_Archive','tbRV_LOG_Archives','tbPatients_ToExport', 'tbPatientLock','tbPatient_FusionToExport','tbRV_LOG','tbPatient_A1','tbPatient_A2','tbPatient_A3','tbPatient_QVCDetail') And a.type = 'U' And a.Name LIKE 'tb%') AA FOR XML PATH('') ), 1, 2, '') ) AS query union all select (STUFF(( SELECT ' ' + AA.WHERECLAUSE FROM (Select ' And ' + '(' + A.name + '.' + B.Name + ') IS NULL ' AS WHERECLAUSE from sysobjects A Join SysColumns B on (A.id = B.id) Where B.name in ('IdPatient','ImageIdPatient') And not A.name in ('tbrv','tbPatient','tbRV_ToExport','tbRV_Archive','tbRV_LOG_Archives','tbPatients_ToExport', 'tbPatientLock','tbPatient_FusionToExport','tbRV_LOG','tbPatient_A1','tbPatient_A2','tbPatient_A3','tbPatient_QVCDetail') And a.type = 'U' And a.Name LIKE 'tb%') AA FOR XML PATH('') ), 1, 2, '') ) AS query ) as AA 

Il revient:

J'ajoute ceci en premier dans mon code:

• Rejoindre NULL si Record n'existe pas SQL
• Plusieurs résultats pour un champ dans une requête SQL jointe
• Requête SQL pour additionner les colonnes de plusieurs tables
• Comment sélectionner des données à partir des résultats d'une jointure à gauche
• Remplacer les valeurs Null Join Left Outer par une string

 Select tbPatient.Id, tbPatient.adresse1, tbPatient.nom, tbPatient.prenom From tbPatient " & _ "Left Join tbRV A On (A.Idpatient = tbPatient.ID and A.DateRV >= GETDATE()) " & _ "Left Join tbRV B On ( B.Idpatient = tbPatient.ID and B.DateRV <= GETDATE() And B.Cloturer = 1) 

  left join tbPatientLettres WITH (NOLOCK) on (tbpatient.Id = tbPatientLettres.IDPatient) left join tbPatientprofessionnel WITH (NOLOCK) on (tbpatient.Id = tbPatientprofessionnel.IDPatient) left join tbDossierPatient WITH (NOLOCK) on (tbpatient.Id = tbDossierPatient.IDPatient) left join tbPatient_CarnetVacXML WITH (NOLOCK) on (tbpatient.Id = tbPatient_CarnetVacXML.IDPatient) left join tbPatientDocuments WITH (NOLOCK) on (tbpatient.Id = tbPatientDocuments.IDPatient) left join tbPatientAttachements WITH (NOLOCK) on (tbpatient.Id = tbPatientAttachements.IDPatient) left join tbPatientMedecins WITH (NOLOCK) on (tbpatient.Id = tbPatientMedecins.IDPatient) left join tbNote WITH (NOLOCK) on (tbpatient.Id = tbNote.IdPatient) left join tbDossierIntervention WITH (NOLOCK) on (tbpatient.Id = tbDossierIntervention.IDPatient) left join tbPatientTaxe WITH (NOLOCK) on (tbpatient.Id = tbPatientTaxe.IdPatient) left join tbPatientModeleTaxe WITH (NOLOCK) on (tbpatient.Id = tbPatientModeleTaxe.IdPatient) left join tbPTI_Constat_Note_Evolution WITH (NOLOCK) on (tbpatient.Id = tbPTI_Constat_Note_Evolution.IDPatient) left join tbPTI_Constat_Eval WITH (NOLOCK) on (tbpatient.Id = tbPTI_Constat_Eval.IDPatient) left join tbPatient_OrdonnanceEntete WITH (NOLOCK) on (tbpatient.Id = tbPatient_OrdonnanceEntete.IDPatient) left join tbPatient_CarnetVac WITH (NOLOCK) on (tbpatient.Id = tbPatient_CarnetVac.IDPatient) left join tbPTIConstatSuivie WITH (NOLOCK) on (tbpatient.Id = tbPTIConstatSuivie.IDPatient) left join tbRNIComprimeEntete WITH (NOLOCK) on (tbpatient.Id = tbRNIComprimeEntete.IDPatient) left join tbPatient_QVCEntete WITH (NOLOCK) on (tbpatient.Id = tbPatient_QVCEntete.IDPatient) left join tbRNITeneursPatient WITH (NOLOCK) on (tbpatient.Id = tbRNITeneursPatient.IDPatient) left join tbPatient_QVCInfirmiere WITH (NOLOCK) on (tbpatient.Id = tbPatient_QVCInfirmiere.IDPatient) left join tbRNIResultat WITH (NOLOCK) on (tbpatient.Id = tbRNIResultat.IDPatient) left join tbPatient_QVCDestination WITH (NOLOCK) on (tbpatient.Id = tbPatient_QVCDestination.IDPatient) left join tbPatientRNI WITH (NOLOCK) on (tbpatient.Id = tbPatientRNI.IDPatient) left join tbPatient_CarnetVacRemarque WITH (NOLOCK) on (tbpatient.Id = tbPatient_CarnetVacRemarque.IDPatient) left join tbPatientImages WITH (NOLOCK) on (tbpatient.Id = tbPatientImages.ImageIDPatient) left join tbRVObjetQuestionnaire WITH (NOLOCK) on (tbpatient.Id = tbRVObjetQuestionnaire.IDPatient) left join tbPTI_Suivie_CLinique WITH (NOLOCK) on (tbpatient.Id = tbPTI_Suivie_CLinique.IDPatient) left join tbPatient_A4 WITH (NOLOCK) on (tbpatient.Id = tbPatient_A4.IDPatient) left join tbPatientRNIProtocole WITH (NOLOCK) on (tbpatient.Id = tbPatientRNIProtocole.IDPatient) left join tbPatient_D1 WITH (NOLOCK) on (tbpatient.Id = tbPatient_D1.IDPatient) left join tbPatient_C1 WITH (NOLOCK) on (tbpatient.Id = tbPatient_C1.IDPatient) left join tbPatientAssurance WITH (NOLOCK) on (tbpatient.Id = tbPatientAssurance.IdPatient) left join tbPatientContacts WITH (NOLOCK) on (tbpatient.Id = tbPatientContacts.IDPatient) left join tbPatient_B1 WITH (NOLOCK) on (tbpatient.Id = tbPatient_B1.IDPatient) 

J'ajoute cette clause dans mon code vb: where tbpatient.idstatut = 1 And A.Id is NULL And B.Id is NULL

 And (tbPatientLettres.IDPatient) IS NULL And (tbPatientprofessionnel.IDPatient) IS NULL And (tbDossierPatient.IDPatient) IS NULL And (tbPatient_CarnetVacXML.IDPatient) IS NULL And (tbPatientDocuments.IDPatient) IS NULL And (tbPatientAttachements.IDPatient) IS NULL And (tbPatientMedecins.IDPatient) IS NULL And (tbNote.IdPatient) IS NULL And (tbDossierIntervention.IDPatient) IS NULL And (tbPatientTaxe.IdPatient) IS NULL And (tbPatientModeleTaxe.IdPatient) IS NULL And (tbPTI_Constat_Note_Evolution.IDPatient) IS NULL And (tbPTI_Constat_Eval.IDPatient) IS NULL And (tbPatient_OrdonnanceEntete.IDPatient) IS NULL And (tbPatient_CarnetVac.IDPatient) IS NULL And (tbPTIConstatSuivie.IDPatient) IS NULL And (tbRNIComprimeEntete.IDPatient) IS NULL And (tbPatient_QVCEntete.IDPatient) IS NULL And (tbRNITeneursPatient.IDPatient) IS NULL And (tbPatient_QVCInfirmiere.IDPatient) IS NULL And (tbRNIResultat.IDPatient) IS NULL And (tbPatient_QVCDestination.IDPatient) IS NULL And (tbPatientRNI.IDPatient) IS NULL And (tbPatient_CarnetVacRemarque.IDPatient) IS NULL And (tbPatientImages.ImageIDPatient) IS NULL And (tbRVObjetQuestionnaire.IDPatient) IS NULL And (tbPTI_Suivie_CLinique.IDPatient) IS NULL And (tbPatient_A4.IDPatient) IS NULL And (tbPatientRNIProtocole.IDPatient) IS NULL And (tbPatient_D1.IDPatient) IS NULL And (tbPatient_C1.IDPatient) IS NULL And (tbPatientAssurance.IdPatient) IS NULL And (tbPatientContacts.IDPatient) IS NULL And (tbPatient_B1.IDPatient) IS NULL 

puis après ça, quand je concatène les deux champs et que j'exécute ma requête, ça prend plus de 2 minutes … Est-ce que quelqu'un sait comment aider les choses? merci et désolé pour le long post.

• Comment returnner une valeur nulle si aucun logging trouvé sur la table jointe
• Différence entre les critères IS NULL dans JOIN et WHERE dans une requête
• Jointure de table null SQL Server
• Obtention du premier résultat d'un LEFT JOIN
• sql – Comment returnner une ligne NULL de la table de gauche pour chaque parent même si des loggings enfants existent

 SELECT Id FROM tbpatient EXCEPT (SELECT IDPatient FROM tbPatientLettres UNION SELECT IDPatient FROM tbPatientprofessionnel UNION SELECT IDPatient FROM tbDossierPatient ... and so on until you get all your tables in here )