Accessing SQL Server 2000 in .NET

There are basically two ways of doing this offered by .Net
environment: using SqlDataClient and OdbcClient. My question is:
what are the advantages of using Sql client over Odbc client?
I understand that specialized classes are supposed to be
optimized for transfer protocol and that Odbc is an additional
layer which I supposedly pass when using Sql client. But are
the differences significant? I performed simple tests and found
out that Sql approach is really faster (about twice) when you
need to return large amount of short rows, like e.g. you do
SELECT * FROM from a single column table of integers, which
situation is not going to happen often. The same query against
big table with long string data, or query returning reasonable
number of rows do not reveal superiocity of the Sql client.
Are there other situations you can think of, when Sql client
beats Odbc badly? I mean at least 20% performance gain.
Thank you.http://whidbey.msdn.microsoft.com/l...6bdb6dcb131.asp
http://www.sql-server-performance.c...performance.asp
AMB
"Sericinus hunter" wrote:

> There are basically two ways of doing this offered by .Net
> environment: using SqlDataClient and OdbcClient. My question is:
> what are the advantages of using Sql client over Odbc client?
> I understand that specialized classes are supposed to be
> optimized for transfer protocol and that Odbc is an additional
> layer which I supposedly pass when using Sql client. But are
> the differences significant? I performed simple tests and found
> out that Sql approach is really faster (about twice) when you
> need to return large amount of short rows, like e.g. you do
> SELECT * FROM from a single column table of integers, which
> situation is not going to happen often. The same query against
> big table with long string data, or query returning reasonable
> number of rows do not reveal superiocity of the Sql client.
> Are there other situations you can think of, when Sql client
> beats Odbc badly? I mean at least 20% performance gain.
> Thank you.
>|||"Sericinus hunter" <serhunt@.flash.net> wrote in message
news:cvd0v8$917$1@.news.monmouth.com...

> There are basically two ways of doing this offered by .Net
> environment: using SqlDataClient and OdbcClient.
Three, actually - you're forgetting OleDb...|||If you are using SQL Server from .NET, use SqlClient. OleDb and Odbc
are there to support other databases, such as Access or non-msft
platforms. SqlClient was designed specifically to take advantage of
SQL Server and can't be used with any other RDBMS. SqlClient allows
you to take advantage of all of SQL Server's functionality and is the
most performant.
Mary
On Mon, 21 Feb 2005 11:07:03 -0500, Sericinus hunter
<serhunt@.flash.net> wrote:

> There are basically two ways of doing this offered by .Net
>environment: using SqlDataClient and OdbcClient. My question is:
>what are the advantages of using Sql client over Odbc client?
> I understand that specialized classes are supposed to be
>optimized for transfer protocol and that Odbc is an additional
>layer which I supposedly pass when using Sql client. But are
>the differences significant? I performed simple tests and found
>out that Sql approach is really faster (about twice) when you
>need to return large amount of short rows, like e.g. you do
>SELECT * FROM from a single column table of integers, which
>situation is not going to happen often. The same query against
>big table with long string data, or query returning reasonable
>number of rows do not reveal superiocity of the Sql client.
> Are there other situations you can think of, when Sql client
>beats Odbc badly? I mean at least 20% performance gain.
> Thank you.|||Alejandro Mesa wrote:

> http://whidbey.msdn.microsoft.com/l...6bdb6dcb131.asp
> http://www.sql-server-performance.c...performance.asp
Thank you, Alejandro (and others).
Indeed, I read about the recommendation to use Sql data
client in many places, but could not find anything more specific
than general words.
Here is the result of the test I performed.
In the test I tried to cover two distinct variables: table size
(in terms of number of rows) and data size (in terms of the size of
a row). I measured the time needed for DataAdapter.Fill call to return,
where the select command was a simple SELECT * statement canned in a
stored procedure.
Each of six tables in the test is a single column table. Each table
is either "small" (100 rows) or "big" (10000 rows). Two tables have
a column of int type, two -- varchar(4000) ("long string") and the
remaining two -- varchar(200) ("short string").
As you can see Sql client is a clear winner only when you need
to return a huge number of rows which is probably not going to
happen very often. In addition, even in this case, there is no
advantage for reasonable row size ("short string").
Could it be that after .NET 1.1 has been released, different
data providers are not that different performance wise as it
was in 1.0 times, when probably all those recommendations were made?
---
| Time, ms
Table Type +--
| Sql Odbc OleDb
========================================
=
Int SmallTb | 1.2 1.6 2.0
---
Int BigTb | 22 48 46
---
LongStr SmallTb | 50 50 51
---
LongStr BigTb | 5200 5400 5400
---
ShortStr SmallTb | 3.8 3.8 4.2
---
ShortStr BigTb | 270 270 280
========================================
=