[请教] 查询效率问题 (当查询条件in和and 同时存在的时候查询效率为啥巨低?),非常感谢

gqhhzlsdhxy

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'

sinexe

CTRL + L 看一下。

wgfxman

只能自己通过执行计划看看哪个地方耗资源了。。

大家没数据，不好诊断

in条件中那几个inner join 是不是可以优化？
还有子查询中的DISTINCT..

hhuaixiaozi

楼主的意思是去掉其中一个条件, 速度就快很多了?

那么可能的原因是sql没有使用正确的执行计划.

尝试用下面的方法来解决:
1. 查询分析器连接到你的实例, 输入你的语句, 按Ctrl+L查看执行计划, 重点检查执行计划中, 有没有红色标注的结点, 如果有, 说明是统计信息丢失, 一般在红色标注的地方按右键--创建丢失的统计就可以解决.

2. 如果确实不是统计信息丢失, 同样用Ctrl+L的方法, 比较,同时应用条件和只加一个条件时, 执行计划的差异(重点在于使用的索引)
   绝大部分情况下, SQL会选择正确的索引, 但有时会选择错误, 这种情况下, 你可以通过强制指定索引的方法来解决(根据Ctrl+L分析的结果确定如何走索引是最有效的)

fanfan1980

用EXISTS作判断试试

mydriver

单独看看子查询的性能：

(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'))

德鲁伊

难道是这两个条件同时用时索引破坏？

我是杀人狂

try:
SELECT * FROM (select * from ... where adet=3)a
WHERE NAME IN ('NAME1')

lianhaipipi

不是有测试工具可以检测的么

gqhhzlsdhxy

首先谢谢各位的热心回答!回复如下:

楼主的意思是去掉其中一个条件, 速度就快很多了?

---是的.子查询性能2秒.; 使用in后的总查询是1苗;使用SCR_FORM.prj_contract_cd = 'cr050053r-0640'后的总查询是不足1秒;使用in并且用SCR_FORM.prj_contract_cd = 'cr050053r-0640'后的总查询是88秒!

尝试用下面的方法来解决:
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'))

--子查询性能2秒.; 使用in后的总查询是1苗;使用SCR_FORM.prj_contract_cd = 'cr050053r-0640'后的总查询是不足1秒;使用in并且用SCR_FORM.prj_contract_cd = 'cr050053r-0640'后的总查询是88秒!

难道是这两个条件同时用时索引破坏？
--不明白什么意思....

try:
SELECT * FROM (select * from ... where adet=3)a
WHERE NAME IN ('NAME1')

--这个早测试过了,没有效果.

不是有测试工具可以检测的么
--啥测试工具?