SQL statement optimization

SQL statement optimization

This is the logic for querying student data. The logic is a bit messy. If the query runs for 30 minutes, no results will be returned. The CPU runs 100% immediately, although it is a virtual machine, however, this query cannot be handled, and there must be some optimizations.
  
SELECT * FROM student table WITH (NOLOCK) WHERE
(FromSys IS NULL OR
         (
(FromSys <> 'A' AND FromSys <> 'B' AND FromSys <> 'C') OR
(
(FromSys = 'a' OR FromSys = 'B' OR FromSys = 'C ')
AND FromSysID not in (SELECT OriginID FROM student table WITH (NOLOCK) WHERE DataFlag = 0)
 )
)
     )

 
The above code looks messy. In fact, there are three conditions:
1: FromSys is null.
2: FromSys <> 'A' AND FromSys <> 'B' AND FromSys <> 'C '.
3 :( FromSys = 'a' OR FromSys = 'B' OR FromSys = 'C') AND FromSysID NOT IN (SELECT OriginID FROM student table WITH (NOLOCK) WHERE DataFlag = 0)
 )

The optimization points are as follows:
First: replace the first and second conditions with union all. This is mainly because too many or queries may cause table scans, resulting in performance degradation.
Second, replace the not in the third or with left join. in this way, I would like to thank the DBA of my previous company. They taught me a lot of SQL knowledge.
Left join SQL:
         
Select count (*) FROM (
SELECT *
FROM student table WITH (NOLOCK) WHERE
FromSys IN ('A', 'B', 'C ')
   
) AS tem left join dbo. Student Table s2 ON tem. FromSysID = s2.OriginID AND s2.DataFlag = 0
WHERE s2.FromSysID IS null

The following figure shows the execution plan of left join, which is clear and simple.

        
 
Not in SQL:
           
Select count (*) FROM student table WITH (NOLOCK) WHERE
(FromSys = 'a' OR FromSys = 'B' OR FromSys = 'C ')
AND FromSysID not in (SELECT OriginID FROM student table WITH (NOLOCK) WHERE DataFlag = 0)
 
The following figure shows the not in execution plan, which is much more complex and has multiple nested queries.
         
Third, it is not easy to optimize SQL statements for non-professional DBAs to create indexes. However, SQL 2008 has a very simple function, you can obtain the index to be created from the estimated execution plan. Let's take a look at it:
 
                                 
  
Fourth, if there are too many or conditions in A condition, for example, FromSys = 'a' OR FromSys = 'B' OR FromSys = 'C', you can use in for code, in this way, the code will be simplified.
 
Effect: The 30-minute execution result-free operation is finally completed within several seconds, which is incredible.

Supplement:

1. Multiple where and less having
Where is used to filter rows, and having is used to filter groups.

2. Multiple union all and fewer union
Union deletes duplicate rows, so it takes some time

3. Multiple Exists, less in
Exists only checks the existence, and the performance is much better than in. Some friends do not use Exists. For example
For example, if you want to obtain the basic information of a person with a phone number, table2 has redundant information.
Select * from table1; -- (id, name, age)
Select * from table2; -- (id, phone)
In:
Select * from table1 t1 where t1.id in (select t2.id from table2 t2 where t1.id = t2.id );
Exists:
Select * from table1 t1 where Exists (select 1 from table2 t2 where t1.id = t2.id );

4. Bind variables
The Oracle database tutorial software caches executed SQL statements. Reusing this statement can reduce execution time.
Reuse is conditional, and SQL statements must be the same
Q: How is it different?
A: Everything is different. No matter what space or case, it is different.
To reuse statements, we recommend that you use PreparedStatement.
Write the statement as follows:
Insert into XXX (pk_id, column1) values (?,?);
Update XXX set column1 =? Where pk_id = ?;
Delete from XXX where pk_id = ?;
Select pk_id, column1 from XXX where pk_id = ?;

5. Use less *
Many friends like to use *, such as: select * from XXX;
Generally, you do not need all the data. You only need some data, and some only need one or two data records,
Take the data volume of 5 W and test the data with 10 attributes:
(The time here refers to the time when PL/SQL Developer displays all data)
Use select * from XXX; average 20 seconds,
Use select column1, column2 from XXX; average 12 seconds
(My machine is not very good ...)
For development, this is a disaster. Knowing it is one thing is another thing.

6. Paging SQL
The general paging SQL statement is as follows:
Sql1: select * from (select t. *, rownum rn from XXX t) where rn> 0 and rn <10;
Sql2: select * from (select t. *, rownum rn from XXX t where rownum <10) where rn> 0;
At first glance, there is no difference. Actually there is a big difference... 1.25 million pieces of data test,
Sql1 takes an average of 1.25 seconds (so accurate? )
Sql2 average... 0.07 seconds
The reason is that sql2 excluded 10 data from the subquery.
Of course, if the last 10 results are queried, the efficiency is the same.

7. You can use one SQL statement. Never use two SQL statements.
Not explained
(Additional content)
Article 3 refers to the oracle database.
Article 3 if there are so many questions that are not intended to be explained, there are still quite a few friends pursuing truth.
I think it is really possible to drop the database. I did not expect it to be my problem.

The above sections only focus on how SQL is optimized. As for low development efficiency and readability, they are not within the scope of consideration.

(Supplement content 2) 6th. If there are pages that need to be sorted, you must package another layer and the result is
Select * from (select t. *, rownum rn from (select * from XXX order by value desc) t where rownum <= 10) where rn> 0;

(Supplement 3) 3rd items. If the number of parameters exceeds 1000, the database will be suspended. (Oracle 10 GB database)