By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 2,606 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

Peformance issues on Win 2003 Server

P: n/a
I need some suggestions. We had previously been running our multi-user
Access 2002 applications on a Novell server. Since switching to
Windows 2003 servers a few months ago, we've been having severe
performance issues whenever multiple users are connected. As simple of
a task as appending 100,000 records to a table can easily take over 30
minutes.

These performance issues occur on all of databases, regardless of
whether they are split FE/BE or integrated.

The problems vanish as soon as all other users drop out of a given
database. Under single user load, the very same append may take less
than 45 seconds.

I've been beating my head against the wall trying to resolve this. So
far, I've looked at the TCPAckFrequency settings,
DisableFlushOnCleanup, EnableSecuritySignature,
RequireSecuritySignature, SMB Signing.

Using a sample database, I've also tried moving the database to a top
level directory, moving the database to a different Windows 2003
server, changing linked tables to imported tables and visa versa, and
setting up persistent recordsets.

The problem remains. As soon as a second user access the database,
append queries slow to an absolute crawl.

I'm open to suggestions...if I can't get this resolved soon, I'm going
to have to convert all of these databases over to SQL, and that's not
something I relish.

Aug 24 '06 #1
Share this Question
Share on Google+
14 Replies


P: n/a
We have about one post per day on this issue, and the answer is always the
same:

Try a perssint connection. 9 out of 10 times, this will solve your
problem...Google the groups for this...

So, all that means is that your start-up code opens up a table to the back
end, and KEEPS it open. You can do this with a global reocrdset, or even
your main start-up menu form can be attached to a back end table. This can
be ANY table in the back end..even a one record table designed for this. By
doing this, you keep/force the connection to stay open (your performance
problem is due to the connection, and how it opens. When you have more then
one users, then making the connection to the back end takes FOREVER, and
thus a persistent connection makes this happen ONLY once).

And, that 30 minute code thing, try opening a table to the back
end...minimize it...and run your code (this will simulate a persistent
connection).

A great faq (that mentions the above trick), and other things to check is
here:

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

Go through each of the above suggetions (but, since one user runs ok..then
it is the persistant connection issue).
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com

Aug 25 '06 #2

P: n/a
Albert,

Thanks for the input. As I mentioned in my original post, I had
already tried persistent recordsets with no result. However, your
certainty that this was a persistence issue spurred me to a little
experiment.

I have discovered this morning that if I open two instances of the same
Access database on the same workstation, open a table, or a form bound
to table, in instance "A", and run a query in instance "B", the
slowdown issue will not appear, even when other users are accessing the
same back end.

Unfortunately, I can't duplicate this behavior without opening a second
instance on a given workstation, even using the various techniques for
achieving a persistant recordset. I have tried both bound forms and
global recordsets.

I'm absolutely stumped as to why a second instance of Access on the
workstation will achieve the desired result, while a persistent
recordset within a single instance will not.

Albert D. Kallal wrote:
We have about one post per day on this issue, and the answer is always the
same:

Try a perssint connection. 9 out of 10 times, this will solve your
problem...Google the groups for this...
Aug 25 '06 #3

P: n/a
I'm absolutely stumped as to why a second instance of Access on the
workstation will achieve the desired result, while a persistent
recordset within a single instance will not.
Gollly...that is interesting....

I wonder what is going on here....

Try opening a table (that is a linked table in the front end to the back
end). Any table will do (as long as it has records). Now, just miminze the
table (so, it stays open).

Now test...you should get the same effect as having two copies open.

Hum, perhaps the persistence recodset is not global, or is going out of
scope. (or, no records??). If the mimzine the table trick above works, then
the reocrdset is not holding.....

Something is strange, since by your "persistence" on this matter, you have
managed to reproduce, or at least verify the idea that keeping a connection
open does seem to solve this...
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
Aug 26 '06 #4

P: n/a
Thanks for the suggestion, Albert. Unfortunately, opening a table and
minimizing it (or opening a form bound to a table) doesn't do the
trick. So far, the only methods I've found for fixing the performance
issues are if I am the only user with a link to the back end, or if I
do the aforementioned kludge of opening two instances of the database.

I've also tried opening a back-end database directly, and running a
query there. Unfortunately, the same results...if any other user is in
the database, it runs like molasses, unless I open two separate
instances.

Today I've also tried these experiments on 4 different XP workstations,
and one Windows 2000 workstation, all with the same results. I've
already tried locating the back-ends on a completely different Windows
2003 server. I'm starting to wonder if there's some sort of
misconfiguration that both of my Windows Servers have in common, but if
that's the case, I can't figure out what it could be.
Albert D. Kallal wrote:
Try opening a table (that is a linked table in the front end to the back
end). Any table will do (as long as it has records). Now, just miminze the
table (so, it stays open).
Aug 27 '06 #5

P: n/a
Still haven't come up with a solution on this...today I moved a copy of
one of the databases to an old NT 4.0 server, just to see how it would
behave.

Unfortunately, the exact same results, which shoots my idea of a
Windows Server 2003 misconfiguration.

Anyone else have any ideas on this? I'm confounded as to why I can
achieve a persistent recordset by opening two concurrent instances of
the same database, but I can't achieve it by just opening a bound form
or table within a single instance.

Aug 29 '06 #6

P: n/a
On 29 Aug 2006 12:42:17 -0700, "J Malt" <jm******@maidritesteak.comwrote:
>Still haven't come up with a solution on this...today I moved a copy of
one of the databases to an old NT 4.0 server, just to see how it would
behave.

Unfortunately, the exact same results, which shoots my idea of a
Windows Server 2003 misconfiguration.

Anyone else have any ideas on this? I'm confounded as to why I can
achieve a persistent recordset by opening two concurrent instances of
the same database, but I can't achieve it by just opening a bound form
or table within a single instance.
what is happening to the ldb file of the backend?
Aug 29 '06 #7

P: n/a
"Albert D. Kallal" <Pl*******************@msn.comwrote in
news:F3CHg.467502$IK3.356992@pd7tw1no:
Try a perssint connection. 9 out of 10 times, this will solve
your
problem...Google the groups for this...

So, all that means is that your start-up code opens up a table to
the back end, and KEEPS it open.
All that is needed is a persistent database connection, which
creates the LDB file. You don't need to open any tables.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Aug 29 '06 #8

P: n/a
"J Malt" <jm******@maidritesteak.comwrote in
news:11********************@i42g2000cwa.googlegrou ps.com:
Still haven't come up with a solution on this...today I moved a
copy of one of the databases to an old NT 4.0 server, just to see
how it would behave.

Unfortunately, the exact same results, which shoots my idea of a
Windows Server 2003 misconfiguration.
Jet 4 had performance issues on NT 4 long before Win2K3 Server
existed. There used to be a locking/caching issue for servers that
was one of the first pieces of advice, but I don't see it any longer
in Tony's performance FAQ:

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

If you haven't systematically gone through everything outlined
there, that's where you should direct your efforts.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Aug 29 '06 #9

P: n/a
I'm not a Novell server person, but for windows servers, when you try to
use a desktop application as a server based application (which a lot of
people do with Access) you will encounter the issues that you are
encountering. The real solution would be to use a server based
appication on your server - like sql server. A lot of the design
features of WinServer2003 were specifically aimed at supporting sql
server, sharepoint, .Net 2.0 framework, along with a host of other
features.

Access remains unmatched in the desktop arena of databases - partly
(mostly) because no one is focusing any more energy in the desktop
database arena (for RDBMS). The RDBMS game has moved to server based
databases, and Access is no match for a server based databases like sql
server or Oracle. Access was designed for desktop use.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Aug 29 '06 #10

P: n/a
Buzzer,
I'm not seeing anything odd about the behavior of the ldb. It is
created when the first user gets into the database, as you would
expect. If I examine it with an ldb viewer, there is a user record for
the first user. When I connect as the second user, a second user record
is created. A third record is created when I open a second instance of
Access.

buzzer wrote:
what is happening to the ldb file of the backend?
Aug 30 '06 #11

P: n/a
Thanks David. I've seen that FAQ and methodically stepped through every
suggestion over the past several weeks. I'm at the point now where I'm
trying to understand what is actually occuring that fixes the issue
when I'm opening two instances on my workstation as opposed to using a
persistent recordset.

David W. Fenton wrote:
Jet 4 had performance issues on NT 4 long before Win2K3 Server
existed. There used to be a locking/caching issue for servers that
was one of the first pieces of advice, but I don't see it any longer
in Tony's performance FAQ:

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

If you haven't systematically gone through everything outlined
there, that's where you should direct your efforts.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Aug 30 '06 #12

P: n/a
Rich,

I wish I could have retained my Novell servers...my applications ran
far more smoothly, but alas, I had no choice but to move on.

Since I'm approaching the 2Gb limit on some of these Access databases,
my strategy is obviously to migrate into a SQL datastore (either
SQLServer or Postgres). Unfortunately, I have around 30 Access
databases that are closely integrated with each other that will require
a lot of massaging to get into compliance with SQL naming standards, so
I've got to come up with a short-term fix for these perfomance issues
before I even have the breathing room to start converting.
Rich P wrote:
I'm not a Novell server person, but for windows servers, when you try to
use a desktop application as a server based application (which a lot of
people do with Access) you will encounter the issues that you are
encountering. The real solution would be to use a server based
appication on your server - like sql server. A lot of the design
features of WinServer2003 were specifically aimed at supporting sql
server, sharepoint, .Net 2.0 framework, along with a host of other
features.

Access remains unmatched in the desktop arena of databases - partly
(mostly) because no one is focusing any more energy in the desktop
database arena (for RDBMS). The RDBMS game has moved to server based
databases, and Access is no match for a server based databases like sql
server or Oracle. Access was designed for desktop use.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Aug 30 '06 #13

P: n/a
>>Unfortunately, I have around 30 Access databases that are >>closely
integrated with each other that will require a lot >>of massaging to get
into compliance with SQL naming >>standards, so I've got to come up with
a short-term fix for >>these perfomance issues before I even have the
breathing >>room to start converting.

Jet Sql is almost the same as Transact Sql (sql server sql). The only
Jet sql functions that Tsql doesn't support would be the First, Last
functions, the IIF fucntion and any user defined functions.

I share the following from personal experience. I took over an Access
project years ago where queries were taking upwards of 45 minutes to
run. They had the Access backend on a server, each person had a front
end. After much heated deliberation, I convinced this crowd to step up
to sql server. Now the queries took 10 seconds to run. I brought
everything up to the standards of conventional RDBMS programming -
proper normalization... I encountered much resistance because the
original developers were not trained in conventional RDBMS developement
and did not understand normalization. Eventually, I threw in the towel
and moved on (these people had the nerve to call me to ask me how to do
stuff later on). I have never encountered resistance like that again.
My point is - if you are going to pursue RDBMS development - do it
correctly from the beginning - otherwise you will never solve the
problem.

I would start out by migrating your data to Sql Server tables. Then you
can at least use ODBC to link to the tables and retain your Access code
in the front End. But for the amount of stuff you have I would step it
up to the .Net environment.

And a quick pitch for .Net - yes it is OOP. But once you learn OOP (and
.Net OOP is EASY), you will find that developing in .Net is Way easier
than Access (that would be VB2005) - it holds your hand almost every
inch of the way, plus you have all the benefits of OOP (like Inheritance
- the biggest feature). IF you set a form property (at the form level)
like font size of 7.5, every control you place on the form will inherit
that property by default... If you have a lot of controls (lots of
textboxes) this is a real time saver. And the list goes on. Microsoft
took all the issues from Access, VB6 and the rest and after .Net 2002,
2003, the .Net 2005 version is the best fix of all the isuess.
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Aug 30 '06 #14

P: n/a
Thanks for the advice, Rich. I think our upgrade to MS SQL is more
imminent than I had previously thought. I spent yesterday and today
upsizing some of our databases onto MS SQL eval, and I was pretty
surpised at how smoothly it transistioned.

Still, I wish I could could figure out this particular perfomance
issue. I hate admitting defeat.

Rich P wrote:
(snip)
I would start out by migrating your data to Sql Server tables. Then you
can at least use ODBC to link to the tables and retain your Access code
in the front End. But for the amount of stuff you have I would step it
up to the .Net environment.

And a quick pitch for .Net - yes it is OOP. But once you learn OOP (and
.Net OOP is EASY), you will find that developing in .Net is Way easier
than Access (that would be VB2005) - it holds your hand almost every
inch of the way, plus you have all the benefits of OOP (like Inheritance
- the biggest feature). IF you set a form property (at the form level)
like font size of 7.5, every control you place on the form will inherit
that property by default... If you have a lot of controls (lots of
textboxes) this is a real time saver. And the list goes on. Microsoft
took all the issues from Access, VB6 and the rest and after .Net 2002,
2003, the .Net 2005 version is the best fix of all the isuess.
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Aug 31 '06 #15

This discussion thread is closed

Replies have been disabled for this discussion.