CODEWITHSUNDEEP
sqlsql-serverperformanceinsertwhile-loop
user1733773
user1733773

Reputation: 360

insert in cycle

I have a script on SQL SERVER:

while (@i < 100000)
begin
  insert into TABLE_NAME (id, pid, value)
    values (@i
          , (select top 1 id from TABLE_NAME order by NEWID())
          , 'b')
  set @i += 1;
end;

It executes really slow. Is it possible to do the same but faster? Thanks.

Upvotes: 1

Views: 976

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Your query is really slow because of the subquery, that has to do a random sort on table_name in each iteration.

You can just do this using rand():

while (@i < 100000)
begin
  insert into TABLE_NAME (id, pid, value)
    select @i, 
           cast(rand() * @i as int) as pid,
           'b'
  set @i += 1;
end;

This will always generate a pid less than id.

If this is slow, you may be slowing things down if your database is in FULL recovery mode, logging all operations.

And, then there is this approach. Generate the 100,000 numbers, assign a random number to each one and then take that random number modulo original number - 1:

with digits as ( select 0 as digit union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 ), nums as ( select (d1.digit*10000+d2.digit*1000+d3.digit*100+d4.digit*10+d5.digit) as val from digits d1 cross join digits d2 cross join digits d3 cross join digits d4 cross join digits d5 ) select val as id, (case when val > 1 then seqnum % (val - 1) end) as pid, 'b' from (select *, ROW_NUMBER() over (order by newid()) seqnum from nums ) s

This runs quite fast on my machine. Without the insert, it finished in a few seconds.

Upvotes: 1

Related Questions