Access Split Performance Problem

[Julian]

Hi,

The problem is as follows:
A split database exists with the back end on a win2k3 file server. Shares
and directory names meet short naming standards. The front end resides on a
winXP / win2k machine. (As per KB articles 843418 and 814112)

At various points in time, I have tried all the points laid out in KB 889588
(various reg hacks and suggestions like persitant recordsets in the front end
on opening the DB), with the exception of upgrading JET as I am already using
the latest version. In addition I have tried disabling oplocks on the server
and SMB signing on the server.

However, my suspicion of network problems being the cause are not solid for
reasons set out in scenario 2.

History: This seems to have developed since moving the backend from an NT4
server share. The database is propogated yearly, cleaned out, regularly
compacted and the drive is defragmented.

Notable design points: The backend has a lot of relationships, queries are
nested to up to three levels and there are on average no more than 3000
records.

This problem does not affect single table queries, it *does* affect queries
where joins are present, it also affects unjoined tables (via SQL) but where
a column is output (ie a join is created implcitly). If a second table is in
the design view but no columns are selected (From statement eg, FROM X,Y),
this problem does not occur

Scenario 1:
Open front end, open query in design view. Time to open: 1min 5s
Close the query within 5s and it generally opens in 20s. After 5s it
generally takes 1min 5s again. These times are **consistent** and I believe
this is significant.

Analysis: It looks to me like the quick opening times are cached in some
way, but, why after 5 of being open is that cache seemingly lost and why the
consisten time of 1min 5s.

I have run processor and network traces on the server and client during this
time and both show no activity other than spikes on request and delivery of
the information.

Scenario 2:
Open the backend on the client via a mapped drive or UNC. Copy the query
opened in the front end to the backend and open it in design view.
Time to open: instant.

Analysis: this suggests that networking is not the cause of the problems. As
also suggested by the lack of problem fix from the KB article suggestion.


I am absolutely stuck on this as I can find no help or discussion like this.
If anyone can help I would be very very grateful. Please feel free to contact
me via email or IM.

Kind Regards

JS ([email protected])

 

[Albert D. Kallal]

(various reg hacks and suggestions like persitant recordsets in the front

end
on opening the DB),

So, you sure you tried the #1 suggestion here, and we state this suggestion
OVER AND OVER probably once a day, or more in this newsgroup to try the
persistent connection trick. Are you 100% SURE you tried this??

Scenario 1:
Open front end, open query in design view. Time to open: 1min 5s

Again, you try the above, but did you try this with a persistent connection
here? (you don't have to use code to test this...but if the persistent
connection trick does work, then you *can* use code at the start of the
application to open up a table to tithe back end, ..and KEEP it open).

So, open up a table in the table tab (any table in the back end). Now
minimize this table. Ok, at this point we got a persistent connection.

now, open the query in design mode....how long does it take?

As mentioned, 99 out 100 times the persistent connection trick fixes this
type of problem . As mentioned, we state to try this as a solution. So, did
you?

Analysis: It looks to me like the quick opening times are cached in some
way, but, why after 5 of being open is that cache seemingly lost and why
the
consisten time of 1min 5s.

No, why does it suggest that?
As mentioned, the persistent connection trick here is significant. When
ms-access opens a table to the back end, the locking file is created. Since
the locking file is now created, and THEN YOU OPEN THE QUERY, the key factor
here is that a locking file WAS ALREADY CRATED when you stared to open the
query. With the windows XP file security system, this can take FOREVER. So,
once again, if you FORCE and KEEP OPEN a connection to the back end table,
and THEN TRY your tests, the locking file dance is eliminated, as the file
is ONLY created once.

Scenario 2:
Open the backend on the client via a mapped drive or UNC. Copy the query
opened in the front end to the backend and open it in design view.
Time to open: instant.

Analysis: this suggests that networking is not the cause of the problems.
As
also suggested by the lack of problem fix from the KB article suggestion.

Do note, that both the front end, and back end often create these temp
locking files. Likely, opening the backend file direct forces the locking
file to be created right away..and thus the delay is gone. When you put the
query in the front end..the back file locking file is NOT YET BEEN CREATED
until you jump into design mode. This causes some problems as now ms-access
is trying to connect to the back end table to "verify" parts of the query.
And, if it opens, and closes the locking file repeatedly..then you got a
HUGE delay.

So, sure, it not really 100% a networking problem, but the fact is that the
behaviors is CLEARLY DIFFERENT WHEN YOU USED A QUERY TO A LINKED table. And,
that linked table is on the other side of a network.

So, as mentioned, the trick (that we say OVER AN OVER like a broking record
here) is to try the persistent connection trick. (I am not saying it will
solve your problem...but you GOT to at least try it in the above tests...as
opening the front end file does NOT create the locking file in the back end
until you actually jump into design mode, ....so likely "extra" opening, and
closing of the table occurs when you try and use design query mode..and
those opening, and closing...perhaps many times during the design load
process will cause the locking file to be make/deleted ---- and, if you open
the back end directly, the locking file is already created (when you opened
the back end....so no locking file will now have to be creatdd DURING the
desing load).

 

[david epsom dot com dot au]

Files (like the Access LDB lock file) are not typically
disconnected and deleted synchronously. Instead the server,
working on the sound presumption that if you just closed
a file, you may be going to open it again, delays tearing
down connections, which also delays file deletion.

One effect of this is, that if you close a database connection
and then re-open it, you may not have to re-create the LDB
file at all. Of course, if you maintain a persistent
connection to the database, you never have to re-create
the ldb file.

If there is only one Windows User using the data and lock
files, Windows will cache those files on the users workstation.
This cache will be automatically invalidated some time after
you disconnect from the file.

Jet caches data (including indexes) for open views in the
Jet cache. This data is invalidated by a timer in a background
process, but is only refreshed by the background process
while the view is open. You can monitor the effect of the
Jet cache by changing the cache size and the validation
intervals.

(david)

 

[Julian]

Albert,

Thanks for your suggestion. I have tried it, here is the code being used:

Private Sub Form_Open(Cancel As Integer)
Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
Me.FilterOn = True
Set rsAlwaysOpen = CurrentDb.OpenRecordset("tblPersist")

End Sub
Private Sub Form_Close()
rsAlwaysOpen.Close
Set rsAlwaysOpen = Nothing
End Sub

Unfortunately this does not solve the problem, nor does opening a table and
minimising it as suggestsd. But if I have made a mistake, please let me know
as any fix will save me a lot of headaches!

Sorry to disappoint your enthusiasm for this fix!

Kind Regards

 

[Julian]

Thanks David,

Post posting, I did some reading re lanmanserver and oplocks on files. There
are a number of reg hack that various companies who run database products
recommend for win2k3 / 2000 / XP. However, I have had inconclusive results
with these. It appeared to work once and then promptly didnt work once more
people started accessing the backend.

I would be interested in trying out your suggestions. Can you point me
towards any info that details these?

Many Thanks

 

[david epsom dot com dot au]

Jet configuration options:

application.setoption dao.dbPageTimeout, 5000

"The length of time between when data that is not read-locked is placed
in an internal cache and when it's invalidated, expressed in milliseconds.
The default is 5000 milliseconds or 5 seconds."

dbPageTimeout
dbSharedAsyncDelay
dbExclusiveAsyncDelay
dbLockRetry
dbUserCommitSync
dbImplicitCommitSync
dbMaxBufferSize
dbMaxLocksPerFile
dbLockDelay
dbRecycleLVs
dbFlushTransactionTimeout

 

[Albert D. Kallal]

Albert,

Thanks for your suggestion. I have tried it, here is the code being used:

Private Sub Form_Open(Cancel As Integer)
Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
Me.FilterOn = True
Set rsAlwaysOpen = CurrentDb.OpenRecordset("tblPersist")

No, you want to open that persistent table at application startup time...and
LEAVE IT OPEN AT ALL TIMES....

You do NOT want to associate this persistent connection with a particular
form load....

And, you also show code that closes the connection..and that again is what
you must avoided here.

Now, of course, if the above code is part of your initial startup
application startup code that runs, then you got the right idea.

Private Sub Form_Close()
rsAlwaysOpen.Close
Set rsAlwaysOpen = Nothing
End Sub

No...don't close it...KEEP it open. You want this to ONLY occur ONCE at
application startup time.....

Unfortunately this does not solve the problem, nor does opening a table
and
minimising it as suggestsd. But if I have made a mistake, please let me
know
as any fix will save me a lot of headaches!

Sorry to disappoint your enthusiasm for this fix!

Gee, thanks. I really did not want to sound harsh on this. However, make
sure you try the suggesting to keep open the connection BEFORE any of those
application forms and code runs (so, a good place would be in your main menu
switchboard, or main form that launches other forms). So, to be clear, that
persistent connection should only occur at application startup ONCE. This
thus means you need a global reocrdset to accomplish this (or, a form
attached to a back end table, and minimize it). Also, make sure there is at
least one record in that linked table to the BE that you open).

The other possible thing here to check is that if your default printer is
not a local printer, but a network one. I seen that cause problems. And,
last but least, having checked the above, then the only suggestion left is
to check all of Tony's list here:

http://www.granite.ab.ca/access/performancefaq.htm

Also, if you do find the source of the problem, and it is none of the above,
please share the solution with Tony..as it really benefits all of us when
another reason for performance problems is found. And, it would be a
understatement to say that this kind of stuff is VERY frustrating...

 

[Julian]

Thanks David.

I have now solved the problem. It was caused by a table reference to a
separate linked backend. Though the table no longer existed and it was not
referenced in any of the queries that were performing badly, it seems that
Access checks all relationships on a tble used in a query. eg. If PersonID is
the primary key for table 1 and there is a relationship to the now missing
table 2 defined in the database (but not a JOIN in the SQL), the relationship
is checked. As the table is not there, the wait times out at about 60s.

:|

 

[Julian]

Albert,

FYI

It was caused by a table reference to a separate linked backend. Though the
table no longer existed and it was not referenced in any of the queries that
were performing badly, it seems that Access checks all relationships on a
tble used in a query. eg. If PersonID is the primary key for table 1 and
there is a relationship to the now missing table 2 defined in the database
(but not a JOIN in the SQL), the relationship is checked. As the table is not
there, the wait times out at about 60s.

Regards.

Albert,

Thanks for your suggestion. I have tried it, here is the code being used:

Private Sub Form_Open(Cancel As Integer)
Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
Me.FilterOn = True
Set rsAlwaysOpen = CurrentDb.OpenRecordset("tblPersist")

No, you want to open that persistent table at application startup time...and
LEAVE IT OPEN AT ALL TIMES....

You do NOT want to associate this persistent connection with a particular
form load....

And, you also show code that closes the connection..and that again is what
you must avoided here.

Now, of course, if the above code is part of your initial startup
application startup code that runs, then you got the right idea.

Private Sub Form_Close()
rsAlwaysOpen.Close
Set rsAlwaysOpen = Nothing
End Sub

No...don't close it...KEEP it open. You want this to ONLY occur ONCE at
application startup time.....

Unfortunately this does not solve the problem, nor does opening a table
and
minimising it as suggestsd. But if I have made a mistake, please let me
know
as any fix will save me a lot of headaches!

Sorry to disappoint your enthusiasm for this fix!

Gee, thanks. I really did not want to sound harsh on this. However, make
sure you try the suggesting to keep open the connection BEFORE any of those
application forms and code runs (so, a good place would be in your main menu
switchboard, or main form that launches other forms). So, to be clear, that
persistent connection should only occur at application startup ONCE. This
thus means you need a global reocrdset to accomplish this (or, a form
attached to a back end table, and minimize it). Also, make sure there is at
least one record in that linked table to the BE that you open).

The other possible thing here to check is that if your default printer is
not a local printer, but a network one. I seen that cause problems. And,
last but least, having checked the above, then the only suggestion left is
to check all of Tony's list here:

http://www.granite.ab.ca/access/performancefaq.htm

Also, if you do find the source of the problem, and it is none of the above,
please share the solution with Tony..as it really benefits all of us when
another reason for performance problems is found. And, it would be a
understatement to say that this kind of stuff is VERY frustrating...


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal

 

[david epsom dot com dot au]

referenced in any of the queries that were performing badly, it seems

Access checks all relationships on a tble used in a query. eg.

In the table properties of each of the tables in your data
file, you should set 'SubDataSheetName' to '[None]'

(david)