ODBC Speed

[Bruce Maston]

I have Access 2K and an ODBC connection to a database totalling only about
3.5 Megs. There are only 3 users, and they rarely use the connection
simultaneously. I have a cable modem.

The speed is very slow. An SQL SELECT statement can take as long as 15
seconds to return data.

Is this normal? Is this a problem at the ISP who hosts this database?
Should I reprogram this some way, or use a different way to get to the db?
Is there a way to monitor the performance of an ODBC connection to figure out
where the time lag is?

I've noticed that a SQL statements "attached" directly to a control
(Me.Control.Recordsource = strSQL) runs much faster than the same SQL
statement handled as an ADO recordset.

Thank you for any input.

Bruce Maston

 

[Jim Douglas]

OLEDB I have found in my tests to be 50% faster than ODBC, if you can use
it.

 

[Albert D. Kallal]

You don't mention how the odbc connection is setup.

Are you talking about connecting from ms-access to a back en sql server?
(because, you can't use odbc to connect to a mdb file sitting on your web
server) (well, in fact, you could if you setup a VPN.)..

A odbc connection to sql server should work ok. Do remember that you
internet connection is 100 times slower then a standard office network which
you "usually" use with ms-access.

If you are in fact talking about a file share..then I surprised you can get
things to work even in 15 seconds....

You can read up on this issue here:

http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html

 

[Damiaan]

depends also on your upload speed from your ISP

 

[Bruce Maston]

Thank you for your replies. I believe I'm running MS-Access as the front end
and that the SQL Server at my ISP is the back end. That is, I have Access on
my machine, and I connect to the database on the Server.

My connection string is:

Set dcn = New ADODB.Connection
dcn.ConnectionString = "Provider=SQLOLEDB.1;User ID=xxx;" & _
"Password=xxx;Initial Catalog=xxx;" & _
"Data Source=xxx.xxx.net"

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
With rst
.CursorLocation = adUseServer
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open "table", dcn.ConnectionString

I am intrigued by the comment that the upload speed may be the problem. My
cable modem is asymmetric, but the slowest it ever goes is several hundred
kb/sec. With a total size of my small db being 3 MB, it seems to me that the
data should come back very fast since I'm never asking for more than a tiny
amount of data. The delay must have to do with how fast the SQL is
translated by the Jet Engine to SQL, how long it takes the server to process
it, etc. Of course, I have a Norton firewall, and I know that is always a
potential problem, but my ISP's address is a "trusted site."

A Google search for "'upload speed'" ISP ODBC" gets sites that offer
monitors for network traffic. Would something like this be useful to me?

My ODBC connection "works perfectly" (except for the speed). I don't see
anything on the internet discussing particular problems with speed until the
db gets to 50 MB or the number of users grows beyond 15. Thus, I suspect I
have a problem that might be correctible.