Shulou(Shulou.com)06/03 Report--

This article introduces how to use SQL SERVER stored procedures to achieve historical data migration, the content is very detailed, interested friends can refer to, hope to be helpful to you.

1. What is historical data migration?

To put it bluntly: some historical data that has been created for a long time and is not commonly used is stored in another place (it can be another data or another table). The data of general historical data migration is the data that will not be changed, and you may only need to query statistics later.

2. The purpose of historical data migration

Reduce the number of databases in use, because the larger the amount of data, the longer it takes for the database to operate data (including query, sorting, etc.). When the data of a table reaches more than 10 million, and then a multi-conditional multi-table query, there is the possibility of slow response. (because of the logic written by different developers, it is impossible to guarantee that every SQL is an efficient SQL.)

Therefore, the timely migration of some historical data is beneficial to the improvement of the performance of the whole system.

3. When do I need to migrate historical data?

In the simplest case, if you feel the program tends to slow down, you can start thinking about historical data migration.

In principle, when there are not many small business servers and the hardware configuration is not very high, it is best to start migrating data with a single table of more than 5 million, and don't wait for tens of millions of data to start migrating.

According to the size of the amount of data generated, it is generally possible to keep the business data for about one year, and the historical data from a year ago have moved into the historical database. If the amount of data generated every day is too large, you generally need to consider automatic table storage. Of course, if you do not do this, you can only retain the data of the last 3-6 months in the real-time in-use business database without affecting the daily business.

4. the basic idea of data migration.

1) create an identical table structure for the first migration (as long as it is created before the first migration)

2) sort according to the creation time of the data, find out the earliest N pieces of data, and insert them into the historical data table at the same time.

Insert into... Select from

3) to test the accuracy of the inserted data, we must make sure that it is N correct. Then delete the business database in use.

4) when there is an error in the process of migrating the data, the program is terminated, but the database in use can not be deleted, and the developer is required to check the data.

5) according to the impact of migration on performance, there cannot be too much N, with a maximum of 5W to 10W at a time (based on the performance configuration of the server, it is recommended to migrate 1W to 5W pieces of data at a time with less impact). If you want to migrate a large amount of data, consider executing it in batches.

5. Sample stored procedure code for data migration

The code is as follows: (no need to explain too much, very simple code, easy to understand)

USE [Tyingsoft.GLPS] GO/* Object: StoredProcedure [dbo]. [TY_SP_ApiRequestToHis] Script Date: 2021-09-16 15:35:55 * / SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =-- Author:-- Create date:-- Last Edit date:-- Description:-- = ALTER PROCEDURE [dbo]. [TY_SP_ApiRequestToHis]-- Add the parameters for the stored procedure here @ PreCountN int = 2000 -- number of NASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements per execution. SET NOCOUNT ON; declare @ tableDataCount int;-- number of data before migration declare @ tableDataCountHis int;-- number of data before migration declare @ tableDataCount2 int;-number of data after migration declare @ tableDataCount2His int;-number of data after migration declare @ maxCreateTime datetime;-- take the maximum creation time of N pieces of data declare @ maxCreateTimeHis datetime -- maximum creation time declare @ beginTime datetime; in the history database-- start execution time declare @ endTime datetime;-execution completion time declare @ execTimeMS int;-execution time (in milliseconds)-- intermediate step debugger time using declare @ tmpBeginTime datetime;-- (temporary) start execution time declare @ tmpEndTime datetime -- (temporary) execution completion time declare @ tmpExecTimeMS int;-- (temporary) execution time (milliseconds) select @ beginTime = getdate ();-- before migration: query the number of data entries select @ tableDataCount = count (1) from [Tyingsoft.GLPS] .dbo.GLPS _ APIREQUEST; select @ tableDataCountHis = count (1) from [Tyingsoft.GLPS_His] .dbo.GLPS _ APIREQUEST Print'[API request record Table (GLPS_APIREQUEST) data Migration] start time:'+ convert (nvarchar (50), @ beginTime,20); print 'number of data entries planned to be migrated:' + cast (@ PreCountN as nvarchar (20)) -- create a temporary common expression (the earliest N pieces of data in the table) with topNRecord (FCREATETIME) as (select top (@ PreCountN) FCREATETIME from GLPS_APIREQUEST order by FCREATETIME)-- take the maximum creation time of N pieces of data select @ maxCreateTime = max (FCREATETIME) from topNRecord print 'corresponding to the migration data FCREATETIME is:' + convert (nvarchar (50), @ maxCreateTime,21) -- convert the date to a string format: yyyy-MM-dd HH:mm:ss.fff select @ tmpBeginTime = GETDATE ();-- start timing in the intermediate step-- first step: write N pieces of data to the historical database insert into [Tyingsoft.GLPS_His] .dbo.GLPS _ APIREQUEST select * from [Tyingsoft.GLPS] .dbo.GLPS _ APIREQUEST where FCREATETIME.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.