Fast insert

Because of locking and serialization, plus logging inserting once is much more faster than inserting by loop. Here i have tested it using CTE

WITH 

 

 

 

– second CTE which returns 100 million rows by using

 

– a CROSS JOIN on the first CTE 

, dig AS (
SELECT
(millions.Number * 1000000)
+ (hThousands.Number * 100000)
+ (tThousands.Number * 10000)
+ (thousands.Number * 1000)
+ (hundreds.Number * 100)
+ (tens.Number * 10)
+ ones.Number AS Number
FROM digits AS ones
CROSS JOIN digits AS tens
CROSS JOIN digits AS  hundreds
CROSS JOIN digits AS  thousands
CROSS JOIN digits AS tThousands
CROSS JOIN digits AS hThousands
CROSS JOIN digits AS millions
)

 – Third CTE which generates a “number”. repeated after 50M

 

 , nums AS (
SELECT number
FROM dig
WHERE number <5000000
)

 

 

– the insert statement goes here

 

INSERT Test.dbo.PerformanceTester (col1,col2,col3)
SELECT number,number, number
FROM  nums

 and performance gain is more than 40 times. Cool.  but this query is bound to memory and if we try to increase 5M to 50M, will complain about revising long running transactions. My trick to this is

insert  

 

 

which basically doubles size of the table by 2 much faster.

into Test.dbo.PerformanceTester(col1,col2,col3)
select col1,col2,col3 from Test.dbo.PerformanceTester

– first CTE which returns 10 rows (0-9)
digits AS (
SELECT 0 as  Number
UNION SELECT 1
UNION SELECT 2
UNION SELECT 3
UNION SELECT 4
UNION SELECT 5
UNION SELECT 6
UNION SELECT 7
UNION SELECT 8
UNION SELECT 9
)