sql 执行效率问题

今天在做北京电力医院患者入组逻辑的时候遇到一个问题，就是有两个条件，分别查出来很快，数量也不多。但是算同时满足两个条件的住院患者号，如果用的查询方式不当就很慢，百思不得其解，无奈只能去请教ETL开发部的同事。以下是我的条件和我使用的查询方式：

--打开0830数据库
USE CDR_DLYY_20170830

--前置条件
--条件一 0秒 1271条 查所有 既有神经内科或神内(2)的出院记录且该记录中不含疾病诊断的，又有神经内科或神内的入院记录的患者PNO
SELECT DISTINCT PATIENT_NO FROM DP_EMR_RECORD WHERE PATIENT_NO IN(
SELECT DISTINCT PATIENT_NO FROM DP_EMR_RECORD WHERE REC_DEPT_NAME IN ('神经内科','神内(2)')
AND EMR_TYPE_NAME='出院记录'
AND LGCY_SUB_TYPE_NAME not LIKE '%疾病诊断%')AND EMR_TYPE_NAME= '入院记录' AND REC_DEPT_NAME IN ('神经内科','神内(2)')
--条件二 0秒 4784条 查所有 既有神经内科或神内(2)的入院记录的,又有出院小结的患者PNO
SELECT DISTINCT PATIENT_NO FROM DP_EMR_RECORD WHERE PATIENT_NO IN (
SELECT DISTINCT PATIENT_NO FROM DP_EMR_RECORD WHERE REC_DEPT_NAME IN ('神经内科','神内(2)')
AND EMR_TYPE_NAME='入院记录')
AND LGCY_SUB_TYPE_NAME LIKE '%出院小结%'

--查同时满足条件一和条件二的PNO
--方法一 INTERSECT 47秒 1271条
SELECT DISTINCT PATIENT_NO FROM DP_EMR_RECORD WHERE PATIENT_NO IN(
SELECT DISTINCT PATIENT_NO FROM DP_EMR_RECORD WHERE REC_DEPT_NAME IN ('神经内科','神内(2)')
AND EMR_TYPE_NAME='出院记录'
AND LGCY_SUB_TYPE_NAME not LIKE '%疾病诊断%')AND EMR_TYPE_NAME= '入院记录' AND REC_DEPT_NAME IN ('神经内科','神内(2)')
INTERSECT
SELECT DISTINCT PATIENT_NO FROM DP_EMR_RECORD WHERE PATIENT_NO IN (
SELECT DISTINCT PATIENT_NO FROM DP_EMR_RECORD WHERE REC_DEPT_NAME IN ('神经内科','神内(2)')
AND EMR_TYPE_NAME='入院记录')
AND LGCY_SUB_TYPE_NAME LIKE '%出院小结%'

--方法二 exists 45秒 1271条
SELECT * FROM (
SELECT DISTINCT PATIENT_NO FROM DP_EMR_RECORD WHERE PATIENT_NO IN(
SELECT DISTINCT PATIENT_NO FROM DP_EMR_RECORD WHERE REC_DEPT_NAME IN ('神经内科','神内(2)')
AND EMR_TYPE_NAME='出院记录'
AND LGCY_SUB_TYPE_NAME not LIKE '%疾病诊断%')AND EMR_TYPE_NAME= '入院记录' AND REC_DEPT_NAME IN ('神经内科','神内(2)'))a
WHERE EXISTS ( SELECT * FROM
(SELECT DISTINCT PATIENT_NO FROM DP_EMR_RECORD WHERE PATIENT_NO IN (
SELECT DISTINCT PATIENT_NO FROM DP_EMR_RECORD WHERE REC_DEPT_NAME IN ('神经内科','神内(2)')
AND EMR_TYPE_NAME='入院记录')
AND LGCY_SUB_TYPE_NAME LIKE '%出院小结%')b
WHERE a.PATIENT_NO=b.PATIENT_NO)

--方法三 ,where 8秒 1271条
SELECT a.* FROM (
SELECT DISTINCT PATIENT_NO FROM DP_EMR_RECORD WHERE PATIENT_NO IN(
SELECT DISTINCT PATIENT_NO FROM DP_EMR_RECORD WHERE REC_DEPT_NAME IN ('神经内科','神内(2)')
AND EMR_TYPE_NAME='出院记录'
AND LGCY_SUB_TYPE_NAME not LIKE '%疾病诊断%')AND EMR_TYPE_NAME= '入院记录' AND REC_DEPT_NAME IN ('神经内科','神内(2)'))a,
(SELECT DISTINCT PATIENT_NO FROM DP_EMR_RECORD WHERE PATIENT_NO IN (
SELECT DISTINCT PATIENT_NO FROM DP_EMR_RECORD WHERE REC_DEPT_NAME IN ('神经内科','神内(2)')
AND EMR_TYPE_NAME='入院记录'))b WHERE a.PATIENT_NO=b.PATIENT_NO
--删掉第一个distinct 54秒 1276条
SELECT a.* FROM (
SELECT PATIENT_NO FROM DP_EMR_RECORD WHERE PATIENT_NO IN(
SELECT DISTINCT PATIENT_NO FROM DP_EMR_RECORD WHERE REC_DEPT_NAME IN ('神经内科','神内(2)')
AND EMR_TYPE_NAME='出院记录'
AND LGCY_SUB_TYPE_NAME not LIKE '%疾病诊断%')AND EMR_TYPE_NAME= '入院记录' AND REC_DEPT_NAME IN ('神经内科','神内(2)'))a,
(SELECT DISTINCT PATIENT_NO FROM DP_EMR_RECORD WHERE PATIENT_NO IN (
SELECT DISTINCT PATIENT_NO FROM DP_EMR_RECORD WHERE REC_DEPT_NAME IN ('神经内科','神内(2)')
AND EMR_TYPE_NAME='入院记录'))b WHERE a.PATIENT_NO=b.PATIENT_NO
--删掉第二个distinct 9秒 1271条
SELECT DISTINCT a.* FROM (
SELECT DISTINCT PATIENT_NO FROM DP_EMR_RECORD WHERE PATIENT_NO IN(
SELECT DISTINCT PATIENT_NO FROM DP_EMR_RECORD WHERE REC_DEPT_NAME IN ('神经内科','神内(2)')
AND EMR_TYPE_NAME='出院记录'
AND LGCY_SUB_TYPE_NAME not LIKE '%疾病诊断%')AND EMR_TYPE_NAME= '入院记录' AND REC_DEPT_NAME IN ('神经内科','神内(2)'))a,
(SELECT PATIENT_NO FROM DP_EMR_RECORD WHERE PATIENT_NO IN (
SELECT DISTINCT PATIENT_NO FROM DP_EMR_RECORD WHERE REC_DEPT_NAME IN ('神经内科','神内(2)')
AND EMR_TYPE_NAME='入院记录'))b WHERE a.PATIENT_NO=b.PATIENT_NO

--方法四 inner join on 0秒 1271条
SELECT * FROM (
SELECT DISTINCT PATIENT_NO FROM DP_EMR_RECORD WHERE PATIENT_NO IN(
SELECT DISTINCT PATIENT_NO FROM DP_EMR_RECORD WHERE REC_DEPT_NAME IN ('神经内科','神内(2)')
AND EMR_TYPE_NAME='出院记录'
AND LGCY_SUB_TYPE_NAME not LIKE '%疾病诊断%')AND EMR_TYPE_NAME= '入院记录' AND REC_DEPT_NAME IN ('神经内科','神内(2)')) a
INNER JOIN (
SELECT DISTINCT PATIENT_NO FROM DP_EMR_RECORD WHERE PATIENT_NO IN (
SELECT DISTINCT PATIENT_NO FROM DP_EMR_RECORD WHERE REC_DEPT_NAME IN ('神经内科','神内(2)')
AND EMR_TYPE_NAME='入院记录')
AND LGCY_SUB_TYPE_NAME LIKE '%出院小结%')b
on a.PATIENT_NO=b.PATIENT_NO
--删掉第一个distinct 1秒 1276条
SELECT a.* FROM (
SELECT PATIENT_NO FROM DP_EMR_RECORD WHERE PATIENT_NO IN(
SELECT DISTINCT PATIENT_NO FROM DP_EMR_RECORD WHERE REC_DEPT_NAME IN ('神经内科','神内(2)')
AND EMR_TYPE_NAME='出院记录'
AND LGCY_SUB_TYPE_NAME not LIKE '%疾病诊断%')AND EMR_TYPE_NAME= '入院记录' AND REC_DEPT_NAME IN ('神经内科','神内(2)')) a
INNER JOIN (
SELECT DISTINCT PATIENT_NO FROM DP_EMR_RECORD WHERE PATIENT_NO IN (
SELECT DISTINCT PATIENT_NO FROM DP_EMR_RECORD WHERE REC_DEPT_NAME IN ('神经内科','神内(2)')
AND EMR_TYPE_NAME='入院记录')
AND LGCY_SUB_TYPE_NAME LIKE '%出院小结%')b
on a.PATIENT_NO=b.PATIENT_NO
--删掉第二个distinct 0秒 1276条
SELECT a.* FROM (
SELECT DISTINCT PATIENT_NO FROM DP_EMR_RECORD WHERE PATIENT_NO IN(
SELECT DISTINCT PATIENT_NO FROM DP_EMR_RECORD WHERE REC_DEPT_NAME IN ('神经内科','神内(2)')
AND EMR_TYPE_NAME='出院记录'
AND LGCY_SUB_TYPE_NAME not LIKE '%疾病诊断%')AND EMR_TYPE_NAME= '入院记录' AND REC_DEPT_NAME IN ('神经内科','神内(2)')) a
INNER JOIN (
SELECT PATIENT_NO FROM DP_EMR_RECORD WHERE PATIENT_NO IN (
SELECT DISTINCT PATIENT_NO FROM DP_EMR_RECORD WHERE REC_DEPT_NAME IN ('神经内科','神内(2)')
AND EMR_TYPE_NAME='入院记录')
AND LGCY_SUB_TYPE_NAME LIKE '%出院小结%')b
on a.PATIENT_NO=b.PATIENT_NO

--方法五 in 0秒 1271条
SELECT * from (
SELECT DISTINCT PATIENT_NO FROM DP_EMR_RECORD WHERE PATIENT_NO IN(
SELECT DISTINCT PATIENT_NO FROM DP_EMR_RECORD WHERE REC_DEPT_NAME IN ('神经内科','神内(2)')
AND EMR_TYPE_NAME='出院记录'
AND LGCY_SUB_TYPE_NAME not LIKE '%疾病诊断%')AND EMR_TYPE_NAME= '入院记录' AND REC_DEPT_NAME IN ('神经内科','神内(2)'))a
where a.PATIENT_NO IN (
SELECT DISTINCT PATIENT_NO FROM DP_EMR_RECORD WHERE PATIENT_NO IN (
SELECT DISTINCT PATIENT_NO FROM DP_EMR_RECORD WHERE REC_DEPT_NAME IN ('神经内科','神内(2)')
AND EMR_TYPE_NAME='入院记录')
AND LGCY_SUB_TYPE_NAME LIKE '%出院小结%')
--删掉第一个条件的distinct 5分47秒 1276条
SELECT * from (
SELECT PATIENT_NO FROM DP_EMR_RECORD WHERE PATIENT_NO IN(
SELECT DISTINCT PATIENT_NO FROM DP_EMR_RECORD WHERE REC_DEPT_NAME IN ('神经内科','神内(2)')
AND EMR_TYPE_NAME='出院记录'
AND LGCY_SUB_TYPE_NAME not LIKE '%疾病诊断%')AND EMR_TYPE_NAME= '入院记录' AND REC_DEPT_NAME IN ('神经内科','神内(2)'))a
where a.PATIENT_NO IN (
SELECT DISTINCT PATIENT_NO FROM DP_EMR_RECORD WHERE PATIENT_NO IN (
SELECT DISTINCT PATIENT_NO FROM DP_EMR_RECORD WHERE REC_DEPT_NAME IN ('神经内科','神内(2)')
AND EMR_TYPE_NAME='入院记录')
AND LGCY_SUB_TYPE_NAME LIKE '%出院小结%')
--删掉第二个条件的distinct 0秒 1271条
SELECT * from (
SELECT DISTINCT PATIENT_NO FROM DP_EMR_RECORD WHERE PATIENT_NO IN(
SELECT DISTINCT PATIENT_NO FROM DP_EMR_RECORD WHERE REC_DEPT_NAME IN ('神经内科','神内(2)')
AND EMR_TYPE_NAME='出院记录'
AND LGCY_SUB_TYPE_NAME not LIKE '%疾病诊断%')AND EMR_TYPE_NAME= '入院记录' AND REC_DEPT_NAME IN ('神经内科','神内(2)'))a
where a.PATIENT_NO IN (
SELECT PATIENT_NO FROM DP_EMR_RECORD WHERE PATIENT_NO IN (
SELECT DISTINCT PATIENT_NO FROM DP_EMR_RECORD WHERE REC_DEPT_NAME IN ('神经内科','神内(2)')
AND EMR_TYPE_NAME='入院记录')
AND LGCY_SUB_TYPE_NAME LIKE '%出院小结%')

--得到的最优的解决方案是：

缕清逻辑，该在一块儿的在一块，in的语句分成两个条件连接，就变成了以下语句：四个条件连接。

 

select distinct a.PATIENT_NO from (

SELECT PATIENT_NO FROM DP_EMR_RECORD WHERE REC_DEPT_NAME IN ('神经内科','神内(2)')

AND EMR_TYPE_NAME='出院记录'

AND LGCY_SUB_TYPE_NAME not LIKE '%疾病诊断%') a

join (

SELECT  PATIENT_NO FROM DP_EMR_RECORD WHERE EMR_TYPE_NAME= '入院记录' AND REC_DEPT_NAME IN ('神经内科','神内(2)')

) b on a.PATIENT_NO=b.PATIENT_NO

join (

SELECT PATIENT_NO FROM DP_EMR_RECORD WHERE REC_DEPT_NAME IN ('神经内科','神内(2)') AND EMR_TYPE_NAME='入院记录'

) c on a.PATIENT_NO=c.PATIENT_NO

join (

SELECT PATIENT_NO FROM DP_EMR_RECORD WHERE LGCY_SUB_TYPE_NAME  LIKE '%出院小结%'

) d on a.PATIENT_NO=d.PATIENT_NO

--最后得出的结论是:

--1、where, 和inner join 并不完全一样
--2、查询复杂时，尽量走hash join，避免嵌套查询
--3、exists不一定比in快，还得看数据
--4、内连接查询最稳定
--5、sqlserver内部的优化器只会选取它认为最右的路线，也不一定靠谱

--6、sqlserver有缓存机制，查询之前查过的数据可能会很快。
--7、缓存是服务器共享的，登录服务器上的所有人查询的缓存都可以互通。缓存有容量，会存很久，只有新增的缓存超出了缓存的容量才会顶掉之前的缓存。
--8、缓存机制不固定，有时候刚查完一遍，再查一模一样的还是很慢，但有的sql查一遍后再查会很快（走缓存） 查询速度还是决定于差询方法、数据以及sqlserver内部的优化器（路线不固定，会走它自己认为最优的路线）
--9、查询机制不固定，同样的sql在不同的时候可能会效率不同，例如in的那个sql，我查询用了0秒，粘过去那边要查询44秒。
--10、捋清思路再写很重要，改放在一起的放一起，放不在一起的用连接。

--11、内连接最稳定（我自己认为的，因为走hash join）。