【SQLServer】sql优化：从300秒+到10秒 - MsSql数据库 - 软件开发资源站

首页 > 数据库 > MsSql数据库 > 【SQLServer】sql优化：从300秒+到10秒

【SQLServer】sql优化：从300秒+到10秒
类别：MsSql数据库作者：码皇来源：互联网点击：

【SQLServer】sql优化：从300秒+到10秒。今天客户反馈有一个报表运行超时。于是登录到系统上，查到运行缓慢报表的对应sql，代码如下。

今天客户反馈有一个报表运行超时。

于是登录到系统上，查到运行缓慢报表的对应sql，代码如下：

SELECT f_temp_biz_date AS f_temp_biz_date , o_id2 AS o_id2 , o_id3 AS o_id3 , o_id4 AS o_id4 , storeChannel_id2 AS storeChannel_id2 , storeChannel_id3 AS storeChannel_id3 , g_sdlb_item_id AS g_sdlb_item_id , sdlb_item_id AS sdlb_item_id , MAX(CASE WHEN g_f_unit = 1 AND g_p_brand_id = 1 THEN M000 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
2885'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
2886'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
2887'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
2890'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
2891'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
2892'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
2893'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
2895'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
2896'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
2898'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
2899'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
2900'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
2902'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
2983'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
11030'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
11032'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
11033'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
11042'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
11043'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
11044'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
11052'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
11054'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
11055'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
11056'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
11057'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
11058'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
11076'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
11078'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
11080'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
11083'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
11207'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
15021'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
15022'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
15023'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
15024'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
15025'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
15026'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
37037'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
46047'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
46048'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
46049'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
46050'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
80093'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
110114'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
110115'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
117119'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
117120'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
117121'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
117122'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
117123'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
117130'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
117131'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
117132'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
117133'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
117134'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
117135'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
117136'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
117137'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
121125'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
121126'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
125162'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
125163'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
127127'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
127128'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
127129'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
127130'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
127131'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
127132'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
127133'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
136139'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND p_brand_id = '
136141'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(L)'
AND g_p_brand_id = 1 THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
2885'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
2886'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
2887'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
2890'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
2891'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
2892'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
2893'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
2895'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
2896'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
2898'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
2899'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
2900'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
2902'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
2983'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
11030'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
11032'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
11033'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
11042'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
11043'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
11044'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
11052'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
11054'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
11055'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
11056'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
11057'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
11058'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
11076'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
11078'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
11080'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
11083'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
11207'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
15021'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
15022'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
15023'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
15024'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
15025'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
15026'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
37037'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
46047'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
46048'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
46049'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
46050'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
80093'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
110114'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
110115'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
117119'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
117120'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
117121'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
117122'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
117123'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
117130'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
117131'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
117132'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
117133'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
117134'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
117135'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
117136'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
117137'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
121125'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
121126'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
125162'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
125163'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
127127'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
127128'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
127129'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
127130'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
127131'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
127132'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
127133'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
136139'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND p_brand_id = '
136141'
THEN M001 END) , MAX(CASE WHEN f_unit = '
当月销量(Box)'
AND g_p_brand_id = 1 THEN M001 END)FROM ( SELECT f.temp_biz_date AS f_temp_biz_date , o.id2 AS o_id2 , o.id3 AS o_id3 , o.id4 AS o_id4 , storeChannel.id2 AS storeChannel_id2 , storeChannel.id3 AS storeChannel_id3 , GROUPING(sdlb.item_id) AS g_sdlb_item_id , sdlb.item_id AS sdlb_item_id , GROUPING(f.unit) AS g_f_unit , f.unit AS f_unit , GROUPING(p.brand_id) AS g_p_brand_id , p.brand_id AS p_brand_id , COUNT(CASE WHEN flag = '
store'
THEN f.store_id END) AS M000 , SUM(CASE WHEN flag = '
HL_BOX'
THEN HL_BOX ELSE 0 END) AS M001 FROM ( SELECT r.org_id , r.store_id , R.LEVEL_ID , r.channel_id , r.biz_date + '
-01'
AS biz_date , r.biz_date AS temp_biz_date , ISNULL(r.prod_id, t.prod_id) AS PROD_ID , HL_BOX , UNIT , flag FROM TB_RPT_001 r CROSS APPLY ( SELECT TOP 1 prod_id FROM TB_PRODUCT WITH ( NOLOCK ) WHERE state = 1 ) t WHERE r.biz_date IN ( '
2016-08'
) ) f INNER JOIN STD_TIME t ON f.biz_date = t.the_date INNER JOIN STD_ORG o ON f.org_id = o.org_id INNER JOIN STD_STORE s ON f.store_id = s.store_id LEFT JOIN STD_PRODUCT p ON f.prod_id = p.prod_id INNER JOIN STD_ITEM storeChannel ON f.channel_id = storeChannel.item_id INNER JOIN STD_ITEM sdlb ON f.level_id = sdlb.item_id WHERE ( o.id2 = '
516'
) AND ( o.id2 = '
516'
) AND ( f.biz_date BETWEEN '
2016-08-01'
AND '
2016-08-31'
) AND ( s.id2 = '
516'
) GROUP BY f.temp_biz_date , o.id2 , o.id3 , o.id4 , storeChannel.id2 , storeChannel.id3 , sdlb.item_id , f.unit , p.brand_id WITH CUBE HAVING NOT ( GROUPING(f.temp_biz_date) = 1 OR GROUPING(f.temp_biz_date) = 0 AND GROUPING(o.id2) = 1 OR GROUPING(o.id2) = 0 AND GROUPING(o.id3) = 1 OR GROUPING(o.id3) = 0 AND GROUPING(o.id4) = 1 OR GROUPING(o.id4) = 0 AND GROUPING(storeChannel.id2) = 1 OR GROUPING(storeChannel.id2) = 0 AND GROUPING(storeChannel.id3) = 1 OR GROUPING(sdlb.item_id) = 0 AND GROUPING(storeChannel.id3) = 1 OR GROUPING(storeChannel.id3) = 0 AND GROUPING(storeChannel.id2) = 1 OR GROUPING(storeChannel.id2) = 0 AND GROUPING(o.id4) = 1 OR GROUPING(o.id4) = 0 AND GROUPING(o.id3) = 1 OR GROUPING(o.id3) = 0 AND GROUPING(o.id2) = 1 OR GROUPING(o.id2) = 0 AND GROUPING(f.temp_biz_date) = 1 ) ) crGROUP BY f_temp_biz_date , o_id2 , o_id3 , o_id4 , storeChannel_id2 , storeChannel_id3 , g_sdlb_item_id , sdlb_item_idORDER BY f_temp_biz_date , o_id2 , o_id3 , o_id4 , storeChannel_id2 , storeChannel_id3 , g_sdlb_item_id

由于这个语句的外层是由工具生成的，修改不了，所以只能修改这个语句中最内层的的这个sql，能做的优化非常有限，sql语句上没有什么可以调整的。

SELECT r.org_id , r.store_id , R.LEVEL_ID , r.channel_id , r.biz_date + '
-01'
AS biz_date , r.biz_date AS temp_biz_date , ISNULL(r.prod_id, t.prod_id) AS PROD_ID , HL_BOX , UNIT , flag FROM TB_RPT_001 r CROSS APPLY ( SELECT TOP 1 prod_id FROM TB_PRODUCT WITH ( NOLOCK ) WHERE state = 1 ) t WHERE r.biz_date IN ( '
2016-08'
)

这个表的基本情况：表中有大概800w条数据，这个查询的8月份，大概有80w的数据。

最开始的速度是170s，创建了索引，但是速度反而更慢了，运行了300s没有出结果。

因为这个表会经常删除，在插入，所以把整个表重建了，但是结果还是300s没有出结果。

经过分析发现这个语句之所以这么慢，主要的原因在于语句中的 with cube 造成的，执行计划非常复杂，一眼望不到头实际上在sql server 2008之后可以采用新的cube语句，只对需要的列进行cube计算，但关键是这个外层代码改不了，是工具生成的。。。

下面的图就是这个sql的执行计划，由于这个执行计划太复杂，太大了，所以这个截图只是原始执行计划的5分之1 ：

想想还可以试一下，就是修改sql server的服务器参数max degree of parallelism ，就是语句执行的并行度，服务器配置是64个线程，所以把参数值调整为20，也就是可以有20个线程同时运行这个sql。

调整之后，sql运行速度降为10秒，太棒了

相关热词搜索：

上一篇：SQLServer大量数据秒级插入/新增/删除
下一篇：SQL SERVER中强制类型转换cast和convert的区别详解

【SQLServer】sql优化：从300秒+到10秒 类别：MsSql数据库 作者：码皇 来源：互联网 点击：

【SQLServer】sql优化：从300秒+到10秒
类别：MsSql数据库作者：码皇来源：互联网点击：