sql server slower than a hound dog on an August day in Georgia

[scroce]

This may be a question for the Access forum, but I'll try posting here first.

I've successfully split a large access database, imported it's tables into SQL Server and connected from my client machine.

Basically did what this link instructs successfully.

http://www.roimail.com/help/linking_mssql.html

Problem is now that it's so slow as to be unusable! I don't understand why! I'm using an unused PC with tons of space on it (like 30 GB), it's a 1 gig processor - it's plugged into my local network. I use resources from it on other applications (it acts as a print server and a test web server, and also a server for another small access application) and it runs very quickly.

I've been scanning the articles for a possible solution, but none so far, so I humbly bring my post to you. For the above reasons, I'm skeptical that it's network problems. What else can I check?




I am a nobody, and nobody is perfect; therefore, I am perfect.

 

[scroce]

I was accessing it from another machine on the local network.

Still haven't had a chance to disconnect the router and try it. I'll continue this thread with that result.

I am a nobody, and nobody is perfect; therefore, I am perfect.

 

[scroce]

Today I was able to disconnect the router, and it provided inconclusive results. It basically exhibited inconsistant behavior. By doing this, sometimes the ODBC connection would time out and wouldn't connect at all, but if I tried it again, it would find it, but no faster than before. The only discernable differences were that;

a. Sometimes the SQL server connection timed out or showed up as unavailable, and sometimes it worked. Usually, it failed on the first request, but would respond on the second request, albeit slowly.

b. I noticed that when it did respond, the processor of my machine would churn a lot more than it ususally did when the router was connected. - however the response time was not faster. - I tried updating the hosts file with the computername/ip address combo of the server machine, but it still couldn't find it any faster

I am not sure, but this seems to be telling me that our WAN connection must be playing some part in causing the bottleneck.

I'm not really any closer to a solution.

I am a nobody, and nobody is perfect; therefore, I am perfect.

 

[JayKusch]

Hmmm ... have has similar issues w/ connectivity before. I would double-check that both the client machine and the server are not starved on resources especially in the memory area!

Thanks

J. Kusch

 

[scroce]

they're pretty decent computers. They are PC's, but i'm only using them for a test environment. They're all 1 gig processors, not the fastest, but not slow. None of them are running any heavy duty memory hogging apps. All are at least 256k memory.

As I mentioned in some of the above threads, I tested the same thing on another network on inferior machines and it ran what I would consider normally.

aaaargh! very frustrating. I'm trying the route of installing 2k Server and and SQL server enterprise edition, but this has the downside of having to get corporate approval for the machine to join our domain, which could take a long time. - Will hopefully post back with those results.

again, I say aaaargh!!!



I am a nobody, and nobody is perfect; therefore, I am perfect.

 

[scroce]

no dice on corporate approval. Guess I'm stuck at this point, unless anyone has any other ideas.

I am a nobody, and nobody is perfect; therefore, I am perfect.

 

[darkwing75]

Did you try using the latest MDAC drivers?

 

[scroce]

no - I assume they are downloadable from microsoft?

So far I've chalked this problem up to our network config for now - that's the only way I can explain normal behavior on our other network, and poor performance here..

but maybe it is a driver issue.

I am a nobody, and nobody is perfect; therefore, I am perfect.

 

[SQLBill]

Yes, you can download the latest MDAC (2.8 I believe) from Microsoft's website.

-SQLBill

 

[TheStriker]

scroce,

Your problem cannot be solved by any network configurations, drivers, or MDAC installations whatsoever. Fist of all, the appropriate MDAC is automatically installed when you install SQL Server. You have Microsoft to thank for your problems. The problem is that SQL Server Personal Edition was designed to slow down when a certain number of people request connectivity. Microsoft figured that in order to sell its Enterprise version of SQL Server, it had to limit the capabilites of its Personal Edition, most notably the performance. They didn't want people to use the have the benefits of the Enterprise Edition while using the much cheaper Personal Edition. It's just business. So unless you upgrade, you will continue to have these problems. Microsft's most successful pitch was always strongly suggesting users to upgrade and this is one of those cases. Sorry, I didn't read your post earlier, I guess I could've saved you alot of time and grief. However, if you feel indifferent to my observation, feel free to continue to plug at this problem. GOOD LUCK!

-You cannot solve a problem with the same level of thinking that created it.

 

[scroce]

Striker - I've read about that "feature" of personal edition - However, at this phase, I'm the only one accessing the SQL server. The problem comes from when I try to connect from another node on the network other than the physical machine it's loaded on.

Here's what confounds the "by design" theory - I had done it successfully across another completely different network that doesn't have a WAN.

But I think you are correct in that I don't think this issue is solvable. I still think it's something to do with the WAN, but who knows what at this point.

I am a nobody, and nobody is perfect; therefore, I am perfect.

 

[TheStriker]

It logs the nodes that connect to it in a special file. So even if you use the same user account to log on but access the Server from different nodes it logs it as a separate connection. So if you had 3 successful logins using the same account, it doesn't matter it still sees it as 3 separate connections. This is to keep people from thinking using the same user account will workaround the problem. It still logs those connections as concurrent connections and by design, slowed the performance when a certain number is reached.

 

[scroce]

right, but then in that scenario you are talking about concurrent connections, aren't you? I never had more than one connection open at one given time, and it was still slow.

I am a nobody, and nobody is perfect; therefore, I am perfect.

 

[TheStriker]

If I remembered correctly, I believe you said that at first it was working fine and then became slow after a while, most notably after attempting to connect with another node. If this was the case then it logged those connections like I said in the previous post. The problem should've started when you tried to connect with a different computer. If you had not tried to connect with a different computer, you would not have had the problem. So even though there was one connection at a time, it still logged the previous connections and added them to the current one. In other words if you had three previous (one-at-a-time) connections and you are connecting a fourth time, SQL Server sees it as FOUR connections and responds accordingly.

 

[scroce]

ok - sorry if I was unclear before.

so yes, it should see that there are too many concurrent connections and then slow down accordingly - this is by design.

but the issue here is that I can connect with no problem from a database located on the same physical machine as my SQL server installation. When I close that connection and try to connect from any other local node on my WAN then it's a dog. There is only ever 1 connection happening at once. I never bothered trying to have more than one, since I couldn't even get one connection that wasn't on the physical machine to operate at a normal speed.

What's confounding is that I moved everything to an entirely separate network that I happen to have access to, which is not on a WAN. I set up everything identically. I was able to make connections there from any node with no slowdown.

So that would seem to point to something about my network, and work against the theory of slowdown by design, wouldn't it?





I am a nobody, and nobody is perfect; therefore, I am perfect.

 

[jgoodman00]

Yes I agree. I think it is a network problem.

Can you describe the network setup in detail please?

James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures

 

[scroce]

OK - we are a small remote office of a big company. we don't have a server here. we are on one large domain. There are 7 wind 2k workstations here, all linked with cat5 cable going into a cabletron "smart hub", which in turn is linked to a cisco router, which in turn is linked to a larscom split-T-1 DSU which routs things like lotus notes email and certain network connections across a 256k private line to our main servers in a far away city. We log in to a win2k server system there which does all authenication and push-down updates, etc.

In some ways, our little network here acts kind of like a peer to peer. I can go \\neighbor'scomputer\c$ and move things back and forth. If I tracerout a neighbor's pc, it only shows one hop - directly to that PC -

We have a little networked database app that resides on one of my win2k boxes, which everyone connects to and access information on - works fine - no slowdowns - this little app seems to use .mdb files to store its data.

We have two network printers here which utilize IP address from the companywide scope.

(where I get confused a lot) The grey areas seem to be when the network is acting like a peer to peer and when it's acting like a WAN.

I am a nobody, and nobody is perfect; therefore, I am perfect.

 

[jgoodman00]

Can you detail the IP address ranges in use?

I presume your office is on a seperate subnet?

James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures