| |

VerySource

 Forgot password?
 Register
Search
Author: gqhhzlsdhxy

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

[Copy link]

1

Threads

6

Posts

5.00

Credits

Newbie

Rank: 1

Credits
5.00

 China

 Author| Post time: 2020-7-23 21:15:01
| Show all posts
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?
Reply

Use magic Report

1

Threads

6

Posts

5.00

Credits

Newbie

Rank: 1

Credits
5.00

 Spain

 Author| Post time: 2020-7-23 21:45:01
| Show all posts
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.
Reply

Use magic Report

0

Threads

1

Posts

2.00

Credits

Newbie

Rank: 1

Credits
2.00

 Invalid IP Address

Post time: 2020-7-26 11:15:01
| Show all posts
in is less efficient than EXISTS
Reply

Use magic Report

0

Threads

34

Posts

17.00

Credits

Newbie

Rank: 1

Credits
17.00

 China

Post time: 2020-7-30 11:30:02
| Show all posts
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
Reply

Use magic Report

0

Threads

34

Posts

17.00

Credits

Newbie

Rank: 1

Credits
17.00

 China

Post time: 2020-7-30 12:45:01
| Show all posts
Can you see the status of your SC_ITEM? Contains index and focus index information
Reply

Use magic Report

1

Threads

6

Posts

5.00

Credits

Newbie

Rank: 1

Credits
5.00

 China

 Author| Post time: 2020-7-30 15:30:01
| Show all posts
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]
Reply

Use magic Report

0

Threads

5

Posts

4.00

Credits

Newbie

Rank: 1

Credits
4.00

 China

Post time: 2020-7-30 21:45:01
| Show all posts
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
Reply

Use magic Report

1

Threads

6

Posts

5.00

Credits

Newbie

Rank: 1

Credits
5.00

 South Africa

 Author| Post time: 2020-7-31 18:30:01
| Show all posts
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.
Reply

Use magic Report

You have to log in before you can reply Login | Register

Points Rules

Contact us|Archive|Mobile|CopyRight © 2008-2023|verysource.com ( 京ICP备17048824号-1 )

Quick Reply To Top Return to the list