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

Answer:

1) Hardware tuning performance is most likely to affect the performance of the disk and network throughput, the solution to expand the 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, the throughput of the SQL database server is adjusted to maximum ; 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 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.

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 are

When using SQL, you often get caught up in the misconception that you are focusing too much on the correct results, especially when database operations are not particularly large for data volumes, whether indexing and using indexes does not respond much to the program, so programmers ignore the possible performance differences between different implementations when writing programs , this performance difference is particularly noticeable when the volume of data is particularly large or in large 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!

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