今天客服反馈 客户在前两天查询一个移动端报表时报错了。
我看了一下报错信息,大致是timeout,这种错误基本可以确定是由于查询时间超过阀值(一般为3秒)。
sql代码如下:
SELECT hh.EMP_ID ,
hh.STORE_ID ,
hh.pn1 ,
SUM(hh.numb) numb ,
SUM(hh.SJ) sj ,
NULL zb
FROM ( SELECT po.EMP_ID ,
v.STORE_ID ,
v.UPLOAD_DATE ,
i.PN1 ,
CAST(iv.ITEM1 AS NUMERIC) numb ,
CAST(iv.ITEM1 AS NUMERIC)
* CAST(p.PRICE AS NUMERIC(10, 2)) SJ
FROM TB_DR v WITH ( NOLOCK )
INNER HASH JOIN TB_DR_ITEM iv WITH ( NOLOCK ) ON iv.MAIN_ID = v.ID
INNER JOIN tb_org o WITH ( NOLOCK ) ON v.emp_id = o.emp_id
INNER JOIN tb_org po WITH ( NOLOCK ) ON po.org_ID = o.PARENT_ORG_ID
INNER JOIN tb_PROD p WITH ( NOLOCK ) ON p.PROD_ID = iv.PROD_ID
INNER JOIN tb_DI i WITH ( NOLOCK ) ON i.DICT_ITEM_ID = p.BRAND_ID
WHERE LEFT(v.bdate, 7) = CONVERT(VARCHAR(7), GETDATE(), 120)
AND v.CODE IN ( 'SB001', 'SB002','SB003')
AND po.emp_id = 12345
) hh
GROUP BY hh.EMP_ID ,
hh.STORE_ID ,
hh.pn1
运行时间:大概是6秒。
表信息:tb_dr大概是500w条,tb_dr_item大概是2800w条数据,其他的表基本上都是几千条。
结果集:大致返回50条。
优化:由于tb_dr已有bdate字段的索引,没有看执行计划,但是由于 left 函数作用在bdate,导致这个索引用不上,同时考虑到tb_dr和tb_dr_item表主表-子表,子表通过main_id和主表的id关联,所以在join时不应该用hash,就算要用也应该是loop,所以改为:
SELECT hh.EMP_ID ,
hh.STORE_ID ,
hh.pn1 ,
SUM(hh.numb) numb ,
SUM(hh.SJ) sj ,
NULL zb
FROM ( SELECT po.EMP_ID ,
v.STORE_ID ,
v.UPLOAD_DATE ,
i.PN1 ,
CAST(iv.ITEM1 AS NUMERIC) numb ,
CAST(iv.ITEM1 AS NUMERIC)
* CAST(p.PROD_PRICE AS NUMERIC(10, 2)) SJ
FROM TB_R v WITH ( NOLOCK )
INNER HASH JOIN TB_DR_ITEM iv WITH ( NOLOCK ) ON iv.MAIN_ID = v.ID
INNER JOIN tb_org o WITH ( NOLOCK ) ON v.emp_id = o.emp_id
INNER JOIN tb_org po WITH ( NOLOCK ) ON po.org_ID = o.PARENT_ORG_ID
INNER JOIN tb_PROD p WITH ( NOLOCK ) ON p.PROD_ID = iv.PROD_ID
INNER JOIN tb_DI i WITH ( NOLOCK ) ON i.DICT_ITEM_ID = p.BRAND_ID
WHERE v.bdate like CONVERT(VARCHAR(7), GETDATE(), 120) +'%'
AND v.FUNC_CODE IN ( 'TB_006', 'TB_006_AT05',
'TB_006_AT06',
'TB_006_AT07',
'TB_006_AT08',
'TB_005_AT02',
'TB_005_AT03',
'TB_005_AT04',
'TB_005_AT05',
'TB_005_AT06',
'TB_005_AT07',
'TB_005_AT08',
'TB_005_AT09' )
AND po.emp_id = 12345
) hh
GROUP BY hh.EMP_ID ,
hh.STORE_ID ,
hh.pn1
其中最主要的是把LEFT(v.bdate, 7) = CONVERT(VARCHAR(7), GETDATE(), 120) 改为 v.biz_date like CONVERT(VARCHAR(7), GETDATE(), 120) +'%',这样索引就能用上了。
查询速度从6秒降为2秒。