SQL SERVER 数据库 查询SQL优化 20C

下面的查询语句,执行了12分-13分钟,希望有好心人能够帮助调优。或者给出指导建议,非常感谢!!!


WITH P1 AS (
SELECT
DISTINCT
营销案ID ,
营销案名称 ,
一级营销ID ,
一级营销名称 ,
二级营销ID ,
二级营销名称
FROM scmec01..YXA_营销案信息表
WHERE

营销案ID = 'YXA27020139201611210001'
AND 一级营销ID = ''
AND 二级营销ID = ''
AND 是否生效 = '1'
), b1 AS (

SELECT
DISTINCT
营销案ID,
一级营销ID,
二级营销ID,
OFFER_ID,
PROMO_ID
FROM scmec01..YXA_营销案合约关系表
WHERE
营销案ID = 'YXA27020139201611210001'
AND 一级营销ID = ''
AND 二级营销ID = ''
AND 是否生效 = '1'
), o1 AS (
SELECT
销售模式编码
, 价格区间前
, 价格区间后

, 活动ID
, 子活动ID
, 策划ID
FROM scmec01..YXA_营销案合约价格区间表

WHERE
营销案ID = 'YXA27020139201611210001'
AND 一级营销ID = ''
AND 二级营销ID = ''
), P2 AS (
SELECT
DISTINCT
b1.营销案ID,
b1.一级营销ID,
b1.二级营销ID,
b1.OFFER_ID,
b1.PROMO_ID,
c.最低消费 ,
c.捆绑月数 ,
c.预存话费 ,
c.活动ID ,
c.子活动ID ,
c.策划ID ,
o1.销售模式编码
, c.直降金额
, i.ECPS合约ID
, i.ECPS合约档次ID
, c.合约档次名称
, o1.价格区间前
, o1.价格区间后
FROM b1
JOIN scmec01..YXA_营销案ECPS映射表 AS i ON b1.offer_id = i.策划ID AND b1.PROMO_ID = i.活动ID
JOIN scmec01..HY_合约基础信息维护表 AS c ON b1.offer_id = c.策划ID
JOIN o1 ON c.活动ID = o1.活动ID AND c.子活动ID = o1.子活动ID AND c.策划ID = o1.策划ID
WHERE
i.是否生效 = '1'
AND c.是否生效 = '1'

), f1 AS (
SELECT
内存编码 ,
内存名称
FROM scmxx01..内存维护表 f

), P3 AS (
SELECT
DISTINCT
d.产品线编码 ,
e.产品线名称 ,
f1.内存编码 ,
f1.内存名称
, d.营销案ID
, d.一级营销ID
, d.二级营销ID
, m.供应商编码
, m.供应商名称
, h.识别码
, h.识别码描述
FROM scmec01..YXA_营销案产品关系表 AS d
JOIN scmxx01..产品线维护表 e ON d.产品线编码 = e.产品线编码
JOIN scmxx01..商品基本信息 m ON e.产品线编码 = m.产品线编码
JOIN f1 ON ( CASE WHEN d.内存编码 = '00' THEN m.内存编码 ELSE d.内存编码 END ) = f1.内存编码
LEFT JOIN scmxx01..供应商 AS g ON ( CASE WHEN d.供应商编码 = '000' THEN '001' ELSE d.供应商编码 END ) = g.供应商编码
LEFT JOIN scmxx01..商品识别码 AS h ON d.颜色编码 = h.识别码
WHERE
d.营销案ID = 'YXA27020139201611210001'
AND d.一级营销ID = ''
AND d.二级营销ID = ''
AND d.是否生效 = '1'
), j1 AS (

SELECT
j.政策编码
, j.营销案ID
, j.一级营销ID
, j.二级营销ID
, j.活动ID
, j.策划ID
, j.内存编码
, j.颜色编码
, CASE WHEN j.供应商编码='000' THEN '001' ELSE j.供应商编码 END 供应商编码
FROM scmec01..HY_政策矩阵关系表 AS j
WHERE j.营销案ID = 'YXA27020139201611210001'
AND j.一级营销ID = ''
AND j.二级营销ID = ''
AND ( j.是否生效 = '1'
OR j.是否生效 IS NULL
)
), P4 AS (
SELECT
j1.政策编码
, k.自动编号
, j1.营销案ID
, j1.一级营销ID
, j1.二级营销ID
, k.购机款
, CASE WHEN 预存话费总额 IS NULL THEN 0
ELSE 预存话费总额
END 预存话费总额
, k.渠道编码
, k.渠道
, CASE WHEN 补贴 IS NULL THEN 0
ELSE 补贴
END 补贴
, j1.活动ID
, k.产品线编码
, j1.策划ID
, j1.内存编码
, j1.颜色编码
, j1.供应商编码
FROM j1
JOIN scmxx01..政策基本信息 AS k ON j1.政策编码 = k.自动编号
WHERE
k.是否生效 = '1'
OR K.是否生效 IS NULL
AND k.渠道编码 IS NULL
/*
j.营销案ID = 'YXA27020139201611210001'
AND j.一级营销ID = ''
AND j.二级营销ID = ''
AND ( j.是否生效 = '1'
OR j.是否生效 IS NULL
)
AND ( k.是否生效 = '1'
OR K.是否生效 IS NULL
)
*/
)

SELECT 
    DISTINCT   
    P1.营销案ID,
    P1.营销案名称,
    P1.一级营销ID,
    P1.一级营销名称,
    P1.二级营销ID,
    P1.二级营销名称,
    P2.最低消费,
    P2.捆绑月数,
    P2.预存话费,
    P2.活动ID,
    P2.子活动ID,
    P2.策划ID,
    P3.产品线编码,
    P3.产品线名称,
    P3.内存编码,
    P3.内存名称,
    P3.供应商编码,
    P3.供应商名称,
    P3.识别码,
    P3.识别码描述,
    P4.购机款,
    P2.直降金额,
    P2.销售模式编码,
    P4.预存话费总额,
    P4.渠道编码,
    P4.渠道,
    P4.自动编号,
    P2.ECPS合约ID,
    P2.ECPS合约档次ID,
    P4.补贴,
    P2.合约档次名称,
    P2.价格区间前,
    P2.价格区间后  

FROM P1
CROSS JOIN P2
CROSS JOIN P3
LEFT JOIN P4 
ON P2.活动ID = P4.活动ID 
    AND P3.产品线编码 = P4.产品线编码 
    AND P2.策划ID = P4.策划ID
    AND P3.内存编码 = P4.内存编码
    AND P3.识别码 = P4.颜色编码
    AND P3.供应商编码 = P4.供应商编码
    -------------------------统计-----------------------------------

SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。

SQL Server 执行时间:
CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
SQL Server 分析和编译时间:
CPU 时间 = 140 毫秒,占用时间 = 144 毫秒。

(86985 行受影响)
表 '政策基本信息'。扫描计数 0,逻辑读取 79287 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'HY_政策矩阵关系表'。扫描计数 86832,逻辑读取 417575088 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'Worktable'。扫描计数 1,逻辑读取 4830 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 '商品识别码'。扫描计数 1,逻辑读取 34127 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 '内存维护表'。扫描计数 17063,逻辑读取 34126 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 '商品基本信息'。扫描计数 201,逻辑读取 52204 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 '产品线维护表'。扫描计数 0,逻辑读取 402 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'YXA_营销案产品关系表'。扫描计数 1,逻辑读取 408 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'YXA_营销案信息表'。扫描计数 1,逻辑读取 4 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'YXA_营销案ECPS映射表'。扫描计数 54,逻辑读取 216 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'HY_合约基础信息维护表'。扫描计数 54,逻辑读取 216 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'YXA_营销案合约价格区间表'。扫描计数 54,逻辑读取 216 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'YXA_营销案合约关系表'。扫描计数 1,逻辑读取 110 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

(1 行受影响)

SQL Server 执行时间:
CPU 时间 = 694017 毫秒,占用时间 = 753993 毫秒。
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。

SQL Server 执行时间:
CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。

7个回答

把握写SQL的原则、理解SQL的执行顺序。
1.先查子查询,父查询从子查询里面查数据
2.根据执行顺序,尽可能在前面过滤掉数据量大的数据
3.数据量大,尽可能使用索引。
4.不要在使用函数的时候,造成全局搜索、甚至舍弃索引。

明白了这4点,应该就没问题的。

当然数据库表的设计,也有很大的关系。

app19830306
睡在云端的猪 十分感谢
3 年多之前 回复

先explain下,查看下执行计划,再根据具体需求添加适当的索引。

mysql join语法及优化 http://www.data.5helpyou.com/article209.html

scmec01..HY_政策矩阵关系表 营销案ID字段 加个索引看看

表 'HY_政策矩阵关系表'。扫描计数 86832,逻辑读取 417575088 次

最后一点建议,查数据的时候不要使用中文列名,因为转码也是需要时间的。。

app19830306
睡在云端的猪 嗯!谢谢!
3 年多之前 回复

表 '政策基本信息'。扫描计数 0,逻辑读取 79287 次
表 'HY_政策矩阵关系表'。扫描计数 86832,逻辑读取 417575088 次,
表 'Worktable'。扫描计数 1,逻辑读取 4830 次
表 '商品识别码'。扫描计数 1,逻辑读取 34127 次
表 '内存维护表'。扫描计数 17063,逻辑读取 34126 次
表 '商品基本信息'。扫描计数 201,逻辑读取 52204 次

分析一下: 扫描计数多的表,可能是进行了太多全表扫描操作,如果不是求count,那就有问题了。

逻辑读取主要集中在这几个表,HY_矩阵关系表,很明显是个BUG重点调优这个。

app19830306
睡在云端的猪 问题是不清楚该怎么调优 对于 其他 营销案ID的查询 这个 查询语句 需要1分钟 矩阵关系表的逻辑读取 也只有2000多次 实在搞不懂执行计划是受什么影响而改变的 能指导下吗
3 年多之前 回复

可以看一下执行计划,看主要开销在哪。
另外,Cross Join是很消耗时间的。

把数据库里面NULL的数据都默认为0,这样也会加快速度,不要在存储过程里面用is null

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问