如何优化SQL Server查询
问题描述:
我正在将数据从一个表复制到另一个表。在复制时我正在做一些计算来修改一列。如何优化SQL Server查询
SQL Server查询:
INSERT INTO rat_proj_duration_map_2
SELECT
r.*,
r.hour_val/(CASE
WHEN week_val = 1 AND
(SELECT TOP 1
hrswk
FROM UserProfileRATinterface_view us
INNER JOIN users u
ON u.username = us.username
WHERE calwk = 2
AND r.uid = u.uid
AND yr = 2016)
> 0 THEN (SELECT TOP 1
hrswk
FROM UserProfileRATinterface_view us
INNER JOIN users u
ON u.username = us.username
WHERE calwk = 2
AND r.uid = u.uid
AND yr = 2016)
WHEN (SELECT
hrswk
FROM UserProfileRATinterface_view us
INNER JOIN users u
ON u.username = us.username
WHERE r.week_val = us.calwk
AND r.uid = u.uid
AND yr = 2016)
< 1 AND
(SELECT
MAX(hrswk)
FROM UserProfileRATinterface_view us
INNER JOIN users u
ON u.username = us.username
WHERE r.uid = u.uid
AND yr = 2016)
> 0 THEN (SELECT
MAX(hrswk)
FROM UserProfileRATinterface_view us
INNER JOIN users u
ON u.username = us.username
WHERE r.uid = u.uid
AND yr = 2016)
WHEN (SELECT
COUNT(*)
FROM UserProfileRATinterface_view us
INNER JOIN users u
ON u.username = us.username
WHERE r.uid = u.uid
AND yr = 2016)
<= 0 THEN 1
ELSE (SELECT
hrswk
FROM UserProfileRATinterface_view us
INNER JOIN users u
ON u.username = us.username
WHERE r.week_val = us.calwk
AND r.uid = u.uid
AND yr = 2016)
END) * 100 AS percentage_val
FROM rat_proj_duration_map r
当我运行此查询我得到超时问题。
提供的TCP:超时错误[258]
SQL Server是不是在我的手,增加超时值。
是否可以优化我的SQL查询?
答
您的case子句中的子查询看起来基本相同。您可以通过定义此子查询的分组版本(... where yr=2016 group by u.uid
)(最好将其作为公用表表达式)来简化整个命令,然后使用该命令。这可能会节省大量冗余操作。
下可能工作(没有测试过):
;WITH usrall as (
SELECT u.uid ui, hrswk hw, r.week wk, us.calwk cw
FROM UserProfileRATinterface_view us
INNER JOIN users u on u.username=us.username
WHERE r.uid=u.uid and yr=2016
), usrgrp as (
SELECT ui gui, MAX(hrswk) ghw, count(*) gcnt FROM usrall group by ui
), denom as (
SELECT gui dui, COALESCE(MAX(w2.hw), MAX(wkwc.hw), MAX(gwh)) dnm
FROM usrgrp
LEFT JOIN usrall w2 ON w2.ui=gui AND w2.cw=2 AND w2.hw>0
LEFT JOIN usrall wkcw ON wkcw.ui=gui AND wkcw.wk=wkcw.cw AND wkwc.hw<1
GROUP BY gui
)
SELECT r.*, r.hour_val/d.dnm
FROM rat_proj_duration_map r
INNER JOIN denom d ON d.dui=u.uid
基本上我已经试过(我希望它的工作原理: - /)取代的情况下由COALESCE()
功能检查三种可能计算的构建一个接一个地值。第一个非空值被接受。
正如我所说:我没有测试过它。祝你好运
答
你确定这个查询在逻辑上是正确的吗?你有几个TOP 1
s没有具体ORDER BY
,子标记比较没有TOP
子选择(我假设,如果您在其他子查询中使用top
具有相同的来源,可能会返回多个行)。
是的 - 这个查询可以优化。你可以得到你需要一个子查询语句中的所有值,避免同一子查询的多个执行的rat_proj_duration_map的每一行,你现在有:
INSERT INTO rat_proj_duration_map_2
SELECT
r.*,
r.hour_val/(CASE
WHEN week_val = 1 AND us.min_hrswk_2 > 0
THEN us.min_hrswk_2
WHEN us.min_hrswk_week_val <1
AND max_hrswk > 0
THEN max_hrswk
WHEN us.cnt <= 0
THEN 1
ELSE min_hrswk_week_val
END) * 100 as percentage_val
FROM
rat_proj_duration_map r
OUTER APPLY
(
SELECT
count(*) as cnt,
MIN(CASE WHEN calcw = 2 THEN hrswk END) as min_hrswk_2,
MIN(CASE WHEN calcw = r.week_val THEN hrswk END) as min_hrswk_week_val,
MAX(hrswk) as max_hrswk
FROM UserProfileRATinterface_view us
inner join users u on u.username=us.username
WHERE r.uid=u.uid and yr=2016
) us
但我不能肯定,如果原来的逻辑是正确的。而这种情况下,我的想法是这样的:
...
r.hour_val/COALESCE(NULLIF(us.min_hrswk_2, 0),
NULLIF(us.min_hrswk_week_val, 0), NULLIF(max_hrswk, 0), 1)
...
附加执行计划问题:https://www.mssqltips.com/sqlservertip/1856/sql-server-query-execution-plans-in- SQL服务器管理工作室/ – Backs