How to make SQL Server efficient-Program Design (itput)

3,Design Database OperationsProgramWhat do you think should be paid attention to ensure the effective use of the database?

Discussion Summary

LMinimize the scope and repeatability of read/write operations,Interim procedures such as temporary tables or table variables can be used

LCache. Cache common data at the program end

L)Static. For relatively stable data, it can be statically stored to avoid Database Access

L)Parameterization: parameterize access statements or change them to stored procedures.

LConnection Pool to control access and concurrency

LBatch processing. Write the data modification cache to the database after a period of time.

LReduce the transaction size to avoid interaction with users in the transaction

LApplications andSQLBalancing between servers and putting computing onSQLWhich Computing servers are placed at the application layer?

Personal supplement

LTransaction operations should be controlled in the database as much as possible to avoid controlling transactions in the program. Improper transaction control in the program may easily lead to the use of distributed transactions. This transaction method is highly efficient and has many factors affecting it. In addition, if the transaction is controlled in the program, the release of the transaction lock resources will slightly increase the resource lock time after receiving the program notification, and increased risk factors (for example, the program did not respond in a timely manner, someBugThe transaction is not committed or rolled back, resulting in pending transactions)

LPay special attention to the query parameter type. The data types in the program are not as strict as those in the database, so many programmers can easily ignore this problem. The data types in the database are strict, and data types will be converted if they do not match, if the resulting conversion is ultimately on a large number of data rows, it will have a great performance impact.

LMinimize interactions between programs and data servers to avoid unnecessary data transmission. For example, shielding the output information not used by the client and ensuring that no query results are not concerned by the client;DBThe retrieved data page is slightly larger than the actual page size (cache to avoid page-by-page extraction from the server; UseSqlbulkcopyBatch Data submission between the client and the database.

 

Discussion post

Previous topics:

1. What factors do you consider when designing SQL Server objects to avoid performance problems? 2. What factors do you think will affect SQL Server efficiency in writing T-SQL (including stored procedures, functions, and views? Subsequent topics:

4. What performance problems are confusing to you during your SQL server usage?