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

As reflected by their colleagues on the spot, their well-used XML export tool has been reporting errors recently, often reporting database connection timeouts, and checking the database found that there is already more than 100G of space.

However, the stored procedures for exporting data only guide more than 1000 pieces of data at a time, so there should be no problem if there is time to filter.

Pull the stored procedure into SQL SERVER and find that it takes 5 minutes.

-- create temporary tables to store data create table # temp (id varchar (10), name varchar (20), zg_id varchar (10), ks_id varchar (10))-insert data insert into # temp (id,name) select id,name from tb1 (nolock) where beginTime between '20150606' and '20150706 tables according to existing data Completion of other data update # tempset zg_id=b.idfrom # temp a Magneto tbdistribuzg (nolock) where a.zg_id=b.idupdate # tempset ks_id=b.idfrom # temp a Magi tblegks b (nolock) where a.ks_id=b.id

Only a few thousand pieces of data have been queried.

Careful analysis shows that at the beginning, the stored procedure inserts the data from three tables into a temporary table and then processes it.

After querying, it is found that each of the three tables has more than 7 million rows of data, and the associated field of each table is the primary key, and the first table has already used the time period to worry, that is to say, only the first table is queried, and it is less than a second to worry about it by time period.

So I inserted the data from the first table into the temporary table.

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.