Slow performance progromatically linking tables in Access

[Mark P]

I have a bit of vb code that uses Tables.Append to programatically
link tables from Oracle and DB2 datasources. The problem I am having
on some client machines is that the link will take anywhere from 1 to
3 minutes to link the table. When I manually link the tables,through
Access, it takes no time at all. Note that only certain client have
the problem, and the problem will sometimes go away.
These clients will usually get an ODBC Call Failed message after 60
seconds. But if I set the timeout settings for Jet ODBC to 0, then the
error goes away, but the link still takes too long.
Any ideas?

[Albert D. Kallal]

In all applications you want to force JET to keep a persistent connection
open to the back end.

So, link the first table..and the result will absolute scream.

By the way..keep that connection open for all of your application...you will
see things speed up by many times in your code.
--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
pl******************@msn.com
http://www.attcanada.net/~kallal.msn

[David W. Fenton]

"Albert D. Kallal" <ka****@msn.com> wrote in
news:rziic.226854$Pk3.38501@pd7tw1no:

In all applications you want to force JET to keep a persistent
connection open to the back end.

So, link the first table..and the result will absolute scream.

By the way..keep that connection open for all of your
application...you will see things speed up by many times in your
code.

I have never done this and have never seen a problem.

Well, I had a performance problem once and tried it, and it
accomplished nothing at all.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"Albert D. Kallal" <ka****@msn.com> wrote in
news:rziic.226854$Pk3.38501@pd7tw1no:

In all applications you want to force JET to keep a persistent
connection open to the back end.

So, link the first table..and the result will absolute scream.

By the way..keep that connection open for all of your
application...you will see things speed up by many times in your
code.

And, BTW, if what you're trying to do is simply keep the LDB file
from being deleted, you only have to declare a db variable pointing
to the back end. You never have to use it for any other reason.

The advantage of a persistent table connection is that you might
actually be able to make use of it.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Trevor Best]

David W. Fenton wrote:

"Albert D. Kallal" <ka****@msn.com> wrote in
news:rziic.226854$Pk3.38501@pd7tw1no:

In all applications you want to force JET to keep a persistent
connection open to the back end.

So, link the first table..and the result will absolute scream.

By the way..keep that connection open for all of your
application...you will see things speed up by many times in your
code.

I have never done this and have never seen a problem.

Well, I had a performance problem once and tried it, and it
accomplished nothing at all.

Are you on the same planet? <g>

I have seen this phenomenom and not just in linking the tables either.
My main menu/switchboard runs from a table but this is copied locally
upon startup for speed resons. Every time I close forms back to the main
menu I observed the LDB file being deleted so opening the next form
would create it again, the slowdown was in closing things. ISTR a
rst.Close statement taking something like 5 seconds to complete.

Perhaps in your case, the design of your application meant you had
something open on the back-end at all times anyway without having to do
it explicitly.

--
Error reading sig - A)bort R)etry I)nfluence with large hammer

[David W. Fenton]

Trevor Best <nospam@localhost> wrote in
news:40***********************@auth.uk.news.easyne t.net:

David W. Fenton wrote:

"Albert D. Kallal" <ka****@msn.com> wrote in
news:rziic.226854$Pk3.38501@pd7tw1no:

In all applications you want to force JET to keep a persistent
connection open to the back end.

So, link the first table..and the result will absolute scream.

By the way..keep that connection open for all of your
application...you will see things speed up by many times in your
code.

I have never done this and have never seen a problem.

Well, I had a performance problem once and tried it, and it
accomplished nothing at all.

Are you on the same planet? <g>

I would be interested to know what, exactly, constitutes the
difference. I don't *think* I leave things open all the time, but
maybe I do.
I have seen this phenomenom and not just in linking the tables
either. My main menu/switchboard runs from a table but this is
copied locally upon startup for speed resons. Every time I close
forms back to the main menu I observed the LDB file being deleted
so opening the next form would create it again, the slowdown was
in closing things. ISTR a rst.Close statement taking something
like 5 seconds to complete.
I just have never seen that.
Perhaps in your case, the design of your application meant you had
something open on the back-end at all times anyway without having
to do it explicitly.

Well, recent apps all use a global db variable, but that points to
the front end only. Maybe I have tables open all the time.

It just has not been an issue where I've seen any benefit to this
technique at all, and I've tried it.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Tony Toews]

ma************@comcast.net (Mark P) wrote:

I have a bit of vb code that uses Tables.Append to programatically
link tables from Oracle and DB2 datasources. The problem I am having
on some client machines is that the link will take anywhere from 1 to
3 minutes to link the table. When I manually link the tables,through
Access, it takes no time at all. Note that only certain client have
the problem, and the problem will sometimes go away.

I wonder if there is an ODBC driver difference between these systems.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[Tony Toews]

"Albert D. Kallal" <ka****@msn.com> wrote:

In all applications you want to force JET to keep a persistent connection
open to the back end.

So, link the first table..and the result will absolute scream.

By the way..keep that connection open for all of your application...you will
see things speed up by many times in your code.

But this is to Oracle and DB2. So it's not the same as the LDB locking. That said
there could be a very similar mechanism and keep a connection of some sort open at
all times could help.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[Albert D. Kallal]

"Tony Toews" <tt****@telusplanet.net> wrote in message
news:ke********************************@4ax.com...

By the way..keep that connection open for all of your application...you willsee things speed up by many times in your code.
But this is to Oracle and DB2. So it's not the same as the LDB locking.

That said there could be a very similar mechanism and keep a connection of some sort open at all times could help.

Tony

DOH!...as Homer Simpson would say!

For odbc.....my suggestion will not help one bit....

Thank you so much for pointing this out!
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl******************@msn.com
http://www.attcanada.net/~kallal.msn

[Albert D. Kallal]

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@24.168.1 28.90...

By the way..keep that connection open for all of your
application...you will see things speed up by many times in your
code.

I have never done this and have never seen a problem.

Well, I had a performance problem once and tried it, and it
accomplished nothing at all.

First, as Tony pointed out, the OP was talking about ODBC..and my suggestion
of ZERO use in that case.

As for speeding up re-linking in a file share mdb....and opening of tables
by keeping a persistent connection?

Yes..I the persistent connection speed things up on MANY of my clients (not
all...but many!).

Usually it is windows XP clients where this happens. The server is usually a
win2000 server.
--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
pl******************@msn.com
http://www.attcanada.net/~kallal.msn

[Trevor Best]

David W. Fenton wrote:

Are you on the same planet? <g>

I would be interested to know what, exactly, constitutes the
difference. I don't *think* I leave things open all the time, but
maybe I do.

As Albert pointed out, perhaps another OS? I've been on WinXP for a long
time now.
--
Error reading sig - A)bort R)etry I)nfluence with large hammer

[Trevor Best]

Mark P wrote:

I have a bit of vb code that uses Tables.Append to programatically
link tables from Oracle and DB2 datasources. The problem I am having
on some client machines is that the link will take anywhere from 1 to
3 minutes to link the table. When I manually link the tables,through
Access, it takes no time at all. Note that only certain client have
the problem, and the problem will sometimes go away.
These clients will usually get an ODBC Call Failed message after 60
seconds. But if I set the timeout settings for Jet ODBC to 0, then the
error goes away, but the link still takes too long.
Any ideas?

Is the server on a remote connection? Bandwidth may be a problem. When I
was in Kazakhstan we had 2Mb/s microwave links around the oil field and
the server was located in a warehouse about 30 miles from the field,
again a microwave link to the main offices there then a breezcom radio
link (3Mb/S) to the warehouse, now the trees would hamper the radio link
so the bandwidth may go down to about 1Mb/s.

Linking tables here (to SQL Server) took a few seconds for clients in
the warehouse but out on the field it took 20 minutes + another 20
minutes to copy down a new code database if one was available (18MB mdb)
so could take 40 minutes to log on in the morning (gives them time to
boil the kettle, etc <g>). Once logged in and lonked it was uber fast.

My point is reduced bandwidth / heavy useage can have an impact on
performance of linking tables, it does appear that the linking process
does transfer large amounts of data across the network, not something
you'd expect in a client/server environment.

Speaking of environment, do make sure that all clients are running the
same office service packs, same odbc drivers, same MDAC, etc. Also look
for silly things that might affect bandwidth like people downloading
stuff and/or using something like bittorrent.

--
Error reading sig - A)bort R)etry I)nfluence with large hammer

[David W. Fenton]

Trevor Best <nospam@localhost> wrote in
news:40***********************@auth.uk.news.easyne t.net:

David W. Fenton wrote:

Are you on the same planet? <g>

I would be interested to know what, exactly, constitutes the
difference. I don't *think* I leave things open all the time, but
maybe I do.

As Albert pointed out, perhaps another OS? I've been on WinXP for
a long time now.

Well, I've had a mix of apps with Win95, Win98, NT 4, Win2K and
WinXP front ends connecting to peer-to-peer "servers" running the
same batch of OS's, and real servers running NT 4 and Win2K, and I
haven't seen any of them exhibit any real performance hits.

The only situation where I had problems was NT 4 to NT 4, and
getting them all on the same service pack (6a) and changing the
server's OpLocks fixed the problem.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"Albert D. Kallal" <ka****@msn.com> wrote in
news:7LIic.235153$Pk3.2773@pd7tw1no:

Yes..I the persistent connection speed things up on MANY of my
clients (not all...but many!).

Usually it is windows XP clients where this happens. The server is
usually a win2000 server

I have virtually no clients in a networked environment who have
WinXP workstations (mostly because I tell them not to buy them if
they can help it).

But this piece of advice long predates WinXP clients, no?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Trevor Best]

David W. Fenton wrote:

The only situation where I had problems was NT 4 to NT 4, and
getting them all on the same service pack (6a) and changing the
server's OpLocks fixed the problem.

IIRC didn't 6a have a jet update in it? (Long time since I used NT).

--
Error reading sig - A)bort R)etry I)nfluence with large hammer

[David W. Fenton]

Trevor Best <nospam@localhost> wrote in
news:40***********************@auth.uk.news.easyne t.net:

David W. Fenton wrote:

The only situation where I had problems was NT 4 to NT 4, and
getting them all on the same service pack (6a) and changing the
server's OpLocks fixed the problem.

IIRC didn't 6a have a jet update in it? (Long time since I used
NT).

I don't remember that it did. Why would it? NT 4 didn't have Active
Directory, so it didn't include Jet.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Tony Toews]

"David W. Fenton" <dX********@bway.net.invalid> wrote:

But this piece of advice long predates WinXP clients, no?

Long predates WinXP. More like A2000 which would've been in the Win2K timeframe.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[Albert D. Kallal]

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@24.168.1 28.86...

I have virtually no clients in a networked environment who have
WinXP workstations (mostly because I tell them not to buy them if
they can help it).

But this piece of advice long predates WinXP clients, no?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

Actually, I say that:

access97...windows XP clients...win2000 server.. (seen the slow down..and
fix by persisting connection).

access 97, windows2000,...win2000 server (NEVER seen the slow down).

access2000, windows 2000...win2000 (seen the slow down...and fix by
persisting connection).

accessXP, windows 2000, win2000 server (seen the slow..and fix by persisting
connection).

However, the above information is kind of useless..as it not really a
pattern that I can say when, and when NOT the slowdown will occur.

But..one pattern does seem to be the win XP clients to a win2000 server (I
can't remember have NEVER seen the slowdown in this case!).


--
Albert D. Kallal
Edmonton, Alberta Canada
pl******************@msn.com
http://www.attcanada.net/~kallal.msn

[David W. Fenton]

"Albert D. Kallal" <ka****@msn.com> wrote in
news:wfCjc.273025$Pk3.164320@pd7tw1no:

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@24.168.1 28.86...

I have virtually no clients in a networked environment who have
WinXP workstations (mostly because I tell them not to buy them if
they can help it).

But this piece of advice long predates WinXP clients, no?

--
David W. Fenton
http://www.bway.net/~dfenton dfenton at bway dot net
http://www.bway.net/~dfassoc

Actually, I say that:

access97...windows XP clients...win2000 server.. (seen the slow
down..and fix by persisting connection).

access 97, windows2000,...win2000 server (NEVER seen the slow
down).

access2000, windows 2000...win2000 (seen the slow down...and fix
by persisting connection).

accessXP, windows 2000, win2000 server (seen the slow..and fix by
persisting connection).

However, the above information is kind of useless..as it not
really a pattern that I can say when, and when NOT the slowdown
will occur.

But..one pattern does seem to be the win XP clients to a win2000
server (I can't remember have NEVER seen the slowdown in this
case!).

Looks like Win2K server is the problem, but I've never seen that
myself. Indeed, the only major performance hit any of my apps ever
had was NT 4 to NT 4.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc