Test and comparison of sqlnotin and leftjoin million-level data

These tests only differ significantly from databases of more than one million databases. Today, we use my application implementation to compare notin and leftjoin statements in SQL to test millions of data records, this is useful to all of you.

Only databases of more than one million levels have obvious differences in these tests, today, we use my application implementation to compare the not in and left join statements in SQL to test millions of data records. This is very useful for my friends.

Two organizational structure tables (Organise) and wage distribution History Table (WagePerMonthHis)

The two tables are associated through Organise. Item_id and WagePerMonthHis. OrgIdS.

In the Organise table (hereinafter referred to as the O table), there are about 6000 records and 11 fields. The WagePerMonthHis table (hereinafter referred to as the W table) has 1.25 million records and 25 fields.

The following statement in the original program

Is to query all records not in the W table at the organizational structure level of 2

The Code is as follows:
OrgId as company code, OrgName as company name From Organise Where OrgLev = 2 And item_id not in (Select OrgidS from WagesPerMonthHis Where WagesYear = '20140901' and WagesMonth = '01' Group by OrgidS, OrgNameS) Order by Orgid

The statement execution takes 33 seconds. The server configuration is relatively high: 16 cores, 4 CPUs, and 24 GB memory. The memory and CPU have no bottlenecks during execution.

The Code is as follows:
(Select OrgidS from WagesPerMonthHis Where WagesYear = '20140901' and WagesMonth = '01' Group by OrgidS, OrgNameS)

The execution of this statement was slow, but it was found that the execution speed was very fast. It took less than 2 seconds to execute this statement, so the crux of the problem was that the performance of the not in keyword was reduced. the most direct cause is that the page does not respond, and a key function cannot be used.

I tried the not exist statement and found that the effect is the same, which does not improve a lot of performance as mentioned on the Internet.

The optimization statement is as follows:

The Code is as follows:
Select a. OrgId as company code, a. OrgName as company name, a. item_id From Organise Left outer join (select distinct B. OrgIdS from WagesPerMonthHis B Where WagesYear = '000000' 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 can also be used), the execution speed of the entire statement is 400 ms, 33 S and 400 ms. I think many did not expect this.