How to optimize SQL Server's massive data

 

We all know that data warehousing and Data Optimization technologies are generally used when massive data problems are involved. So how to optimize the data? This article briefly introduces how to optimize massive data.

1. Use of indexes: indexes can increase the speed of data retrieval, but also reduce the speed of data writing.

In SQL Server, there is an index table to store our indexes. Generally, we create indexes on fields of the Int or char type.

2. Use of data locks: Row locks, page locks, table locks, etc. (Page lock by default)

Remember to prevent deadlocks.

3. database transactions and data concurrency.

Generally, the master and slave tables are placed in transactions for processing.

4. Try not to read data cyclically. When appropriate, you can add some temporary tables or add some temporary fields to the temporary tables to avoid loops.

5. Horizontal splitting

Split frequently-queried and infrequently used tables into several tables.
 

6. vertical segmentation

Divide different types into several tables

7. Try not to read data cyclically

You can use temporary tables and some temporary fields to avoid loops. A temporary table can prevent data from being modified midway through, and the data inserted into the temporary table is not written into the log, so the index creation is fast and the index entity table is not inserted. The data to be inserted is first inserted into the temporary table, and then imported into the formal table at one time.

8. reasonably create a table index and use the table Index

9. The associated tables should be as few as possible, and tables with a small amount of data should be placed before tables with a large amount of data.

10. Avoid using a function after the where Condition

For example, Sum.

11. Avoid long transactions (long wait time)

12. The data table cannot contain too many fields.

13. Multiple usage attempts (view)

14. The processing of large amounts of data should be implemented in the stored procedure as much as possible.

15. Or statement writing should be standardized as far as possible

For example: Select tid, tname from table where tid = 5 and (tname = '3' or tname = 'T') try to write it as select tid, tname from table where (TID = 5 and tname = '3') or (TID = 5 and tname = 'T ')

16. Try not to nest SQL statements (subqueries) in SQL statements)

17. When the data volume is large, table redundancy can be appropriately increased to improve the retrieval speed.

18. replace not in with join and delete statements. In is faster than not in.

 

This article is purely about the experience of a company and myself, and has no reference value.