subquery runs really slow SQL Server -


the following query runs quick without ttfj_wtd , ttfj_mtd subqueries. without added in, run 5+ minutes. ideas on how tune it?

select eid, tech_id, tech_name, w_start, w_end, m_start, m_end ,(select firstname+' '+lastname ems_nyc_employee eid=a.supeid)   supname ,(select firstname+' '+lastname ems_nyc_employee eid=a.mngreid) mngrname ,activitydate ,shift_start+' - '+shift_end [shift] ,shift_start_time ,login_time ,first_ip_time ,datediff(mi, shift_start_time, first_ip_time) ttfj_yest ,(select avg(datediff(mi, shift_start_time, first_ip_time)) arr_tech a2 a2.eid=a.eid , a2.activitydate between w_start , w_end) ttfj_wtd ,(select avg(datediff(mi, shift_start_time, first_ip_time)) arr_tech a2 a2.eid=a.eid , a2.activitydate between m_start , m_end) ttfj_mtd    arr_tech a, dates d d.rep_date=convert(date, getdate()-1) , a.activitydate=convert(date, getdate()-1) 

this code fixed wee bit readability:

select eid, tech_id, tech_name, w_start, w_end, m_start, m_end,        (select firstname+' '+lastname         ems_nyc_employee e         e.eid = a.supeid        ) supname,        (select firstname+' '+lastname         ems_nyc_employee e         e.eid = a.mngreid        ) mngrname,        activitydate,        (shift_start+' - '+shift_end) [shift],        shift_start_time,        login_time,        first_ip_time,        datediff(mi, shift_start_time, first_ip_time) ttfj_yest,        (select avg(datediff(mi, shift_start_time, first_ip_time))         arr_tech a2         a2.eid = a.eid , a2.activitydate between w_start , w_end        ) ttfj_wtd,        (select avg(datediff(mi, shift_start_time, first_ip_time))         arr_tech a2         a2.eid  = a.eid , a2.activitydate between m_start , m_end        ) ttfj_mtd   arr_tech join      dates d      on a.activitydate = d.rep_date a.activitydate = convert(date, getdate() - 1); 

first, want indexes outer query: arr_tech(rep_date) , dates(rep_date).

then want indexes subqueries: ems_nyc_employee(eid, firstname, lastname) , arr_tech(eid, m_start, m_end).

if these indexes not work, may need rewrite query. however, may sufficient.

and notes on formatting:

  • never use commas in from clause; always use explicit join syntax.
  • qualify column names, especially in correlated subqueries. easy correlation conditions wrong, , easy suffer trying fix it.
  • try format query if you'll have read , understand again.

Comments

Popular posts from this blog

java - nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet Hibernate+SpringMVC -

sql - Postgresql tables exists, but getting "relation does not exist" when querying -

asp.net mvc - breakpoint on javascript in CSHTML? -