我正在使用SQL Server 2008，并试图提高我的查询速度。 该查询基于重新开始日期为患者分配点数。
示例：患者在1 / 2,1 / 5,1 / 7,1 / 8,1 / 9,2 / 4处可见。 我想先在3天内进行团体访问。 1 / 2-5分组，1 / 7-9分组。 1/5不与1/7分组，因为1/5的实际访问日期是1/2。 1/7将获得3分，因为它是1/2的重新接受。 2/4也会获得3分，因为它是1/7的重新接纳。 将日期分组后，第一个日期是实际访问日期。
大多数文章建议限制数据集或添加索引以提高速度。 我已将行数限制为约15,000并添加了索引。 当运行具有45个测试访问日期/ 3个测试患者的查询时，查询需要1.5分钟才能运行。 使用我的实际数据集需要> 8小时。 如何让此查询运行<1小时？ 有没有更好的方法来编写我的查询？ 我的索引看起来是否正确？ 任何帮助将不胜感激。
示例预期结果低于查询。
;CREATE TABLE RiskReadmits(MRN INT, VisitDate DATE, Category VARCHAR(15)) ;CREATE CLUSTERED INDEX Risk_Readmits_Index ON RiskReadmits(VisitDate) ;INSERT RiskReadmits(MRN,VisitDate,CATEGORY) VALUES (1, '1/2/2016','Inpatient'), (1, '1/5/2016','Inpatient'), (1, '1/7/2016','Inpatient'), (1, '1/8/2016','Inpatient'), (1, '1/9/2016','Inpatient'), (1, '2/4/2016','Inpatient'), (1, '6/2/2016','Inpatient'), (1, '6/3/2016','Inpatient'), (1, '6/5/2016','Inpatient'), (1, '6/6/2016','Inpatient'), (1, '6/8/2016','Inpatient'), (1, '7/1/2016','Inpatient'), (1, '8/1/2016','Inpatient'), (1, '8/4/2016','Inpatient'), (1, '8/15/2016','Inpatient'), (1, '8/18/2016','Inpatient'), (1, '8/28/2016','Inpatient'), (1, '10/12/2016','Inpatient'), (1, '10/15/2016','Inpatient'), (1, '11/17/2016','Inpatient'), (1, '12/20/2016','Inpatient') ;WITH a AS ( SELECT z1.VisitDate , z1.MRN , (SELECT MIN(VisitDate) FROM RiskReadmits WHERE VisitDate > DATEADD(day, 3, z1.VisitDate)) AS NextDay FROM RiskReadmits z1 WHERE CATEGORY = 'Inpatient' ), a1 AS ( SELECT MRN , MIN(VisitDate) AS VisitDate , MIN(NextDay) AS NextDay FROM a GROUP BY MRN ), b AS ( SELECT VisitDate , MRN , NextDay , 1 AS OrderRow FROM a1 UNION ALL SELECT a.VisitDate , a.MRN , a.NextDay , b.OrderRow +1 AS OrderRow FROM a JOIN b ON a.VisitDate = b.NextDay ), c AS ( SELECT MRN, VisitDate , (SELECT MAX(VisitDate) FROM b WHERE b1.VisitDate > VisitDate AND b.MRN = b1.MRN) AS PreviousVisitDate FROM b b1 ) SELECT distinct c1.MRN, c1.VisitDate , CASE WHEN DATEDIFF(day,c1.PreviousVisitDate,c1.VisitDate) < 30 THEN PreviousVisitDate ELSE NULL END AS ReAdmissionFrom , CASE WHEN DATEDIFF(day,c1.PreviousVisitDate,c1.VisitDate) < 30 THEN 3 ELSE 0 END AS Points FROM c c1 ORDER BY c1.MRN预期成绩：
MRN VisitDate ReAdmissionFrom Points 1 2016-01-02 NULL 0 1 2016-01-07 2016-01-02 3 1 2016-02-04 2016-01-07 3 1 2016-06-02 NULL 0 1 2016-06-06 2016-06-02 3 1 2016-07-01 2016-06-06 3 1 2016-08-01 NULL 0 1 2016-08-15 2016-08-01 3 1 2016-08-28 2016-08-15 3 1 2016-10-12 NULL 0 1 2016-11-17 NULL 0 1 2016-12-20 NULL 0I am using SQL Server 2008 and am trying to increase the speed of my query below. The query assigns points to patients based on readmission dates.
Example: A patient is seen on 1/2, 1/5, 1/7, 1/8, 1/9, 2/4. I want to first group visits within 3 days of each other. 1/2-5 are grouped, 1/7-9 are grouped. 1/5 is NOT grouped with 1/7 because 1/5's actual visit date is 1/2. 1/7 would receive 3 points because it is a readmit from 1/2. 2/4 would also receive 3 points because it is a readmit from 1/7. When the dates are grouped the first date is the actual visit date.
Most articles suggest limiting the data set or adding indexes to increase speed. I have limited the amount of rows to about 15,000 and added a index. When running the query with 45 test visit dates/ 3 test patients, the query takes 1.5 min to run. With my actual data set it takes > 8 hrs. How can I get this query to run < 1 hr? Is there a better way to write my query? Does my Index look correct? Any help would be greatly appreciated.
Example expected results below query.
;CREATE TABLE RiskReadmits(MRN INT, VisitDate DATE, Category VARCHAR(15)) ;CREATE CLUSTERED INDEX Risk_Readmits_Index ON RiskReadmits(VisitDate) ;INSERT RiskReadmits(MRN,VisitDate,CATEGORY) VALUES (1, '1/2/2016','Inpatient'), (1, '1/5/2016','Inpatient'), (1, '1/7/2016','Inpatient'), (1, '1/8/2016','Inpatient'), (1, '1/9/2016','Inpatient'), (1, '2/4/2016','Inpatient'), (1, '6/2/2016','Inpatient'), (1, '6/3/2016','Inpatient'), (1, '6/5/2016','Inpatient'), (1, '6/6/2016','Inpatient'), (1, '6/8/2016','Inpatient'), (1, '7/1/2016','Inpatient'), (1, '8/1/2016','Inpatient'), (1, '8/4/2016','Inpatient'), (1, '8/15/2016','Inpatient'), (1, '8/18/2016','Inpatient'), (1, '8/28/2016','Inpatient'), (1, '10/12/2016','Inpatient'), (1, '10/15/2016','Inpatient'), (1, '11/17/2016','Inpatient'), (1, '12/20/2016','Inpatient') ;WITH a AS ( SELECT z1.VisitDate , z1.MRN , (SELECT MIN(VisitDate) FROM RiskReadmits WHERE VisitDate > DATEADD(day, 3, z1.VisitDate)) AS NextDay FROM RiskReadmits z1 WHERE CATEGORY = 'Inpatient' ), a1 AS ( SELECT MRN , MIN(VisitDate) AS VisitDate , MIN(NextDay) AS NextDay FROM a GROUP BY MRN ), b AS ( SELECT VisitDate , MRN , NextDay , 1 AS OrderRow FROM a1 UNION ALL SELECT a.VisitDate , a.MRN , a.NextDay , b.OrderRow +1 AS OrderRow FROM a JOIN b ON a.VisitDate = b.NextDay ), c AS ( SELECT MRN, VisitDate , (SELECT MAX(VisitDate) FROM b WHERE b1.VisitDate > VisitDate AND b.MRN = b1.MRN) AS PreviousVisitDate FROM b b1 ) SELECT distinct c1.MRN, c1.VisitDate , CASE WHEN DATEDIFF(day,c1.PreviousVisitDate,c1.VisitDate) < 30 THEN PreviousVisitDate ELSE NULL END AS ReAdmissionFrom , CASE WHEN DATEDIFF(day,c1.PreviousVisitDate,c1.VisitDate) < 30 THEN 3 ELSE 0 END AS Points FROM c c1 ORDER BY c1.MRNExpected Results:
MRN VisitDate ReAdmissionFrom Points 1 2016-01-02 NULL 0 1 2016-01-07 2016-01-02 3 1 2016-02-04 2016-01-07 3 1 2016-06-02 NULL 0 1 2016-06-06 2016-06-02 3 1 2016-07-01 2016-06-06 3 1 2016-08-01 NULL 0 1 2016-08-15 2016-08-01 3 1 2016-08-28 2016-08-15 3 1 2016-10-12 NULL 0 1 2016-11-17 NULL 0 1 2016-12-20 NULL 0最满意答案
哎呀我改了几个cte的名字（帖子搞砸了什么是代码）
它应该是这样的：
b AS（选择VisitDate，MRN，NextDay，1 AS OrderRow FROM a1
UNION ALL SELECT a.VisitDate , a.MRN , a.NextDay , b.OrderRow +1 AS OrderRow FROM a AS a JOIN b ON a.VisitDate = b.NextDay AND a.MRN = b.MRN）
oops I changed the names of a few cte's (and the post messed up what was code)
It should be like this:
b AS ( SELECT VisitDate , MRN , NextDay , 1 AS OrderRow FROM a1
UNION ALL SELECT a.VisitDate , a.MRN , a.NextDay , b.OrderRow +1 AS OrderRow FROM a AS a JOIN b ON a.VisitDate = b.NextDay AND a.MRN = b.MRN)
更多推荐
发布评论