Two Tables Organizational structure table (organise) and payroll history Sheet (wagepermonthhis)
Two tables are associated by organise.item_id and Wagepermonthhis.orgids
Organise table (hereinafter referred to as O table) about 6,000 records in 11 fields, Wagepermonthhis (hereinafter referred to as W table) has 1.25 million records and 25 fields
The following statement in the original program
is to query all records that have an organizational hierarchy of 2 that is not in the W table
Copy Code code 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= ' and wagesmonth= '
' Group by Orgids,orgnames '
ORDER BY OrgID
Statement execution is 33 seconds long, the server configuration is relatively high: 16 core 4cpu,24g memory, and the memory and CPU in the implementation of the bottleneck does not occur, began to think is (select Orgids from Wagespermonthhis
Where wagesyear= ' and wagesmonth= '
' Group by Orgids,orgnames ' This statement is slow to execute, but it is found that execution is fast, less than 2 seconds out, so the crux comes out, is not in the full scan keyword performance degradation. The most immediate result is a loss of response to the page, A key feature is not available.
Tried the not exist statement, found that the effect is the same, not as the internet can be said to improve a lot of performance.
Then the following is the optimization of the statement
Copy Code 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= ' and wagesmonth= ') as B
On a.item_id = B.orgids
where A.orglev = 2
and B.orgids is Null
Order BY Company Code
Use the left outer join (in fact, the left connection can also), the entire statement execution speed of 400ms, 33 seconds and 400ms I think many people did not think of.
