Why the sqlserver not in statement causes the program to crash

Today, I would like to talk to you about why the sqlserver not in sentence caused Cheng Chong to collapse. Many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something according to this article.

Two tables Organization structure Table (Organise) and payroll History Table (WagePerMonthHis) two tables are associated with Organise.Item_id and WagePerMonthHis.OrgIdS. There are about 6000 records and 11 fields in the Organise table (hereinafter referred to as the O table). WagePerMonthHis (hereinafter referred to as Table W) has 1.25 million records and 25 fields. The following statement in the original program queries all records that are not in the W table at organizational structure level 2. The copy code is as follows: select OrgId as company code, OrgName as company name from Organise where OrgLev=2 and item_id not in (select OrgidS from WagesPerMonthHis where WagesYear='2010' and WagesMonth= '01' Group by OrgidS,OrgNameS) order by Orgid

The execution of the statement takes as long as 33 seconds, and the configuration of the server is relatively high: 16 core 4CPU not only has 24G memory, and there are no bottlenecks in memory and CPU execution. At first, I thought it was caused by the slow execution of this statement (select OrgidS from WagesPerMonthHis where WagesYear='2010' and WagesMonth= '01' Group by OrgidS,OrgNameS). When executed alone, I found that the execution speed was very fast, about 2 seconds later, so the crux of the problem came out. It is the performance degradation brought about by the full-scan keyword not in. The most direct is to cause the page to lose response, a key function can not be used. Try the not exist sentence, found that the effect is the same, not like the Internet said can improve a lot of performance. So the re-optimization statement copies the code as follows: select a.OrgId as company code, a.OrgName as company name, a.item_id from Organise a left outer join (select distinct b.OrgIdS from WagesPerMonthHis b where WagesYear='2010' and WagesMonth='01') as b on a.item_id = b.OrgidS where a.OrgLev = 2 and b.OrgIdS is Null order by company code

After switching to the left outer connection (in fact, the left connection is also possible), the whole sentence execution speed is 400ms, 33 seconds and 400ms. I don't think many people thought of it.

After reading the above, do you have any further understanding of why the sqlserver not in statement crashed Cheng Chong? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.