Slow access on XP using ADO with ODBC to SQL Server 2000 - any ideas?

[Daniel Crichton]

I've got one PC here running XP Pro SP2 that is acting "weird". I have an
application that uses ADO via an ODBC DSN to do SELECTs and UPDATEs on some
records in a table. Until 2 days ago this application ran perfectly well on
this PC, but now runs very slowly (tests indicate over 100 times slower)
than before. Switching to Named Pipes cures the problem, but I'd rather try
and keep all the machines here configured the same way using TCP/IP. All
other XP Pro and 2000 Pro machines I've run this app on using TCP/IP works
fine.

Another application using the same database code, just dealing with a
different set of records (actually has a larger ForwardOnly recordset in
memory that it loops through to feed the recordset that does the SELECTs and
UPDATEs, around 12000 items compared to 4500 in the one that's running
superslow), runs as quickly as expected using the same DSN when set to
TCP/IP. This is really confusing me - I've checked everything I can think of
on the machine itself (firewall is off, updates applied, MDAC checks out,
etc).

I guess I'm looking for a way to check, and replace with originals, any DLLs
or other files that are used by the SQL Server ODBC driver over TCP/IP that
are not used when connecting via Named Pipes. Any ideas?

Dan

[Dave Patrick]

Just a thought but you might try changing the SQL server name in the
connection string to the server's IP address as a test. Also make sure that
tracing is turned off in Data Sources ODBC administrator.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

[Daniel Crichton]

Dave wrote on Fri, 28 Apr 2006 16:41:09 -0600:

> Just a thought but you might try changing the SQL server name in the
> connection string to the server's IP address as a test. Also make sure
> that tracing is turned off in Data Sources ODBC administrator.
>

First thing I did was try the IP. Tracing is also definitely not enabled.

Dan

[Stephen Howe]

> Until 2 days ago this application ran perfectly well on
> this PC, but now runs very slowly (tests indicate over 100 times slower)
> than before.

The obvious thing to ask (I am sure you have asked it), is, "whats changed?"

The first thing I would check is see if any of the Microsoft patches have
been applied automatically.
Because I have noticed that every now and then, someone complains about a
security patch having some adverse side effect. If something has been
applied I would be tempted to turn automatic updates to notify (or off),
uninstall patches and see if situation improves.

Another thing I would be tempted to do (just for inspiration, fishing for
clues) is get a complete listing of files that have changed from those 2
days ago onwards. Anything in System32 directory I wold focus on.

Stephen Howe

[Daniel Crichton]

All XP patches had been installed. What I'm at a loss to explain is why one
copy of the app works, yet the other doesn't. I have also tested the copy
that has problems on this machine with another that is near identical - same
hardware, same patches - and it doesn't have a problem. I guess I'll just
have to stick with Named Pipes for now and try to nail down the issue some
other time when Named Pipes no longer works properly ... not the ideal
situation, but this machine is in use 24/7 and I don't have another spare to
use while attempting to find the source of the problem. I was hoping someone
else might have seen something similar and be able to give me a starting
point. If I find the cause I'll post the solution.

Dan

[Stephen Howe]

> All XP patches had been installed. What I'm at a loss to explain is why
one
> copy of the app works, yet the other doesn't. I have also tested the copy
> that has problems on this machine with another that is near identical -
same
> hardware, same patches - and it doesn't have a problem. I guess I'll just
> have to stick with Named Pipes for now and try to nail down the issue some
> other time when Named Pipes no longer works properly ... not the ideal
> situation, but this machine is in use 24/7 and I don't have another spare
to
> use while attempting to find the source of the problem. I was hoping
someone
> else might have seen something similar and be able to give me a starting
> point. If I find the cause I'll post the solution.

I sympathise. I have been in this situation. There has to be a difference.
Is it worthwhile to run some TCPIP configuration diagnostics?
I would be looking for static diagnostics - something to run on both
machines and try and isolate any factor that is different.

Stephen Howe