[请教] 查询效率问题 (当查询条件in和and 同时存在的时候查询效率为啥巨低?),非常感谢
1.SELECT * FROM ....
WHERE NAME IN ( 'NAME1 ')
2.SELECT * FROM ....
WHERE AGET= 3
3.SELECT * FROM ....
WHERE NAME IN ( 'NAME1 ') AND AGET= 3
1和2的效率差不多多.但3却需要1的40倍时间来查询,为什么会这样????
真实语句如下:
SELECT dbo.USER_INFO.USER_NAME, dbo.SCR_FORM.SCR_FORM_ID, dbo.SCR_FORM.SCR_FORM_CODE, dbo.SCR_FORM.PRJ_CONTRACT_CD,
dbo.SCR_FORM.SC_TYPE, dbo.SCR_FORM.SC_FRAME, dbo.SCR_FORM.OS_VENDOR_CODE, dbo.SCR_FORM.OS_TEAM_CODE,
dbo.SCR_FORM.SUBMIT_DATE, dbo.SCR_FORM.STATUS, dbo.SCR_FORM.SUBMITTER_USER_CD, dbo.SCR_FORM.BGM_USER_CD,
dbo.SCR_FORM.CPM_USER_CD, dbo.SCR_FORM.CPD_USER_CD, dbo.SCR_FORM.OS_USER_CD, dbo.OS_VENDOR.FULL_NAME AS VENDORNAME,
dbo.OS_TEAM.FULL_NAME AS TEAMNAME, dbo.PROJECT.PROJECT_NAME, dbo.PROJECT.PARTY_ID
FROM dbo.SCR_FORM INNER JOIN
dbo.PROJECT ON dbo.SCR_FORM.PRJ_CONTRACT_CD = dbo.PROJECT.PROJECT_CD LEFT OUTER JOIN
dbo.USER_INFO ON dbo.SCR_FORM.SUBMITTER_USER_CD = dbo.USER_INFO.USER_CD LEFT OUTER JOIN
dbo.OS_TEAM ON dbo.SCR_FORM.OS_TEAM_CODE = dbo.OS_TEAM.OS_TEAM_CODE LEFT OUTER JOIN
dbo.OS_VENDOR ON dbo.SCR_FORM.OS_VENDOR_CODE = dbo.OS_VENDOR.OS_VENDOR_CODE
--下面是in条件
where SCR_FORM.SCR_FORM_CODE in
(SELECT
distinct SC_ITEM.SCR_FORM_CODE
FROM dbo.SC_ITEM
INNER JOIN
dbo.EI_ORDER_ITEM on EI_ORDER_ITEM.TASK_ID =SC_ITEM.task_id INNER JOIN
dbo.PRODUCT_LINE ON dbo.EI_ORDER_ITEM.PRODUCT_LINE = dbo.PRODUCT_LINE.PRODUCT_LINE_CD
where SC_ITEM.STATUS= 'APPROVED ' --and SC_ITEM.SCR_FORM_CODE is not null
and (product_line.product_type_cd <> 'adsl ' and product_line.product_type_cd <> 's12 '))
--下面是AND条件
and SCR_FORM.prj_contract_cd = 'cr050053r-0640 '
[解决办法]
CTRL + L 看一下。
[解决办法]
只能自己通过执行计划看看哪个地方耗资源了。。
大家没数据，不好诊断
in条件中那几个inner join 是不是可以优化？
还有子查询中的DISTINCT..
[解决办法]
楼主的意思是去掉其中一个条件, 速度就快很多了?
那么可能的原因是sql没有使用正确的执行计划.
尝试用下面的方法来解决:
1. 查询分析器连接到你的实例, 输入你的语句, 按Ctrl+L查看执行计划, 重点检查执行计划中, 有没有红色标注的结点, 如果有, 说明是统计信息丢失, 一般在红色标注的地方按右键--创建丢失的统计就可以解决.
2. 如果确实不是统计信息丢失, 同样用Ctrl+L的方法, 比较,同时应用条件和只加一个条件时, 执行计划的差异(重点在于使用的索引)
绝大部分情况下, SQL会选择正确的索引, 但有时会选择错误, 这种情况下, 你可以通过强制指定索引的方法来解决(根据Ctrl+L分析的结果确定如何走索引是最有效的)
[解决办法]
用EXISTS作判断试试
[解决办法]
单独看看子查询的性能：
(SELECT
distinct SC_ITEM.SCR_FORM_CODE
FROM dbo.SC_ITEM
INNER JOIN
dbo.EI_ORDER_ITEM on EI_ORDER_ITEM.TASK_ID =SC_ITEM.task_id INNER JOIN
dbo.PRODUCT_LINE ON dbo.EI_ORDER_ITEM.PRODUCT_LINE = dbo.PRODUCT_LINE.PRODUCT_LINE_CD
where SC_ITEM.STATUS= 'APPROVED ' --and SC_ITEM.SCR_FORM_CODE is not null
and (product_line.product_type_cd <> 'adsl ' and product_line.product_type_cd <> 's12 '))
[解决办法]
难道是这两个条件同时用时索引破坏？
[解决办法]
不是有测试工具可以检测的么
[解决办法]
in 比 EXISTS 效率要差些
[解决办法]
很明显，从你的执行计划看，加了and SCR_FORM.prj_contract_cd = 'cr050053r-0640 '--聚焦索引之后，In字句的查询性能大大降低
首先是In字查询中的两个连接由聚焦索引扫描之后结果再做连接，然后筛选
但是加了and之后，被改作了In子查询中先两个表做全表扫描的连接--极大耗时出现的原因
[解决办法]
set statistics profile on
set statistics io on
set statistics time on
go
语句
go
set statistics profile off
set statistics io off
set statistics time off
