SQL Stored Procedure Optimization experience

After the field colleagues reflect, they use a good XML export tool has been error, often reported the database connection time-out, view the database has been found to have more than 100G space.

However, the data stored in the export process only 1000 data per time, near the manager said there is time to filter should not cause problems.

Pull the stored procedure to execute in SQL SERVER, it takes 5 minutes to find

--create temporary tables to hold dataCreate Table#Temp(IDvarchar(Ten), namevarchar( -), zg_idvarchar(Ten), ks_idvarchar(Ten))--Insert data by time periodInsert  into#Temp(Id,name)SelectId,name fromtb1 (NOLOCK)whereBeginTimebetween '20150606'  and '20150706'--correlate other tables with existing data to complete other dataUpdate#TempSetzg_id=b.id from#TempA,tb_zg B (nolock)wherea.zg_id=b.idUpdate#TempSetks_id=b.id from#TempA,tb_ks B (nolock)wherea.ks_id=b.ID

Only thousands of of the data were queried.

After careful analysis, we have just started the stored procedure to insert three tables of data into a temporary table and then do the processing.

After the query found that three tables each table has more than 7 million rows of data, the associated fields of each table is the primary key, and the first table has been used for the time period to worry about, that is, only query the first table, and by the time period to worry about less than a second.

Because I inserted the data from the first table into a temporary table.

SQL Stored Procedure Optimization experience