select distinct a.RegID,a.PatientName, IsReCheck from CheckResults a where a.RegID between @aRegID and @bRegID and a.IsFinish=0 and a.RegID not in (select distinct RegID from CheckResults where CheckDate between @aCheckDate and @bCheckDate and a.RegID between @aRegID and @bRegID and IsFinish=0 and IsReCheck=1) AND REGID NOT IN (SELECT REGID FROM EVALUATIONS WHERE REGID BETWEEN @aRegID AND @bRegID)
union
select distinct b.RegID ,b.PatientName, IsReCheck from SampeRegsit b where b.RegID between @aRegID and @bRegID and b.IsFinish=0 AND REGID NOT IN (SELECT REGID FROM EVALUATIONS WHERE REGID BETWEEN @aRegID AND @bRegID)
union
select distinct c.RegID ,c.PatientName, IsReCheck from RadGraphyRegist c where c.RegID between @aRegID and @bRegID and c.IsFinish=0 AND REGID NOT IN (SELECT REGID FROM EVALUATIONS WHERE REGID BETWEEN @aRegID AND @bRegID) order by RegID 这是哪个存储过程的查询语句。
我分别执行,或直接在查询分析器里面执行都是不到1s中完成。但是,用这个存储过程后,就会花50s。
union
select distinct b.RegID ,b.PatientName, IsReCheck from SampeRegsit b where b.RegID between @aRegID and @bRegID and b.IsFinish=0 AND REGID NOT IN (SELECT REGID FROM EVALUATIONS WHERE REGID BETWEEN @aRegID AND @bRegID)
union
select distinct c.RegID ,c.PatientName, IsReCheck from RadGraphyRegist c where c.RegID between @aRegID and @bRegID and c.IsFinish=0 AND REGID NOT IN (SELECT REGID FROM EVALUATIONS WHERE REGID BETWEEN @aRegID AND @bRegID) order by RegID 这是哪个存储过程的查询语句。
我分别执行,或直接在查询分析器里面执行都是不到1s中完成。但是,用这个存储过程后,就会花50s。
还有确定需要 UNION 去重复而不是 UNION ALL?
首先谢谢你的回复。这个存储过程正常执行时非常快,因此我觉得和这几条sql语句没有什么特别的关系,而是应该和数据库的状态有关。
我昨天尝试了将这几天语句分别执行,然后再将他们组合起来执行,都是非常快的。
另外,也有朋友建议我用with as语句,我测试了下,使用这个语句改写过后,执行时间明显缩短了5倍左右。
可以取消,放到你说的with as外面进一步嵌套,cte as 加GROUP BY
貌似GROUP BY要比DISTINCT要更早执行,也要快点~
SELECT distinct RegID,PatientName, IsReCheck
FROM ...
WHERE RegID between @aRegID and @bRegID
AND REGID NOT IN (SELECT REGID FROM EVALUATIONS WHERE REGID BETWEEN @aRegID AND @bRegID)