alexandre alexandre - 22 days ago 15
SQL Question

MS SQL Query all table too slow

I'm encoutering a problem with my query. i'm in MS SQL and I try to get all associated table from my patient. From here,everything is going fine. I build up my query first then I execute it. When I execute it, It takes more than 2 min to finish. It's way too long And I have index on all my table. Here's what i try to achieved:

Here's my query build up:

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


It's returning:

I add this first in my code :

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)


I add this clause in my vb code :
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


then after that, when i concatenate the two field and executed my query it take more than 2 min... Does someone know how to help things up ? thank you and sorry for the long post.

Answer

I think you could simplify things with the below code to get the Patients which have not been to your clinic, and I think it might work better:

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
)

Also, if there is any chance you don't have a non-clustered index on any or some of the IDPatient columns, I this should be faster.

Comments