CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Vinoth
Vinoth

Reputation: 2439

Fastest way to insert 100000 records into SQL Server

I am using the following script to insert 100,000 records into a table. Basically int from 500,001 to 600,000 are inserted. I am casting the integer into a string and inserting coz thats how i want it in the table (an integer in the form of string). I am using a merge to check if the record already exists or not.

DECLARE @first AS INT
SET @first = 500001
DECLARE @step AS INT
SET @step = 1
DECLARE @last AS INT
SET @last = 600000

BEGIN TRANSACTION
WHILE(@first <= @last)
BEGIN
MERGE dbo.Identifiers As target
USING (SELECT CAST(@first as varchar(10)) AS Identifier) AS source 
    ON (source.Identifier = target.Identifier)
WHEN NOT MATCHED THEN
INSERT (Identifier) 
VALUES (source.Identifier);
SET @first += @step
END
COMMIT TRANSACTION

Its taking more than 2 minutes to load. I am doing something terribly wrong but unable to trace out where. Note: The table has unique non-clustered index on Identifier Column.

Upvotes: 3

Views: 14554

Answers (4)

stakx - no longer contributing
stakx - no longer contributing

Reputation: 84725

I am wondering how much your procedural looping and the MERGE (instead of a simple INSERT) contributes to bad performance. I would opt for a strictly set-based solution like this:

INSERT INTO dbo.Identifiers (Identifier)
SELECT n FROM dbo.GetNums(500001, 600000)
WHERE n NOT IN (SELECT Identifier FROM dbo.Identifiers);

Now, this relies on a user-defined table-valued function dbo.GetNums that returns a table containing all numbers between 500,001 and 600,000 in a column called n. How do you write that function? You need to generate a range of numbers on the fly inside it.

The following implementation is taken from the book "Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions" by Itzik Ben-Gak.

CREATE FUNCTION dbo.GetNums(@low AS BIGINT, @high AS BIGINT) RETURNS TABLE
AS
    RETURN
    WITH L0   AS (SELECT c FROM (VALUES(1),(1)) AS D(c)),
         L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B), 
         L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
         L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
         L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
         L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
         Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rownum FROM L5)
    SELECT @low + rownum - 1 AS n
    FROM Nums
    ORDER BY rownum
    OFFSET 0 ROWS FETCH FIRST @high - @low + 1 ROWS ONLY;

(Since this comes from a book on SQL Server 2012, it might not work on SQL Server 2008 out-of-the-box, but it should be possible to adapt.)

Upvotes: 5

Jithin Shaji
Jithin Shaji

Reputation: 6073

Vinoth, Something given below could also help you.

Declare @tab table (id int identity(1,1),num int)
Insert  into @tab (num) Values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)

Declare @start as int
set @start = 500000 

Insert into dbo.Identifiers (Identifier) 
Select  @start + ((E.id-1)*10000) +((D.id-1)*1000) +((C.id-1)*100) + ((B.id-1) * 10) + A.id 
from    @tab A,@tab B,@tab C,@tab D,@tab E
Order by @start + ((E.id-1)*10000) +((D.id-1)*1000) +((C.id-1)*100) + ((B.id-1) * 10) + A.id

In my DB, dbo.Identifiers is a table without any index. It took only 230 ms for the insertion.

Upvotes: 2

Felix Pamittan
Felix Pamittan

Reputation: 31879

Try this one. It uses a tally table. Reference: http://www.sqlservercentral.com/articles/T-SQL/62867/

create table #temp_table(
    N int
)

declare @first as int
set @first = 500001
declare @step as int
set @step = 1
declare @last as int
set @last = 600000

with 
    e1 as(select 1 as N union all select 1),  --2 rows
    e2 as(select 1 as N from e1 as a, e1 as b), --4 rows
    e3 as(select 1 as N from e2 as a, e2 as b), --16 rows
    e4 as(select 1 as N from e3 as a, e3 as b), --256 rows
    e5 as(select 1 as N from e4 as a, e4 as b), --65,356 rows
    e6 as(select 1 as N from e5 as a, e1 as b), -- 131,072 rows
    tally as (select 500000 + (row_number() over(order by N) * @step) as N from e6) -- change 500000 with desired start
insert into #temp_table
select  cast(N as varchar(10))
from tally t
where
    N >= @first
    and N <=@last
    and not exists(
        select 1 from #temp_table where N = t.N
    )

drop table #temp_table

Upvotes: 5

Yogesh86
Yogesh86

Reputation: 49

Create index on the Identifier column and then try the above insert

Upvotes: 0

Related Questions