TDS and character encoding

[William Vaughn]

As cynical as this sounds, I'm with Stephen here. ADO.NET has (IMHO)
regressed in functionality in some respects from ADO classic. I would
embrace opening up the interface to see what it's doing so would could get
around some of the "won't fix" or "can't fix" issues.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

 

[Ralph]

As cynical as this sounds, I'm with Stephen here. ADO.NET has (IMHO)
regressed in functionality in some respects from ADO classic. I would
embrace opening up the interface to see what it's doing so would could get
around some of the "won't fix" or "can't fix" issues.

Yes I believe you can see a clear trail of the improvements.

I am somewhat timid in submitting a post in mild* favor of DAO, ADO, and
Microsoft's data access technologies in general. After all, Messrs. Howe and
Vaughn, have likely forgotten more about data access that I ever knew. <g>

But I believe it is frequently overlooked that any data access library is
merely one layer in a stack of technologies. For example...
[ADO | DAO] <-> [OLE DB | ODBC] <-> ClientAPI <-> DatabaseEngine.
If one actually attempts to trace what goes on with even the simplest of
queries - it quickly becomes amazing that it works at all.

"They are all interfaces on the same set of database technology - which does
not change." No, they are not. Database engines change, OLE implementations
change. Microsoft has not only presented us with the programming data
access, they have also made amazing changes and improvements all down the
line and back again.

Also Microsoft (who was talking to IBM in those days <g>) was the primary
sponsor and the major reason ODBC was adopted. ODBC took us out of the dark
ages of when every datasource, vendor, and language platform, had its own
access "libraries". I don't think MS gets nearly the credit it deserves.
Doesn't anyone remember the nightmare it used to be?

But sure ODBC had a flaw. It depended on everything to look like tables with
a hierarchal structure. This made working with non-table datasource
difficult. So ADO came about. ADO's stated goal was Universal Access. And it
accomplished that. We think nothing of using ADO to connect to anything.
"Give me a provider and I can move the world." <g>

But it is not my intention to provide a history lesson. Wikipedia can do
that.

Now to the question of why there are mysteries?

With ADO and SQLServer this seems strange - as Steve pointed out - "You
would think they would have it down pat by now". Especially when you
consider ADO and OLE DB and SQLServer were until recently the responsibility
of the same team. But why, is basically because of three problems.
1) ADO is so prevalent that any fundlemental changes would require massive
regression testing.
2) the specter of the 1998 "United States vs.. Microsoft" civil suit. While
it didn't happen MS came close to being broken up. As it is, they have to be
very careful to share its application programming interfaces with
third-party companies, and not make any 'improvements' that effectively
break anything else, or perhaps even more important provide an obvious
advantage for themselves.
And 3) the shear complexity of the problem in the sense - what is optimal?
We all know there are more than one way in programming and no single choice
is always the best choice.

Since the dawn of programming several things have always been paramount,
picking the best solution, you never knew until you tested it, and the next
situation was likely different.

Yes ADO.Net has flaws, so did ADO, and so did DAO, and so did... But all of
them had their strengths as well. Contrary to popular belief - NONE was ever
a 100% optimal replacement of the other.
Which is why I always laugh when someone comes out says things like - "ADO
is the only way to go." Even today DAO with the right engine and server will
thrash ADO. (eg, Jet and DB2)

Anyway this is too long. My apologizes to everyone.
[*"mild", I wouldn't want anyone to think I'm a MS bigot. I'm still pretty m
iffed over what they did to VB, and the clumsy half-a** PITA that is Vista.
Plus IMHO, if they would listen to me and done things the way I would have
done it, they would be better off. After all everything goes smoother when
people just do things MY way. <g>]

-ralph

 

[Stephen Howe]

"They are all interfaces on the same set of database technology - which

does
not change." No, they are not. Database engines change, OLE
implementations
change. Microsoft has not only presented us with the programming data
access, they have also made amazing changes and improvements all down the
line and back again.

They do not change.
Yes, database engines do change, there are improvements.
There are improvements to drivers. I dont disagree.
But the access methods have not changed.

We still have
Rowsets and Cursors
Stored Procedures
Input/Output parameters

Has that access method list got longer or changed in nature? No!
So why keep reinventing yet another interface to exactly the same set of
methods?
How many different ways can you call a Stored Procedure?

Also Microsoft (who was talking to IBM in those days <g>) was the primary
sponsor and the major reason ODBC was adopted. ODBC took us out of the
dark
ages of when every datasource, vendor, and language platform, had its own
access "libraries". I don't think MS gets nearly the credit it deserves.
Doesn't anyone remember the nightmare it used to be?

Yes it is a good thing.
A universal method of access.
Insulate programmers from the specifics of each database source - which can
vary in detail.

[snip rest]

- Yes I guess I mostly agree with you. But I am weary.

Thanks for the comments

Stephen Howe

 

[Erland Sommarskog]

As cynical as this sounds, I'm with Stephen here. ADO.NET has (IMHO)
regressed in functionality in some respects from ADO classic. I would
embrace opening up the interface to see what it's doing so would could get
around some of the "won't fix" or "can't fix" issues.

I'm in the opposite camp. I have difficulties to use "classic" when
speaking of old ADO. "Classic" indicates that it was actually good and
useful, but it wasn't. I also have difficulties to avoid using the word
"crap" when I talk about ADO (see, I failed this time too). Old ADO
performs too many tricks behind your back, and gets in the way to often.
I can't update that field, because it's a computed column? So what, I'm
going to update through a stored procedure. And then it the bug where
it submits a query with SET FMTONLY ON, and the procedure bombs and the
transaction is rolled back - and ADO drops this error on the floor.

On the ADO .Net and SqlClient is a very clean interface, particularly
if you stick to the basics for data access. No tricks behind your back
(well save the use of sp_executesql for parameterised queries), and
errors are caught. And if you run with FireInfoMessagesEventOnUserErrors
you can catch all errors and result sets even if they come mixed with
each other. Yes, there is an funny thing with the rowcount, but it
rarely matters.

It's almost as clean as DB-Library.
--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 

[William Vaughn]

I (personally) have to take the blame for some of this churn. As I said any
number of times in my books (especially the early ones), the JET/DAO
interface was designed as a native interface to JET and made a terrible
interface to SQL Server. ODBC the first one-size-fits-all (OSFA) generic
interface was supposed to bring utopia to the data access world. It ignored
the fact that just because you put in a door that anyone with a key could
open, once you got inside the room nothing was the same. So what if you
could open a connection to SQL Server or Oracle (or even JET) using the same
interface method. Most aspects of doing so were different. Once connected,
one was an ISAM engine, the next was best accessed with stored procedures
and the next while it could use stored procedures, they weren't coded the
same or didn't behave the same. We were right back where we started but
running the train from the caboose via teletype twelve cars back from the
engine.

RDO was my (and about three others in the VB team) attempt to get a better,
cheaper, lighter interface to Microsoft SQL Server. It worked (to a great
extent). As an intended side-effect it got the JET team to fix the first
versions of ADO to work correctly with stored procedures. 9 or 10 versions
later they're still trying to get it right. Each time they change it, the
apps that depend on the MDAC stack creak, bend or snap. Thank the stars
they've now decoupled the stack for SQL Server (SNAC)--everyone else is
still pooched.

According to the data access gurus, the downside to ODBC was perceived by
(... don't get me started on the other Mr. V) to be only suitable for
"relational" databases and MS needed more. They wanted the data access layer
to ALSO access flat data, round data, object data and corkscrewed up
data--thus OLE DB was hatched. It added layer upon layer and when all things
were done it was no faster and actually slower than ODBC and DAO--but you
could access virtually any kind of data. All you needed was a rocket
scientist to create the OLE DB provider and a language other than VB to do
it with. In their infinite wisdom they created a DAL that could not be
accessed with the most popular language on the planet (outside Redmond).

ADO.NET on the other hand was more of a start-from-scratch approach to let
the objects get closer to the native interfaces like DB-Lib and PL/SQL. It
was supposed to be very light (too light in my humble opinion) and fast and
not an OSFA. Each provider implemented similar base functionality in the
..NET provider but left you in the same place if you tried to create a single
application to access more than one backend.

At this point in time, I'm a bit miffed that we had to abandon some pretty
serious (and powerful, but difficult-to-get-right) architectures in ADO
classic like server-side cursors, fully async ops (including async Open and
fetch) and others. Instead the team has been focused on more and more
client-side library work like LINQ and TableAdapter code generators.

Now we hear that LINQ is (dramatically?) slower than not... I'm not a bit
surprised--is anyone? Of course the processors now-a-days are faster so the
real difference is smaller--as long as you're running a Quad Core Duo at
3.GHz with a RAID 5 array.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

As cynical as this sounds, I'm with Stephen here. ADO.NET has (IMHO)
regressed in functionality in some respects from ADO classic. I would
embrace opening up the interface to see what it's doing so would could
get
around some of the "won't fix" or "can't fix" issues.

Yes I believe you can see a clear trail of the improvements.

I am somewhat timid in submitting a post in mild* favor of DAO, ADO, and
Microsoft's data access technologies in general. After all, Messrs. Howe
and
Vaughn, have likely forgotten more about data access that I ever knew. <g>

But I believe it is frequently overlooked that any data access library is
merely one layer in a stack of technologies. For example...
[ADO | DAO] <-> [OLE DB | ODBC] <-> ClientAPI <-> DatabaseEngine.
If one actually attempts to trace what goes on with even the simplest of
queries - it quickly becomes amazing that it works at all.

"They are all interfaces on the same set of database technology - which
does
not change." No, they are not. Database engines change, OLE
implementations
change. Microsoft has not only presented us with the programming data
access, they have also made amazing changes and improvements all down the
line and back again.

Also Microsoft (who was talking to IBM in those days <g>) was the primary
sponsor and the major reason ODBC was adopted. ODBC took us out of the
dark
ages of when every datasource, vendor, and language platform, had its own
access "libraries". I don't think MS gets nearly the credit it deserves.
Doesn't anyone remember the nightmare it used to be?

But sure ODBC had a flaw. It depended on everything to look like tables
with
a hierarchal structure. This made working with non-table datasource
difficult. So ADO came about. ADO's stated goal was Universal Access. And
it
accomplished that. We think nothing of using ADO to connect to anything.
"Give me a provider and I can move the world." <g>

But it is not my intention to provide a history lesson. Wikipedia can do
that.

Now to the question of why there are mysteries?

With ADO and SQLServer this seems strange - as Steve pointed out - "You
would think they would have it down pat by now". Especially when you
consider ADO and OLE DB and SQLServer were until recently the
responsibility
of the same team. But why, is basically because of three problems.
1) ADO is so prevalent that any fundlemental changes would require massive
regression testing.
2) the specter of the 1998 "United States vs.. Microsoft" civil suit.
While
it didn't happen MS came close to being broken up. As it is, they have to
be
very careful to share its application programming interfaces with
third-party companies, and not make any 'improvements' that effectively
break anything else, or perhaps even more important provide an obvious
advantage for themselves.
And 3) the shear complexity of the problem in the sense - what is optimal?
We all know there are more than one way in programming and no single
choice
is always the best choice.

Since the dawn of programming several things have always been paramount,
picking the best solution, you never knew until you tested it, and the
next
situation was likely different.

Yes ADO.Net has flaws, so did ADO, and so did DAO, and so did... But all
of
them had their strengths as well. Contrary to popular belief - NONE was
ever
a 100% optimal replacement of the other.
Which is why I always laugh when someone comes out says things like - "ADO
is the only way to go." Even today DAO with the right engine and server
will
thrash ADO. (eg, Jet and DB2)

Anyway this is too long. My apologizes to everyone.
[*"mild", I wouldn't want anyone to think I'm a MS bigot. I'm still pretty
m
iffed over what they did to VB, and the clumsy half-a** PITA that is
Vista.
Plus IMHO, if they would listen to me and done things the way I would have
done it, they would be better off. After all everything goes smoother when
people just do things MY way. <g>]

-ralph

 

[bzh_29]

(e-mail address removed) a écrit :

I've seen a dump of the TDS traffic going from my webserver to the SQL
Server database and it seems encoded in Unicode (it has two bytes per
char). Seems it would have a huge impact on performance if it
travelled in one byte. Why might this be?

rj

Hi,
I've exactly the same trouble ... My apps is responding fine on a LAN
but when I goes on a WAN, getting horrible !

From 5 sec to 7 min ... When I look SQL Time is the same so I look

network usage with network analyser and find the same result a you !

Something strange : in some case when string are in parameter it using
only one byte instead of two !

I'm wondering, if you find any solution ...

 

[Erland Sommarskog]

I've exactly the same trouble ... My apps is responding fine on a LAN
but when I goes on a WAN, getting horrible !

If you run your application on a WAN, you need to be more considerate
with your design. Make sure you don't return unneeded columns in your
result set (no SELECT *!). Also make sure that you don't have a lot of
extra network roundtrips. If you need to find data for ten orders, run
one query not ten.

Something strange : in some case when string are in parameter it using
only one byte instead of two !

varchar data, I assume. Query text is sent as Unicode. Parameter values
in RPC calls are sent in their native format. So nvarchar will be Unicode,
varchar will be 8-bit chars, and an integer will be 4 bytes.


--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 

[bzh_29]

If you run your application on a WAN, you need to be more considerate
with your design.

Excatly. We're working since a long time to avoid any unneccessary
table or column.

Make sure you don't return unneeded columns in your result set (no SELECT *!). Also make sure that you don't have a lot of extra network roundtrips. If you need to find data for ten orders, run one query not ten.

Request are build to return only needed columns and we use a package
analyser to see what's going on on network to optimize network
traffic.

It's with this analyser I see that when I send one byte in fact I send
two ... I understand the reason you explain before but as my apps will
never feet for japanese ou chinese needs, I'm a little sad to not be
able to avoir such things ...

Need to continue optimize every char I send ...

 

[Bob Barrows [MVP]]

Excatly. We're working since a long time to avoid any unneccessary
table or column.


Request are build to return only needed columns and we use a package
analyser to see what's going on on network to optimize network
traffic.

It's with this analyser I see that when I send one byte in fact I send
two ... I understand the reason you explain before but as my apps will
never feet for japanese ou chinese needs, I'm a little sad to not be
able to avoir such things ...

Need to continue optimize every char I send ...

Ummm ... there is obviously something else going on here. You're trying to
tell us that sending two bites instead of one results in 84X (5 sec to 7
min) slower performance??? I don't think so. At worst, there would be a 2X
drop in performance, and even that is not likely (please, someone step in
and correct me if I'm wrong). My guess is an overloaded network or perhaps a
defective router or switch somewhere.

 

[Erland Sommarskog]

It's with this analyser I see that when I send one byte in fact I send
two ... I understand the reason you explain before but as my apps will
never feet for japanese ou chinese needs, I'm a little sad to not be
able to avoir such things ...

But maybe you need the oe digraph? Or the euro character? Those characters
are not on in Latin-1.

I don't know your business, but even if you are not aiming at the Far
Eastern market, you may expand into Poland or Hungary one day. That's
enough reason to use Unicode.

Developing for Unicode from the start is cheap. Changing to Unicode after
the fact is expensive.

And if you use varchar in your application, what is really your problem?
The only Unicode you need to send is the name of the stored procedures you
call? Or are you sending query batches from the application? Now, if you
do that, there are some bytes you can save by using stored procedures
instead.




--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 

[Roy Harvey (SQL Server MVP)]

I've exactly the same trouble ... My apps is responding fine on a LAN
but when I goes on a WAN, getting horrible !

network usage with network analyser and find the same result a you !

Just to make sure one basic point is covered, do all the stored
procedures have SET NOCOUNT ON right at the beginning? Leaving that
out can magnify network issues.

Roy Harvey
Beacon Falls, CT