关于sqlserver一个查询语句的速度问题
这样查询的速度超级慢,要一分钟:SELECTx.ldmc,SUM(CASEWHENx.fqgdhISNOTNULLTHEN1ELSE0END)AStfsjl,SUM(CA...
这样查询的速度超级慢,要一分钟:
SELECT x.ldmc,
SUM(CASE WHEN x.fqgdh IS NOT NULL THEN 1 ELSE 0 END) AS tfsjl,
SUM(CASE WHEN x.fqgdh IS NULL THEN 1 ELSE 0 END) AS zjzyl
FROM
(
SELECT a.rwlsh,e.fqgdh,b.ldmc
FROM tgzctaskls_lz a
LEFT JOIN tldzd b ON a.lddm = b.lddm
LEFT JOIN ttaskmainls d ON a.rwlsh = d.gllsh
LEFT JOIN txcjlls_tfsj e ON d.rwgdh = e.fqgdh
WHERE 1 = 1
and convert(varchar(4), a.fqsj ,120) = '2018'
GROUP BY a.rwlsh,e.fqgdh,b.ldmc
) x
GROUP BY x.ldmc;
但是这样查询就很快,只要1s:
SELECT x.ldmc,
SUM(CASE WHEN x.fqgdh IS NOT NULL THEN 1 ELSE 0 END) AS tfsjl,
SUM(CASE WHEN x.fqgdh IS NULL THEN 1 ELSE 0 END) AS zjzyl
FROM
(
SELECT a.rwlsh,e.fqgdh,b.ldmc
FROM tgzctaskls_lz a
LEFT JOIN tldzd b ON a.lddm = b.lddm
LEFT JOIN ttaskmainls d ON a.rwlsh = d.gllsh
LEFT JOIN txcjlls_tfsj e ON d.rwgdh = e.fqgdh
WHERE 1 = 1
and a.fqsj >= '2018-01-01' AND a.fqsj < '2019-01-01'
GROUP BY a.rwlsh,e.fqgdh,b.ldmc
) x
GROUP BY x.ldmc;
想请教是什么原因导致这么慢
两者的区别就只是日期的那个条件:
一个是:
and convert(varchar(4), a.fqsj ,120) = '2018'
一个是:
and a.fqsj >= '2018-01-01' AND a.fqsj < '2019-01-01' 展开
SELECT x.ldmc,
SUM(CASE WHEN x.fqgdh IS NOT NULL THEN 1 ELSE 0 END) AS tfsjl,
SUM(CASE WHEN x.fqgdh IS NULL THEN 1 ELSE 0 END) AS zjzyl
FROM
(
SELECT a.rwlsh,e.fqgdh,b.ldmc
FROM tgzctaskls_lz a
LEFT JOIN tldzd b ON a.lddm = b.lddm
LEFT JOIN ttaskmainls d ON a.rwlsh = d.gllsh
LEFT JOIN txcjlls_tfsj e ON d.rwgdh = e.fqgdh
WHERE 1 = 1
and convert(varchar(4), a.fqsj ,120) = '2018'
GROUP BY a.rwlsh,e.fqgdh,b.ldmc
) x
GROUP BY x.ldmc;
但是这样查询就很快,只要1s:
SELECT x.ldmc,
SUM(CASE WHEN x.fqgdh IS NOT NULL THEN 1 ELSE 0 END) AS tfsjl,
SUM(CASE WHEN x.fqgdh IS NULL THEN 1 ELSE 0 END) AS zjzyl
FROM
(
SELECT a.rwlsh,e.fqgdh,b.ldmc
FROM tgzctaskls_lz a
LEFT JOIN tldzd b ON a.lddm = b.lddm
LEFT JOIN ttaskmainls d ON a.rwlsh = d.gllsh
LEFT JOIN txcjlls_tfsj e ON d.rwgdh = e.fqgdh
WHERE 1 = 1
and a.fqsj >= '2018-01-01' AND a.fqsj < '2019-01-01'
GROUP BY a.rwlsh,e.fqgdh,b.ldmc
) x
GROUP BY x.ldmc;
想请教是什么原因导致这么慢
两者的区别就只是日期的那个条件:
一个是:
and convert(varchar(4), a.fqsj ,120) = '2018'
一个是:
and a.fqsj >= '2018-01-01' AND a.fqsj < '2019-01-01' 展开
1个回答
推荐律师服务:
若未解决您的问题,请您详细描述您的问题,通过百度律临进行免费专业咨询