Analysis of SQL statement execution principles

Principle:
Step 1: The application sends the SQL query statement to the server for execution.
When we execute SQL statements at the data layer, the application will connect to the corresponding database server and send the SQL statements to the server for processing.
Step 2: The server parses the request's SQL statement.
1: The SQL plan is cached. Friends who use the query analyzer may know the fact that a query statement needs to be executed for a very long time during the first running, however, if you run the same statement immediately or within a certain period of time, the query results will be returned within a short period of time.
Cause:
1): After receiving the query request, the server does not query the database immediately. Instead, it checks whether there is an execution plan in the Plan cache of the database. If yes, the compiled execution plan is called directly, saving the Compilation Time of the execution plan.
2): If the queried row already exists in the data buffer area, you do not need to query physical files, but retrieve data from the cache, in this way, retrieving data from the memory is much faster than reading data from the hard disk, improving the query efficiency. the data buffer storage area will be mentioned later.
2: if there is no corresponding execution plan in the SQL plan cache, the server first checks the syntax of the SQL statement requested by the user. If a syntax error occurs, the server ends the query operation, and return the corresponding error information to the application that calls it.
Note: The returned error information only contains basic syntax error information, for example, select is written as selec. If the error information contains a column that is not in the list, at this time, the server will not check the syntax, because it is just a syntax verification, whether the semantics is correct in the next step.
3: After the syntax is met, verify whether the semantics of the database object is correct, such as the table name, column name, and stored procedure. If any database object exists, an error is reported to the application and the query ends.
4: The next step is to obtain the parsing lock of the object. When we query a table, the server First locks the object to ensure data uniformity. If no lock is applied, at this time, there is data insertion, but because there is no lock, the query has read this record, and some inserts will be rolled back due to transaction failure, it will form a dirty read phenomenon.
5: The next step is to verify the Database User Permissions. The SQL statement syntax and semantics are correct. At this time, the query results may not be obtained. If the database user does not have the corresponding access permissions, the server reports an error of insufficient permissions to the application. In a slightly larger project, a project usually contains several database connection strings. These database users have different permissions, some of which are read-only, some are write-only permissions, some are readable and writable, and different users are selected for execution based on different operations. No matter how well your SQL statements are written, they are useless.
6: The last step of parsing is to determine the final execution plan. When the syntax, semantics, and permissions are verified, the server will not immediately return the results to you, but will optimize your SQL, select different query algorithms and return them to the application in the most efficient form. For example, when performing table join queries, the server will decide whether to use hash join, merge join, or loop join Based on the overhead cost. Which index will be more efficient, however, its automatic optimization is limited. To write efficient query SQL statements, you must optimize your own SQL query statements.
After the execution plan is determined, the execution plan will be saved to the SQL plan cache. The next time you have the same execution request, it will be directly retrieved from the plan cache, avoid re-compiling the execution plan.
Step 3: Execute the statement.
After the server completes parsing the SQL statement, the server will know what the statement actually means. Then, the server will actually execute the SQL statement.
There are two scenarios::
1): if the data row contained in the query statement has been read to the data buffer area, the server will directly read the data from the data buffer area and return it to the application, this avoids reading from physical files and improves the query speed.
2): if the data row is not in the data buffer area, it will read the record from the physical file and return it to the application. At the same time, it will write the data row into the data buffer area, for the next use.
Note: The SQL cache is divided into several types. If you are interested, you can search for it. Sometimes, because of the existence of the cache, it is difficult for us to see the optimization results immediately, because the second execution will be extremely fast because of the existence of a cache, the cache is usually eliminated first, and then the performance before and after optimization is compared. Here are several common methods:
DBCC DROPCLEANBUFFERS
Delete all cleared buffers from the buffer pool.
DBCC FREEPROCCACHE
Deletes all elements from the process cache.
DBCC FREESYSTEMCACHE
Release all unused cache entries from all caches. SQL Server 2005 database engine clears unused cache entries in the background in advance so that the memory can be used for the current entry. However, you can use this command to manually delete unused entries from all caches.
This can only basically eliminate the impact of SQL cache. At present, it seems that there is no solution to completely eliminate the cache. If you have any, please advise.
Execution sequence:
1. The FROM clause returns the initial result set.
2. The WHERE clause is used to exclude rows that do not meet the search criteria.
3. The group by clause collects selected rows into the GROUP with unique values in the group by clause.
4. Select the aggregate function specified in the list to calculate the aggregate values of each group.
5. In addition, the HAVING clause is used to exclude rows that do not meet the search criteria.
6. Calculate all expressions;
7. Use order by to sort the result set.
8. Search for the fields you want to search.