Fastest way to insert data into Sql Server Database

January 23, 2018 22:34

Từ khóa:

SQL Server Inserting huge number of rows to a table
Insert 2 million rows into SQL Server quickly
Importing Data in Parallel with Table Level Locking
Inserting a large amount of data in SQL table
Insert Large Text data into SQL Server
Different Options for Importing Data into SQL Server
- BCP
- Bulk Insert
- OPENROWSET
- OPENDATASOURCE
- OPENQUERY
- Linked Servers
Data Import Performance Comparison T-SQL vs SSIS for large import
Insert data from CSV to SQL Server
Insert XML to SQL Server
Take Care When Scripting Batches

Hướng giải quyết:

Read ebook "Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016"
Read ebook "T-SQL Fundamentals (3rd Edition)"
SSIS Import/Export Wizard
SSIS Tasks Components Scripts Services
Bulk Copy (BCP) Utility
Bulk Insert Example
SqlBulkCopy: Sao chép dữ liệu cực nhanh với SqlBulkCopy
Disable | Drop relationship between tables > Insert > Re-enable | Re-create relationship between tables
- Disable / drop indexes / constraints on target table.
- INSERT INTO dbo.OrderDetails... see more here
- Re-enable / re-create indexes / constraints on target table (and perhaps you can defer some of those, if they are not necessary for all operations, and it is more important to get the base data online quickly).
The Data Loading Performance Guide
Inserting a large amount of data in SQL table

First of all, optimize the SELECT part of INSERT...SELECT....:

http://www.sqlusa.com/articles/query-optimization/

Second, make sure the disk resources support high performance:

http://www.sqlusa.com/bestpractices/configureharddisk/

Generally speaking, dropping all indexes, inserting, rebuilding all indexes is a good strategy for large insert. The reason is that you need to rebuild the indexes anyhow because they deteriorate after many INSERTs.

Consider also using the SSIS Import/Export Wizard with query source and table destination:

http://www.sqlusa.com/bestpractices2008/administration/ssiswizardicon/

I just noticed you mention the table is actively used. Then batching the INSERTs is the best solution and rebuilding indexes at the first opportunity:

http://www.sqlusa.com/bestpractices2005/hugeupdate/

Notice that WAITFOR DELAY in the batching loop is very important! That is the pause when users can get in with their queries.

Cách insert, update dữ liệu vào SQL Server

Don't forget about transactions. Performance is good, but simple (IF EXISTS..) approach is very dangerous.

When multiple threads will try to perform Insert-or-update you can easily get primary key violation.

Solutions provided by @Beau Crawford & @Esteban show general idea but error-prone.

To avoid deadlocks and PK violations you can use something like this:

begin tran
if exists (select * from table with (updlock,serializable) where key = @key)
begin
   update table set ...
   where key = @key
end
else
begin
   insert into table (key, ...)
   values (@key, ...)
end
commit tran

HOẶC

begin tran
   update table with (serializable) set ...
   where key = @key

   if @@rowcount = 0
   begin
      insert into table (key, ...) values (@key,..)
   end
commit tran

Insert Large Text data into SQL Server:

According to the StreamReader.ReadLine documentation (http://msdn.microsoft.com/library/de...LineTopic.asp), an OutOfMemoryException is generated when there is insufficient memory to allocate a buffer for the returned string.

You can try to read the text row by row using the ReadLine method.

class Program
{
    static void Main(String[] args)
    {
        const int bufferSize = 1024;
 
        var sb = new StringBuilder();
        var buffer = new Char[bufferSize];
        var length = 0L;
        var totalRead = 0L;
        var count = bufferSize;
 
        using (var sr = new StreamReader(@"C:\Temp\file.txt"))
        {
            length = sr.BaseStream.Length;
            while (count > 0)
            {
                count = sr.Read(buffer, 0, bufferSize);
                sb.Append(buffer, 0, count);
                totalRead += count;
            }
        }
 
        Console.ReadKey();
    }
}

SQL Insert Speed Up

https://stackoverflow.com/questions/7090243/sql-insert-speed-up

To get the best possible performance you should:

Remove all triggers and constraints on the table
Remove all indexes, except for those needed by the insert
Ensure your clustered index is such that new records will always be inserted at the end of the table (an identity column will do just fine). This prevents page splits (where SQL Server must move data around because an existing page is full)
Set the fill factor to 0 or 100 (they are equivalent) so that no space in the table is left empty, reducing the number of pages that the data is spread across.
Change the recovery model of the database to Simple, reducing the overhead for the transaction log.

Are multiple clients inserting records in parallel? If so then you should also consdier the locking implications.

Note that SQL Server can suggest indexes for a given query either by executing the query in SQL Server Management Studio or via the Database Engine Tuning Advisor. You should do this to make sure you haven't removed an index which SQL Server was using to speed up the INSERT.

If this still isn't fast enough then you should consider grouping up inserts an using BULK INSERTinstead (or something like the bcp utility or SqlBulkCopy, both of which use BULK INSERT under the covers). This will give the highest throughput when inserting rows.

Also see Optimizing Bulk Import Performance - much of the advice in that article also applies to "normal" inserts.

Nguồn tham khảo:

https://dba.stackexchange.com/questions/59193/what-is-the-fastest-way-to-insert-large-numbers-of-rows