Inserting Multiple Rows

Arsen V.

Hello,

What is the optimal way to insert multiple rows (around 1000) from a web
application into a table?

The user enters multiple lines into a text box (up to 10,000). The ASP.NET
application breaks that data into a string array. Each line is an item of
that array.

The user clicks Submit.

I want to insert all those lines into a table in SQL Server.

I know that with MySQL 4.00 and newer, I can simply issue the following
command:

INSERT INTO Table1 (field1) VALUES ('apple'), ('pear'), ('soda'), ('drink')

This will very quickly insert all those values into Table1.field1.

I know that in SQL Server, I can use a BULK INSERT from a file or BCP.
However, I need to do the insert from a web application.

It is better to create one large SqlCommand with all the insert statements:

INSERT Table1 (field1) VALUES ('apple');
INSERT Table1 (field1) VALUES ('pear');
INSERT Table1 (field1) VALUES ('fruit');
INSERT Table1 (field1) VALUES ('drink');

and execute it in one shot.

Or is it better to execute each insert separatly.

Thanks,
Arsen

Nov 18 '05 #1

Tibor Karaszi

Arsen,

Here's a reply I just posted in -programming in the same topic:

In addition to that [BCP, BULK INSERT, DTS], it might be worth mentioning below two things. In case Jon wants
to stick with INSERT
statements:

1. Group several INSERT in the same transaction. Each transaction requires an I/O (write to the transaction
log). You can cut time, perhaps to some 10% by doing several in the same transaction. Not too many, though.
Start with about 1k - 5k and test from there.

2. Group several INSERT in the same batch. Each batch requires a network roundtrip, parsing etc. By batch, I
mean what we see as "GO" in Query Analyzer and the method you use in ADO.NET to send the command to SQL Server
(each ExecuteNonQuery is a batch, for instance).

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Arsen V." <ar***@community.nospam> wrote in message news:%2****************@tk2msftngp13.phx.gbl...

Hello,

What is the optimal way to insert multiple rows (around 1000) from a web
application into a table?

The user enters multiple lines into a text box (up to 10,000). The ASP.NET
application breaks that data into a string array. Each line is an item of
that array.

The user clicks Submit.

I want to insert all those lines into a table in SQL Server.

I know that with MySQL 4.00 and newer, I can simply issue the following
command:

INSERT INTO Table1 (field1) VALUES ('apple'), ('pear'), ('soda'), ('drink')

This will very quickly insert all those values into Table1.field1.

I know that in SQL Server, I can use a BULK INSERT from a file or BCP.
However, I need to do the insert from a web application.

It is better to create one large SqlCommand with all the insert statements:

INSERT Table1 (field1) VALUES ('apple');
INSERT Table1 (field1) VALUES ('pear');
INSERT Table1 (field1) VALUES ('fruit');
INSERT Table1 (field1) VALUES ('drink');

and execute it in one shot.

Or is it better to execute each insert separatly.

Thanks,
Arsen

Nov 18 '05 #2

Narayana Vyas Kondreddi

Making a call for each insert would be the slowest option.

In SQL Server, you could insert multiple rows as shown below:

INSERT INTO TableName (Col1, Col2)
SELECT 1, 2
UNION ALL
SELECT 3, 4
UNION ALL
SELECT 5, 6

Also see OPENXML in SQL Server 2000 Books Online.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Arsen V." <ar***@community.nospam> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...

Hello,

What is the optimal way to insert multiple rows (around 1000) from a web
application into a table?

The user enters multiple lines into a text box (up to 10,000). The ASP.NET
application breaks that data into a string array. Each line is an item of
that array.

The user clicks Submit.

I want to insert all those lines into a table in SQL Server.

I know that with MySQL 4.00 and newer, I can simply issue the following
command:

INSERT INTO Table1 (field1) VALUES ('apple'), ('pear'), ('soda'), ('drink')
This will very quickly insert all those values into Table1.field1.

I know that in SQL Server, I can use a BULK INSERT from a file or BCP.
However, I need to do the insert from a web application.

It is better to create one large SqlCommand with all the insert statements:
INSERT Table1 (field1) VALUES ('apple');
INSERT Table1 (field1) VALUES ('pear');
INSERT Table1 (field1) VALUES ('fruit');
INSERT Table1 (field1) VALUES ('drink');

and execute it in one shot.

Or is it better to execute each insert separatly.

Thanks,
Arsen

Nov 18 '05 #3

Tibor Karaszi

Vyas,

Do you think that a SELECT with UNION will significally outperform several INSERTs in the same batch and
transaction? I never thought about comparing the two...

If it weren't for my girlfriend complaining right now in the living room, I'd do a test right now... :-)

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Narayana Vyas Kondreddi" <an*******@hotmail.com> wrote in message
news:u0**************@TK2MSFTNGP09.phx.gbl...

Making a call for each insert would be the slowest option.

In SQL Server, you could insert multiple rows as shown below:

INSERT INTO TableName (Col1, Col2)
SELECT 1, 2
UNION ALL
SELECT 3, 4
UNION ALL
SELECT 5, 6

Also see OPENXML in SQL Server 2000 Books Online.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Arsen V." <ar***@community.nospam> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...

Hello,

What is the optimal way to insert multiple rows (around 1000) from a web
application into a table?

The user enters multiple lines into a text box (up to 10,000). The ASP.NET
application breaks that data into a string array. Each line is an item of
that array.

The user clicks Submit.

I want to insert all those lines into a table in SQL Server.

I know that with MySQL 4.00 and newer, I can simply issue the following
command:

INSERT INTO Table1 (field1) VALUES ('apple'), ('pear'), ('soda'),

('drink')

This will very quickly insert all those values into Table1.field1.

I know that in SQL Server, I can use a BULK INSERT from a file or BCP.
However, I need to do the insert from a web application.

It is better to create one large SqlCommand with all the insert

statements:

INSERT Table1 (field1) VALUES ('apple');
INSERT Table1 (field1) VALUES ('pear');
INSERT Table1 (field1) VALUES ('fruit');
INSERT Table1 (field1) VALUES ('drink');

and execute it in one shot.

Or is it better to execute each insert separatly.

Thanks,
Arsen

Nov 18 '05 #4

Snake

How important is loading all rows or no rows? Are all 1000 rows one logical
transaction? Is the application fault-tolerant and able to handle a "partial
load"?

Michael

"Arsen V." wrote:

Hello,

What is the optimal way to insert multiple rows (around 1000) from a web
application into a table?

The user enters multiple lines into a text box (up to 10,000). The ASP.NET
application breaks that data into a string array. Each line is an item of
that array.

The user clicks Submit.

I want to insert all those lines into a table in SQL Server.

I know that with MySQL 4.00 and newer, I can simply issue the following
command:

INSERT INTO Table1 (field1) VALUES ('apple'), ('pear'), ('soda'), ('drink')

This will very quickly insert all those values into Table1.field1.

I know that in SQL Server, I can use a BULK INSERT from a file or BCP.
However, I need to do the insert from a web application.

It is better to create one large SqlCommand with all the insert statements:

INSERT Table1 (field1) VALUES ('apple');
INSERT Table1 (field1) VALUES ('pear');
INSERT Table1 (field1) VALUES ('fruit');
INSERT Table1 (field1) VALUES ('drink');

and execute it in one shot.

Or is it better to execute each insert separatly.

Thanks,
Arsen

Nov 18 '05 #5

Toby Herring

I concur with Narayana, I think that in your specific case, OPENXML is the
way to go for you.

--
Toby Herring
MCDBA, MCSD, MCP+SB
Need a Second Life?
http://secondlife.com/ss/?u=03e0e5b3...e80ee40119a65e
"Arsen V." <ar***@community.nospam> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...

Hello,

What is the optimal way to insert multiple rows (around 1000) from a web
application into a table?

The user enters multiple lines into a text box (up to 10,000). The ASP.NET
application breaks that data into a string array. Each line is an item of
that array.

The user clicks Submit.

I want to insert all those lines into a table in SQL Server.

I know that with MySQL 4.00 and newer, I can simply issue the following
command:

INSERT INTO Table1 (field1) VALUES ('apple'), ('pear'), ('soda'), ('drink')
This will very quickly insert all those values into Table1.field1.

I know that in SQL Server, I can use a BULK INSERT from a file or BCP.
However, I need to do the insert from a web application.

It is better to create one large SqlCommand with all the insert statements:
INSERT Table1 (field1) VALUES ('apple');
INSERT Table1 (field1) VALUES ('pear');
INSERT Table1 (field1) VALUES ('fruit');
INSERT Table1 (field1) VALUES ('drink');

and execute it in one shot.

Or is it better to execute each insert separatly.

Thanks,
Arsen

Nov 18 '05 #6