strange performance-problems after upgrading from ADO 2.5 to ADO 2.6

strange performance-problems after upgrading from ADO 2.5 to ADO 2.6

Post by T?tt » Sat, 06 Apr 2002 18:54:50



Hello!

After installing MDAC 2.6 SP1 over MDAC 2.5, we've got performance problems.
Scenario:
IIS4 with ASP, connecting to the SQL-Server 7.0 on the same machine per
ODBC.

With ADO 2.6 some queries (not all) are running nearly 50 times slower than
before.
Analyzing these queries with the Query Analyzer showed no problems, they all
use Indices, no table scans.

When changing from ODBC/DSN to a no-DSN-Connection, the perfomance is as
fast as before using ADO 2.6.

Slow on ADO 2.6 and fast on ADO 2.5:

conn.Open "DSN=Test"
...queries...

Fast as with ODBC and ADO 2.5:

conn.open "PROVIDER=MSDASQL;DRIVER={SQL
Server};SERVER=localhost;DATABASE=dbTest;UID=username;PWD=sagichdirnicht;"

or:

conn.Open "Provider=SQLOLEDB.1;User
ID=username;password=sagichdirnicht;Initial Catalog=dbTest;Data
Source=localhost"

DeInstalling ADO 2.6 resulted in SQL Server 7.0 not working anymore.

We don't want to change from ODBC/DSN to a DSN-less connection, because
there are minor changes in the behaviour at some queries, we could not solve
(no sources).

Anybody else who got so strange results or even better, a solution to this
problem?

Greetings, Chris.

 
 
 

strange performance-problems after upgrading from ADO 2.5 to ADO 2.6

Post by Bob Pfeiff [MS » Sun, 07 Apr 2002 01:04:41


Do SQL Profiler traces look different for the different connections?

--
Bob Pfeiff
Microsoft Consulting Services

This posting is provided AS IS, with no warranties, and confers no rights.

 
 
 

strange performance-problems after upgrading from ADO 2.5 to ADO 2.6

Post by T?tt » Sun, 07 Apr 2002 01:38:05




Quote:> Do SQL Profiler traces look different for the different connections?

It would be helpful to know what's the target of this action.
Is a profiled run really necessary? What options should be used?
What's your assumption?

No hurry, I'll be back Monday.

Greetings, Chris.

 
 
 

strange performance-problems after upgrading from ADO 2.5 to ADO 2.6

Post by Bob Pfeiff [MS » Sun, 07 Apr 2002 10:22:52


Data access APIs like ODBC and OLE DB will do different things between
versions, the actual calls to SQL Server can be done in several different
ways (sp_prepexec/sp_execute, sp_executesql,
sp_cursoropen/sp_cursorfetch/sp_cursorclose, straight SQL batch).  I usually
check Profiler output if I have any questions about what is happening
between a client application and SQL Server.  You can just run a default
trace in profiler to get this info.  I don't have a particular assumption,
other than you're experiencing different behavior with different client
configuration and Profiler is one way to gather information.

--
Bob
Microsoft Consulting Services
------
This posting is provided AS IS with no warranties, and confers no rights.

 
 
 

strange performance-problems after upgrading from ADO 2.5 to ADO 2.6

Post by T?tt » Thu, 11 Apr 2002 22:43:48


Thank for your explanations.

The problem has been solved.
After reconfiguring the DSN from TCP/IP to Named Pipes, all things run as
fast as before.

We changed manually nothing, and i don't know, which protocol was used
before, seems like the default has changed and that tcp/ip has much more
overhead, at least when the sql-server is on the same machine.

Greetings, chris.



Quote:> Data access APIs like ODBC and OLE DB will do different things between
> versions, the actual calls to SQL Server can be done in several different
> ways (sp_prepexec/sp_execute, sp_executesql,
> sp_cursoropen/sp_cursorfetch/sp_cursorclose, straight SQL batch).  I
usually
> check Profiler output if I have any questions about what is happening
> between a client application and SQL Server.  You can just run a default
> trace in profiler to get this info.  I don't have a particular assumption,
> other than you're experiencing different behavior with different client
> configuration and Profiler is one way to gather information.

> --
> Bob
> Microsoft Consulting Services
> ------
> This posting is provided AS IS with no warranties, and confers no rights.

 
 
 

strange performance-problems after upgrading from ADO 2.5 to ADO 2.6

Post by Bob Pfeiff [MS » Fri, 12 Apr 2002 00:10:35


If your app is on the same machine as the SQL Server, then it can use a
local named pipe to the SQL Server an bypass the network altogether.  This
is probably why you see the difference between Named Pipes and TCP/IP.

--
Bob
Microsoft Consulting Services
------
This posting is provided AS IS with no warranties, and confers no rights.

 
 
 

1. Problem in installing ADO 2.6 onNT 4.0 server having ADO 2.5

Hello,
I am having a problem in installing ADO 2.6 (through MDAC 2.6) on a Windows
NT 4.0 Server having MDAC 2.5 already installed. The setup is completed
without any problem but ADO 2.6 is not getting installed.

Has anyone faced similar problem Or knows the solution to resolve this
problem?

Thanks,
Prasad

2. 9158-MD-Baltimore-ORACLE-PL/SQL-Windows NT-Senior Programmer/Analyst

3. Any problems Upgrading ADO 2.1 to ADO 2.5?

4. Does SQL Server support calls to .Net DLLs?

5. ADO 2.5 or ADO 2.6

6. DTS with Exchange Server 2000

7. register error ADO 2.5 and ADO 2.6

8. Access 97 vs 2k

9. Strange error after upgrading to ado 2.5

10. ADO 2.5 to 2.6 problem !!!

11. ADO 2.5 vs 2.6 problems

12. Upgrading from ADO 2.1 to ADO 2.5

13. URGENT: Instalation Problems with ADO 2.6 and ADO 2.6 sp1