Easily improve TENS database query efficiency

Optimize database design

1, the Data field type uses Varchar/nvarchar to replace the Char/nchar, the variable long field storage space is small, saves the storage space. Small spatial field searches are more efficient when querying.

2. Avoid full table scans when querying, and you can index the fields in where and order by.

3, the WHERE query clause does not judge the null value, it will cause the retrieval engine to abandon the use of the index and full table scan, such as: Select Id,name from the user where is the null can set the default value of age 0, guaranteed no null value, The modified SQL query statement is: Select Id,name from user where age = 0.

4, use the index sparingly, the index is not more the better. The average number of indexes in a table should not exceed 6, if too many to discuss whether the business is reasonable or whether the index is built on the infrequently used fields. Indexes can improve the efficiency of select queries, but they also reduce the efficiency of insert and update, because indexes can also be rebuilt when you perform insert and update.

5, try not to update the index data, because the order of the index data is the physical order of the table records, once the change will cause the whole memento order changes, will consume a lot of resources. If the business needs to update the index data column frequently, consider whether the index is created reasonably, such as a user ID, a social Security number, or a column that the mobile phone number does not change frequently to consider creating an index.

6. The character field can be modified to a numeric type field if it meets the business requirements because the character field reduces query and connection performance and increases storage overhead. The appropriate query and connection to perform the search compares each character of the string one by one, if it is a data type.

SQL query optimization

1. In the WHERE query statement avoid using the **!= or <> * * operator, the search engine performs a full table scan without performing the created index.

2, the WHERE query statement to avoid using or to join the condition query data, will also cause the search engine to perform a full table scan without performing the created index, for example: Select Id,name from the user where Age = 25 can be modified to Selec T id,name from user where Age = the union ALL selects Id,name from user where Age = 25.

3, * * and not in also avoid the use, will also cause a full table scan, for example: Select Id,name from the user where age in (18,19,20) * * If it is continuous you can consider using between and, for example: SELECT ID , name from the user where age between and 20.

4. The like statement causes a full table scan, for example: Select Id,name from user where name as '% '.

5, Wehre query statements to avoid the use of parameters, but also the full table scan, SQL at run time to parse the local variables, the optimizer cannot defer the selection of access plans to run; it must be selected at compile time. If an access plan is established at compile time, the value of the variable is still unknown and cannot be selected as an entry for the index. The following statement will perform a full table scan: Select Id,name from user where Age = @age can of course also be forced to use the index instead: Select Id,name from user with (index name) where age [ Email protected] Age

6, where query statement to avoid the use of expressions, it will also cause the query to abandon the use of indexes resulting in a full table scan. Example select Id,name from user where AGE/2 = 10 * * Can be changed to * * Select Id,name from user where Age = 102*.

7, where query statement to avoid the use of function operation, it will also cause the query to abandon the use of indexes caused by the full table scan. For example: Select Id,name from user where substring (name,1,3) = ' abc ' can be changed to select Id,name from the user where name like ' abc% '.

8, do not use the SELECT * from the user query, to use the specific field name. Do not return any fields that are not available.

9, do not use cursors, we all know that the efficiency of the cursor is very poor.

10, to avoid the emergence of large practice business, will reduce the concurrency of the system capabilities.

Java Background optimization

1. Connect to the database using JDBC.

2. Reasonable use of data cache.

3, control the memory, do not put all the data into the inside out to do processing, you can read while processing.

4, less creative objects.

Database performance Optimization

1, the use of stored procedures in the specific business implementation process, you can use the stored procedure operation of the database can be used as far as possible, because the stored procedure is stored on the database server one-time is designed, coded, tested, called again, need to execute the stored procedure can be very simple to use. It can improve the response speed, reduce network traffic and so on.

2, hardware adjustment affects database performance may also be disk and network throughput, you can expand virtual memory, the database server and the primary server are deployed separately. Data server throughput is tuned to maximum.

3, adjust the database if in the actual business implementation of the table query frequency is too high, you can create indexes on the table, according to where query criteria to establish an index, as far as possible for the integral type key to have only one cluster index, the data is physically sequentially on the data page, shorten the search scope, To establish a non-clustered index on all columns that are used frequently by the query, the query can be overwritten as much as possible, but the index is not too large, and the update DELETE 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 columns indexed with large data types ; Ensure that there are a few rows for each index key value.

Easily improve TENS database query efficiency