inserting rows into a DB -- dotnet performance

Kenny M.
6/10/2005 5:37:08 AM

Well I first make the comparison and if that is ok I copy the row into the
Data Set

My question is:

Should I send the row to the SP to insert at this time? I mean one y One?

The row has 6 column to be saved, and I 'm looking the faster performance,
millseconds are important to me.

thks
--
Kenny M.

[quoted text, click to view]

Robbe Morris [C# MVP]
6/12/2005 5:46:50 PM

I suspect that you may be better off not using a DataSet at all.
Perhaps an ArrayList of your own data classes. An array
if you know the exact amount of records.

Theoretically, the one row at a time calling the stored
procedure should be faster. The DataSet is eventually
going send each row one at a time anyway.

--
2004 and 2005 Microsoft MVP C#
Robbe Morris
http://www.masterado.net

Earn $$$ money answering .NET Framework
messageboard posts at EggHeadCafe.com.
http://www.eggheadcafe.com/forums/merit.asp

[quoted text, click to view]

Rafal Gwizdala
6/13/2005 10:33:05 AM

There's quite a nice technique you can use: build a batch of insert commands
and execute them all at once. If the number of rows is small (hundred or
two) you will make only one roundtrip to SQL server and will probably get
the best performance.

[quoted text, click to view]

Travis
6/17/2005 1:43:02 PM

I've used XML to handle tasks such as this (and parent/child operations)

In your .NET code, build up an XML string:
"<items><item><name>Bob</name></item><item><name>Sam</name></item></items>"

(you can use the string builder class OR the xml classes)

In your stored proc, pass in the XML

CREATE PROCEDURE spTest
@xml ntext
AS

DECLARE @hdoc int;
DECLARE @table TABLE (firstName nvarchar(50));

exec sp_xml_preparedocument @hdoc OUTPUT, @xml

INSERT INTO @table
SELECT [name] FROM OPENXML(@hdoc, '/items/item', 2) WITH ([name] nvarchar(50))

SELECT * FROM @table;

exec sp_xml_removedocument @hdoc

Not sure how this would perform against multiple calls to the DB, but would
be worth a test...

Mike Swaim
7/13/2005 2:04:59 PM

[quoted text, click to view]

Often yes.
If you are using an Oracle database, and the Oracle .net client, it
supports array arguments for SQL statements. So you can issue 1 insert
statement with arrays as the argument. This can be a fairly big win.
Another option, which someone else mentioned, is to concatenate SQL
strings. This'll cut down on the network traffic, with a possible
increase in time spent executing the commands.
Another option might be to spin the SQL into another thread. Most of
the time spent executing queries is spent waiting, so if you could be
doing something else while the SQL's executing, that could be a decent
solution. I've gotten pretty good speedups on code with 5+ threads
doing parallel inserts with separate DB connections. (The server was
pretty hefty, though. YMMV.)

--
Mike Swaim swaim@hal-pc.org at home | Quote: "Boingie"^4 Y,W & D
MD Anderson Dept. of Biostatistics & Applied Mathematics
mpswaim@mdanderson.org or mswaim@odin.mdacc.tmc.edu at work

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about dotnet performance

dotnet performance : inserting rows into a DB