How database SQL SELECT queries work

As b/s architecture developers, always inseparable from the database. A general developer will only apply the SQL four classic statements: Select,insert,delete,update. But I've never studied how they work, and I'd like to say how select works in the database.

b/S architecture, the most classic topic is nothing more than three-tier architecture, can be divided into data layer, business logic layer and presentation layer, and the role of the data layer is generally interacting with the database, such as Query records. We often write query SQL and then invoke the program to execute SQL. But what is the internal workflow? What to do first, then what step, and so on, I think most of my friends and I do not necessarily know.

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

1.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 query results.

Reason:

• After receiving the query request, the server does not immediately go to the database query, but in the database in the plan cache to find the corresponding execution plan, if present, directly call the compiled execution plan, save the Execution plan compile time.
• If the queried row 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 the data from the memory will be much faster than reading data from the hard disk, and improve the efficiency of the query. The data buffer store is mentioned later.

2. 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.

3. When the syntax is met, it begins to verify that its semantics are correct, such as the existence of database objects such as table names, column names, stored procedures, and so on, and if they do not exist, an error is given to the application and the query is ended.

4. 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 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.

5. Next is the database user rights validation, 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, some read-only permissions, some write-only permissions, some can read and write, according to different operations to choose different users to execute, a little attention, no matter how well you write SQL statements, perfect is useless.

6. The final step in the resolution is to determine the final execution plan. When syntax, semantics, and permissions are validated, the server will not give you immediate results, but will be 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 will ultimately decide whether to adopt Hashjoin,mergejoin, or Loopjoin, which index will be more efficient, depending on the cost of the overhead, but its automation optimization is limited, To write an efficient query SQL, or to optimize your 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 be actually executed.

In this case there are two kinds of situations:

• 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.
• 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 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 optimization, Here are a few common ways to do this:

Dbccdropcleanbuffers

Removes all purge buffers from the buffer pool.

Dbccfreeproccache

Removes all elements from the procedure cache.

Dbccfreesystemcache

Frees all unused cache entries from all caches. The SQLSERVER2005 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.

Conclusion: It is only good to debug our application by knowing the operation flow of the SQL that the service executes the application commits.

1. Make sure that SQL syntax is correct;
2. Ensure that SQL is semantically correct, that is, whether the object exists;
3. Whether the database user has appropriate access rights.

How database SQL SELECT queries work