CODEWITHSUNDEEP
sqlsql-serverperformancesql-insert
James Rodriguez
James Rodriguez

Reputation: 119

Sql server how to to inserting million rows

I have a source_table with 3 million rows in sql server. The primary key is the built in sql uniqueidentifier.

I want to copy all 3 million rows into 4 tables:

Table1 has some main information such as uniqueidentifier, book_title, book_author, book_price. Table2, Table3 and Table4 will all different columns but they will have the same uniqueidentifier primary key as Table1 and also that primary key will be a foreign key to Table1's uniqueidentifier primary key.

Copying from source_table takes a long time because each of Table1, Table2, Table3 and Table4 have 50 million rows. It is slow and I want to improve performance. My code is below. Does anyone have thoughts to improve performance even by a little bit? Every day the source_table is populated and I must reinsert into Table1, Table2, Table3 and Table4.

Thx for your suggestions.

insert into Table1 values (UID, book_title, book_author, book_price)
select values (@UID, @title, @author, @price)
from source_table

insert into Table2 values (UID, col2, col3, col4)
select values (@UID, @col2value, @col3value, @col4value)
from source_table

insert into Table3 values (UID, col2, col3, col4)
select values (@UID, @col2value, @col3value, @col4value)
from source_table

Upvotes: 0

Views: 4137

Answers (1)

Steve Ford
Steve Ford

Reputation: 7763

Try using INSERT INTO…SELECT to Bulk Import Data with Minimal Logging (see MSDN article)

Minimal logging for this statement has the following requirements:

  • The recovery model of the database is set to simple or bulk-logged.
  • The target table is empty or is a nonempty heap.
  • The target table is not used in replication.

  • The TABLOCK hint is specified for the target table.

    -- Temporarily set the recovery model to BULK_LOGGED.
ALTER DATABASE MyDB
SET RECOVERY BULK_LOGGED;
GO
-- You May have to drop the clustered index here

INSERT INTO INTO Table1 WITH (TABLOCK)
     (UID, book_title, book_author, book_price)
SELECT UID, title, author, price)
FROM source_table

-- RECREATE CLUSTERED INDEX HERE

-- Reset the recovery model.
ALTER DATABASE MyDB
SET RECOVERY FULL;
GO

    *** NOW DO A FULL BACKUP

Upvotes: 2

Related Questions