SQL查询优化问题

[education520]

select a.bx_eid,max(b.mastname) as 单位名称,max(b.deptname) as 部门名称,
sum(a.个人合计),max(b.shortcode) as shortcode,max(b.mastid) as mastid,
sum(b.oldbxjs) as 养老险基数,sum(a.个人养老险) from
(select bx_eid,max(bxdate) as bxdate,sum(a.bxmoney) as 个人合计,
sum(case when a.sb_itemname like '%养老保险%' then a.bxmoney else 0 end) as 个人养老险
from ED_BXDATA a where Convert(datetime,bxdate)>=Convert(datetime,'2017-9-1')
and Convert(datetime,bxdate)<=Convert(datetime,'2017-10-1') group by bx_eid) as a
left join (select * from ed_social where Convert(datetime,cjdate)>=Convert(datetime,'2017-9-1')
and Convert(datetime,cjdate)<=Convert(datetime,'2017-10-1')) b on a.bx_eid=b.si_eid
where 1=1 and b.mastid in(10077) group by a.bx_eid


大概意思是:
select * from (统计表A) left join (统计表B) on a.bx_eid=b.si_eid where ....
这样查询很慢要20秒，请问如何建索引优化？

[OwenZeng_DBA]

mastid a.bx_eid b.si_eid 这些列都有索引吗》最好发现执行计划

[RINK_1]

感觉你这逻辑有些问题，如果a和b这两个衍生表是一对多的关系，那你最外层再SUM(a.个人合计)、sum(a.个人养老险)，这样会不会重复计算呢。如果是一对一，那最外层的group by a.bx_eid又有什么意义呢。

[吉普赛的歌]

--先加索引
CREATE INDEX ix_ED_BXDATA_bxdate ON ED_BXDATA(bxdate)
CREATE INDEX ix_ed_social_cjdate ON ed_social(cjdate)

--语句改一下，主要是日期不要用函数包装！
SELECT a.bx_eid,
       MAX(b.mastname)   AS 单位名称,
       MAX(b.deptname)   AS 部门名称,
       SUM(a.个人合计),
       MAX(b.shortcode)  AS shortcode,
       MAX(b.mastid)     AS mastid,
       SUM(b.oldbxjs)    AS 养老险基数,
       SUM(a.个人养老险)
FROM   (
           SELECT bx_eid,
                  MAX(bxdate)     AS bxdate,
                  SUM(a.bxmoney)  AS 个人合计,
                  SUM(
                      CASE 
                           WHEN a.sb_itemname LIKE '%养老保险%' THEN a.bxmoney
                           ELSE 0
                      END
                  )               AS 个人养老险
           FROM   ED_BXDATA          a
           WHERE  bxdate>='2017-9-1'
                  AND bxdate<='2017-10-2'
           GROUP BY
                  bx_eid
       ) AS a
       LEFT JOIN (
                SELECT *
                FROM   ed_social
                WHERE  cjdate>='2017-9-1'
                   AND cjdate<='2017-10-2'
            ) b
            ON  a.bx_eid = b.si_eid
WHERE  1 = 1
       AND b.mastid IN (10077)
GROUP BY
       a.bx_eid