SET NO_BROWSETABLE ON ?

I've noticed in my .NET application (with ADO) that whenever a query
is done to SQL Server, a query executing
SET NO_BROWSETABLE ON
is done before.
This has an huge impact on performance, as the round-trip to the
server takes the same time as the correct query. I believe that if
"SET NO_BROWSETABLE ON" wasn't executed, performance would almost
double and network traffic would be reduced.
I've tried to find a reasonable answer on the net for this, but
haven't managed. Has someone noticed this and knows how to correct it?

rj

Re: SET NO_BROWSETABLE ON ?

raymond_b_jimenez@yahoo.com wrote:
> I've noticed in my .NET application (with ADO)

Classic ADO? I.E., with ADODB?
or ADO.Net?

I was going to paste in my standard "dotnet" reply, but I just noticed
that you crossposted to some dotnet groups. You need to be aware that
classic (non-dotnet) ado and ado.net are two very different things. This
question is off-topic in one of the ado groups you included in your
crosspost. It is also off-topic in the sqlserver.server group. Since I
am posting via msnews, this reply will not likely make it to the comp
group. When you follow up to this, please remove the off-topic groups
from your post.


> that whenever a query
> is done to SQL Server, a query executing
> SET NO_BROWSETABLE ON
> is done before.
> This has an huge impact on performance, as the round-trip to the
> server takes the same time as the correct query. I believe that if
> "SET NO_BROWSETABLE ON" wasn't executed, performance would almost
> double and network traffic would be reduced.
> I've tried to find a reasonable answer on the net for this, but
> haven't managed. Has someone noticed this and knows how to correct it?

I've never noticed this, but i am sure the technique used to execute
your queries might have something to do with it. Be sure to post a small
repro script to whichever group you follow up with

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: SET NO_BROWSETABLE ON ?

raymond_b_jimenez@yahoo.com wrote:
> I've noticed in my .NET application (with ADO) that whenever a query
> is done to SQL Server, a query executing
> SET NO_BROWSETABLE ON
> is done before.
> This has an huge impact on performance, as the round-trip to the
> server takes the same time as the correct query. I believe that if
> "SET NO_BROWSETABLE ON" wasn't executed, performance would almost
> double and network traffic would be reduced.
> I've tried to find a reasonable answer on the net for this, but
> haven't managed. Has someone noticed this and knows how to correct it?
>
This may help:
http://www.vsj.co.uk/dotnet/display.asp?id=249
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: SET NO_BROWSETABLE ON ?

(raymond_b_jimenez@yahoo.com) writes:
> I've noticed in my .NET application (with ADO) that whenever a query
> is done to SQL Server, a query executing
> SET NO_BROWSETABLE ON
> is done before.
> This has an huge impact on performance, as the round-trip to the
> server takes the same time as the correct query. I believe that if
> "SET NO_BROWSETABLE ON" wasn't executed, performance would almost
> double and network traffic would be reduced.
> I've tried to find a reasonable answer on the net for this, but
> haven't managed. Has someone noticed this and knows how to correct it?

Which .Net Data provider do you use?

ADO (the one is not worthy to be called Classic, but which is not .Net)
spits this out, and it is about impossible to stop.

SqlClient does not send this by default, if you use CommandBehaviour.KeyInfo
it does.


--
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

Re: SET NO_BROWSETABLE ON ?

Actually, I use ADO.Net. Don't recall using KeyInfo anywhere.
http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B 178366
gives some good information, but the interesting part is:
"SET NO_BROWSETABLE ON is an undocumented option performed for Remote
Data Service (RDS) ActiveX Data Connector (ADC) connections to SQL
Server. Enabling this option..."
Where would the option be? I've looked for it, but no luck.
Has anyone managed to get rid of these "undocumented options"? And
measured the results, as it seem's quite promising?

rj


On 29 Ago, 22:38, Erland Sommarskog wrote:

> Which .Net Data provider do you use?
>
> ADO (the one is not worthy to be called Classic, but which is not .Net)
> spits this out, and it is about impossible to stop.
>
> SqlClient does not send this by default, if you use CommandBehaviour.KeyInfo
> it does.

Re: SET NO_BROWSETABLE ON ?

> ADO (the one is not worthy to be called Classic, but which is not .Net)
> spits this out, and it is about impossible to stop.

Well it may be able to do it but the problem is there is little in the way
of documentation about what ADO does under the hood and what choices affect
what SQL code that gets executed.
It could be that some dynamic proerty changes this.
Over the years, MS documentation has improved on ADO. It is still not good
enough.

Stephen Howe

Re: SET NO_BROWSETABLE ON ?

(raymond_b_jimenez@yahoo.com) writes:
> Actually, I use ADO.Net. Don't recall using KeyInfo anywhere.

Which data provider do you use? SqlClient, OleDb or Odbc?

> http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B 178366
> gives some good information, but the interesting part is:
> "SET NO_BROWSETABLE ON is an undocumented option performed for Remote
> Data Service (RDS) ActiveX Data Connector (ADC) connections to SQL
> Server. Enabling this option..."
> Where would the option be? I've looked for it, but no luck.
> Has anyone managed to get rid of these "undocumented options"? And
> measured the results, as it seem's quite promising?

That article was written for 6.5 and a very early version of ADO, so there
is not much guidance there.


--
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

Re: SET NO_BROWSETABLE ON ?

On 29 Ago, 22:38, Erland Sommarskog wrote:

>
> SqlClient does not send this by default, if you use CommandBehaviour.KeyInfo
> it does.
>
For all the docs I've seen till now, KeyInfo really seems like the
most interesting option. A couple of remarks:
1-I use an ExecuteReader with no parameters, so no CommandBehavior is
being used.
2-I do not see any "SET FMTONLY OFF" being executed, despite some
people complaining about that on the net. If one sees
http://msdn2.microsoft.com/en-us/library/system.data.command behavior.aspx
it would also be appearing in my case.

I'm now looking into the SqlCommand and SQLConnection classes to see
if something can make the "SET NO_BROWSETABLE ON" disappear. Anyone
got a clue?

rj

Re: SET NO_BROWSETABLE ON ?

(raymond_b_jimenez@yahoo.com) writes:
> For all the docs I've seen till now, KeyInfo really seems like the
> most interesting option. A couple of remarks:
> 1-I use an ExecuteReader with no parameters, so no CommandBehavior is
> being used.
> 2-I do not see any "SET FMTONLY OFF" being executed, despite some
> people complaining about that on the net. If one sees
> http://msdn2.microsoft.com/en-us/library/system.data.command behavior.aspx
> it would also be appearing in my case.
>
> I'm now looking into the SqlCommand and SQLConnection classes to see
> if something can make the "SET NO_BROWSETABLE ON" disappear. Anyone
> got a clue?

Since I don't see SET NO_BROWSETTABLE ON in my test setup, it's a bit
difficult to advice. Maybe you can post a sample program which produces
the dreaded NO_BROWSETABLE? Preferrably this should be a simple
command-line program without GUI.


--
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

Re: SET NO_BROWSETABLE ON ?

There is some hope... I've put my code in a new test environment, and
it does not issue "SET NO_BROWSETABLE ON". I've debugged the situation
further, and found some interesting differences doing some networking
debugging (using Wireshark):

-When I connect in the production environment, the one that gets "SET
NO_BROWSETABLE ON", the "App Name" is set to "Microsoft (R) .Net
Framework" and "Library Name" is set to OLEDB
-When I connect in the testing environment, both "App Name" and
"Library Name" are set to ".Net SqlClient Data Provider".

The "App Name" that appears in Wireshark is the same that appears in
SQL Profiler.

An adapted code example follows:
-----------------------------------------
Dim SQLConx As SqlConnection = New SqlConnection
Dim SQLCom As SqlCommand = New SqlCommand
Dim SQLDR As SqlDataReader

Try
SQLCom.Connection = SQLConx
SQLCom.CommandType = System.Data.CommandType.StoredProcedure
SQLCom.CommandText = "myStoredProcedure"
SQLCom.Parameters.Add("@param", _param)
SQLConx.ConnectionString = SQLConnectionString
SQLConx.Open()
SQLDR = SQLCom.ExecuteReader()
SQLConx.Close()
Catch exc As Exception
-----------------------------------------

What's stranger is the OLEDB reference. I'm not using it in my code,
but it appears in the network trace. What might I be missing?

rj

Re: SET NO_BROWSETABLE ON ?

(raymond_b_jimenez@yahoo.com) writes:
> -When I connect in the production environment, the one that gets "SET
> NO_BROWSETABLE ON", the "App Name" is set to "Microsoft (R) .Net
> Framework" and "Library Name" is set to OLEDB

Apparently you are using OleDbConnection etc in production. With OleDb
Client you may experience NO_BROWSETABLE more often.




--
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