SQL for Program Performance Optimization

SQL for Program Performance Optimization

If the function is the body of the program, the performance is the soul of the program. The complete function can ensure that the physical body of the program is sound, and good performance is the symbol of the soul of the program. This article will briefly introduce the performance optimization of the program.

Recently, I have a deep understanding of program performance. Recently, a data query and display function was provided to query and display 25 data records from about 1500 data records in 7 tables, which took 1 second. My computer parameters are: CPU: i5 processor, 4G memory. This execution speed is quite slow. Fortunately, the amount of data I query is small and the waiting time is not much. However, it is imperative to optimize the program performance.

After carefully analyzing the program, we found that many changes are required. Let's start with the database. Let's take a look at the code. This is the SQL statement in the program:

select {0} from ((((({1} inner join {2}) inner join {3}) inner join {4}) inner join {5}) inner join {6}) inner join {7} where {8} ",                      "lp.CnName as ProjectCityName,t.TaskName,ls.CnName as StageAreaName,tm.TaskName as TaskTemplateName,t.StartTime,t.RealStartTime,t.EndTime,t.RealEndTime,t.Executor,cc.CnName as CorporationName",                       "[SubjectDB].[WorkPlan].[Task] as t",                       "[SubjectDB].[WorkPlan].[Plan] as p on t.PlanCode=p.Code",                       "[SubjectDB].[WorkPlan].[TemplateTask] as tm on t.TemplateTaskCode = tm.Code",                        "[SubjectDB].[LandObtained].[ProjectCity] as lp on p.ProjectCityCode = lp.CityCode",                        "[SubjectDB].[LandObtained].[StageArea] as ls on p.StageAreaCode = ls.Code",                        "[SubjectDB].[LandObtained].[Corporation_ProjectInfo] as lcp on p.ProjectCode  = lcp.ProjectInfoCode",                        "[SubjectDB].[Common].[Corporation] as cc on lcp.CorporationCode = cc.Code",                        "t.RealEndTime <= '" + endTime + "'and t.RealStartTime >='" + startTime + "' and t.VersionEndTime is null and p.ProjectCityCode in ('" + strCityCodeIn + "') and t.TaskStatus = '" + taskStatusCode + "' and ( t.TaskName like " + strTaskPointNameIn + ") and t.TemplateTaskCode not in ('" + strTaskPointCodeIn + "')

Analyze this code and use 6 inner join, 1 in, 1 not in, and an unfixed Number of like (strTaskPointNameIn also has orlike attached ). First, data query is very difficult. How should we optimize it? What should I pay attention to when using databases in terms of performance?

One inner join, left join, right join

1 inner join (equivalent join) returns records with equal join fields in two tables

2 left join returns all records in the left table that are equal to the connection fields in the right table.

3 right join returns all records in the right table that are equal to the connection fields in the left table.

For example:

Table

ID1	Name1
1	A
2	A
3	A

 

 

Table B

ID2	Name2
1	B
3	B
4	B

 

Innerjoin:

Select * from A inner join B onA. ID = B. ID

Result:

ID1	Name1	ID2	Name2
1	A	1	B
3	A	3	B

 

Leftjoin:

Select * from A left join B on A. ID = B. ID

Result:

ID1	Name1	ID2	Name2
1	A	1	B
2	A	Null	Null
3	A	3	B

 

Rightjoin:

Select * from A right join Bon A. ID = B. ID

Result:

ID1	Name1	ID2	Name2
1	A	1	B
3	A	3	B
Null	Null	4	B

 

These three table join methods cannot directly say that the performance is the best. One is to look at the functional needs, and the other is to look at the specific query statements. In terms of the preceding SQL statements and functions, innerjoin should be the most suitable. First, the data volume is small, and second, no extra data is required.

2 in Exists

Another point worth discussing is the use of in and exists, which are used in nested queries. Let's first look at their respective query principles.

In is to hash the External table and the internal table, and exists is to loop the External table, and then query the internal table in each loop, it has always been said that exists is faster and inaccurate than the in query method, and the results are different in different situations. in addition, if the internal and external data volumes are consistent, the efficiency of the two methods is the same.

Now, we assume there are two tables:

Table A: The data volume is small, and Table B: The data volume is large.

Select * from A where cc in (select cc from B)

Efficiency is low, because the in connection uses the index of the cc column of external Table A for loop, and then searches for equivalent records in table B.

 

Select * from A where exists (select cc from B where cc = A. cc)

High efficiency, because the index of the cc column in table B is used.

Not in and not exists

If the query statement uses not in, both the internal and external tables need to scan the entire table, but the index is not used and the efficiency is low. not exists uses the index, so no matter which table is large, not exists is more efficient.

Write it here for the time being. The next article will continue performance optimization.