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.

 

[jgoodman00]

Ok,

Are you trying to open a table directly from access?
If so, how many rows does it have?
How big are the rows?

Do you have access to Query Analyzer?

James Goodman MCSE, MCDBA

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

 

[scroce]

the tables are of all different sizes - I used the link manager to link them to the DSN I created on my PC that points to the SQL Server data store.

As a test I just created a test link in a new and completely different mdb file to the Northwind example databases' employees table, which is like 9 records of 15 columns - and that runs really slow as well (like maybe 20 seconds to open)- so you can imagine what happens when a big table tries to open or is queried

Yes - I do have access to the query analyzer.


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

 

[jgoodman00]

So, how long does it take to open a table inside query analyzer?

James Goodman MCSE, MCDBA

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

 

[scroce]

no discernable delay. which would lead you to believe it has to do with my network - but then how come everything else runs quickly?


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

 

[jgoodman00]

Can you install QA on a client so you can test the network connectivity?

James Goodman MCSE, MCDBA

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

 

[scroce]

I'm not sure how I do that - but let me guess

I can probably run setup off of the CD and choose to install only client services?

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

 

[SQLBill]

I believe this is the problem: "...it acts as a print server and a test web server, and also a server for another small access application...".

Best practice is to NOT have any other application on the server used for SQL Server. SQL Server is a memory hog, it wants to use all the memory available. If other applications are using memory, SQL Server will be e-x-t-r-e-m-e-l-y s-l-o-w.

For an SQL Server with other applications, your machine isn't very powerful.

-SQLBill

 

[scroce]

no, you're right - it's not even really a server machine. But in our particular office, only 5 people would only ever hit it at one time max, and even that's unlikely.

as far as a mem hog, you're right about that, I sql server.exe eats up 20,000k of mem usage, and the next closest it 8,000k

But I don't notice any degradation on the machine itself, or on any of the other things running.

I installed my access front end on that machine, and it works at normal speed.

But when I go to my pc, which is in on the same network, it's a dog.

maybe it has something to do with the fact that our entire office is on a WAN.

Could it be that even though we're on the same network here and I can go \\ComputerName\Resource, that the packets have to go out over our T-1 to our Domain Controller,(in Tennesee) which has to send the packets all the way back to my other PC?

We have a hub, but no server here( in NJ), and we're all behind a router connected to our main domain in Tennesee.

I am unsure if our network is acting as a simple peer to peer or if it is acting as a WAN.

Anyone care to weigh in on that one?




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

 

[jgoodman00]

Could it be that even though we're on the same network here and I can go \\ComputerName\Resource, that the packets have to go out over our T-1 to our Domain Controller,(in Tennesee) which has to send the packets all the way back to my other PC?

Ahh, making sense now. That sounds like your problem.

Can you not get the sys admin to create a local DC, & a local site? That way you should be able to isolate the traffic, reducing the T1 usage & greatly improving performance...

James Goodman MCSE, MCDBA

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

 

[scroce]

i wish. They're not going to let me do that, budget constraints, red tape, blah blah. I'm on my own out here (has good and bad merits)

do really think that's it? - if that's the case, why do my MS access applications work just fine?

For example, if I split a database and put it's tables on my other machine, and link to them, thru link manager it doesn't seem to run slowly - That would lead me to believe that a split mdb file is more efficient in terms of network overhead than is an ODBC connection to SQL server - can that conclusion possibly make sense?

maybe that's what I'll have to do to share my database, but I was under the impression that this was not the thing to do, since access doesn't handle multiple users very well.


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

 

[jgoodman00]

i wish. They're not going to let me do that, budget constraints, red tape, blah blah. I'm on my own out here (has good and bad merits)

Ok, although you must be saturating an expensive T1 connection with unnecessary traffic.

But in our particular office, only 5 people would only ever hit it at one time max, and even that's unlikely.

So you want to host SQL server on your LAN, & let people connect to it across the WAN?

For example, if I split a database and put it's tables on my other machine, and link to them, thru link manager it doesn't seem to run slowly - That would lead me to believe that a split mdb file is more efficient in terms of network overhead than is an ODBC connection to SQL server - can that conclusion possibly make sense?

No, a SQL server connection will be much more efficient than, for example, a Jet connection.



I think this could be down to the protocol being used to connect. If you do a tracert to your slow sql server, what hops are made? I suspect it is going through the WAN, to the DNS server, then back across the WAN to your local SQL server. This being so, assuming the PC running SQL server is running Win2K server (which it will be if you are running Standard Edition or higher), you could install the DNS service on this computer. I think your first aim should be to reduce the amount of traffic being sent over the WAN...


P.S. Adding a few extra services to your current local server, & getting it setup as a seperate site in AD will take a little configuring, but should improve performance greatly, as well as reducing WAN saturation...

James Goodman MCSE, MCDBA

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

 

[scroce]

hey james.

I did a tracert (good suggestion) and it was only one hop - directly to the IP of my server. - hmmmmm - so does that mean it went thru the wan or not?

Ok, although you must be saturating an expensive T1 connection with unnecessary traffic.

- what can I say - it was this way when I got here.

So you want to host SQL server on your LAN, & let people connect to it across the WAN?

No - only the other 5 users here locally in this office need connectivity.


also, one thing I didn't mention in this post - this is SQL Server Personal Edition, installed as a test on a win2k professional Box - why? - budget constraints, red tape, blah, blah -

Tried to set up 2000k server on a new domain in this office, but was having trouble connecting due to network configuration issues, so I abandoned this for the time being in favor of this solution - i figured since the traffic isn't heavy (yet) I could test it this way just to make sure everything worked without tackling networking problems




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

 

[scroce]

OK I think I figured it out - It doesn't have anything to do with the network configuration. It has to do with the fact that when connecting an access database to an SQL server, you shouldn't use ODBC.

Apparently this adds an extra layer of libraries to churn through which is the reason for the slowness.

The way you by-pass odbc DSNs is as follows:

You can't use your standard mdb file. Access has a special variant file called a "project file", and has the extension .adp You can get to it by going file>new and choosing new project file (existing data) or (new Data)

You are then prompted for the name of the server you wish to connect to that is running SQL Server, and then for the data store name. Very simple screen. You press OK, and you are off and running.

here is the wording of the help screen that you get when you type "adp" into microsoft access 2002 help:

Create a SQL Server database and connect an Access project to it

In the New File task pane, under New, click Project (New Data).

In the File New Database dialog box, click a location in the Save in box, and type a name in the File name box; make sure that Microsoft Access Projects appears in the Save as type box, and then click Create.

In the Microsoft SQL Server Database Wizard, type the name of the server you would like to use, the logon ID and password of an account with CREATE DATABASE privileges on the server, and the name of the new SQL Server database.

Click Next.

If you are creating a Microsoft SQL Server 6.5 database, enter the database device and size, the transaction log device and size, the database size, and the log size. You don't need to specify devices and sizes for Microsoft SQL Server 7.0 or later.

Click Finish.

The tables open in a normal amount of time now.

I hope this helps someone else out there sometime.



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

 

[jgoodman00]

Apparently this adds an extra layer of libraries to churn through which is the reason for the slowness.

I cannot replicate this slowness here. We already have an ODBC dsn to our SQL Server DB, because it is used by microstation. I created a standard mdb & linked the tables through the dsn. There was no apparent difference in performance...

Whilst using an adp is now optimal, prior to Access 2000 this didnt exist & AFAIK the only way to connect to SQL Server was through ODBC.

I suspect the combination of ODBC & the nature of your network are causing the latency. It might be worth running network monitor to see what is happening. However, this is only available on Win2K Server & upwards.

James Goodman MCSE, MCDBA

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

 

[scroce]

that's interesting and frustrating news. I don't understand why this would be happening here.

I would very much rather not have to use an adp file since it seems like it would be very difficult if not impossible to import all my code, forms and queries into the adp file

I thought I had ruled out that it's the network. - I mean if I do a tracert to the machine SQL Server sits on - it's just one hop right to that machine. I do not think it's going thru our WAN, since if I tracert a machine i know is in the main office, it shows 3 hops. As far as I can tell, it's acting as if it were a peer to peer network.

I think it may have something to do with the way I have access configured with the SQL server personal edition. - or maybe SQL server edition is just not a good choice when it's not on the local machine.

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

 

[jgoodman00]

Personal addition shouldnt make too much difference.

What type of connection do the local users establish to the SQL Server?
You can get this information from Enterprise Manager, under Current Activity, Process Info. If you scroll to the right you will see a column "Network Library".
Establish a connection through an adp & see what the connection is. Then establish a connection through ODBC & compare.

To test for the WAN, can you temporarily disable it? Disable it & try connecting through ODBC.

James Goodman MCSE, MCDBA

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

 

[RobertT687]

Just a question. After you imported your tables into SQL, did you create/recreate Indexes?
We have lots of SQL tables accessed by Access front ends via ODBC and have no speed issues. All the tables are indexed regardless of their size. A lack of indexes or poorly choosen indexes will do more to degrade performance than ODBC.

 

[scroce]

Robert - I reinstated all the primary key indexes, but didn't really do much else to index other commonly searched fields.

james
I was able to find the process info, but nothing discernably changes in it when I access the data either thru the adp or thru my odbc connection

There are about 13 processes that show up when I open this. All of them are little white circles except for 3. Two out of those three look liked greyed out little globe icons. The third is a globe icon that looks active, and it's process number is 51. Under Network library it says LPC.

I think I need a translator, b/c I don't know what this is telling me.

As far as detaching from the WAN, all I need do is detatch our hub from the router, correct? I could probably try that later today, since people will most likely be leaving early.

Also James, you may be interested to know that I have access to another office network, and yesterday I tried the same setup with no slowness.

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

 

[jgoodman00]

As far as detaching from the WAN, all I need do is detatch our hub from the router, correct?

Should do, yes.

LPC is local procedure call & is the local client. I presume you were accessing the server from the server yourself?

James Goodman MCSE, MCDBA

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