How are SQL statements executed in the database?

Step 1: When the application sends the SQL query statement to the server for execution at the data layer, the application connects to the corresponding database server and sends the SQL statement to the server for processing. Step 2: The server parses the request's SQL statement, and the SQL plan is cached. Friends of common query analyzer may know this fact.

Step 1: When the application sends the SQL query statement to the server for execution at the data layer, the application connects to the corresponding database server and sends the SQL statement to the server for processing. Step 2: The server parses the request's SQL statement, and the SQL plan is cached. Friends of common query analyzer may know this fact.

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

The SQL plan cache is often known by Friends of common query analyzer. It takes a very long time for a query statement to be executed during the first run, 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. The reason is:

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 it exists, 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.

If no execution plan exists in the SQL plan cache, the server first checks the syntax of the SQL statements requested by the user. If a syntax error occurs, the server stops querying, and return the corresponding error information to the application that calls it.

Note: The returned error information only contains the basic syntax error information, such as select? Written as selec. If the error message contains a column that is not in the list, the server will not check the column because it only verifies the syntax and whether the semantics is correct in the next step.

Verify that the syntax is correct. For example, whether the table name, column name, stored procedure, and other database objects actually exist. If any nonexistent database objects exist, an error is reported to the application and the query is terminated.

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, data is inserted, but because there is no lock, the query has read this record, and some inserts will roll back because of transaction failure, which will lead to dirty read.

The next step is to verify the database user permissions. The SQL statement syntax and syntax are correct. The query results may not be obtained at this time. If the database user does not have the corresponding access permission, the server reports an error due to insufficient permissions to the application, in a slightly larger project, a project usually contains several database connection strings. These database users have different permissions, including read-only permissions, write-only permissions, and read-write permissions, select different users for execution based on different operations. No matter how well your SQL statements are written, they are useless.

The last step of parsing is to determine the final execution plan. After 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 Union queries, the server will decide to use hash Based on the overhead cost? Join, merge? Join or loop join. Which index will be used more efficiently. 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: statement execution

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 reads the record from the physical file and returns it to the application. At the same time, it writes the data row to the data buffer area for the next use.

Note: There are several SQL cache types. If you are interested, you can search for them. Sometimes, because of the existence of the cache, it is difficult for us to immediately see the optimization results, because the second execution will be very fast because of the existence of the cache, so it is generally first to eliminate the cache, then compare the performance before and after optimization. Here are several common methods:

Dbcc dropcleanbuffers deletes all clear buffers from the buffer pool. Dbcc freeproccache deletes all elements from the process cache. Dbcc freesystemcache releases all unused cache entries from all caches.

SQL Server 2005? The 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. FROM? Clause returns the initial result set.
2. WHERE? Clause to exclude rows that do not meet the search criteria.
3. Group? Clause collects the selected rows? Group? Clause.
4. Select the aggregate function specified in the list to calculate the aggregate values of each group.
5. In addition, HAVING? Clause 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.