[Ask] Query efficiency (when the query conditions in and and exist at the same time, why is the query efficiency so low?

gqhhzlsdhxy

Supplement: There are 40W records of SCR_FORM_CODE in table SC_ITEM that are null; there are more than 30 records that are not null; SCR_FORM_CODE is an index.

If you add "and SC_ITEM.SCR_FORM_CODE is not null" in the subquery, the speed will be very fast (the results of the subquery and the total query (both in and and) are very fast (less than one second))

Why is this happening?

gqhhzlsdhxy

You can only see which areas are consuming resources through the execution plan. .

Everyone has no data, difficult to diagnose

Can those inner joins in the in condition be optimized?
There is also DISTINCT in the subquery..
------------
--The subquery DISTINCT... can't be optimized anymore. Only one field is taken in 3 tables.

fdphylsy

in is less efficient than EXISTS

德鲁伊

Obviously, judging from your execution plan, adding and SCR_FORM.prj_contract_cd ='cr050053r-0640' - After focusing on the index, the query performance of the In sentence is greatly reduced

First, the two connections in the In word query are scanned by the focused index and then the results are connected, and then filtered
But after adding and, it was changed to join the first two tables in the In subquery to do a full table scan-the reason for the extremely time-consuming

德鲁伊

Can you see the status of your SC_ITEM? Contains index and focus index information

gqhhzlsdhxy

SCR_FORM index:

/****** 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 index:
/****** 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
Statement
go
set statistics profile off
set statistics io off
set statistics time off

gqhhzlsdhxy

Obviously, judging from your execution plan, adding and SCR_FORM.prj_contract_cd ='cr050053r-0640'--After focusing on the index, the query performance of the In sentence is greatly reduced
-------------
thanks for the reply.

SCR_FORM.prj_contract_cd
No indexing.