One thousand fixed number of records and above

Subject:

1.After several years of use, the customer's data becomes very huge, and the paging speed is almost unbearable.

2.When paging to the last page, the speed is often slow and there may be crashes, especially when the number of dead hosts is many times larger.

 

Steps for solving the problem

1.First, optimize the database, first fromDatabase FieldTypeStart with regionalization.

FirstDatetimeChange SmalldatetimeThe database can be reduced to hundreds.M, The starting force of the compaction magnet can be reduced. Due to data statisticsOne thousand unable to use administrative tools to modify the endpoint(If you want to use it, the current image will appear), Can only be usedSQLBegin statement.

Alter TableTestdata_rptAlter ColumnCratedate_rptSmalldatetime Null

 

2.ThenOptimize Data Indexing

Remove unnecessary indexes, and then re-create indexes. because the number of records is too large, excessive indexes will increase the number of records.

Set the master Region(Primary Key)Set to inverted, non-clustered(No clustered)In this way, you can sort the latest data records at the beginning.

Set the key search condition as an index,GroupbyAnd then set it as a clustered index. The better thing is that it will be much faster during the query.

After this step is completed, the shard speed is much faster.

 

3.Focus on regionalization,Memory duration of data points. Shard usedSelect Top NCalculate the exact number of records in the last screenshot.

 

 

Exam question