Efficent database access in terms of record insertion

Question

I apologize in advance for the "rookie" question, but I am still fundamentally a novice in most aspects of SQL. My question stems from part of Chapter Two in The Art of SQL. In the passage titled, "Stable Database Connections", the author mentions several ways to insert a large number of rows into a database. Here is the corresponding list in the format

Test; Results
Connect/Disconnect for each line in turn; 7.4 lines loaded per second  
Connect Once, all candidate lines individually inserted; 1,681 lines loaded per second  
Connect Once, all candidate lines inserted in arrays of 10 lines; 5,914 lines inserted per second  
Connect Once, all candidate lines inserted in arrays of of 100 lines; 9,190 lines inserted per second

In addition, the author mentions "direct-loading techniques that are even faster."

Unfortunately, I do not completely understand the idea of insertion of arrays (if at all). Could anyone elaborate on the techniques the author referring to, in terms of inserting arrays and the other "direct loading techniques" that he referenced?

Andrew · Accepted Answer · 2011-10-03 18:43:32Z

6

The author is showing that

INSERT INTO Table (Column) 
VALUES ('myValue')

is only going to be so fast.

It will be significantly faster to do

INSERT INTO Table (Column) 
VALUES ('myFirstValue'), 
('mySecondValue'), ...

The author is saying that having 10 VALUES items inserts 5,914 lines per second, but if there 100 items in the single SQL statement, then the load rate goes up to 9,190 lines per second.

Finally, the "direct-loading techniques" are referring to bulk insert operations. You can google msSQL bulk insert to find numerous tutorials for uploading a file and then processing it into a table.

answered Oct 3, 2011 at 18:43

Andrew

1,3571 gold badge17 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

xanatos Over a year ago

I'll add that (at least on SQL Server) you can send an XML file and insert the rows from it. It's quite fast. Not as fast as a bulk insert, but as much fast as "formatting" the INSERT INTO Table

user957902 · Accepted Answer · 2011-10-03 18:46:33Z

4

The fastest way to insert large amounts of data into a SQLServer table is with a process know as "Bulk Insert". There are several ways to do this. There is a command line app that comes with SQL Server called bcp, there is a TSQL command BULK INSERT, and there is a .NET sql client object, System.Data.SqlClient.SqlBulkCopy.

edited Oct 3, 2011 at 18:46

answered Oct 3, 2011 at 18:40

user957902

3,07017 silver badges19 bronze badges

Collectives™ on Stack Overflow

Efficent database access in terms of record insertion

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related