how to solve speed issues with Access SQL upsize?

  • Thread starter Thread starter Quintus Snapper
  • Start date Start date
Q

Quintus Snapper

I upsized relatively small Access DB application to SQL to be able to
access the DB from multiple locations.

The upsizing was first done locally (using the upsizing wizard in
access) and after having to do some modifications to make some of the
things that no longer worked in the forms work it all seemed to run
perfectly. That was however until we moved the SQL DB over to where we
have the SQL server hosted. After doing that, although everything
still worked it has become unbearably slow.

Since the speed was not an issue on the office LAN locally, I'm
presuming it's so slow because of the data that is being transferred.
We have a downstream internet connection 256kbs and the SQL server is
housed in a managed datacenter. Is my preumption correct and if so is
there some way of overcoming this problem? How? If not, what could the
problem be and is there a way of overcoming this problem to make it
workable again.

Thanks in advance for any help, suggestions.

Quintus
 
If I understand you correctly, the problem is the wire. Everything
works fine locally, but not when it has to travel over a slow
connection. The only thing you can do is fetch only needed data and
try to have as little traffic as possible over the connection. Usually
this means a complete rewrite of the front-end application to utilize
unbound data access techniques.

-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
 
That doesn't sound all to promising! A complete rewrite! Is there no
way to keep it bound but not to have it send all the information. If I
try to optimise the SQL queries would you expect that to help? Or does
it always fetch all the information regardless if the data is bound?

Thanks,

Quintus
 
If you want it bound then you need to do a partial rewrite and use a stored
procedure as your form source. The procedure needs to accept parameters
that will define only one record to return. The tricky part is the
navigation. You have to write your own navigation into the procedure rather
than using Access's built in navigation.

This works fine for us. The remote is still slower than local, but that is
to be expected. The more you can trim down the data that needs to be sent
across the line the better. This means smaller forms with minimal to no
subforms or tab controls with lots of info. For speed you would be better
off having a small master form that you can use buttons to pop open
additional small forms for detailed info.

HTH,
J. Clay
 
FWIW, LAN connection speeds are significantly higher that any DSL/Cable/ISDN
internet connection.

Some details on this can be found in this article:
http://www.attcanada.net/~kallal.msn/Wan/Wans.html

If you are using an Access MDB to connect to a hosted SQL Server, you may be
asking for trouble. If your Access FE is an ADP, it might work.

Recommend upsizing to your local SQL Server, then DTS the relevant objects
to the hosted SQL Server.

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
 
Quintus Snapper said:
I upsized relatively small Access DB application to SQL to be able to
access the DB from multiple locations.

The upsizing was first done locally (using the upsizing wizard in
access) and after having to do some modifications to make some of the
things that no longer worked in the forms work it all seemed to run
perfectly. That was however until we moved the SQL DB over to where we
have the SQL server hosted. After doing that, although everything
still worked it has become unbearably slow.

Since the speed was not an issue on the office LAN locally, I'm
presuming it's so slow because of the data that is being transferred.
We have a downstream internet connection 256kbs and the SQL server is
housed in a managed datacenter. Is my preumption correct and if so is
there some way of overcoming this problem? How? If not, what could the
problem be and is there a way of overcoming this problem to make it
workable again.

Thanks in advance for any help, suggestions.

Quintus
Click to expand...
 
Back
Top