How to compile efficient SQL query statements and SQL statements

How to compile efficient SQL query statements and SQL statements
Overview

How can I write SQL query statements with high performance? Two Methods: creating reasonable indexes and writing efficient SQL statements

Basic Principles of Indexing

Indexes are classified into clustered indexes and non-clustered indexes. A table can only create one clustered index and N non-clustered indexes. The reason for this statement is mainly determined by the indexing principle.

Whether you create a table in a database without creating an index, or whether you create that type of index, the storage on the hard disk is the same. Then, you can create an index or no index, or, what is the difference between creating a clustered index and a non-clustered index?

The difference is that the table data exists in the memory. When a table without an index is loaded into the memory, only data blocks exist. When a table with a clustered index is loaded into the memory, a tree is formed, the leaf node contains specific data. For non-clustered indexes (when no clustered index is available), the actual data is an independent block when the memory is loaded, the indexes form an index tree. The leaf node of the index data corresponds to the rowid of the real data block. For tables with both non-clustered indexes and clustered indexes, when it is loaded into the memory, two trees are formed. The specific data is combined with the clustered index tree. The non-clustered index has a single tree. Note that, the index of the clustered index stored in the leaf node, and then the specific data is found in the clustered index tree through the index of the clustered index. This is why a table can have only one clustered index and multiple non-clustered indexes.

How can we know the performance of our SQL query statement execution?

When we write an SQL query statement, how can we know whether the SQL query statement is good or bad?

One method, we all know, is directly executed in the SQL Server queryer, and then its execution status bar will show our corresponding results, such as: execution time (seconds ); another method is to execute set statistics io on, enable resource consumption information, execute set statistics time on, and enable the execution time. After these two functions are enabled, when we run the SQL statement again, the corresponding results are displayed in the message view next to the result view. Another method is to use the SQL Server Profiler tool (SQL Server Profiler ), it helps us monitor the execution of the SQL statements executed. This method is more suitable for cases where we cannot extract SQL statements in the program.

How to create a proper index?

Through the above method, we can know the quality of our SQL statements. For bad SQL statements, we need to try to make corresponding changes. However, for bad SQL statements, during optimization, we always need to know where the problem is. I can't say I have changed it. So, at this time, we need another tool to help us analyze it, that is, in the execution plan view (query-display the execution query plan), the execution plan shows whether the index is used when the SQL statement is executed. The following describes how to query table scan, index scan, and index seek in table 3.

Table scan is a full table scan that performs full scanning directly in real data blocks. index scan is a full scan on the index tree. In this case, if it is a clustered index, the corresponding data (specific data on the leaves) will be obtained directly. If the data is not clustered index, You need to obtain the corresponding clustered index key on the leaves. Then, then, the corresponding real data is found in the clustered index tree based on the clustered index key. index scan is the search on the index tree. The implementation of this method is related to its internal principle because it is a tree, therefore, based on some algorithms (such as binary forks), you can quickly locate specific data, which can be divided into clustering and non-clustering. We will not go into details here.

Through the execution plan, we can know that our SQL statement does not use an index in that block. Then, we can modify the SQL statement or create the corresponding index. here we need to know, it is not necessary to use an index. If your data is very small and you create a large number of indexes, this may happen: not as good as full table index blocks. Therefore, when optimizing our SQL statements, we try to move closer to the existing indexes as much as possible, so we can see whether we need to create the corresponding indexes based on the amount of data.

How can I write efficient SQL query statements?

As we can see from the above piece of content, our SQL statement should be as close as possible to the existing index, so what if we can use the existing index? You may say that it is not enough to create more indexes. It is impossible to create an index for each column. In this way, the index can be used. As you said, if we do not consider anything else, we think that using indexes during SQL statement execution is fast, but what I want to say is that you should create indexes on each column in time, I don't know why index seek is used when the SQL statement you write is executed? Because the system cannot find a reasonable execution plan for index seek when analyzing your SQL statements, how can we write our SQL statements so that the system can be analyzed, what if index seek is used in the execution plan? How can we get the optimal execution plan by writing our SQL statements?

1. Column Query Optimization

If you use the column in the table, select * is not required. Unless you use each column, because select * is the same as all select columns.

2. Optimization of where query Conditions

Do not perform operations on condition columns. For example, use a function or column to convert the data type, and use the parameter operators (filed + 'aaa') on the column '), use non-parameterized conditions (like '% A', not ......, Or ...... And so on.

3. Others

Do not use cursors. You can use the set condition query code cursor. Use exists instead of count (*) to verify the existence of data.

Summary

If there are too many indexes, there is no index, and there is no index, it is based on the specific data volume. Do you want to create an index for this column, it is based on the proportion of this column as a condition query. Good SQL statements should not only move closer to the index, but also shield unnecessary data and execution times.

How to Write refined and efficient SQL statements

Refined and efficient SQL statements are very useful for a large website or a large database. SQL redundancy increases the occupation and time of the system and resources.
1. What kind of SQL is efficient SQL? 2. Why does SQL leave indexing? How to index SQL statements, that is, to change the SQL Execution Plan 3. What types of indexes are there? 4. When to use indexes, when to scan the table statistics of the oracle optimizer in the full table, and evaluate the table's optimal connection sequence, table connection method, and execution path; finally, the execution plan is generated. oracle will execute sql1. what kind of SQL statements are efficient SQL statements? A: The most essential answer is the shortest execution time, and how can we get the shortest result? It is to use the least amount of resources to get things done without any effort. Even if SQL has the least I/O, how can we get the least? Try to use indexes instead of full table scans. When multiple tables are associated, developers should select the correct table connection method and execution path. why does SQL leave the index. type does not match B. The condition column contains functions but does not create the corresponding function index C. The leading column in the composite index is not used as the query condition D. In CBO mode, the number of rows selected is large, and the optimizer selects full table scan E. In CBO mode, the table has not been analyzed for A long time. The optimizer selects full table scan 3. Index type and creation method. B * index create index indexname on tablename (columnname); B. reverse index create index indexname on tablename (columnname) reverse; C. create index indexname on tablename (columnname desc); D. create bitmap index indexname on tablename (columnname); E. Function index create index indexname on tablename (functionname (columnname); 4. When to use an index and when to use a full table scan? A: When Using indexes, how many rows are there in some basic information tables first? How many rows are returned? Which columns of the table have indexes? What are the indexes? What indexes should I select when there are multiple condition columns? A. when the number of queried records is less than 40% in an ordered table, it is best to use an index; otherwise, use the full table to scan B. when the number of queried records is less than 7% in an unordered table, it is best to use an index; otherwise, use the full table to scan C. many table locks (this table has frequent dml operations)

How can I write efficient SQL 2005 database query statements-Java

Select * from namedb where name = 'loo'
The more efficient method is
The asterisk for select is replaced with the field name from namedb where name = 'loo'
For example
You just want to display the name field in this table
Write it like this
Select name from namedb where name = 'loo'
If you still need to use the id field, write it like this
Select name, id from namedb where name = 'loo'
This should be the most efficient, at least what I know is the most efficient.