Re: Performance of ODBC

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hi Norman,

Thank you for your reply. I will research the options you are suggesting
(learning curve for me here). I have a fixed IP address in my office, and
from what you write, it seems I could improve performance at work (where it
is most necessary) if I set up a server in the office. Machines in the
office would be client/server and would get the data rapidly. Meanwhile, my
other machines off site would get the data at my present slow rate.

In that case (assuming I'm correct), would I need something like a Dell
small business server with SQL Server 2005 express?

"Norman Yuan" wrote:

Your performance issue is not with ODBC, rather, it is your (physical)
network connection. Usiing Access front-end to connection a back end through
the Internet connection is definitely slow with currently affordable network
connection. Just compare: a typical LAN speed is 100Mbp, a very high-speed
cable connection to the Internet would be 100K - 1Mbp, 100 to 1000 times
difference. Unless you can get a network connection that fast enough and
compareable to the LAN speed (with big money), your Access front end to the
remote backend via the Internet would be alway slow and not really useful in
most cases.

You need to re-think the way to get data from remote server. For example,
using web app/service to get data; or connect via terminal services, if the
remote host provides such service; ...

"Bruce Maston" <homebrew@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E8BF6231-397F-4A4F-81BA-61773F86D668@xxxxxxxxxxxxxxxx
I apologize if this is the wrong group. I have an Access 2000 app running
at
three locations (my home, my office, and my partner's home). The database
sits on an SQL Server at a "commercial" ISP site. The machines connect to
the database via ODBC with the Jet database engine. I guess the lingo is
that Access is the front end, and the SQL server is the back end.

Over the 5 years I've used this set-up, I've always been annoyed by the
slow
response times. My database is extremely small, and it seems to me that
for
the limited information I get out of it per query and the fact that a
query
is sent to the SQL Server by somebody about once per hour, it should be
virually instantaneous.

Today I timed it as 15 seconds to make a chance in one record of one
from -1
to 0. The table has 230 records and 24 columns. Most columns are either
yes/no or have only a date in them. In other words, it took 15 seconds
for
ADO to do a
.find and then !field=no then .update.

I'm wondering if there is any way to improve this. Is there a better way
to
run my app rather than with ODBC? Would my problem improve if I had my
own
server in my office (where only the machines at home had to get data over
the
internet)? Could the problem be that the ISP's server is doing too many
other things for other customers?

I'm a hobbiest, and I'm my own IT guy. (In my "real" job, I'm a doctor.)
I
can't go to a main frame with Oracle, etc., but is there a "next level"
out
there that I should consider moving to?

Thank you for any input.



.

Relevant Pages

  • Re: ADO Connection Timeout
    ... to the central server, but you are willing to live with periods where it ... i.e. a local database or even a text file. ... to function until the connection can be restored to the server. ...
    (microsoft.public.data.ado)
  • Complicated Connection Problems bewteen ADP and SQL Server
    ... This database ... expertise for getting the user workstations talking to the SQL Server. ... connection would fail and the adp wouldn't be able to talk to the server. ... might be in my ADO connection string. ...
    (microsoft.public.access.adp.sqlserver)
  • Complicated Connection Problem between ADP and SQL Server
    ... This database ... expertise for getting the user workstations talking to the SQL Server. ... connection would fail and the adp wouldn't be able to talk to the server. ... might be in my ADO connection string. ...
    (microsoft.public.sqlserver.connect)
  • Re: ADO Connection Timeout
    ... so what happens when a connection failure forces one station to revert ... to a local database? ... Further, you *will* have contention issues, Jet does not support record ... to the central server, but you are willing to live with periods where it ...
    (microsoft.public.data.ado)
  • Re: ADO Connection Timeout
    ... When the first test is run, the results are stored in the central database. ... to the central server, but you are willing to live with periods where it ... i.e. a local database or even a text file. ... to function until the connection can be restored to the server. ...
    (microsoft.public.data.ado)