Split DB (2007) on network is very slow

[sk]

I have a split db with linked tables that is VERY slow. the fe is on local
machines and Name autocorrect is off, record-level locking is off, default
open mode is shared. The database is not very large.

I populate listboxes using by putting sql statements in the RowSource
property the call a requery on the listboxes.

I'm also using CurrentDb.CreateQueryDef to make the updates to the data as
needed.

Any thoughts on what I can do to speed things up or check to see what is
holding up the processing?

 

[Bill Mosca]

One little trick is to open a recordset at startup and leave it open until
the database is closed. The recordset doesn't have to contain any records.

Another thing that helps is to have the back end as close to the server root
folder as possible.

 

[sk]

I will try the open recordset. I have no input to the location of the BE and
it is deep. It is slow to even bring up the sign-on screen which does not do
much of anything. All it does is get the network user name and put it in a
textbox. It may take 60+ seconds just to do this. I know the network is a
little slow but the performance of the db is really a big issue.

 

[Daniel Pineault]

I would highly suggest you read Tony Toew's page on Database Performance, see:

http://www.granite.ab.ca/access/performancefaq.htm
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

 

[.Len B]

Pardon me for butting in here Bill but does the open recordset trick only
apply
to 2007?

Would it help to map the deep back end folder to a new drive letter?
--
Len
______________________________________________________
remove nothing for valid email address.
| One little trick is to open a recordset at startup and leave it open
until
| the database is closed. The recordset doesn't have to contain any
records.
|
| Another thing that helps is to have the back end as close to the server
root
| folder as possible.
|
| --
| Bill Mosca, Microsoft Access MVP
| http://www.thatlldoit.com
|
|
| "sk" wrote:
|
| > I have a split db with linked tables that is VERY slow. the fe is on
local
| > machines and Name autocorrect is off, record-level locking is off,
default
| > open mode is shared. The database is not very large.
| >
| > I populate listboxes using by putting sql statements in the RowSource
| > property the call a requery on the listboxes.
| >
| > I'm also using CurrentDb.CreateQueryDef to make the updates to the
data as
| > needed.
| >
| > Any thoughts on what I can do to speed things up or check to see what
is
| > holding up the processing?

 

[Tony Toews [MVP]]

Pardon me for butting in here Bill but does the open recordset trick only
apply to 2007?

No, that applies back to at least A97.

Would it help to map the deep back end folder to a new drive letter?

It might. It depends on what happens behind the scenes with the OS.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

 

[david]

Pardon me for butting in here Bill but does the open recordset trick only
apply
to 2007?

All versions. Less important for A97 and earlier, but only because earlier
versions were faster.

Would it help to map the deep back end folder to a new drive letter?
--

Your mileage may vary. Unless something is wrong, a deep back end folder
shouldn't matter much.

In this case, something is clearly wrong, so it's worth trying.

For example, normally, file servers are set to ignore the permissions on the
middle layers of your deep folder structure. If you have to check those
permissions, a new drive letter won't help.

File servers also have to calculate short-names or long-names for
intermediate layers. If you are having trouble caclculating short-names or
long-names for intermediate layers, a drive mapping would help.

 

[Stefan Hoffmann]

hi,

I have a split db with linked tables that is VERY slow. the fe is on local
machines and Name autocorrect is off, record-level locking is off, default
open mode is shared. The database is not very large.
Any thoughts on what I can do to speed things up or check to see what is
holding up the processing?

Check the server I/O. Is it a Windows Server 2003 or higher? Is Tcp
Chimney active? This may lead to lower throughput.

http://support.microsoft.com/kb/912222

Do you have heavy load on it - many concurrent users?

Take a look at the through put on the network level. NetIO can give you
some base values to compare with.

http://www.nwlab.net/art/netio/netio.html

Do you use Vista?

http://blogs.technet.com/markrussinovich/archive/2007/08/27/1833290.aspx


mfG
--> stefan <--

 

[David W. Fenton]

No, that applies back to at least A97.

I think it would kick in with A95, the first version that deleted
the LDB file on close.

It might. It depends on what happens behind the scenes with the
OS.

No, of course it wouldn't help. A mapped drive letter is for the
"convenience" of the users (I'd say for the "confusion" of the
users, but I've been anti-drive-letter mapping since having to work
with driver-letter-happy Novell network engineers back in the 90s)
-- it is actually resolved to the UNC path by Windows, so you gain
nothing in performance (it may actually be slower the first time
it's used in a Windows session, but only by a minuscule amount since
the main performance drain is resolving the UNC path itself).

Actually, there may be a slight performance improvement from NET USE
with a drive letter that you wouldn't get with no mapped drive
letter, but you can get the same thing by having your logon scripts
have NET USE commands without drive letters for the commonly-used
UNC paths. I don't know if you'd improve performance with a NET USE
for each share on a single server or if one NET USE for one of them
would do the job for all of them. I suspect the latter, but I could
be wrong.

 

[David W. Fenton]

One little trick is to open a recordset at startup and leave it
open until the database is closed. The recordset doesn't have to
contain any records.

You don't actually need to open a recordset. All that's necessary is
to initialize a database variable pointing to the back end, which
creates the LDB file. My dbLocal() function (which you can find in
Google Groups quite easily, since I've posted it a bazillion times)
accomplishes that transparently, though you do need to de-initialize
it in your app's shutdown routine.