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
(raymond_b_jimenez@yahoo.com) writes:
> 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?
I have never eavesdropped on TDS, but Unicode is indeed the character
set of SQL Server. You are perfectly able to name your tables in
Cyrillic or Hindi characters if you feel like. And of course character
strings may include all sorts of characters. So an batch of SQL statement
that is sent over the wire must be Unicode. That is beyond dispute.
However, you don't encode something in Unicode. Unicode is the character
set, and there are several encodings available, of which the most popular
are UTF-16 and UTF-8. In UTF-8 each character in the base plane takes up
2 bytes, and characters beyond that takes up 4 bytes. (The base plane
covers the vast majority of living langauges). In UTF-8, ASCII characters
takes up one byte, other characters in the Latin, Greek and Cyrillic
script takes two bytes, and Chinese and Japanese characters takes up three
bytes.
SQL Server uses UTF-16 exclusively. It is true that for network traffic
in the western world, it would be more effective if TDS used UTF-8, but
as you can see that it is necessarily the case in the Far East. And had
TDS used UTF-8, both ends of the wire would have had to convert to
UTF-16, so any reduced network traffic could be eaten up by extra CPU
time.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx
Snooping into the TDS would be the very last place I would look when trying
to improve performance. It would be like polishing a clean mirror to remove
one's zits.
--
____________________________________
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)
------------------------------------------------------------ -----------------------------------------------------------
"Erland Sommarskog"
news:Xns999CEFDAB2FB4Yazorman@127.0.0.1...
> (raymond_b_jimenez@yahoo.com) writes:
>> 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?
>
> I have never eavesdropped on TDS, but Unicode is indeed the character
> set of SQL Server. You are perfectly able to name your tables in
> Cyrillic or Hindi characters if you feel like. And of course character
> strings may include all sorts of characters. So an batch of SQL statement
> that is sent over the wire must be Unicode. That is beyond dispute.
>
> However, you don't encode something in Unicode. Unicode is the character
> set, and there are several encodings available, of which the most popular
> are UTF-16 and UTF-8. In UTF-8 each character in the base plane takes up
> 2 bytes, and characters beyond that takes up 4 bytes. (The base plane
> covers the vast majority of living langauges). In UTF-8, ASCII characters
> takes up one byte, other characters in the Latin, Greek and Cyrillic
> script takes two bytes, and Chinese and Japanese characters takes up three
> bytes.
>
> SQL Server uses UTF-16 exclusively. It is true that for network traffic
> in the western world, it would be more effective if TDS used UTF-8, but
> as you can see that it is necessarily the case in the Far East. And had
> TDS used UTF-8, both ends of the wire would have had to convert to
> UTF-16, so any reduced network traffic could be eaten up by extra CPU
> time.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx
Well William, that is clearly not the case where you have a REAL
database with REAL traffic. When I mean REAL, I mean a 25Mbps stream
between the IIS servers and SQL Server... Getting away from about
10Mbps of unneeded traffic does not seem like polishing to me...
I can guarantee you that this is having serious impact on performance,
and when you're digging really into it (things like TCP/IP slow-
starts...), you really get to know why it's huge impact for the
client, the DB server and performance.
rj
On 30 Ago, 23:16, "William Vaughn"
> Snooping into the TDS would be the very last place I would look when trying
> to improve performance. It would be like polishing a clean mirror to remove
> one's zits.
>
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant, Dad, Grandpa
> Microsoft MVP
> INETA Speakerwww.betav.comwww.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.
> __________________________________
>
Given that SQL Server has the highest TPC-E benchmarks in the industry,
don't you think that the SQL Server team has made the TDS stream as
efficient as possible? IMHO, it's not the line protocol or the lowest layers
of the interface that should be the focus of performance tuning, but the
applications, database designs and query methodologies that should dominate
your attempts to improve throughput and scalibility. Reducing the traffic on
the TDS channel will go a long way to improving performance if you have to
move that much volume over the wire to make a difference.
SQL Server Holds Record for TPC-E Database Benchmark
by Brian Moran, brian@solidqualitylearning.com
SQL Server now holds every conceivable world record for the TPC-E database
benchmark. That news would be slightly more impressive if TPC-E scores
existed for any database besides SQL Server, but heck, winning a race with
just one runner doesn't mean that runner did a bad job. I first wrote about
TPC-E, the latest benchmark from the Transaction Processing Performance
Council, in my commentary "TPC's New Benchmark Strives for Realism," October
2006, InstantDoc ID 93955.
Microsoft became the first database vendor to have a published TPC-E result
when Unisys published a TPC-E score on July 12 using SQL Server 2005 on a
dual-core 16-processor ES7000. IBM followed suit with a dual-core
2-processor server two weeks later, and Dell posted a dual-core 4-processor
result on August 24. Both IBM's and Dell's results used SQL Server, so SQL
Server is currently the only database vendor listed, meaning SQL Server
currently holds all the top scores. Sane vendors don't post TPC-E scores
that make them look bad, but I suspect it's only a matter of time before IBM
and Oracle post TPC- E scores for their database products that leapfrog the
latest SQL Server scores, which will in turn be bested by Microsoft in the
never-ending game of benchmark leapfrog.
Read the full article at:
http://lists.sqlmag.com/t?ctl=642B5:CC6CF972C3DECB8DA4B50D36 88BDE645
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
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)
------------------------------------------------------------ -----------------------------------------------------------
news:1188578980.032571.55290@g4g2000hsf.googlegroups.com...
> Well William, that is clearly not the case where you have a REAL
> database with REAL traffic. When I mean REAL, I mean a 25Mbps stream
> between the IIS servers and SQL Server... Getting away from about
> 10Mbps of unneeded traffic does not seem like polishing to me...
> I can guarantee you that this is having serious impact on performance,
> and when you're digging really into it (things like TCP/IP slow-
> starts...), you really get to know why it's huge impact for the
> client, the DB server and performance.
> rj
>
> On 30 Ago, 23:16, "William Vaughn"
>> Snooping into the TDS would be the very last place I would look when
>> trying
>> to improve performance. It would be like polishing a clean mirror to
>> remove
>> one's zits.
>>
>> --
>> ____________________________________
>> William (Bill) Vaughn
>> Author, Mentor, Consultant, Dad, Grandpa
>> Microsoft MVP
>> INETA Speakerwww.betav.comwww.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.
>> __________________________________
>>
>
(raymond_b_jimenez@yahoo.com) writes:
> Well William, that is clearly not the case where you have a REAL
> database with REAL traffic. When I mean REAL, I mean a 25Mbps stream
> between the IIS servers and SQL Server... Getting away from about
> 10Mbps of unneeded traffic does not seem like polishing to me...
> I can guarantee you that this is having serious impact on performance,
> and when you're digging really into it (things like TCP/IP slow-
> starts...), you really get to know why it's huge impact for the
> client, the DB server and performance.
Rather than blaming TDS, maybe you should look into trimming the
application. TDS is not going to change.
First step is to analyse what is making up those 25 Mbps. Is it SQL
commands? Or is data? SQL batches are, as I discussed in my previous post,
Unicode by necessity. Data is another matter.
As I said, I have not eavesdropped on TDS, but I would execpt varchar
data to be sent as bytes. That is, the value 'character' would be eleven
bytes on the wire. On the other hand, the value N'character' would be
20 bytes. And of course, metadata goes as Unicode.
Now, what can you do to reduce the amount the network traffic? If you
feel that you don't need Unicode, use varchar for you character data
and not nvarchar. (But keep in mind that the day when you need to support,
say, Japanese may be closer in time than you think.) But most of all,
trim your result sets from unneeded columns. Make sure that there are
not a lot of "SELECT *" in your queries, and that you don't retrieve
rows you don't need.
Furthermore, network traffic is not only about bytes, but also about
roundtrips. Don't get the details of the order, and then make one
call for each product on the order, but get all data at once.
And, yes, while you would have seen a gross cut if TDS was UTF-8 on
the wire and not UTF-16, a Chinese user would have seen an increase
instead.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx
In addition to agreement with others statements about refactoring the
application to reduce network traffic, I proffer the following: lets assume
that you are an Amazon.com wannabee and you really DO have 25Mbps
steady-state bandwidth needs between IIS and SQL Server. That gives you
have almost THREE orders of magnitude before running out of currently
available network capacity (10GB ethernet). Even cheapo 1000MB ethernet
gives a huge amount of headroom to grow into.
BTW, I have been a SQL Server database consultant for 10+ years. I have
NEVER YET been to a client that had a fully optimized database application,
and most of them were HORRID from a performance standpoint. :-))
--
TheSQLGuru
President
Indicium Resources, Inc.
"Erland Sommarskog"
news:Xns999DEDA151319Yazorman@127.0.0.1...
> (raymond_b_jimenez@yahoo.com) writes:
>> Well William, that is clearly not the case where you have a REAL
>> database with REAL traffic. When I mean REAL, I mean a 25Mbps stream
>> between the IIS servers and SQL Server... Getting away from about
>> 10Mbps of unneeded traffic does not seem like polishing to me...
>> I can guarantee you that this is having serious impact on performance,
>> and when you're digging really into it (things like TCP/IP slow-
>> starts...), you really get to know why it's huge impact for the
>> client, the DB server and performance.
>
> Rather than blaming TDS, maybe you should look into trimming the
> application. TDS is not going to change.
>
> First step is to analyse what is making up those 25 Mbps. Is it SQL
> commands? Or is data? SQL batches are, as I discussed in my previous post,
> Unicode by necessity. Data is another matter.
>
> As I said, I have not eavesdropped on TDS, but I would execpt varchar
> data to be sent as bytes. That is, the value 'character' would be eleven
> bytes on the wire. On the other hand, the value N'character' would be
> 20 bytes. And of course, metadata goes as Unicode.
>
> Now, what can you do to reduce the amount the network traffic? If you
> feel that you don't need Unicode, use varchar for you character data
> and not nvarchar. (But keep in mind that the day when you need to support,
> say, Japanese may be closer in time than you think.) But most of all,
> trim your result sets from unneeded columns. Make sure that there are
> not a lot of "SELECT *" in your queries, and that you don't retrieve
> rows you don't need.
>
> Furthermore, network traffic is not only about bytes, but also about
> roundtrips. Don't get the details of the order, and then make one
> call for each product on the order, but get all data at once.
>
> And, yes, while you would have seen a gross cut if TDS was UTF-8 on
> the wire and not UTF-16, a Chinese user would have seen an increase
> instead.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx
> Rather than blaming TDS, maybe you should look into trimming the
> application. TDS is not going to change.
BTDT. Especially with Profiler, which is a great tool.
> First step is to analyse what is making up those 25 Mbps. Is it SQL
> commands? Or is data? SQL batches are, as I discussed in my previous post,
> Unicode by necessity. Data is another matter.
Queries and replies. Replies are in the 3-4 packets range. Lots of
stored procedures of course.
> As I said, I have not eavesdropped on TDS, but I would execpt varchar
> data to be sent as bytes. That is, the value 'character' would be eleven
> bytes on the wire. On the other hand, the value N'character' would be
> 20 bytes. And of course, metadata goes as Unicode.
Don't accept anything for granted. Seeing is a completely different
experience.
> Now, what can you do to reduce the amount the network traffic? If you
> feel that you don't need Unicode, use varchar for you character data
> and not nvarchar. (But keep in mind that the day when you need to support,
> say, Japanese may be closer in time than you think.) But most of all,
> trim your result sets from unneeded columns. Make sure that there are
> not a lot of "SELECT *" in your queries, and that you don't retrieve
> rows you don't need.
BTDT
> Furthermore, network traffic is not only about bytes, but also about
> roundtrips. Don't get the details of the order, and then make one
> call for each product on the order, but get all data at once.
You're absolutely correct! That's why I got another thread going on
about "SET NO_BROWSETABLE ON". This one seems particularly annoying,
as it seems it is getting inserted automatically by ADO.Net. Accounts
for about 20% of the data traffic, and one additional round trip do
the DB server for each query.
> And, yes, while you would have seen a gross cut if TDS was UTF-8 on
> the wire and not UTF-16, a Chinese user would have seen an increase
> instead.
Wouldn't it be great to have an option?
rj
On 31 Ago, 22:45, "TheSQLGuru"
> In addition to agreement with others statements about refactoring the
> application to reduce network traffic, I proffer the following: lets assume
> that you are an Amazon.com wannabee and you really DO have 25Mbps
> steady-state bandwidth needs between IIS and SQL Server. That gives you
> have almost THREE orders of magnitude before running out of currently
> available network capacity (10GB ethernet). Even cheapo 1000MB ethernet
> gives a huge amount of headroom to grow into.
While all that is true, you have several other factors pounding you
with these amounts of traffic. Name two: TCP/IP slow-start and
interrupts on the receiving servers. Last one can be solved with
better network cards, but mines are already offloading work on the
hardware.
rj
On 31 Ago, 18:54, "William \(Bill\) Vaughn"
> Given that SQL Server has the highest TPC-E benchmarks in the industry,
> don't you think that the SQL Server team has made the TDS stream as
> efficient as possible? IMHO, it's not the line protocol or the lowest layers
> of the interface that should be the focus of performance tuning, but the
> applications, database designs and query methodologies that should dominate
> your attempts to improve throughput and scalibility. Reducing the traffic on
> the TDS channel will go a long way to improving performance if you have to
> move that much volume over the wire to make a difference.
Don't know a lot about TPC-E benchmarks. Are they measured over a
network?
rj
On 31 Ago, 18:54, "William \(Bill\) Vaughn"
> Given that SQL Server has the highest TPC-E benchmarks in the industry,
> don't you think that the SQL Server team has made the TDS stream as
> efficient as possible? IMHO, it's not the line protocol or the lowest layers
> of the interface that should be the focus of performance tuning, but the
> applications, database designs and query methodologies that should dominate
> your attempts to improve throughput and scalibility. Reducing the traffic on
> the TDS channel will go a long way to improving performance if you have to
> move that much volume over the wire to make a difference.
Don't know a lot about TPC-E benchmarks. Are they measured over a
network?
rj
(raymond_b_jimenez@yahoo.com) writes:
>> Furthermore, network traffic is not only about bytes, but also about
>> roundtrips. Don't get the details of the order, and then make one
>> call for each product on the order, but get all data at once.
>
> You're absolutely correct! That's why I got another thread going on
> about "SET NO_BROWSETABLE ON". This one seems particularly annoying,
> as it seems it is getting inserted automatically by ADO.Net. Accounts
> for about 20% of the data traffic, and one additional round trip do
> the DB server for each query.
And as I've noted in another thread, SqlClient does not do this when
you use it plainly. Run a plain ExecuteReader or DataAdapter.Fill and
you will not see it. There was a link posted that lead to an article
pointing out the problem with the CommandBuilder.
So that is one more thing to look into. Exactly what in your usage
of ADO .Net (and you still have not told us which data provider you
are using) triggers the usage of SET NO_BROWSETABLE ON.
>> And, yes, while you would have seen a gross cut if TDS was UTF-8 on
>> the wire and not UTF-16, a Chinese user would have seen an increase
>> instead.
>
> Wouldn't it be great to have an option?
If you thinks, submit this suggest on
http://connect.microsoft.com/SqlServer/Feedback.
Personally, I don't think it is worth the pain, also it would also
require changes in the client APIs. And all it would affect is query
batches sent to SQL Server and metadata sent back. If the query batches
sent to SQL Server is killing your network, maybe you should look into
using stored procedures.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx
> Don't know a lot about TPC-E benchmarks. Are they measured over a
> network?
Database benchmarks are typically done with a dedicated database server and
remote client(s). You can download the results disclosure reports from
http://www.tpc.org/tpce/tpce_perf_results.asp to get details of the actual
configurations used. Looking at the specs of the network gear, it doesn't
look to me like the benchmark sponsors were too concerned about network
performance.
I agree with the others in this thread that the application and database
design are by far the biggest contributing factors to overall performance.
A little common sense, like filtering data on the server rather than the
client, goes a long way towards improving scalability and performance.
--
Hope this helps.
Dan Guzman
SQL Server MVP
news:1188644332.019526.110140@y42g2000hsy.googlegroups.com.. .
> On 31 Ago, 18:54, "William \(Bill\) Vaughn"
>
>> Given that SQL Server has the highest TPC-E benchmarks in the industry,
>> don't you think that the SQL Server team has made the TDS stream as
>> efficient as possible? IMHO, it's not the line protocol or the lowest
>> layers
>> of the interface that should be the focus of performance tuning, but the
>> applications, database designs and query methodologies that should
>> dominate
>> your attempts to improve throughput and scalibility. Reducing the traffic
>> on
>> the TDS channel will go a long way to improving performance if you have
>> to
>> move that much volume over the wire to make a difference.
> Don't know a lot about TPC-E benchmarks. Are they measured over a
> network?
>
> rj
>
Erland Sommarskog (esquel@sommarskog.se) writes:
> (raymond_b_jimenez@yahoo.com) writes:
>>> And, yes, while you would have seen a gross cut if TDS was UTF-8 on
>>> the wire and not UTF-16, a Chinese user would have seen an increase
>>> instead.
>>
>> Wouldn't it be great to have an option?
>
> If you thinks, submit this suggest on
> http://connect.microsoft.com/SqlServer/Feedback.
> Personally, I don't think it is worth the pain, also it would also
> require changes in the client APIs. And all it would affect is query
> batches sent to SQL Server and metadata sent back. If the query batches
> sent to SQL Server is killing your network, maybe you should look into
> using stored procedures.
Thinking of it, rather than having to select the character encoding, it's
better if the option was for compression of the network traffic in general.
But I find it difficult to believe that this would be a good option
for the traffic between a web server and an SQL Server that are on
the same LAN. It could possibly be an option if you are on a slow connection
over VPN. In general, I have a feeling that the network considerations
for SQL Server are for LAN connections, because that is surely the most
common scenario.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx
> Now, what can you do to reduce the amount the network traffic? If you
> feel that you don't need Unicode, use varchar for you character data
> and not nvarchar. (But keep in mind that the day when you need to support,
> say, Japanese may be closer in time than you think.) But most of all,
> trim your result sets from unneeded columns. Make sure that there are
> not a lot of "SELECT *" in your queries, and that you don't retrieve
> rows you don't need.
>
> Furthermore, network traffic is not only about bytes, but also about
> roundtrips. Don't get the details of the order, and then make one
> call for each product on the order, but get all data at once.
Pardon me for interjecting here Erland, I am not disagreeing with you but
agreeing, but this subject seems hard to tackle.
I have thought that network packet traffic contributes a lots, you ideally
want "package" as many results as you can into a packet (I mean this is
Nagle's algorithm) but I see precious little written on this subject. I see
nothing in Microsoft Whitepapers or anything else.
For example if I make a straight SELECT off a table for a Forward, ReadOnly
Cursor iusing ADO, what is the ideal Cache Size for maximum throughput?
It seems to me that it ought be Network Packet Size / Size of record. I am
sure there are some overhead bytes.
It ought to be determinable rather than just empirical guess work.
But how?
Thanks
Stephen Howe
> And as I've noted in another thread, SqlClient does not do this when
> you use it plainly. Run a plain ExecuteReader or DataAdapter.Fill and
> you will not see it. There was a link posted that lead to an article
> pointing out the problem with the CommandBuilder.
Can you get classic ADO and SQLOLEDB in combination not to do that?
Thanks
Stephen Howe
> In addition to agreement with others statements about refactoring the
> application to reduce network traffic
Sure. Dont disagree. I could arrange that I get 5 records back from a SP
query instead of 1 record back per query.
And it may well be that 5 records fit in a network packet.
That is 1 round-trip rather than 5 round-trips.
But it could be that I squeeze 10 records or 20 records etc.
How do we determine this without endlessly going round some design-cycle
trying magic numbers which immediately alter if the fields in the query
alters?
Thanks
Stephen Howe
Stephen Howe (stephenPOINThoweATtns-globalPOINTcom) writes:
>> And as I've noted in another thread, SqlClient does not do this when
>> you use it plainly. Run a plain ExecuteReader or DataAdapter.Fill and
>> you will not see it. There was a link posted that lead to an article
>> pointing out the problem with the CommandBuilder.
>
> Can you get classic ADO and SQLOLEDB in combination not to do that?
Yes, don't use it.
Seriously, it's is very difficult to get ADO only what it supposed to
and not a lot more. For instance, say that you set up a Command object
to call a stored procedure with a number of parameters. The first call
is clean, just a direct RPC call. But if you keep the object to call
the procedure again with different parameters, ADO now issues SET FMTONLY
ON to get some information about the result sets. And if you think that
NO_BROWSETABLE is bad, you should know that FMTONLY causes SQL Server to
sift through the statements in the procedure in a sort of half-NOEXEC
mode.
I did some quick testing with a some sort of general test app that I have
for ADO, and it appears that you get the browsetable stuff when you
request a lock type other than ReadOnly. If you perform your updates
through stored procedures, I see no reason why you should use anything
but ReadOnly and implement optimistic locking yourself.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx
IMHO, with all other things being equal designing for fewer round trips to
the server usually equals better scalability and usually better performance
as well. Note that these two things are NOT synonymous and can often be at
odds with each other - especially on the high end of each spectrum.
Performance analysis and tuning is a VERY complex and complicated process
when you are really trying to optimize an entire application. There are
HUGE numbers of moving parts and parameters within each subsystem. Usually,
however, an expert at this realm of problem solving can use knowledge,
experience and his/her toolset to quickly isolate the most egregious
offenders and point the dev/hardware team to the sections that are either
"low-hanging fruit" or will give the biggest bang for the buck.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Stephen Howe"
news:%23YrbnvM7HHA.3900@TK2MSFTNGP02.phx.gbl...
>> In addition to agreement with others statements about refactoring the
>> application to reduce network traffic
>
> Sure. Dont disagree. I could arrange that I get 5 records back from a SP
> query instead of 1 record back per query.
> And it may well be that 5 records fit in a network packet.
> That is 1 round-trip rather than 5 round-trips.
> But it could be that I squeeze 10 records or 20 records etc.
> How do we determine this without endlessly going round some design-cycle
> trying magic numbers which immediately alter if the fields in the query
> alters?
>
> Thanks
>
> Stephen Howe
>
>> Can you get classic ADO and SQLOLEDB in combination not to do that?
>
> Yes, don't use it.
>
> Seriously, it's is very difficult to get ADO only what it supposed to
> and not a lot more.
Well it is a stupid state of affairs that Microsoft invest 7 years in ADO -
and even now we dont fully know what it does - there is a heck of a lot
undocumented or poorly documented.
I am amazingly tired of playing the game, "ooohh, you have discovered some
flaws in our existing mature technology - why dont you shift to our brand
technology which does not have that flaw?".
Because it is only a matter of time before someone discovers that ADO.NET
has flaws and how long will it be before MS declares ADO.NET and .NET
legacy, they are bored with it, and start creating other new "cool
technology"? And then we are back to the same cycle. It is a crap state of
affairs.
There are limited ways of accessing databases - rowsets, SP's, input/output
parameters - why cant they sort it out so that if there are, say, 6
different methods, say, of retrieving data - we all know the overhead of
using each method - nothing is left undocumented - we all know under what
circumstances SET FMTONLY ON, NO_BROWSETABLE (which I have seen before - 4
years ago) are issued and why.
Does anyone really believe with ODBC, DAO, RDO, ADO, ADO.NET - each
generation has been an improvement?
They are all interfaces on the same set of database technology - which does
not change.
Stephen Howe
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)
------------------------------------------------------------ -----------------------------------------------------------
"Stephen Howe"
news:uPVphpj7HHA.1164@TK2MSFTNGP02.phx.gbl...
>>> Can you get classic ADO and SQLOLEDB in combination not to do that?
>>
>> Yes, don't use it.
>>
>> Seriously, it's is very difficult to get ADO only what it supposed to
>> and not a lot more.
>
> Well it is a stupid state of affairs that Microsoft invest 7 years in
> ADO - and even now we dont fully know what it does - there is a heck of a
> lot undocumented or poorly documented.
>
> I am amazingly tired of playing the game, "ooohh, you have discovered some
> flaws in our existing mature technology - why dont you shift to our brand
> technology which does not have that flaw?".
> Because it is only a matter of time before someone discovers that ADO.NET
> has flaws and how long will it be before MS declares ADO.NET and .NET
> legacy, they are bored with it, and start creating other new "cool
> technology"? And then we are back to the same cycle. It is a crap state of
> affairs.
>
> There are limited ways of accessing databases - rowsets, SP's,
> input/output parameters - why cant they sort it out so that if there are,
> say, 6 different methods, say, of retrieving data - we all know the
> overhead of using each method - nothing is left undocumented - we all know
> under what circumstances SET FMTONLY ON, NO_BROWSETABLE (which I have seen
> before - 4 years ago) are issued and why.
>
> Does anyone really believe with ODBC, DAO, RDO, ADO, ADO.NET - each
> generation has been an improvement?
> They are all interfaces on the same set of database technology - which
> does not change.
>
> Stephen Howe
>
"William Vaughn"
news:eVAfqhk7HHA.1900@TK2MSFTNGP02.phx.gbl...
> 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.
>
> >
> > Does anyone really believe with ODBC, DAO, RDO, ADO, ADO.NET - each
> > generation has been an improvement?
> > They are all interfaces on the same set of database technology - which
> > does not change.
> >
> > Stephen Howe
> >
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.
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
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."
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.
-ralph
> "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
> 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
William Vaughn (billvaNoSPAM@betav.com) writes:
> 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, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx
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)
------------------------------------------------------------ -----------------------------------------------------------
"Ralph"
news:OR6qUIm7HHA.1164@TK2MSFTNGP02.phx.gbl...
>
> "William Vaughn"
> news:eVAfqhk7HHA.1900@TK2MSFTNGP02.phx.gbl...
>> 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.
>>
>
>> >
>> > Does anyone really believe with ODBC, DAO, RDO, ADO, ADO.NET - each
>> > generation has been an improvement?
>> > They are all interfaces on the same set of database technology - which
>> > does not change.
>> >
>> > Stephen Howe
>> >
>
> 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.
>
> 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
> 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."
>
> 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.
>
> -ralph
>
>
>
raymond_b_jime...@yahoo.com a =E9crit :
> 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 ...
bzh_29 (xiii29@free.Fr) writes:
> 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, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx
> 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 ...
bzh_29 wrote:
>> 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 ...
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.
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
bzh_29 (xiii29@free.Fr) writes:
> 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, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx
On Thu, 27 Sep 2007 08:54:30 -0700, bzh_29
>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 !
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