5

Description:

I try to insert 1 million rows into empty table on MSSQL 2012 Express. Here my script:

-- set statistics time off
drop table t1
create table t1 (id int, a text, b text) 
go

-- #1 - 1,000,000 - 30s -> 45s
with ID(number) as
(
    select 1 as number
    union all
    select number + 1
    from ID
    where number < 1000000 + 1
)
insert into t1
    select number, 'a_' + cast (number as varchar), 'b_' + cast (number/2 as varchar)
    from ID  
    option(maxrecursion 0)


-- #2 - 1 million rows => ~140,000 rows = 120s (have to cancel query)
declare @count int
set @count = 0
while @count < 1000000
begin
    set @count = @count + 1
    insert into t1 
        values(@count, 'a_' + cast (@count as varchar), 'b_' + cast (@count/2 as varchar))
end

-- #3 - ~1,300,000 rows - 18s -> 20s  

with temp as 
(
    SELECT  ROW_NUMBER() OVER(ORDER BY a.object_id) as tcount 
    from sys.all_columns a,  sys.all_columns b
    where a.object_id = b.object_id  
) 
insert into t1
    select tcount, 'a_' + cast (tcount as varchar), 'b_' + cast (tcount/2 as varchar) 
    from temp 
go

declare @count int
set @count = 0
while @count < 3
begin
    with temp as (select max(id) + 1 as max_id from t1)
    insert into t1
        select max_id, 'a_' + cast (max_id as varchar), 'b_' + cast (max_id/2 as varchar) 
        from t1, temp 
    set @count = @count + 1
end

-- #4 -- 1,000,000 = 3s -> 4s (have to drop t1 first)
with a(k) as
(
select 1 as k
union all
select k + 1 from a where k < 99 + 1
) , 
t2 as (
select row_number() over(order by x.k) as k
from a x , a y , a z 
) 
select k as id , 'a_' + cast (k as varchar) as a, 'b_' + cast (k/2 as varchar) as b into t1
from t2

Question:

After researching , I found 4 solutions. Are there any better solution (not using copy data from files) ?

  • 2
    Don't use "text" field types. They're horrible for performance and indexes on those fields cannot be rebuilt "online". Use a varchar(x) where x is the reasonable maimun size you expect to support. And reasonable is not normally > several hundred. – Max Vernon Feb 25 '16 at 5:14
  • Thank Max - In PostgreSQL, I often use "text" type , so I thought MS SQL has the same. – Luan Huynh Feb 25 '16 at 6:59
  • 1
    @Luan It supports it too: character varying(n), varchar(n) = variable-length with limit VS text = variable unlimited length. In the context of this question, it doesn't seem that you need unlimited length strings. – Julien Vavasseur Feb 25 '16 at 8:57
13

Itzik Ben-Gan uses following approach This is probably the fastest way he found and he's quite smart :-)

WITH
  L0   AS (SELECT c FROM (SELECT 1 UNION ALL SELECT 1) AS D(c)), -- 2^1
  L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),       -- 2^2
  L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),       -- 2^4
  L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),       -- 2^8
  L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),       -- 2^16
  L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),       -- 2^32
  Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS k FROM L5)

select k as id , 'a_' + cast (k as varchar) as a, 'b_' + cast (k/2 as varchar) as b into t1
from nums
where k <= 1000000
6

A variation on dnoeth's answer:

WITH Ten(N) AS 
(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)   
SELECT
    id = IDENTITY(int, 1, 1)
INTO dbo.T1
FROM Ten T10
CROSS JOIN Ten T100
CROSS JOIN Ten T1000
CROSS JOIN Ten T10000
CROSS JOIN Ten T100000
CROSS JOIN Ten T1000000;

ALTER TABLE dbo.T1
ADD a AS CONVERT(varchar(11), id);

ALTER TABLE dbo.T1
ADD b AS CONVERT(varchar(11), id / 2);

This avoids storing the values of a and b; their values will be calculated at runtime as necessary. This may be cheating slightly, but it does have advantages:

  • No storage space used for columns a and b
  • The id column is directly typed as integer (4 bytes uncompressed); whereas ROW_NUMBER returns bigint (8 bytes uncompressed).
  • The id column is assigned the identity property, so it is not updateable.

Alternatively, storing all columns in the table:

WITH Ten(N) AS 
(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)   
SELECT
    id = CONVERT(integer, ROW_NUMBER() OVER (ORDER BY T10.N)),
    a = CONVERT(varchar(11), ROW_NUMBER() OVER (ORDER BY T10.N)),
    b = CONVERT(varchar(11), ROW_NUMBER() OVER (ORDER BY T10.N) / 2)
INTO dbo.T1
FROM Ten T10
CROSS JOIN Ten T100
CROSS JOIN Ten T1000
CROSS JOIN Ten T10000
CROSS JOIN Ten T100000
CROSS JOIN Ten T1000000;

Note the conversion to integer on the id column, and the use of a specific length on the varchar types. See:

Bad habits to kick : declaring VARCHAR without (length) by Aaron Bertrand

2

Method 1: @dnoeth above, insert time: 1077ms - 1180ms (10 times testing)

Method 2: I try to insert using this method, insert time 989ms -> 1132ms
It's simple .

select t1.k as id , 'a_' + cast (t1.k as varchar) as a, 'b_' + cast (t1.k/2 as varchar) as b  into t1
from ( 
SELECT  ROW_NUMBER() OVER(ORDER BY a.object_id) as k 
from sys.all_columns, sys.all_columns a ) t1
where t1.k < 1000001

Method 3 : from Paul White's idea, 450ms

with x1 as (select top 1000 object_id from sys.all_columns )
SELECT  id = IDENTITY(int, 1, 1) into t1
from x1 a, x1 b
ALTER TABLE dbo.T1 ADD a AS 'a_' + CONVERT(varchar(20),  id);
ALTER TABLE dbo.T1 ADD b AS  'b_' + CONVERT(varchar(20),  id / 2);
1

Another variation of dnoeth's answer:

WITH
L0   AS (SELECT c FROM (SELECT 1 UNION ALL SELECT 1 UNION ALL 
                        SELECT 1 UNION ALL SELECT 1 UNION ALL 
                        SELECT 1 UNION ALL SELECT 1) AS D(c)), -- 6^1
L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),       -- 6^2
L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),       -- 6^4
L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),       -- 6^8
Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS k FROM L3)

select k as id , 'a_' + cast (k as varchar) as a, 'b_' + cast (k/2 as varchar) as b into t1
from nums
where k <= 1000000

Much more efficient to use 6 as base since 6^8 (1 679 616) is much closer to 1000000 than 2^32 (4 294 967 296)

  • Even more efficient, but less convenient, is to use base 32. 32^4 = 1 048 576 – matgul Jul 8 at 13:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.