Poor performance through slow line

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Dear all,

I have an application which use ODBC link to access data in my SQL server.
It works fine in LAN environment. Somehow this application needs to be run in
a remote location which connects to SQL server through a low bandwidth line.
Poor performance results. Is there any way to improve this? Can .adp file
help? I notice that some suggestions in this forum mentions there is the
"pass through query" technique. I'm not quite familar with this technique ...
can anybody help?

thanks!!!
 
In order to know how to fix this it is necessary for you to have a much
better understanding of why it is slow.

Access using ODBC linked tables against SQL Server is functionally very
rich, pretty much any Access query you can create will run. However, unless
you are careful this richness comes at a cost: if you create a query which
cannot be translated from Access SQL to SQL Server SQL then Access will
potentially drag large quantities of data from the server to the desktop in
order for Access itself to run the query (or part of it), hence poor
performance. Some things are pretty good indicators of this problem - for
example, an Access query which uses VBA functions is likely to cause
problems. Other times, potential problems may not be obvious and it will be
necessary to use the SQL Profiler at the server to find out exactly what
queries are arriving there.

When such problems are detected, it will be necessary to EITHER redesign the
queries in Access so that they are more effectively translated into SQL
Server queries, OR to create passthrough queries. However, this latter
approach comes at a cost because passthrough queries have to be constructed
entirely in SQL Server syntax, so many Access queries cannot simply be
converted to passthrough queries, a certain amount of redesign is needed.
Consequently, using passthrough queries is not a blanket panacea which can
be applied across your application, it needs to be done on a case-by-case
basis and then only where a problem has been identified with a particular
query.

If (and only if) you are experiencing problems such as described above, an
ADP file MIGHT help, but since converting your application to an ADP will
take a great deal of work, including rewriting ALL your queries, not just
the problem ones, so that they are in SQL Server syntax, it would be much
better to fix the problems with the existing application.

And, if you have a really low bandwidth, you might optimise all your queries
and STILL find that it is too slow. When you reach this point you have only
two choices: redesign the application so that it queries the server less
often for less data, or get more bandwidth.
 
To add my penny's worth here, a trick I employ is this: I use a
passthrough query to download just the required source data from SQL
Server into a table in a local temp .mdb (outside my FE), link to that
table programatically and then run a second query on it, with all the
data massaging, calculations, VBA functions etc. I have found this to
speed things up considerably (the aim being to retrieve as little as
possible from SQL Server over the line).

HTH,
Nikos
 
Back
Top