今天遇到了一个问题,一个查询语句,在测试环境执行没问题,但是正式环境就一直超时。研究了好久,发现是因为时间checktime如果设置为
CheckTime >= '2020-05-01'
AND CheckTime < '2020-05-31'
就一直超时(最后发现要执行80多秒才能显示结果)
checkdata表有1600多条数据,子查询查出来是6条数据。
但如果时间设为
CheckTime >= '2020-04-01'
AND CheckTime < '2020-05-31'
就能在1秒内查询出语句。就很奇怪,不知道为啥。
sql如下:
SELECT
CheckUser,
UserName,
COUNT (
DISTINCT ( ApplyID )) AS NUM
FROM
CheckData
LEFT JOIN UserData ON CheckData.CheckUser = UserData.UserID
WHERE
CheckTime >= '2020-05-01'
AND CheckTime < '2020-05-10'
AND CheckUser IN (
SELECT DISTINCT
VALUE
= SUBSTRING (
a.ProManagerJobNum,
b.number,
CASE
WHEN charindex( ',', a.ProManagerJobNum + ',', b.number ) - b.number > 0 THEN
charindex( ',', a.ProManagerJobNum + ',', b.number ) - b.number
WHEN charindex( ',', a.ProManagerJobNum + ',', b.number ) - b.number <= 0 THEN
0
END
)
FROM
ProjectMainInfo a
JOIN master..spt_values b ON b.type = 'p'
AND b.number BETWEEN 1
AND len( a.ProManagerJobNum )
WHERE
SUBSTRING ( ',' + a.ProManagerJobNum, b.number, 1 ) = ','
)
GROUP BY
CheckUser,
UserID,
UserName
ORDER BY
CheckUser DESC offset ( 0 ) ROWS FETCH NEXT 10 ROWS ONLY
解决方案:
1.把checktime 长时间类型“2020-05-01 00:00:00”
转为字符类型
AND CONVERT(VARCHAR(100),CheckTime,23)>= '2020-05-01'
AND CONVERT(VARCHAR(100),CheckTime,23)< '2020-05-31'
查询就很快了。
后来同事告诉我说,是因为SQL server的优化器,当时间为2020-05-01至2020-05-31时,checkdata表有300多条数据,sqlserver就不进行优化,子查询也不会进行优化。就导致时间很慢。当把时间转化使用convert函数时,就会默认为当前语句进行优化。(主要还是由于sqlserver优化器的判断,所以同事不建议写子查询。)
2.改写sql,因为子查询与主表没有关联。所以把子查询的表当作主表去关联checkdata表与userdata表。