0

Which of the following queries is more efficient?

DECLARE @i int 

SET @i = 1 

WHILE (@i < 40) 
BEGIN 
    INSERT INTO table (number) 
    VALUES (@i) 

    SET @i = @i + 1 
END

or

INSERT INTO table (number) 
    SELECT n 
    FROM anothertable 
    WHERE n <= @i

anothertable is a table with a column n with numbers from 1..100.

Please help

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Chandan
  • 155
  • 1
  • 7
  • 3
    Set-based approaches are almost always faster than anything loop-based simply because the T-SQL parser/compiler is so slow, plus the overhead of per-row transactions if you don't do it all in one. But as always with questions of "which is faster" -- just *test*, it's simple enough in this case. – Jeroen Mostert Mar 06 '22 at 19:57
  • the former is like programming and the latter is query. using the latter should be much faster.However, that should be determined from execution plan. – RF1991 Mar 06 '22 at 20:00
  • 2
    Learning to design and write set-based solutions will serve you much more that optimizing a 40 row insert (which seems an odd thing to do as presented). – SMor Mar 06 '22 at 20:09
  • Most RDBMS are optimized to perform better with set based actions *(like an insert from select)* compared to procedural logic. Sql Server won't be the exception. – LukStorms Mar 06 '22 at 20:13
  • Use execution plan. – RF1991 Mar 06 '22 at 20:24
  • Second one is more efficient, but you could improve it by doing `SELECT TOP (@i)`. See also [Number series generator challenge solutions](https://sqlperformance.com/2021/01/t-sql-queries/number-series-solutions-1) by Itzik Ben-Gan for much better solutions, some of which don't involve any table at all – Charlieface Mar 06 '22 at 21:59

0 Answers0