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.


Comments

Post a Comment

Popular posts from this blog

asynchronous - C# WinSCP .NET assembly: How to upload multiple files asynchronously -

i using winscp .net assembly uploading files. want upload multiple files asynchronous way. have created method works single upload. public class uploadingdata { private sessionoptions _sessionoptions; private session _session; //connection etc private void onconnected() { foreach (var fileinfo in localfileslist) { var task = task.factory.startnew(() => uploadfilesasync(fileinfo)); } } private async task uploadfilesasync(string file) { string remotefilepath = _session.translatelocalpathtoremote(file, @"/", "/test_data_upload"); var uploading = _session.putfiles(file, remotefilepath, false); //when done await task.run(() => thread.sleep(1000)); } } please suggest me correct way. thanks you can use backgroundworker class this: string[] images = new string[50]; foreach (var path in images) { backgroundworker
Read more

aws api gateway - SerializationException in posting new Records via Dynamodb Proxy Service in API -

i getting "__type": "com.amazon.coral.service#serializationexception" as reply in postman & in test console in api gateway trying post record directly dynamodb using api proxy services.. referring aws article - https://aws.amazon.com/blogs/compute/using-amazon-api-gateway-as-a-proxy-for-dynamodb/ here's mapping { "tablename": "tablenamegoeshere", "item": { "id" : "$context.requestid" "eventname" : "$input.path('$.eventname')", "timestamp" : $input.path('$.timestamp'), "answers": "$util.parsejson($input.path('$.answers'))" } } update: did asked ... , worked if try add array of json objects gives me above same error - here's trying now. please - couldnt find on google well #set($inputroot = $input.path('$')) { "tablename": "answer", "item": {
Read more

asp.net - Problems sending emails from forum -

i have been redesigning site has message board, has been written guy looked after site. it has few problems , written in .asp , uses .mdb files database. you can see whole thing here . feel free post if helps, test thing have set using original files , new database. the basic problem when posts message, supposed email sent them, have posted it. when replies, originator supposed message, replier has been posted. i’d have thought @ point in process there should script/file in postmessage.asp? the postmessage.asp page below. <% option explicit %> <!-- #include virtual="common/adovbs.inc" --> <html> <head> <meta http-equiv="content-type" content="text/html; charset=windows-1252"> <meta name="generator" content="microsoft frontpage 4.0"> <meta name="progid" content="frontpage.editor.document"> <title>new page 2</title> <base target="_self"
Read more