SELECT Rev_Code,
Rev_ShowPath + ' ' + Rev_Name AS Rev_Name,
Rev_Level,
Rev_ID,
Rev_Path,
Rev_IsPoint,
Rev_ShowPath,
(SELECT Count(Rev_ID)
FROM QSPIS_Review
WHERE Rev_Path LIKE a.Rev_Path + '%'
AND Rev_IsPoint = 0) AS SumCount1,
(SELECT Count(tmp.Rev_ID)
FROM QSPIS_Review tmp
WHERE EXISTS (SELECT 'X'
FROM V_REVIEW_TASK where tmp.Rev_ID = Rev_ID)) AS AssignCount1,
(SELECT Count(tmp.Rev_ID)
FROM QSPIS_Review tmp
WHERE not EXISTS (SELECT 'X'
FROM V_REVIEW_TASK where tmp.Rev_ID = Rev_ID)
AND Rev_Path LIKE a.Rev_Path + '%'
AND Rev_IsPoint = 0) AS NoAssignCount1,
(SELECT Count(Rev_ID)
FROM QSPIS_Review
WHERE Rev_Path LIKE a.Rev_Path + '%'
AND Rev_IsPoint = 1) AS SumCount2,
(SELECT Count(tmp.Rev_ID)
FROM QSPIS_Review tmp
WHERE EXISTS (SELECT 'X'
FROM V_REVIEW_TASK where tmp.Rev_ID = Rev_ID)
AND Rev_Path LIKE a.Rev_Path + '%'
AND Rev_IsPoint = 1) AS AssignCount2,
(SELECT Count(tmp.Rev_ID)
FROM QSPIS_Review tmp
WHERE NOT EXISTS (SELECT 'X'
FROM V_REVIEW_TASK where tmp.Rev_ID = Rev_ID)
AND Rev_Path LIKE a.Rev_Path + '%'
AND Rev_IsPoint = 1) AS NoAssignCount2,
(SELECT Count(Rev_ID)
FROM V_REVIEW_TASK
WHERE task_Status NOT IN( 3 )
AND A.Rev_ID = Rev_ID) AS AssignCount3,
(SELECT Count(Rev_ID)
FROM V_REVIEW_TASK
WHERE task_Status = 1
AND A.Rev_ID = Rev_ID) AS AcceptedCount3,
(SELECT Count(Rev_ID)
FROM V_REVIEW_TASK
WHERE task_Status IN ( 0, 2 )
AND A.Rev_ID = Rev_ID) AS NoAcceptedCount3
FROM QSPIS_Review A求大神帮忙优化一下这条SQL,总共五千多条数据,执行完需要二十几秒,索引已建。。SQL优化
Rev_ShowPath + ' ' + Rev_Name AS Rev_Name,
Rev_Level,
Rev_ID,
Rev_Path,
Rev_IsPoint,
Rev_ShowPath,
(SELECT Count(Rev_ID)
FROM QSPIS_Review
WHERE Rev_Path LIKE a.Rev_Path + '%'
AND Rev_IsPoint = 0) AS SumCount1,
(SELECT Count(tmp.Rev_ID)
FROM QSPIS_Review tmp
WHERE EXISTS (SELECT 'X'
FROM V_REVIEW_TASK where tmp.Rev_ID = Rev_ID)) AS AssignCount1,
(SELECT Count(tmp.Rev_ID)
FROM QSPIS_Review tmp
WHERE not EXISTS (SELECT 'X'
FROM V_REVIEW_TASK where tmp.Rev_ID = Rev_ID)
AND Rev_Path LIKE a.Rev_Path + '%'
AND Rev_IsPoint = 0) AS NoAssignCount1,
(SELECT Count(Rev_ID)
FROM QSPIS_Review
WHERE Rev_Path LIKE a.Rev_Path + '%'
AND Rev_IsPoint = 1) AS SumCount2,
(SELECT Count(tmp.Rev_ID)
FROM QSPIS_Review tmp
WHERE EXISTS (SELECT 'X'
FROM V_REVIEW_TASK where tmp.Rev_ID = Rev_ID)
AND Rev_Path LIKE a.Rev_Path + '%'
AND Rev_IsPoint = 1) AS AssignCount2,
(SELECT Count(tmp.Rev_ID)
FROM QSPIS_Review tmp
WHERE NOT EXISTS (SELECT 'X'
FROM V_REVIEW_TASK where tmp.Rev_ID = Rev_ID)
AND Rev_Path LIKE a.Rev_Path + '%'
AND Rev_IsPoint = 1) AS NoAssignCount2,
(SELECT Count(Rev_ID)
FROM V_REVIEW_TASK
WHERE task_Status NOT IN( 3 )
AND A.Rev_ID = Rev_ID) AS AssignCount3,
(SELECT Count(Rev_ID)
FROM V_REVIEW_TASK
WHERE task_Status = 1
AND A.Rev_ID = Rev_ID) AS AcceptedCount3,
(SELECT Count(Rev_ID)
FROM V_REVIEW_TASK
WHERE task_Status IN ( 0, 2 )
AND A.Rev_ID = Rev_ID) AS NoAcceptedCount3
FROM QSPIS_Review A求大神帮忙优化一下这条SQL,总共五千多条数据,执行完需要二十几秒,索引已建。。SQL优化
解决方案 »
- 啊,啊,啊,高手(谁是高手中的高手,谁是叼中之王?)!请帮忙,在线等~~~
- 判断一个表是否存在的SQL怎么写?
- 数据库系统 查询 连接 操作的复杂度
- 时间戳有什么用处,谁能举个比较形象的例子说一下,理论上不明白.
- 急,sql2000怎么也安装不成功,高分相赠
- 请部内联接,左联接 右联接有什么区别.在什么情况下用哪种联接?
- count()怎么用呢?怎样得到COUNT(*)的返回值
- 怎样用select语句查出指定范围内的数据?
- 用SQl能实现吗?
- sql的全文索引问题,极简单,谢谢了,在有较多的NULL的列上做全文索引有什么影响?
- 网站使用的sql2000上想做一个负载
- 求大神指教,关于用SQL SERVER2000开发简单数据库
看语句,主要的时候应该是消耗在Rev_Path LIKE a.Rev_Path + '%'上,你换成charindex(Rev_Path, a.Rev_Path)>=1 看看能不能行吧。
Rev_ShowPath + ' ' + Rev_Name AS Rev_Name,
Rev_Level,
Rev_ID,
Rev_Path,
Rev_IsPoint,
Rev_ShowPath,
(SELECT Count(Rev_ID)
FROM QSPIS_Review
WHERE Rev_Path LIKE a.Rev_Path + '%'
AND Rev_IsPoint = 0) AS SumCount1
FROM QSPIS_Review A
其中部分SQL执行计划,如下图
这类语句要检查 where条件后面的task_status和rev_id是否建立复合索引?
2.not exists通常开销大
3. exists这种语句改成inner join会发现开销变少。。
4. 这个查询生成了很多中间表,其实可以使用临时表替代,如果临时表再加上索引然后再去做join性能会更有提升的。