|
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? | |
Share:
|
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 | | |
"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 | | |
"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 | | |
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 | | |
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 | | | 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 | | |
"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 | | |
"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 | | |
"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 | | |
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 | | |
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 | | |
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 | | |
"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 | | |
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 | | |
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 | | |
"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 | | |
"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 | | |
"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 | | This discussion thread is closed Replies have been disabled for this discussion. Similar topics
11 posts
views
Thread by DJJ |
last post: by
|
9 posts
views
Thread by Neil |
last post: by
|
19 posts
views
Thread by Mark P |
last post: by
|
1 post
views
Thread by IkyL234 |
last post: by
|
7 posts
views
Thread by ddsvi78 |
last post: by
|
14 posts
views
Thread by google@darincline.com |
last post: by
|
2 posts
views
Thread by Mark |
last post: by
| | | | | | | | | | |