sql server - SQL Query is taking too long to execute -
hi below query written sql 2008. takes more 2 hours insert 500000 records. 1 suggest way improve performance?
insert tbluserfile select case when ud.identitystatus = 'a' 'active' when ud.identitystatus in ('t','') 'inactive' when ud.identitystatus null '' end, --'ud.is' "status", isnull(ud.hltid,'') "userid", isnull(ud.hltid,'') "username", isnull(ud.firstname,'') "firstname", isnull(ud.lastname,'') "lastname", isnull(ud.middleinitials,'') "mi", '' "gender", isnull(ud.emailaddress,'') "email", case when su.usertype = 'c' isnull(mcu.manager, '') ----look when su.usertype = 'r' 'from lms superviser' when su.usertype null or su.usertype = '' '' end, '' hr, '' "department", '' "jobcode", '' "division", isnull(ud.office,'') "location", '' "timezone", '' "hiredate", isnull(ud.title,'') "title", isnull(ud.streetaddress,'') "addr1", '' "addr2", isnull(ud.city,'') "city", isnull(ud.state,'') "state", isnull(ud.zip,'') "zip", isnull(ud.countrycode,'') "country", '' "review_freq", '' "last_review_date", isnull(ud.employeetype,'') "custom01", '' "custom02", case when su.usertype = 'c' '' when su.usertype = 'r' isnull(fsbd.name,'') when su.usertype null or su.usertype = '' '' end, '' "custom04", '' "custom05", '' "custom06", '' "custom07", '' "custom08", case when su.usertype = 'c' 'corporate' when su.usertype = 'r' 'hotel' when su.usertype null or su.usertype = '' '' end, isnull(ud.empid,'') "custom11", '' "custom13", '' "custom14", '' "custom15", '' "positioncode", isnull(su.homefacility, '') "homefacility", 'nps' psflag search..userdata ud left join search..managerforcorpusers mcu ------ on mcu.empid = ud.empid , ud.empid != '' , ud.empid not null left join search..securityusers su ------ on ud.userid = su.userid , ud.userid != '' , ud.userid not null left join eis.dbo.newqueryfilter nq on su.homefacility = nq.fcnb , su.homefacility != '' , su.homefacility not null left join facility..fcsubbranddesc fsbd on nq.subbrand = fsbd.subbrand , nq.subbrand != '' , nq.subbrand not null isnull(ud.identitystatus,'') not in ('d','u','l') , isnull(ud.employeetype,'') not in ('o','v','') , isnull(ud.hltid,'') != '' , isnull(ud.empid,'') not in (select distinct userid search..currentuserfile)
have tried avoiding inner query distinct
query?
what size of search..currentuserfile??
try -
select .... search..userdata ud ... -- earlier joins left join search..currentuserfile cu on (ud.empid=cu.userid) ... -- clause , cu.userid null;--only show results not in currentuserfile
Comments
Post a Comment