Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to improve the operation speed of insert". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

We usually use insert statements, especially sometimes we need a large amount of data to do testing, an insert will be very slow, so how can we make our inser faster.

Let's look at an example:

We need to insert test data in the following table, which contains two columns, one is itemid and the other is itemname. If you insert 103680000 records into this table, the normal insertion method may take more than 20 days to complete, but it can be done in 5 hours with the new method described here.

Let's first look at the general data insertion method, assuming that we insert 100000 pieces of data into the table:

The copy code is as follows:

CREATE TABLE # tempTable ([Item ID] [bigint], [Item Name] nvarchar (30))

DECLARE @ counter int

SET @ counter = 1

WHILE (@ counter)

So look at my new insert code:

The copy code is as follows:

CREATE TABLE # tempTable ([Item ID] [bigint], [Item Name] nvarchar (30))

INSERT INTO # tempTable VALUES (1, 'Hammer')

WHILE ((SELECT COUNT (*) FROM # tempTable) < 100000)

BEGIN

INSERT INTO # tempTable ([Item ID], [Item Name])

(SELECT [Item ID] + (SELECT COUNT (*) FROM # tempTable), 'Hammer' FROM # tempTable)

END

SELECT * FROM # tempTable

DROP TABLE # tempTable

Using the first method may take dozens of minutes to insert 100000 data, but using the second method only takes 4 seconds. With further improvement, it will be completed in 2 seconds:

The copy code is as follows:

CREATE TABLE # tempTable ([Item ID] [bigint], [Item Name] nvarchar (30))

INSERT INTO # tempTable VALUES (1, 'Hammer')

DECLARE @ counter int

SET @ counter = 1

WHILE (@ counter)

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.