Từ khóa:
- Tips For Lightning-Fast Insert Performance On SQL Server
- Fastest way to insert data into Sql Server Database
- Multiple Ways to do Multiple Inserts
- Fastest way to insert new records where one doesn’t already exist
- Fastest way to insert 30 thousand rows in SQL Server
- 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:
Bulk Inserts via TSQL in SQL Server
https://www.red-gate.com/simple-talk/sql/learn-sql-server/bulk-inserts-via-tsql-in-sql-server/
http://www.sqlines.com/sql-server/insert_autocommit_on_off_performance
Batch commit on large INSERT operation in native SQL?
https://stackoverflow.com/questions/1602244/batch-commit-on-large-insert-operation-in-native-sql
SQL Server Developer Tips and Tricks
https://www.mssqltips.com/sql-server-developer-resources/
Tạo số lượng lớn dữ liệu: