Stored Procedures vs ADO vs T-Sql

[Cheryl]

Which method would give me the best performance for logging large
records into a database with over 25000 rows in SQL Server 2003?

[Trevor Best]

On 19 Sep 2003 12:45:11 -0700 in comp.databases.ms-sqlserver,
ch********@aol.com (Cheryl) wrote:

Which method would give me the best performance for logging large
records into a database with over 25000 rows in SQL Server 2003?

Depends where the data is coming from, if from outside of SQL Server
then you would need ADO.

T-SQL will have to compiled before running, this is not really that
much of an overhead.

Stored procedures are written in T-SQL and as their name suggests,
stored on the server and pre-compiled. They will run ever so slightly
faster than just chucking T-SQL at the server... mostly.

ADO OTOH is just a method of connecting to the server and executing
either of the above.

--
A)bort, R)etry, I)nfluence with large hammer.

[Erland Sommarskog]

Cheryl (ch********@aol.com) writes:

Which method would give me the best performance for logging large
records into a database with over 25000 rows in SQL Server 2003?

From slowest to fastest:

.AddNew in ADO
INSERT statement passed from ADO
Stored procedure passed as adCmdText
Stored procedure called through adCmdStoredProcedure
the BulkCopy interface in OLE DB.
Build an XML document and unpack it with OPENXML.

For a somewhat less terse, see <Xn**********************@127.0.0.1>
that I posted yesterday.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Cheryl]

Trevor Best <bouncer@localhost> wrote in message news:<52********************************@4ax.com>. ..

On 19 Sep 2003 12:45:11 -0700 in comp.databases.ms-sqlserver,
ch********@aol.com (Cheryl) wrote:

Which method would give me the best performance for logging large
records into a database with over 25000 rows in SQL Server 2003?

Depends where the data is coming from, if from outside of SQL Server
then you would need ADO.

T-SQL will have to compiled before running, this is not really that
much of an overhead.

Stored procedures are written in T-SQL and as their name suggests,
stored on the server and pre-compiled. They will run ever so slightly
faster than just chucking T-SQL at the server... mostly.

ADO OTOH is just a method of connecting to the server and executing
either of the above.

What is an advantage of using ADO to log to the SQL Server opposed the
a stored procedure or and T-SQl. What part would DTS play in this
procedure?

[Erland Sommarskog]

Cheryl (ch********@aol.com) writes:

What is an advantage of using ADO to log to the SQL Server opposed the
a stored procedure or and T-SQl. What part would DTS play in this
procedure?

Your question was very terse, why Trevor had to make some guesswork
about your surroundings.

Maybe you can give some more details about your situation. Where does
the data come from? Do you get files from another site? Is the data
being generated by a program?

That sort of information is really necessary to give a good answer to
your question.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp