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

gqhhzlsdhxy

1.SELECT * FROM ....
 WHERE NAME IN ('NAME1')

2.SELECT * FROM ....
 WHERE AGET = 3

3.SELECT * FROM ....
 WHERE NAME IN ('NAME1') AND AGET = 3


The efficiency of 1 and 2 is almost the same. But 3 needs 40 times of time to query, why is this so ????

The real statement is as follows:

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
  --The following is the in condition
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
Ranch
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'))
--The following is the AND condition
 and SCR_FORM.prj_contract_cd = 'cr050053r-0640'

sinexe

CTRL + L take a look.

wgfxman

You can only see where the resources are consumed by the execution plan. .

Everyone has no data, it is not easy to diagnose

Can those inner joins in the in condition be optimized?
There is also DISTINCT in the subquery...

hhuaixiaozi

The landlord means to remove one of the conditions, the speed is much faster?

Then the possible reason is that sql is not using the correct execution plan.

Try to solve it with the following method:
1. The query analyzer is connected to your instance, enter your statement, press Ctrl+L to view the execution plan, and focus on checking whether there are nodes marked in red in the execution plan. If so, it means that the statistical information is missing, generally in red Press the right button on the marked place-you can solve it by creating missing statistics.

2. If it is indeed not that the statistical information is lost, also use the Ctrl+L method to compare, when applying the condition and adding only one condition at the same time, the difference in the execution plan (the key is the index used)
   In most cases, SQL will choose the correct index, but sometimes it will choose the wrong one. In this case, you can solve it by forcing the specified index (according to the results of Ctrl+L analysis to determine how to index is the most effective )

fanfan1980

Try to judge with EXISTS

mydriver

Take a look at the performance of subqueries alone:

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

德鲁伊

Could it be that these two conditions use index destruction at the same time?

我是杀人狂

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

lianhaipipi

Isn’t there a test tool that can be tested?

gqhhzlsdhxy

First of all, thank you for your enthusiastic answers! The reply is as follows:

The original poster means that if one of the conditions is removed, the speed will be much faster?

---Yes. The subquery performance is 2 seconds.; The total query after using in is 1 seed; the total query after using SCR_FORM.prj_contract_cd ='cr050053r-0640' is less than 1 second; using in and using SCR_FORM.prj_contract_cd = The total query after'cr050053r-0640' is 88 seconds!

Try the following methods to solve:
1. Connect the query analyzer to your instance, enter your statement, press Ctrl+L to view the execution plan, and focus on checking whether there are nodes marked in red in the execution plan. If so, it means that the statistics are missing, usually in red Click the right button on the marked place-create the missing statistics to solve it.

--No error

2. If it is really not the loss of statistical information, use the same method of Ctrl+L to compare, the difference between the execution plan when the condition is applied and only one condition is added (the focus is on the index used)
   In most cases, SQL will select the correct index, but sometimes it will select the wrong index. In this case, you can solve it by forcing the index to be specified (according to the results of the Ctrl+L analysis to determine how to use the index is the most effective )

--The results of the analysis are not very clear (the percentage of each module is quite average).


Try using EXISTS as a judgment
---Tested, the effect has not changed.


Take a look at the performance of the subquery separately:

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

--Sub query performance is 2 seconds.; The total query after using in is 1 seedling; the total query after using SCR_FORM.prj_contract_cd ='cr050053r-0640' is less than 1 second; using in and using SCR_FORM.prj_contract_cd ='cr050053r-0640' The total query after 'is 88 seconds!

Could it be that the index breaks when these two conditions are used at the same time?
--I do not understand the meaning....

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

- This has been tested early and it has no effect.

Isn’t there a test tool that can be tested?
--What test tool?