sql server - MS SQL Query all table too slow -

i'm encoutering problem query. i'm in ms sql , try associated table patient. here,everything going fine. build query first execute it. when execute it, takes more 2 min finish. it's way long , have index on table. here's try achieved:

here's query build up:

  select *  (                 select (stuff((     select '  ' + aa.leftjoin      (select 'left outer join ' + a.name + ' (nolock) on (tbpatient.id = ' + (a.name + '.' + b.name) + ')' leftjoin              sysobjects join syscolumns b on (a.id = b.id)              b.name in ('idpatient','imageidpatient')               , 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')              , a.type = 'u' , a.name 'tb%') aa     xml path('')     ), 1, 2, '') ) query union  select (stuff((     select '  ' + aa.whereclause      (select  ' , ' + '(' + a.name + '.' + b.name + ')  null '   whereclause  sysobjects join syscolumns b on (a.id = b.id)              b.name in ('idpatient','imageidpatient')               , 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')              , a.type = 'u' , a.name 'tb%') aa     xml path('')     ), 1, 2, '') ) query ) aa 

it's returning:

i add first in code :

select  tbpatient.id, tbpatient.adresse1, tbpatient.nom, tbpatient.prenom tbpatient " & _                                          "left join tbrv on (a.idpatient = tbpatient.id , a.daterv >= getdate()) " & _                                          "left join tbrv b on ( b.idpatient = tbpatient.id ,  b.daterv <= getdate() , b.cloturer = 1)  

 left join tbpatientlettres (nolock) on (tbpatient.id = tbpatientlettres.idpatient)   left join tbpatientprofessionnel (nolock) on (tbpatient.id = tbpatientprofessionnel.idpatient)   left join tbdossierpatient (nolock) on (tbpatient.id = tbdossierpatient.idpatient)   left join tbpatient_carnetvacxml (nolock) on (tbpatient.id = tbpatient_carnetvacxml.idpatient)   left join tbpatientdocuments (nolock) on (tbpatient.id = tbpatientdocuments.idpatient)   left join tbpatientattachements (nolock) on (tbpatient.id = tbpatientattachements.idpatient)   left join tbpatientmedecins (nolock) on (tbpatient.id = tbpatientmedecins.idpatient)   left join tbnote (nolock) on (tbpatient.id = tbnote.idpatient)   left join tbdossierintervention (nolock) on (tbpatient.id = tbdossierintervention.idpatient)   left join tbpatienttaxe (nolock) on (tbpatient.id = tbpatienttaxe.idpatient)   left join tbpatientmodeletaxe (nolock) on (tbpatient.id = tbpatientmodeletaxe.idpatient)   left join tbpti_constat_note_evolution (nolock) on (tbpatient.id = tbpti_constat_note_evolution.idpatient)   left join tbpti_constat_eval (nolock) on (tbpatient.id = tbpti_constat_eval.idpatient)   left join tbpatient_ordonnanceentete (nolock) on (tbpatient.id = tbpatient_ordonnanceentete.idpatient)   left join tbpatient_carnetvac (nolock) on (tbpatient.id = tbpatient_carnetvac.idpatient)   left join tbpticonstatsuivie (nolock) on (tbpatient.id = tbpticonstatsuivie.idpatient)   left join tbrnicomprimeentete (nolock) on (tbpatient.id = tbrnicomprimeentete.idpatient)   left join tbpatient_qvcentete (nolock) on (tbpatient.id = tbpatient_qvcentete.idpatient)   left join tbrniteneurspatient (nolock) on (tbpatient.id = tbrniteneurspatient.idpatient)   left join tbpatient_qvcinfirmiere (nolock) on (tbpatient.id = tbpatient_qvcinfirmiere.idpatient)   left join tbrniresultat (nolock) on (tbpatient.id = tbrniresultat.idpatient)   left join tbpatient_qvcdestination (nolock) on (tbpatient.id = tbpatient_qvcdestination.idpatient)   left join tbpatientrni (nolock) on (tbpatient.id = tbpatientrni.idpatient)   left join tbpatient_carnetvacremarque (nolock) on (tbpatient.id = tbpatient_carnetvacremarque.idpatient)   left join tbpatientimages (nolock) on (tbpatient.id = tbpatientimages.imageidpatient)   left join tbrvobjetquestionnaire (nolock) on (tbpatient.id = tbrvobjetquestionnaire.idpatient)   left join tbpti_suivie_clinique (nolock) on (tbpatient.id = tbpti_suivie_clinique.idpatient)   left join tbpatient_a4 (nolock) on (tbpatient.id = tbpatient_a4.idpatient)   left join tbpatientrniprotocole (nolock) on (tbpatient.id = tbpatientrniprotocole.idpatient)   left join tbpatient_d1 (nolock) on (tbpatient.id = tbpatient_d1.idpatient)   left join tbpatient_c1 (nolock) on (tbpatient.id = tbpatient_c1.idpatient)   left join tbpatientassurance (nolock) on (tbpatient.id = tbpatientassurance.idpatient)   left join tbpatientcontacts (nolock) on (tbpatient.id = tbpatientcontacts.idpatient)   left join tbpatient_b1 (nolock) on (tbpatient.id = tbpatient_b1.idpatient) 

i add clause in vb code : where tbpatient.idstatut = 1 , a.id null , b.id null

and (tbpatientlettres.idpatient)  null     , (tbpatientprofessionnel.idpatient)  null     , (tbdossierpatient.idpatient)  null     , (tbpatient_carnetvacxml.idpatient)  null     , (tbpatientdocuments.idpatient)  null     , (tbpatientattachements.idpatient)  null     , (tbpatientmedecins.idpatient)  null     , (tbnote.idpatient)  null     , (tbdossierintervention.idpatient)  null     , (tbpatienttaxe.idpatient)  null     , (tbpatientmodeletaxe.idpatient)  null     , (tbpti_constat_note_evolution.idpatient)  null     , (tbpti_constat_eval.idpatient)  null     , (tbpatient_ordonnanceentete.idpatient)  null     , (tbpatient_carnetvac.idpatient)  null     , (tbpticonstatsuivie.idpatient)  null     , (tbrnicomprimeentete.idpatient)  null     , (tbpatient_qvcentete.idpatient)  null     , (tbrniteneurspatient.idpatient)  null     , (tbpatient_qvcinfirmiere.idpatient)  null     , (tbrniresultat.idpatient)  null     , (tbpatient_qvcdestination.idpatient)  null     , (tbpatientrni.idpatient)  null     , (tbpatient_carnetvacremarque.idpatient)  null     , (tbpatientimages.imageidpatient)  null     , (tbrvobjetquestionnaire.idpatient)  null     , (tbpti_suivie_clinique.idpatient)  null     , (tbpatient_a4.idpatient)  null     , (tbpatientrniprotocole.idpatient)  null     , (tbpatient_d1.idpatient)  null     , (tbpatient_c1.idpatient)  null     , (tbpatientassurance.idpatient)  null     , (tbpatientcontacts.idpatient)  null     , (tbpatient_b1.idpatient)  null  

then after that, when concatenate 2 field , executed query take more 2 min... know how things ? thank , sorry long post.

i think simplify things below code patients have not been clinic, , think might work better:

select id tbpatient except (select idpatient tbpatientlettres union select idpatient tbpatientprofessionnel union select idpatient tbdossierpatient ... , on until tables in here ) 

also, if there chance don't have non-clustered index on or of idpatient columns, should faster.