I would like to share with you an experience in SQL optimization.

I haven't been engaged in SQL for a long time, and recently I have been back to the original. I have some experience in optimization and want to share with you.

Let's look at the following query. This is the logic for querying student data. The logic is a bit messy. This query will not run for 30 minutes, and the execution of the CPU will immediately reach 100%, 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 aboveCodeIt looks a little 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.