相关数据已经脱敏处理
原SQL
MERGE INTO IntoTable t USING (
SELECT
x.PID,
b.SEC_ID,
b.T_SYB,
b.E_CD,
x.END_DATE,
x.xh 区间级别,
isnull(x.hj, 0) 区间人数
FROM
(
SELECT
a.PID,
a.END_DATE,
a.xh,
c.hj
FROM
(
SELECT
PID,
END_DATE,
row_number () OVER (
PARTITION BY PID,
END_DATE
ORDER BY
END_DATE
) xh
FROM
ScTable a
WHERE
EXISTS (
SELECT
*
FROM
(
SELECT
PID,
END_DATE,
COUNT(*) hj
FROM
ScTable
GROUP BY
PID,
END_DATE
HAVING
COUNT(*) >= 5
) b
WHERE
a.PID = b.PID
AND a.END_DATE = b.END_DATE
)
AND EXISTS (
SELECT
*
FROM
ScTable c
WHERE
UPDATE_TIME >= CONVERT (VARCHAR(20), GETDATE() - 1, 23)
AND R_TYPE = '5'
AND a.PID = c.PID
AND a.END_DATE = c.END_DATE
)
AND a.DR = '1'
) a
LEFT JOIN (
SELECT
PID,
END_DATE,
jb,
COUNT(*) hj
FROM
(
SELECT
PID,
END_DATE,
mm,
CASE
WHEN mm >= 0
AND mm <= 200000 THEN
5
WHEN mm > 200000
AND mm <= 500000 THEN
4
WHEN mm > 500000
AND mm <= 1000000 THEN
3
WHEN mm > 1000000
AND mm <= 2000000 THEN
2
WHEN mm > 2000000 THEN
1
END jb
FROM
(
SELECT
PID,
END_DATE,
CASE
WHEN A_REWARD IS NULL THEN
0
ELSE
A_REWARD
END mm
FROM
ScTable
WHERE
DR = '1'
) b
) c
GROUP BY
PID,
END_DATE,
jb
) c ON (
a.PID = c.PID
AND a.END_DATE = c.end_date
AND a.xh = c.jb
)
WHERE
xh <= 5
) x,
(
SELECT
*
FROM
m_sec
WHERE
E_CD IN ('xshe', 'xshg')
AND A_CLASS = 'e'
AND D_U_FLG = 1
AND LEFT (T_SYB, 1) NOT IN ('2', '9')
AND PID IN (
SELECT
PID
FROM
m_sec
WHERE
E_CD IN ('xshe', 'xshg')
AND A_CLASS = 'e'
AND D_U_FLG = 1
GROUP BY
PID
HAVING
COUNT(*) > 1
)
UNION
SELECT
*
FROM
m_sec
WHERE
E_CD IN ('xshe', 'xshg')
AND A_CLASS = 'e'
AND D_U_FLG = 1
AND PID IN (
SELECT
PID
FROM
m_sec
WHERE
E_CD IN ('xshe', 'xshg')
AND A_CLASS = 'e'
AND D_U_FLG = 1
GROUP BY
PID
HAVING
COUNT(*) = 1
)
) b
WHERE
x.PID = b.PID
AND b.A_CLASS = 'e'
AND b.E_CD IN ('xshe', 'xshg')
AND b.D_U_FLG = '1'
AND b.DELIST_DATE IS NULL
) r ON (
t.PID = r.PID
AND t.END_DATE = r.END_DATE
AND t.I_LEVEL = r.区间级别
)
WHEN matched
AND t.I_NUMBER <> r.区间人数 THEN
UPDATE
SET t.I_NUMBER = r.区间人数,
t.update_by = 'PROGRAM 更新',
t.update_time = getdate (),
t.QA_FLG = NULL
WHEN NOT matched THEN
INSERT (
PID,
SEC_ID,
T_SYB,
E_CD,
END_DATE,
I_LEVEL,
I_NUMBER,
DR,
CREATE_BY,
CREATE_TIME,
UPDATE_BY,
UPDATE_TIME
)
VALUES
(
r.PID,
r.SEC_ID,
r.T_SYB,
r.E_CD,
r.END_DATE,
r.区间级别,
r.区间人数,
'1',
'PROGRAM 更新',
getdate (),
'PROGRAM 更新',
getdate ()
);
step 1 SQL拆分，找出存在效率问题的主体
SELECT
PID,
END_DATE,
row_number () OVER (
PARTITION BY PID,
END_DATE
ORDER BY
END_DATE
) xh
FROM
ScTable a
WHERE
EXISTS (
SELECT
*
FROM
(
SELECT
PID,
END_DATE,
COUNT(*) hj
FROM
ScTable
GROUP BY
PID,
END_DATE
HAVING
COUNT(*) >= 5
) b
WHERE
a.PID = b.PID
AND a.END_DATE = b.END_DATE
)
AND EXISTS (
SELECT
*
FROM
ScTable c
WHERE
UPDATE_TIME >= CONVERT (VARCHAR(20), GETDATE() - 1, 23)
AND R_TYPE = '5'
AND a.PID = c.PID
AND a.END_DATE = c.END_DATE
)
AND a.DR = '1'
step 2 SQL改写
SELECT a.PID,
a.END_DATE,
row_number ()
OVER (PARTITION BY a.PID, a.END_DATE
ORDER BY a.END_DATE)
xh
FROM ScTable a
JOIN
(SELECT DISTINCT PID, END_DATE
FROM ScTable
WHERE UPDATE_TIME >=
CONVERT (VARCHAR (20),
GETDATE () - 1,
23)
AND R_TYPE = '5') AS c
ON ( a.PID = c.PID
AND a.END_DATE = c.END_DATE)
JOIN (SELECT PID, END_DATE, COUNT (*) hj
FROM ScTable
GROUP BY PID, END_DATE
HAVING COUNT (*) >= 5) AS b
ON ( a.PID = b.PID
AND a.END_DATE = b.END_DATE)
WHERE a.DR = '1'
Step 3 增加hints更改外表关联方式，以保证内表先执行。
MERGE INTO IntoTable t
USING (SELECT x.PID,
b.SEC_ID,
b.T_SYB,
b.E_CD,
x.END_DATE,
x.xh 区间级别,
isnull (x.hj, 0) 区间人数
FROM (SELECT a.PID,
a.END_DATE,
a.xh,
c.hj
FROM (SELECT a.PID,
a.END_DATE,
row_number ()
OVER (PARTITION BY a.PID, a.END_DATE
ORDER BY a.END_DATE)
xh
FROM ScTable a
JOIN
(SELECT DISTINCT PID, END_DATE
FROM ScTable
WHERE UPDATE_TIME >=
CONVERT (VARCHAR (20),
GETDATE () - 1,
23)
AND R_TYPE = '5') AS c
ON ( a.PID = c.PID
AND a.END_DATE = c.END_DATE)
JOIN (SELECT PID, END_DATE, COUNT (*) hj
FROM ScTable
GROUP BY PID, END_DATE
HAVING COUNT (*) >= 5) AS b
ON ( a.PID = b.PID
AND a.END_DATE = b.END_DATE)
WHERE a.DR = '1') a
LEFT HASH JOIN /*Edit*/
(SELECT PID,
END_DATE,
jb,
COUNT (*) hj
FROM (SELECT PID,
END_DATE,
mm,
CASE
WHEN mm >= 0 AND mm <= 200000
THEN
5
WHEN mm > 200000 AND mm <= 500000
THEN
4
WHEN mm > 500000 AND mm <= 1000000
THEN
3
WHEN mm > 1000000 AND mm <= 2000000
THEN
2
WHEN mm > 2000000
THEN
1
END
jb
FROM (SELECT PID,
END_DATE,
CASE
WHEN A_REWARD IS NULL
THEN
0
ELSE
A_REWARD
END
mm
FROM ScTable
WHERE DR = '1') b) c
GROUP BY PID, END_DATE, jb) c
ON ( a.PID = c.PID
AND a.END_DATE = c.end_date
AND a.xh = c.jb)
WHERE xh <= 5) x,
(SELECT *
FROM m_sec
WHERE E_CD IN ('xshe', 'xshg')
AND A_CLASS = 'e'
AND D_U_FLG = 1
AND LEFT (T_SYB, 1) NOT IN ('2', '9')
AND PID IN
(SELECT PID
FROM m_sec
WHERE E_CD IN ('xshe', 'xshg')
AND A_CLASS = 'e'
AND D_U_FLG = 1
GROUP BY PID
HAVING COUNT (*) > 1)
UNION
SELECT *
FROM m_sec
WHERE E_CD IN ('xshe', 'xshg')
AND A_CLASS = 'e'
AND D_U_FLG = 1
AND PID IN
(SELECT PID
FROM m_sec
WHERE E_CD IN ('xshe', 'xshg')
AND A_CLASS = 'e'
AND D_U_FLG = 1
GROUP BY PID
HAVING COUNT (*) = 1)) b
WHERE x.PID = b.PID
AND b.A_CLASS = 'e'
AND b.E_CD IN ('xshe', 'xshg')
AND b.D_U_FLG = '1'
AND b.DELIST_DATE IS NULL) s
ON ( t.PID = s.PID
AND t.END_DATE = s.END_DATE
AND t.I_LEVEL = s.区间级别)
WHEN MATCHED AND t.I_NUMBER <> s.区间人数
THEN
UPDATE SET t.I_NUMBER = s.区间人数,
t.update_by = 'PROGRAM 更新',
t.update_time = getdate (),
t.QA_FLG = NULL
WHEN NOT MATCHED
THEN
INSERT (PID,
SEC_ID,
T_SYB,
E_CD,
END_DATE,
I_LEVEL,
I_NUMBER,
DR,
CREATE_BY,
CREATE_TIME,
UPDATE_BY,
UPDATE_TIME)
VALUES (s.PID,
s.SEC_ID,
s.T_SYB,
s.E_CD,
s.END_DATE,
s.区间级别,
s.区间人数,
'1',
'PROGRAM 更新',
getdate (),
'PROGRAM 更新',
getdate ());
执行时间从>10min缩短到5s内。
转载请注明原文地址: https://www.6miu.com/read-20161.html
