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

gqhhzlsdhxy

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

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

为什么会这样?

gqhhzlsdhxy

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

大家没数据，不好诊断

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

fdphylsy

in 比 EXISTS 效率要差些

德鲁伊

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

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

德鲁伊

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

gqhhzlsdhxy

SCR_FORM 索引：

/****** Object:  Index [IX_SCR_FORM_CODE]    Script Date: 01/09/2017 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/2017 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/2017 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/2017 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/2017 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/2017 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/2017 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/2017 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/2017 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/2017 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]

solo_u

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

gqhhzlsdhxy

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

SCR_FORM.prj_contract_cd
没有建立索引.