How SQL statements are performed in the database

The first step: the application sends the query SQL statement to the server-side execution

When we execute SQL statements at the data layer, the application connects to the appropriate database server and sends the SQL statements to the server for processing.

Step two: SQL statement for the server to parse the request

SQL plan cache, often with Query Analyzer friends probably know the fact that often a query statement in the first run when the need to execute a very long time, but if you immediately or in a certain time to run the same statement, will be in a short period of time to return the results of the query. The reasons are:

1. After the server receives the query request, it does not immediately go to the database query, but in the database in the plan cache to find the corresponding execution plan. If present, the compiled execution plan is called directly, saving the compilation time of the execution plan.
2. If the row being queried already exists in the data buffer store, it is not necessary to query the physical files, but to fetch the data from the cache, so that fetching data from memory is much faster than reading data from the hard disk, which improves the query efficiency. The data buffer store is mentioned later in this section.

If there is no corresponding execution plan in the SQL plan cache, the server first performs the syntax validation of the SQL statement requested by the user, and if there is a syntax error, the server ends the query and returns the appropriate error message to the application that called it.

Note: The error message returned will only contain basic syntax error information, such as select written Selec, etc., if the error message contains a column in the list, the server will not be checked out, because only the syntax validation, the semantics are correctly placed in the next step.

Once the syntax is met, it begins to verify that it is semantically correct. For example, database objects such as table names, column names, stored procedures, and so on, are actually present, and if they are found to be nonexistent, they will be given an error to the application and end the query.

Next is to obtain the object's resolution lock, when we query a table, the first server will lock the object, this is to ensure the unity of the data, if not locked, there is data insertion, but because there is no lock the reason, the query has read this record, and some of the insert will be rolled back because of the failure of the transaction, Will form a dirty reading phenomenon.

The next step is the validation of database user rights. SQL statement syntax, the semantics are correct, at this time not sure to get query results, if the database users do not have the appropriate access rights, the server will report insufficient permissions to the application, in a slightly larger project, often a project contains several database connection strings, these database users have different permissions, There are read-only permissions, some write-only permissions, some can be read and writable, according to different operations to choose different users to execute. A little less careful, no matter how well your SQL statements are written, it's useless to be perfect.

The final step in parsing is to determine the final execution plan. When syntax, semantics, and permissions are validated, the server does not immediately return results to you, but is optimized for your SQL, choosing different query algorithms to return to the application in the most efficient form. For example, when making a table union query, the server ultimately decides whether to use a hash join,merge join, or a loop join, and which index is more efficient, depending on the cost of the overhead. However, its automation optimization is limited, in order to write efficient query SQL or to optimize its own SQL query statements.

When the execution plan is determined, the execution plan is saved to the SQL plan cache, and the next time the same execution request is made, it is taken directly from the plan cache to avoid recompiling the execution plan.

Step three: Statement execution

After the server has completed parsing the SQL statement, the server will know exactly what the statement means, and then the SQL statement will actually execute.

There are two things in this case:

1. If a query statement contains rows of data that have been read to the data buffer store, the server reads the data directly from the data buffer store and returns it to the application, avoiding reading from the physical file and increasing the query speed.
2. If the data row is not in the data buffer store, the records are read back to the application from the physical file, and the data rows are written to the data buffer store for the next use.

Description: There are several kinds of SQL cache, here are interested friends can go to search. Sometimes because of the existence of the cache, it makes it difficult to see the results of the optimization immediately, because the second execution because of the existence of the cache, will be particularly fast, so it is generally first to eliminate the cache, and then compare the performance before and after the optimization, there are a few common methods:

1	DBCC DROPCLEANBUFFERS

2	从缓冲池中删除所有清除缓冲区。

3	DBCC FREEPROCCACHE

4	从过程缓存中删除所有元素。

5	DBCC FREESYSTEMCACHE

6	从所有缓存中释放所有未使用的缓存条目。

The SQL Server 2005 database engine cleans unused cache entries in the background beforehand so that memory is available for the current entry. However, you can use this command to manually remove unused entries from all caches.

This can only basically eliminate the impact of SQL cache, and currently does not seem to completely eliminate the cache of the scheme, if you have, please advise.

Execution order:

1. The FROM clause returns the initial result set.
2. The WHERE clause excludes rows that do not meet the search criteria.
3. The GROUP BY clause collects the selected rows into groups of individual unique values in the GROUP BY clause.
4. The aggregation function specified in the SELECT list calculates the summary values for each group.
5. In addition, the HAVING clause excludes rows that do not meet the search criteria.
6. calculate all the expressions;
7. Use order by to sort the result set.
8. Find the fields you want to search.

How SQL statements are performed in the database