Performance of ODBC

[Guest]

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.

 

[Norman Yuan]

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; ...

 

[Guest]

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]

If you want to host your data in your LAN, you can choose any edition of SQL
Server and suitable computer, depending on the need. It is good idea to
start with SQL Server 2005 Express, since it is free and can be move up to
SQL Server 2005 St/Ent edition without issues.

As for the hardware (computer taht runs SQL Server/Express), it really
depends on the load of data access, it can be very low-end basic box to very
high-end one, as long as it meets the system requirement for SQL
Server/Express specified by MS.

 

[Brendan Reynolds]

In a previous post, Norman mentioned Terminal Services. I agree that this is
a potential solution that you might want to check out. This could solve the
problem for your remote users as well as your local users. Using Terminal
Services, remote users execute the application on your local network. Only
the screen display and keyboard/mouse input needs to be sent across the WAN.
All communication between the application and your database happens on your
LAN. Here's a link to the Terminal Services documentation at TechNet ...

http://technet2.microsoft.com/windowsserver/en/technologies/featured/termserv/default.mspx

 

[Sylvain Lafontaine]

Because in this case that there will be only one or two external posts and
that they will accessing the post at the office with a low frequency
(something like one time per hour (see OP)), creating a virtual session with
VPC or Virtual Server (both free) and accessing this session via VNC (see
RealVNC) or with the Internet control (for Virtual Server) or any other
remote control (PC-Anywhere) would be cost effective because it's free
(excerpt in the case of PC-Anywhere) and can be used with their actual WinXP
installation.

I agree that Terminal Server will be a little faster but this will require
that they buy Win2003 Server.

There are also ISP sites that will offer you access to Terminal Services to
access your databases. This can be highly cost effective (one of my client
is using one at a cost of something like 50$ per month) because not only you
don't have to buy your own hardware but also you don't have to be worried
about making regular backups.

 

[dbahooker]

you're full of shit, Norman.
ODBC / MDB is a waste of time.

Using an Access Data Project might give you a lot better performance

 

[dbahooker]

for the record; you could always terminal services on an Windows XP
Professional machine that was running MSDE.

I just disagree; yes the probem is MDB. Using ADO .Find methods and all
that client side crap is bullshit. the problem is that you're having
locking problems.

Use simple sql statements to update your tables and you'll be a lot
better off.
Use ADP instead of MDB-- MDB was obsolete almost a decade ago.


-Aaron

 

[Vadim Rapp]

Most likely this is a problem with your or your ISP network configuration,
most likely with DNS. I just tried to connect from home computer to sql
server running at my work computer and published on the firewall; both
Access through odbc and vb6 program were able to connect and extract the
data practically instantly..

Did you try tracert to your server?

Also, if you close your Access and then reopen and reaccess the server data,
is it still slow? perhaps sql server is autostarted when you connect first
time, or maybe the database is autoclosed and needs to reopen?

Vadim Rapp

 

[aaron.kempf]

Vadim

that worked because you had a port forwarded.. on the firewall side
right?

that is an insecure setup; most real security people sure wouldn't
allow that

-Aaron

 

[dbahooker]

and again..

when you use MDB against linked tables; you'll have awful performance..
because it SCANS THE WHOLE TABLE AND DOWNLOADS IT BEFORE STARTING A
SEEK

ADP doesn't have this same problem; if you request 20 seconds from SQL
Server you get 20 records.

again, to simplify

if you send a request fom MDB to SQL Server.. saying 'give me 20
records' MDB sits there and says 'I dont know wtf a database is; let me
bring this WHOLE TABLE across the network, piece through it.. and
finally find the 20 records that im looking for'

lol

MDB Is completely obsolete; spit on anyone that still uses it

 

[Guest]

Hi Vadim,

Thank you for your reply. I've delayed responding to try out some of these
ideas. I've now created my own server with SQL Server 2005 Express, and I've
gotten this going on my network at the office. As such, I have two XP
computers with Access front end, and I've hooked up my older computer (with
W2K pro) where I have the SQL Server and my database tables.

I encountered some problems due to my stupidity. The more interesting
glitch concerns the Upsizing Wizard.

A bit of history here. I originally set up my ODBC connection via
Settings/Administrative Tools/ODBC and the User DSN tab. The upsizing wizard
in Access 2K worked perfectly. About a year ago, the Upsizing Wizard crashed
and didn't work anymore. I eventually learned (from tech support at the ISP)
that I needed to set up the connection with System DSN. I did this, and the
problem was solved. I can't clearly remember the reason I needed to do this
-- I think is was some Windows or Office update that lead to this.

When I used the upsizing wizard to my own server yesterday, the wizard again
crashed. I figured out that I needed to go back to a connection to
[servername]\SQLEXPRESS via User DSN. I thought I was golden until I tried
to open the app. I was getting error messages that turned out to be because
any Yes/No Access column is a Bit column in SQL. As such, whereas Access
sees Yes/No as -1/0, SQL sees this as +1/0. Amazingly (to me anyway), when
you view the data in a table via SSMS, you see the +1's. When you view the
same table via Access, you see the -1's. I corrected my problem by changing
the VBA code in Access, and I've now got it going.

As far a speed, the connection is now virtually instanteous. Thus, I've
concluded my speed problem was either in the ISP's server, or one of the
other situations that you posed (and that I've not totally deciphered yet).

Anyway, the next question is what the speed will be when I use my home
computer with the Access front end to connect to the SQL server 2005 Exp. at
the office.

This is really my next question, and maybe the end of a very long entry is
not the best place for it. How to I reach the server at my office from home?
I have a fixed IP address at the office (but not at home where I'm simply on
RoadRunner). I can't figure out how to type something in the boxes for the
ODBC connection that gets anything to happen. There is lots of info on
enabling TCP/IP to connect via a LAN (along with using SQL authentication),
but nothing on what the connection string looks like from a remote site.

Bruce Maston

 

[aaron.kempf]

you will have to use SQL authentication for the most part; unless you
have a VPN connection or something

you will need to redirect the port through the firewall so that it can
hit your server.

if you have a software firewall; allow 1433/1434 through it
if you have a software firewall; forward 1433/1434 to your server

Hi Vadim,

Thank you for your reply. I've delayed responding to try out some of these
ideas. I've now created my own server with SQL Server 2005 Express, and I've
gotten this going on my network at the office. As such, I have two XP
computers with Access front end, and I've hooked up my older computer (with
W2K pro) where I have the SQL Server and my database tables.

I encountered some problems due to my stupidity. The more interesting
glitch concerns the Upsizing Wizard.

A bit of history here. I originally set up my ODBC connection via
Settings/Administrative Tools/ODBC and the User DSN tab. The upsizing wizard
in Access 2K worked perfectly. About a year ago, the Upsizing Wizard crashed
and didn't work anymore. I eventually learned (from tech support at the ISP)
that I needed to set up the connection with System DSN. I did this, and the
problem was solved. I can't clearly remember the reason I needed to do this
-- I think is was some Windows or Office update that lead to this.

When I used the upsizing wizard to my own server yesterday, the wizard again
crashed. I figured out that I needed to go back to a connection to
[servername]\SQLEXPRESS via User DSN. I thought I was golden until I tried
to open the app. I was getting error messages that turned out to be because
any Yes/No Access column is a Bit column in SQL. As such, whereas Access
sees Yes/No as -1/0, SQL sees this as +1/0. Amazingly (to me anyway), when
you view the data in a table via SSMS, you see the +1's. When you view the
same table via Access, you see the -1's. I corrected my problem by changing
the VBA code in Access, and I've now got it going.

As far a speed, the connection is now virtually instanteous. Thus, I've
concluded my speed problem was either in the ISP's server, or one of the
other situations that you posed (and that I've not totally deciphered yet).

Anyway, the next question is what the speed will be when I use my home
computer with the Access front end to connect to the SQL server 2005 Exp. at
the office.

This is really my next question, and maybe the end of a very long entry is
not the best place for it. How to I reach the server at my office from home?
I have a fixed IP address at the office (but not at home where I'm simply on
RoadRunner). I can't figure out how to type something in the boxes for the
ODBC connection that gets anything to happen. There is lots of info on
enabling TCP/IP to connect via a LAN (along with using SQL authentication),
but nothing on what the connection string looks like from a remote site.

Bruce Maston

Most likely this is a problem with your or your ISP network configuration,
most likely with DNS. I just tried to connect from home computer to sql
server running at my work computer and published on the firewall; both
Access through odbc and vb6 program were able to connect and extract the
data practically instantly..

Did you try tracert to your server?

Also, if you close your Access and then reopen and reaccess the server data,
is it still slow? perhaps sql server is autostarted when you connect first
time, or maybe the database is autoclosed and needs to reopen?

Vadim Rapp

 

[aaron.kempf]

you will have to use SQL authentication for the most part; unless you
have a VPN connection or something

you will need to redirect the port through the firewall so that it can
hit your server.

if you have a software firewall (like Windows XP or Server 2003); allow
1433/1434 through it
if you have a hardware firewall (like ISA Server or NAT for example);
forward 1433/1434 to your server

Hi Vadim,

Thank you for your reply. I've delayed responding to try out some of these
ideas. I've now created my own server with SQL Server 2005 Express, and I've
gotten this going on my network at the office. As such, I have two XP
computers with Access front end, and I've hooked up my older computer (with
W2K pro) where I have the SQL Server and my database tables.

I encountered some problems due to my stupidity. The more interesting
glitch concerns the Upsizing Wizard.

A bit of history here. I originally set up my ODBC connection via
Settings/Administrative Tools/ODBC and the User DSN tab. The upsizing wizard
in Access 2K worked perfectly. About a year ago, the Upsizing Wizard crashed
and didn't work anymore. I eventually learned (from tech support at the ISP)
that I needed to set up the connection with System DSN. I did this, and the
problem was solved. I can't clearly remember the reason I needed to do this
-- I think is was some Windows or Office update that lead to this.

When I used the upsizing wizard to my own server yesterday, the wizard again
crashed. I figured out that I needed to go back to a connection to
[servername]\SQLEXPRESS via User DSN. I thought I was golden until I tried
to open the app. I was getting error messages that turned out to be because
any Yes/No Access column is a Bit column in SQL. As such, whereas Access
sees Yes/No as -1/0, SQL sees this as +1/0. Amazingly (to me anyway), when
you view the data in a table via SSMS, you see the +1's. When you view the
same table via Access, you see the -1's. I corrected my problem by changing
the VBA code in Access, and I've now got it going.

As far a speed, the connection is now virtually instanteous. Thus, I've
concluded my speed problem was either in the ISP's server, or one of the
other situations that you posed (and that I've not totally deciphered yet).

Anyway, the next question is what the speed will be when I use my home
computer with the Access front end to connect to the SQL server 2005 Exp. at
the office.

This is really my next question, and maybe the end of a very long entry is
not the best place for it. How to I reach the server at my office from home?
I have a fixed IP address at the office (but not at home where I'm simply on
RoadRunner). I can't figure out how to type something in the boxes for the
ODBC connection that gets anything to happen. There is lots of info on
enabling TCP/IP to connect via a LAN (along with using SQL authentication),
but nothing on what the connection string looks like from a remote site.

Bruce Maston

Most likely this is a problem with your or your ISP network configuration,
most likely with DNS. I just tried to connect from home computer to sql
server running at my work computer and published on the firewall; both
Access through odbc and vb6 program were able to connect and extract the
data practically instantly..

Did you try tracert to your server?

Also, if you close your Access and then reopen and reaccess the server data,
is it still slow? perhaps sql server is autostarted when you connect first
time, or maybe the database is autoclosed and needs to reopen?

Vadim Rapp

 

[dbahooker]

and you'll need to upgrade to Windows XP Pro to use a software VPN..
right?

-Aaron

Hi Vadim,

Thank you for your reply. I've delayed responding to try out some of these
ideas. I've now created my own server with SQL Server 2005 Express, and I've
gotten this going on my network at the office. As such, I have two XP
computers with Access front end, and I've hooked up my older computer (with
W2K pro) where I have the SQL Server and my database tables.

I encountered some problems due to my stupidity. The more interesting
glitch concerns the Upsizing Wizard.

A bit of history here. I originally set up my ODBC connection via
Settings/Administrative Tools/ODBC and the User DSN tab. The upsizing wizard
in Access 2K worked perfectly. About a year ago, the Upsizing Wizard crashed
and didn't work anymore. I eventually learned (from tech support at the ISP)
that I needed to set up the connection with System DSN. I did this, and the
problem was solved. I can't clearly remember the reason I needed to do this
-- I think is was some Windows or Office update that lead to this.

When I used the upsizing wizard to my own server yesterday, the wizard again
crashed. I figured out that I needed to go back to a connection to
[servername]\SQLEXPRESS via User DSN. I thought I was golden until I tried
to open the app. I was getting error messages that turned out to be because
any Yes/No Access column is a Bit column in SQL. As such, whereas Access
sees Yes/No as -1/0, SQL sees this as +1/0. Amazingly (to me anyway), when
you view the data in a table via SSMS, you see the +1's. When you view the
same table via Access, you see the -1's. I corrected my problem by changing
the VBA code in Access, and I've now got it going.

As far a speed, the connection is now virtually instanteous. Thus, I've
concluded my speed problem was either in the ISP's server, or one of the
other situations that you posed (and that I've not totally deciphered yet).

Anyway, the next question is what the speed will be when I use my home
computer with the Access front end to connect to the SQL server 2005 Exp. at
the office.

This is really my next question, and maybe the end of a very long entry is
not the best place for it. How to I reach the server at my office from home?
I have a fixed IP address at the office (but not at home where I'm simply on
RoadRunner). I can't figure out how to type something in the boxes for the
ODBC connection that gets anything to happen. There is lots of info on
enabling TCP/IP to connect via a LAN (along with using SQL authentication),
but nothing on what the connection string looks like from a remote site.

Bruce Maston

Most likely this is a problem with your or your ISP network configuration,
most likely with DNS. I just tried to connect from home computer to sql
server running at my work computer and published on the firewall; both
Access through odbc and vb6 program were able to connect and extract the
data practically instantly..

Did you try tracert to your server?

Also, if you close your Access and then reopen and reaccess the server data,
is it still slow? perhaps sql server is autostarted when you connect first
time, or maybe the database is autoclosed and needs to reopen?

Vadim Rapp

 

[Vadim Rapp]

BM> How to I reach the server at my office from home?

The best way is via VPN. You connect via VPN to the office; from there you
are already in the office network, and connection string is the same as when
you are in the office. Integrated authentication will work.

Vadim Rapp