CODEWITHSUNDEEP
sqlsql-server
cris gomez
cris gomez

Reputation: 131

Inserting to TYPE Table is so slow

I have a USER DEFINE TABLE TYPE that stores more than 100K rows, now when I try to insert 100k records the insertion of data is very slow. It takes me 1 minute before the insertion is finish. Any idea how to make insertion fast?

   --USER DEFINE TABLE TYPE
               CREATE TYPE [dbo].[BigIntegerList] AS TABLE(
                    [ID] [bigint] NULL
                )
                GO

    --sample query    
            DECLARE
            @Data   dbo.BigIntegerList 

            insert into @Data   values(1824953)
            insert into @Data   values(1824954)
            insert into @Data   values(1824955)
            insert into @Data   values(1824956)
            insert into @Data   values(1824996)
            insert into @Data   values(1824997)
            insert into @Data   values(1824998)
            insert into @Data   values(1824999)
            insert into @Data   values(1825000)
            insert into @Data   values(1825001)
            insert into @Data   values(1825002)
            insert into @Data   values(1825003)
            insert into @Data   values(1825004)
            insert into @Data   values(1825005)
            insert into @Data   values(1825006)
            insert into @Data   values(1825007)


        select * from @Data

Upvotes: 2

Views: 1004

Answers (1)

paparazzo
paparazzo

Reputation: 45106

Why not tag this .NET?

For insert speed use TVP
maximizing-performance-with-table-valued-parameters

Even if not TVP at least pass multiple values
values (1824953), (1824954)
About 800 at a time

And does it really need to be bigint?

Upvotes: 1

Related Questions