procedure or direct query?which one is best

Question

procedure or direct query?which one is best

mayank jarsaniya

Hall of Fame

Points: 3532
More actions
March 9, 2010 at 8:11 am

#230603

I have millions of records to insert through dotnet application and i want to know which one is best for me in following.
create table test(id int,name varchar(30));
now two scenarios to fill this table:
1. through dotnet application which work in following Manner:
-------------------------------------------------------------
declare @id int;
declare @name varchar(30);
set @id = 1;
set @name='mayank'
insert into test values(@id,@name);
--------------------------------------------------------------
2. through dotnet application which work in following Manner:
use the exec sp ins_test(@id,@name)
code for SP
create procedure ins_test(@id int,@name varchar(30))
AS
BEGIN
SET NOCOUNT ON
BEGIN TRAN
insert into test values(@id,@name);
END TRAN
END;

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 1

Assuming the complexity of the queries is as you've outlined, both will probably work about the same way.

However, are single row inserts the best way to move millions of rows? Have you looked at, instead, setting up an SSIS (SQL Server Integration Services), the built in ETL (Extract, Transform & Load) tool for SQL Server? Moving large sets of data one row at a time is likely to be extremely costly, if nothing else, in terms of time.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 2

I agree with Grant. Inserting million records one by one doesn’t sound at the correct way. If you don’t want to use SSIS, you can check out Bulk Copy operations that are available in ADO.NET. Although the name resembles the name of the BCP command line utility, this has nothing to do with files. It is one way that ADO lets you do bulk insert for many records. You can read about it here – http://msdn.microsoft.com/en-us/library/7ek5da1a.aspx%5B/url%5D

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

mayank jarsaniya Hall of Fame Points: 3532 More actions · Answer 3

you are right.

but i am not extracting data from text or any type of file.

my dotnet application generate new records based on some criteria and it is always row by row.and i have to insert them same way one by one.

anyway another question my dotnet application runs from five different nodes(computer) and submitting this data to my server this way........have you any tips/suggetions to improve network io or any related to performance improvement ??

it is sure each node give me millions of records this way.

thanks,

in advance.

Paul White SSC Guru Points: 150457 More actions · Answer 4

mjarsaniya (3/9/2010)
my dotnet application generate new records based on some criteria and it is always row by row.and i have to insert them same way one by one.

Can you not write them to a file instead?

mjarsaniya (3/9/2010)
have you any tips/suggetions to improve network io or any related to performance improvement ??
it is sure each node give me millions of records this way.

Don't process the data row-by-row, batch it.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Piotr.Rodak SSCrazy Eights Points: 9296 More actions · Answer 5

Agreed, if it possible, batch the data. You can build an xml fragment and flush it to database at appropriate interval, the performance of your app will gain tremendously.

Regards

Piotr

...and your only reply is slàinte mhath

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

mjarsaniya (3/9/2010)
my dotnet application generate new records based on some criteria and it is always row by row.

Ummm... you could possibly do the same thing in a stored procedure. It's not difficult to generate millions of rows in T-SQL based on criteria or whatever. Done correctly, SQL Server can gen a million rows of constrained/calculated data in just a second or two.

I just went through such a conversion with a client... they were using the .Net code to build and pass a quarter million separate Insert/Value statements and not only was it crushing their server, it also filled their pipe and no one could do anything while the code was running. The run time was sometimes more than 8 minutes.

Using a simple cross-join (which is what the .Net code was emulating) against the correct tables, we made it work on the server in the proverbial blink of an eye.

We just need more detail about where you're getting the data from and what the .Net code does. If you can provide table details and some sample data (see the first link in my signature line for how to do that properly), you might be surprised at what folks can come up with even if it doesn't require a cross-join.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Paul White SSC Guru Points: 150457 More actions · Answer 7

Summary: the way you are currently doing it is probably close to the worst possible. The more details you give, the more specific and useful and answer you will get.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

Heh... I did beat around the bush a bit, huh? 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Paul White SSC Guru Points: 150457 More actions · Answer 9

Jeff Moden (3/10/2010)
Heh... I did beat around the bush a bit, huh? 😛

Could've been worse...you have a long way to go to beat some of Barry's posts :laugh:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 10

Heh... not so sure about that. The article on Triangular Joins actually started out as a single post... including the graphics.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Paul White SSC Guru Points: 150457 More actions · Answer 11

Jeff Moden (3/10/2010)
Heh... not so sure about that. The article on Triangular Joins actually started out as a single post... including the graphics.

Ah. Ok then - you are as bad as Barry! 😛

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi