Extremely slow data

[WhitbyNumber2]

I've recently changed a database application over to SQL Server with an Access front end using ODBC. Once the bugs were removed and things were looking good problems arose when users from other sites tried accessing the data over the network.

For some users the new system is very quick but others it is very, very slow and painfull.

Any Clues?

Cheers

Whitby

 

[mpastore]

Can be a variety of things but I would suspect networking is the culprit, you need to understand how message routing from the remote sites is done back to your server. Could be bottlenecks there.

Even though you are using SQL as the back-end, there still may be a lot of data passing btween the server and your MS Access front ends. Also, do your remote users have an access database on their local PC's or do they have to use the .mdb file located at a central site? This will affect performance also.

Mike Pastore

Hats off to (Roy) Harper

 

[dvannoy]

mpastore,

is correct I too think it's your network. is your SQL server running from a box that other users are on? SQL server should be on it's own seperate server. thats rule #1.

as far as access. you can probally speed things up by using ADO, but you would have to do alot of recoding.

i use VB as a front end if I am connecting to SQL server.

 

[WhitbyNumber2]

SQL server is on it's own seperate server. The application itself used to be a simple Access database, it was so poor since it was used all over the place I put the data into SQL Server with the front end remaining as it was (give or take a few tweaks to get it working). At the moment there are 2 users testing it. One of which is finding it ridiculously slow the other finding it very quick.

Any clues.

 

[dhulbert]

Whitby.

You may need to look at the use of Stored Porcedures on the SQL server to imporove speed, this way the only data sent to the user is the results data, rather then all of the data required fro them to perform the query processing.
If the 2 users are in different locations then there may be a difference in the netowrk response times, try pinging the SQL server from each machine and looking at the times it gives back, also try using Tracert to look at the route being used byt he data between the user and the SQL server, it might give you an idea of where the bottleneck lies.

 

[dvannoy]

how far is your remote user?? If you are trying to use this DB over a WAN there is really nothing you can use to improve speed except get more bandwidth. even then you wont find it much faster. My advice would be to create a ASP front end for your remote users. Or, use something like terminal server to have them access the DB.

 

[willir]

WhitbyNumber2

As you gathered from your resposnes, there are numberous possibilities for the bottle neck. And as reviewers, we have a lot of assumptions to make.

Some basic trouble shooting...

Look for simialrities between those with acceptable access and those with slow access.

If access is slow for an entire site, then suspect the network conneciton. For example, network access is faster than WAN frame relay access which is faster the DSL access which is faster than regular phone line access.

Look at the desktop. Slow PC's vs fast PC's. Also, windows 95 vs NT vs 2000. (For some, I am finding Win 2K very slow for some LAN activities - still working on this).

Look at Access version. For Access 2000 turn off autocorrect.

Check ODBC version number.

Lastly, there are some quirks that are real tough to detremine. I had a guy running a 233 MHz Pentium II PC. I upgraded him to a Pentium III 550 MHz. Same OS, save software. Guess what, the ODBC connection from Canada to the south US was 20x slower. Go figure.

Richard