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

[adandelion]

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'

[mengmou]

CTRL + L 看一下。

[xiequanqin]

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

大家没数据，不好诊断

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

[zjcxc]

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

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

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

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

[marco08]

用EXISTS作判断试试

[bugchen888]

单独看看子查询的性能：

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

[lovcal]

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

[caixia615]

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

[tmc1703]

in 比 EXISTS 效率要差些

[哇咔咔]

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

[adandelion]

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

Sql server 2005 环境

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

---是的.子查询性能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分析的结果确定如何走索引是最有效的)

--分析后的结果看得不是很清楚(各模块所占的百分比是挺平均的).我把分析结果上传了.帮助看看.
http://www.cnblogs.com/Files/adandelion/Search.rar


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

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

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

[adandelion]

补充下:表SC_ITEM里的SCR_FORM_CODE 有40W记录是null;不是null的有30多条;SCR_FORM_CODE 是索引.

如果把子查询里的"and SC_ITEM.SCR_FORM_CODE is not null"加上的话,速度会很快(子查询和总的查询(in和and都在)结果都很快(不足一秒))

为什么会这样?

[adandelion]

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

大家没数据，不好诊断

in条件中那几个inner join 是不是可以优化？
还有子查询中的DISTINCT..
------------
--子查询DISTINCT..无法优化了.3个表内联只取了一个字段啊.

[lovcal]

很明显，从你的执行计划看，加了and SCR_FORM.prj_contract_cd = 'cr050053r-0640'--聚焦索引之后，In字句的查询性能大大降低

首先是In字查询中的两个连接由聚焦索引扫描之后结果再做连接，然后筛选
但是加了and之后，被改作了In子查询中先两个表做全表扫描的连接--极大耗时出现的原因

[lovcal]

能看看你的SC_ITEM的情况么？包含索引、聚焦索引的信息

[adandelion]

SCR_FORM 索引：

/****** Object: Index [IX_SCR_FORM_CODE] Script Date: 01/09/2007 09:28:49 ******/
CREATE NONCLUSTERED INDEX [IX_SCR_FORM_CODE] ON [dbo].[SCR_FORM]
(
[SCR_FORM_CODE] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]


/****** Object: Index [IX_SCR_FORM_STATUS] Script Date: 01/09/2007 09:28:54 ******/
CREATE NONCLUSTERED INDEX [IX_SCR_FORM_STATUS] ON [dbo].[SCR_FORM]
(
[STATUS] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]


/****** Object: Index [IX_SCR_FORM_VENDOR_CODE] Script Date: 01/09/2007 09:29:01 ******/
CREATE NONCLUSTERED INDEX [IX_SCR_FORM_VENDOR_CODE] ON [dbo].[SCR_FORM]
(
[OS_VENDOR_CODE] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]


/****** Object: Index [PK318] Script Date: 01/09/2007 09:29:19 ******/
ALTER TABLE [dbo].[SCR_FORM] ADD CONSTRAINT [PK318] PRIMARY KEY CLUSTERED
(
[SCR_FORM_ID] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]




sc_item 索引：
/****** Object: Index [IX_SC_ITEM_SCR_FORM_CODE] Script Date: 01/09/2007 09:26:52 ******/
CREATE NONCLUSTERED INDEX [IX_SC_ITEM_SCR_FORM_CODE] ON [dbo].[SC_ITEM]
(
[SCR_FORM_CODE] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]


/****** Object: Index [IX_SC_ITEM_STATUS] Script Date: 01/09/2007 09:26:57 ******/
CREATE NONCLUSTERED INDEX [IX_SC_ITEM_STATUS] ON [dbo].[SC_ITEM]
(
[STATUS] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]


/****** Object: Index [IX_SC_ITEM_SUBTYPE] Script Date: 01/09/2007 09:27:16 ******/
CREATE NONCLUSTERED INDEX [IX_SC_ITEM_SUBTYPE] ON [dbo].[SC_ITEM]
(
[SC_ADSLS12_SUBTYPE] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]


/****** Object: Index [IX_SC_ITEM_TASK_ID] Script Date: 01/09/2007 09:27:24 ******/
CREATE NONCLUSTERED INDEX [IX_SC_ITEM_TASK_ID] ON [dbo].[SC_ITEM]
(
[TASK_ID] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]


/****** Object: Index [IX_SC_ITEM_VENDOR_CODE] Script Date: 01/09/2007 09:27:32 ******/
CREATE NONCLUSTERED INDEX [IX_SC_ITEM_VENDOR_CODE] ON [dbo].[SC_ITEM]
(
[OS_VENDOR_CODE] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

/****** Object: Index [PK329] Script Date: 01/09/2007 09:27:42 ******/
ALTER TABLE [dbo].[SC_ITEM] ADD CONSTRAINT [PK329] PRIMARY KEY CLUSTERED
(
[SC_ITEM_ID] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

[flyin2006]

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

[adandelion]

很明显，从你的执行计划看，加了and SCR_FORM.prj_contract_cd = 'cr050053r-0640'--聚焦索引之后，In字句的查询性能大大降低
-------------
谢谢回复.

SCR_FORM.prj_contract_cd
没有建立索引.