Query speed across network. Eek...Access97!!

['69 Camaro]

Everyone please note that Aaron Kem.pf is attempting to impersonate one of
our regular posters again. Tom would never post such a message.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Albert D. Kallal]

I did state at the outset that this is a split database situation

client/server or fe/be. There has not been any migration to SQL as yet.

Excellent, my apologies..I missed that! (good you point this out).

I like Tony's suggestion also.....

 

[SmartbizAustralia]

The biggest issue is where you try to load too many records at once
and this is regardless of whether it is an access backend or sql
server.

Just filtering the data prevents most issues, but normally, speed
issues like you raise are due to too many joins.

e.g. I had a client once who had 20 tables linked to a member table
and wanted every member with tabs showing every linked table.

It took 2 minutes to load.
Then by only loading the members and showing one linked table at a
time, it loaded in 2 seconds.

The design is sooo important....

Regards,
Tom Bizannes
Microsoft Access Development
Sydney,Australia

 

[David W. Fenton]

I did state at the outset that this is a split database situation
client/server or fe/be.

A Jet back end with an Access front end is not by any stretch of the
imagination "client/server," because there's no "server" involved
(except as a file server). By your definition, editing a Word
document stored on a file server would be "client/server" and that's
simply not what the term means.

 

[Paul_in_NZ]

Forgive me.....

As Albert has already revealed, I'm a relative novice on Access. 27 years of
working with clients and servers.....it's hard to break a mindset.


I feel suitably rebuked.

 

[Paul_in_NZ]

As I stated earlier, there is an awful amount of debris in both the FE and
BE. There are several tables, forms and reports on the FE that are not used,
yet link to to the BE. Similarly there are several tables and fields within
valid tables on the BE.

After I get to clean all of this up, I sense your comment is going to prove
some worth.

P.

 

[Paul_in_NZ]

Ahh, some guidance and not some echoes of frustration.

Tony's "advice line", which you share, is getting attention right now. The
AV side of things, was the first thing that I checked, before I even tackled
Oplocks.

P.

 

[Paul_in_NZ]

Thanks for this link Tony. I had found it yesterday and bookmarked it before
being called to another site.

I hadn't even thought to check the setting under 'LanManWorkstation'. I'll
find out later how well it works.


P.

 

[DAVID]

A Jet back end with an Access front end is not by any stretch of the
imagination "client/server," because there's no "server" involved
(except as a file server). By your definition, editing a Word
document stored on a file server would be "client/server" and that's
simply not what the term means.

Oddly enough, that's exactly what the term meant
when we all had banyan vines or novell data servers.

The native MSDOS database primatives (used by
JET) are redirected by the file re-director
(part of the client) to the network server.

I know you know that: I know you know that Word
doesn't actually do that: It's just good to see
that someone else knows it as well. To often
what I see here is some naive luser saying
that 'Access brings down the whole file' because
'Access isn't client/server - it just uses
a file'

(david)

 

[David W. Fenton]

Oddly enough, that's exactly what the term meant
when we all had banyan vines or novell data servers.

It was wrong then, too. There is a clearly defined meaning for the
term that has existed for a very long time, as long as
mini-computers have existed to start replacing dumb terminals.

The native MSDOS database primatives (used by
JET) are redirected by the file re-director
(part of the client) to the network server.

This is all file system redirection, and has nothing to do with
client/server *applications*. Yes, the workstation is a client of
the file server, but there is no processing of data on the server,
and no communication between two processes running on two different
CPUs.

I know you know that: I know you know that Word
doesn't actually do that: It's just good to see
that someone else knows it as well. To often
what I see here is some naive luser saying
that 'Access brings down the whole file' because
'Access isn't client/server - it just uses
a file'

That makes me crazy, too.

 

[Paul_in_NZ]

To report back on what was done to eventually resolve this issue.

The first thing I'd done before coming to these boards was disable
'Opportunistic Locking' on both server and clients. In the past, and in a
peer2peer situation, this typically worked. Microsoft Support has an article
at http://support.microsoft.com/?kbid=296264

However the problem persisted. I was then steered towards
http://www.granite.ab.ca/access/performancefaq.htm, and systematically went
through the discussions there. Most of them have provided an inkling for
future work, but I was under urgency to resolve the current crisis.

One link,
http://groups.google.com/group/micr..._frm/thread/7188759b0c85bf5a/e9f11abbad2c36b8,
with a reference to 'enablesecuritysignature' proved to be extremely
effective. As did setting 'SharingViolationDelay' as per
http://support.microsoft.com/?id=150384

The query that started this thread, now takes 2-2.5secs to run, whereas
previously it was running to 3minutes.

Even after all of these steps there was still some erratic behaviour when it
came to performance on the clients specific Access app. Checking all the
network cards speed settings revealed some were set to 'Force 100 Full
Duplex', changing these to 'Auto' provided an amazing improvement.

Now the system is stable and performing well, I can focus on development of
the replacement solution. It will have a SQL backend and initially an Access
2003 frontend. The links and input in these threads are, and will, prove
invaluable.


Thanks for the help and the positive input.

Paul_in_NZ

 

[Susie DBA [MSFT]]

Access is client-server

Access Data Projects that is-- it's infinitely more powerful than MDB