Improving query rate of database and optimization of SQL statements

Tag: SEL Access SQL statement through Date diff Union All database table variable

In a TENS database search, how to improve the efficiency of the query?

1 ) Database design aspects:  

A, to optimize the query, should try to avoid full table scan, first of all should consider the where and order by the columns involved in the index.

b, you should try to avoid the null value of the field in the Where clause to judge, otherwise it will cause the engine to abandon the use of indexes for full table scanning, such as: Selectid from t where num is null can set the default value of 0 on NUM, to ensure that the table NUM column does not have a null value, and then After this query: Select ID from Twhere num=0

c, not all indexes are valid for the query, SQL is based on the data in the table for query optimization, when the index column has a large number of data duplication, the query may not use the index, such as the table has a field sex,male, female almost half, So even if you build an index on sex, it doesn't work for query efficiency.

D, the index is not the more the better, although the index can improve the efficiency of the corresponding select, but also reduce the efficiency of insert and UPDATE, because the INSERT or update when the index may be rebuilt, so how to build the index needs careful consideration, depending on the situation. The number of indexes on a table should not be more than 6, if too many you should consider whether some of the indexes that are not commonly used are necessary.

E, as far as possible to avoid updating the index data columns, because the order of the index data columns is the physical storage order of table records, once the column value changes will cause the entire table records the order of adjustment, it will consume considerable resources. If the application needs to update the index data columns frequently, you need to consider whether the index should be built as an index.

F, try to use a numeric field, if the field containing only numeric information should not be designed as a character type, which will reduce the performance of query and connection, and increase storage overhead. This is because the engine compares each character in a string one at a time while processing queries and joins, and it is sufficient for a numeric type to be compared only once.

G, as far as possible to use Varchar/nvarchar instead of Char/nchar, because the first variable long field storage space is small, you can save storage space, and secondly for the query, in a relatively small field search efficiency is obviously higher.

H, try to use table variables instead of temporary tables. If the table variable contains a large amount of data, be aware that the index is very limited (only the primary key index).

I, avoid frequent creation and deletion of temporary tables to reduce the consumption of system table resources.

J, temporary tables are not unusable, and they can be used appropriately to make certain routines more efficient, for example, when you need to repeatedly reference a dataset in a large table or a common table. However, for one-time events, it is best to use an export table.

K, in the new temporary table, if the disposable data volume is large, then you can use Selectinto instead of createtable, avoid causing a lot of log to improve speed, if the amount of data is not large, in order to mitigate the resources of the system table, you should first createtable, and then insert.

L, if a temporary table is used, make sure that all temporary tables are explicitly deleted at the end of the stored procedure, truncatetable first, and then droptable, which avoids longer locking of the system tables.

2) in terms of SQL statements:

A, try to avoid using the! = or <> operator in the WHERE clause, or discard the engine for full table scanning using the index.

b, you should try to avoid using or in the WHERE clause to join the condition, otherwise it will cause the engine to abandon using the index for full table scan, such as: Selectid from T where num=10 or num=20 can query: Select ID from t wher E num=10 UNION All select IDs from T wherenum=20

C, N, and not in should also be used with caution, otherwise it will cause a full table scan, such as: Selectid from T where Num in (three-in) for consecutive values, can be used between do not use in: SELECT ID from Twhere num Between 1 and 3

D, the following query will also cause a full table scan: Selectid from T where name like '%abc% '

E, if you use a parameter in the WHERE clause, it also causes a full table scan. Because SQL is only available at run time

The local variable is parsed, but the optimizer cannot defer the selection of the access plan to the runtime; it must be selected at compile time. However, if an access plan is established at compile time, the value of the variable is still unknown and therefore cannot be selected as an input for the index. The following statement will perform a full table scan: select Idfrom t where [email protected] can instead force the query to use the index: Selectid from T with (index name) Where[email protected ]

F, try to avoid the expression of the field in the Where clause, which causes the engine to discard the use of the index for a full table scan. such as: Selectid from T where num/2=100 should be changed to: Selectid from T where num=100*2

G, you should try to avoid function operations on the field in the Where clause, which will cause the engine to abandon using the index for full table scanning. such as: Selectid from T where substring (name,1,3) = ' abc ' –name an ID that begins with ABC Selectid from t where DateDiff (Day,createdate, ' 2005-11-30′) =0– ' 2005-11-30 ' generated ID should be changed to: Select ID from twhere name "abc% ' select ID from t where createdate>= ' 2005-1 1-30′andcreatedate< ' 2005-12-1′

H, do not perform functions, arithmetic operations, or other expression operations on the left side of "=" in the WHERE clause, or the index may not be used correctly by the system.

I, do not write some meaningless queries, such as the need to generate an empty table structure: Selectcol1,col2 into #t from T where 1=0 such code will not return any result set, but will consume system resources, should be changed to this: CREATE TABLE #t (...)

J, many times replacing in with exists is a good choice: Select num from Awhere num in (select num from B) is replaced with the following statement: Selectnum from a where

Exists (Select1 from B where Num=a.num)

K, do not use select *from t anywhere, use a specific field list instead of "*", and do not return any fields that are not available.

L, avoid using cursors as much as possible because cursors are inefficient and should be considered for overwriting if the cursor is manipulating more than 10,000 rows of data.

M, try to avoid the return of large data to the client, if the amount of data is too large, should consider whether the corresponding demand is reasonable.

N, try to avoid large transaction operation, improve the system concurrency ability.

3) Java aspect: Key content

A, as far as possible to create less objects.

b, the position of reasonable pendulum system design. A large number of data operations, and a small number of data operations must be separate. A lot of data manipulation, certainly not the ORM framework is fixed. ，

C. Manipulating data using the JDBC link database

D, control the memory, let the data flow up, not all read to the memory and processing, but the edge of reading edge processing;

E, reasonable use of memory, some data to be cached

How to optimize database and how to improve the performance of database ?

Answer:

1 ) Hardware tuning performance is most likely to affect the performance of the disk and network throughput, the solution to expand virtual memory, and ensure that there is enough space to expand, the database server to shut down the unnecessary services, the database server and the primary domain server separate To maximize the throughput of the SQL database server, and to run SQL on a machine with more than one processor.

2 ) Tuning the database

If the query frequency of the table is relatively high, the index is established, when the index is established, all query search operations on the table are made, the index is indexed according to where selection criteria, as far as possible, the integer key is set as having only one cluster index, the data is physically sequentially on the data page, and the lookup range is shortened. To establish a non-clustered index for all the columns that are used frequently by the query, the query can be overwritten most, but the index is not too large, and the Updatedelete INSERT statement needs to be used to maintain a sharp increase in the sales of these indexes; Avoid having too many index keys in the index; avoid indexes with large data types ; Ensure that there are a few rows for each index key value.

3 ) using Stored procedures

In the implementation of the application process, the operation of the database can be implemented by the stored procedure as far as possible through the stored procedure, because the stored procedure is stored on the database server one-time is designed, coded, tested, and reused, the application that needs to perform the task can simply execute the stored procedure, And only returns the result set or the numeric value, thus not only can make the program modularization, simultaneously improves the response speed, reduces the network traffic, and accepts the input through the input parameter, causes the realization of the logical consistency in the application.

4 ) application architecture and Algorithms

Establishing a query condition index is only a precondition for speed improvement, and the increase in response speed also relies on the use of indexes. Because people in the use of SQL often fall into a misunderstanding, that is too focused on the results are correct, especially for the data volume is not particularly large database operations, whether to index and use the index of the good or bad response to the program is not very fast, As a result, programmers ignore the possible performance differences between different implementations when writing a program, which is particularly noticeable in large data volumes or in larger or complex database environments, such as online transaction processing OLTP or DSS for decision support systems. In practice, poor SQL often comes from inappropriate index design, non-sufficient connection conditions, and a WHERE clause that is not optimized. After the proper optimization of them, the speed of operation has been significantly improved!

The role of the database index:

The meaning of the index the role of the index in the database is similar to that of a directory in a book, and is used to improve the speed of finding information. • The index is a list of values contained in a table that indicates where the rows containing the values in the table are stored, and when the index is used to find the data, the storage location of the related columns , and then go directly to its storage location to find the information you need, so you don't need to scan the table to quickly find the data you need.

the role of database cursors ( cursor ):

A cursor is actually a mechanism that extracts one record at a time from a result set that includes multiple data records. Cursors act as pointers. Although the cursor can traverse all the rows in the result, he points to only one row at a time.

In summary, a SQL cursor is a temporary database object, a copy of a row of data that can be used to hold in a database table, or a pointer to a row of data stored in a database. Cursors provide a way to manipulate data in a table on a line-by-row basis.

A common use of cursors is to save query results for later use. The result set of a cursor is generated by a SELECT statement, and if the process requires a record set to be reused, it is much faster to create a cursor and reuse it several times than to query the database repeatedly.

Most program data design languages can use cursors to retrieve data from SQL databases, embed cursors in programs, and embed SQL statements in programs

Improving query rate of database and optimization of SQL statements