VB6 ADO connection SQL Express 2005 SP2 - severe lag with network connection enabled
- From: hboothe@xxxxxxx
- Date: 28 Mar 2007 21:13:20 -0700
Hello, all, I'll try to make this as concise as I can. I have a
problem with my VB6 app running on Vista Home Premium (Athlon 3800+, 2
GB, brand new, blah blah blah). I have an instance of SQL Express
2005 SP2 running on the system. I just (yesterday) installed my
application on it and then installed the system at a client's site
today. The system was running slowly (relative to how it runs on my
XP SP2 dev box with MSDE 2000 (current sp...4?). I tried many things
for hours but never got anywhere. Here's what I have done, briefly,
and what the situation is:
Two computers, connected via crossover cable. One is the Vista system
in question, one is XP SP2. The Vista system has a NIC set to
192.168.2.1 which connects via crossover to the XP system at
192.168.2.2. Subnet is 255.255.255.0. The Vista system has a
wireless adapter that connects to a Linksys router to share a DSL
connection. The wireless IP is 192.168.1.103, also subnet
255.255.255.0. So, the networks are separate.
On the Vista system (with Express 2005 SP2) I have enabled Shared
Memory (of course, it's always on), TCP/IP (port 1433, all IP), and
Named Pipes.
The XP system, connecting over the network (crossover) connects
perfectly. Same program (EXE on both systems). It does NOT exhibit
ANY of the problems I'm about to describe on the Vista system with the
SQL Express 2005 SP2 installed.
When I pull information in one query (returns about 30-50 records out
of about 4000) the performance varies tremendously depending on a few
factors. First, I have a high-res timer function that I use to
measure the response time of some queries. On the XP system, that
query takes about 0.4 seconds to 0.8 seconds (over the wire). On the
Vista system it takes 3 minutes 58 to 59 seconds, *EVERY* time I ran
it (about a dozen times today, with the same settings, the same
query). Which makes me think it's a timeout issure. So, to cut to
the chase, in the process of troubleshooting this I disabled BOTH the
wired and wireless network adapters. When they are BOTH disabled the
query returns in about 0.4 seconds to 0.5 seconds. Enabling one or
both network adapters causes the dramatic slowdown to return.
I am using a DSN-less connection with a connection string like this:
Driver={SQL Server};server=(local)
\instancename;database=dbname;Uid=sa; Pwd=**********
I have tried: disabling all network protocols (leaving only shared
memory), changing the server= line to period "." \instancename,
server= to 127.0.0.1\instancename. The only difference is that when I
use the server=127.0.0.1\instancename the response time goes from 3
minutes 59 seconds to 1.3 seconds. MUCH better, sure, but still not
as good as the 4 tenths or so seconds they see on their XP system.
I'm spent, I sat in their office for 12 hours today with witnesses to
the whole slaughter. The only thing I can guess is my connection
string is crap or that I missed some client config setting (strange
since I'm running it on the box so I can use shared memory which I
thought was the creme de la creme. But I like Busch beer, too, so
there's no telling about me. :) Anyway, if there's some pertinent
detail I'm leaving out that I should have obviously included I'm
sorry, I'll try to fill in what I missed if you ask.
Any help is appreciated. Thank you for your time.
--HC
.
- Follow-Ups:
- Re: VB6 ADO connection SQL Express 2005 SP2 - severe lag with network connection enabled
- From: Bob Barrows [MVP]
- Re: VB6 ADO connection SQL Express 2005 SP2 - severe lag with network connection enabled
- Prev by Date: Re: Updating a table and row locking.
- Next by Date: Re: WHILE LOOP NOT WORKING IN INCLUDE ASP FILE - LOOPING FOREVER
- Previous by thread: Updating a table and row locking.
- Next by thread: Re: VB6 ADO connection SQL Express 2005 SP2 - severe lag with network connection enabled
- Index(es):
Relevant Pages
|
|
