ACCPAC 200 ERP Bad Performances

[Nibbler01]

we have ACCPAC 200 ERP (v5.5a) installed on about 10 Windows XP Pro workstations and attached to a Windows Small Business Server 2003 R2 SP2. The database is Pervasive SQL 9.50.077.002
All the workstations are installed on a share on the server "\\server\accpac\runtime" as this was indicated by the distributor as the most appropriate installation model to minimize maintenance costs.

The system works but we have some long delays when executing certain operations like:
1- open G/L transaction Batch list (30-40 secs)
2- close a batch window (40+ secs)
3- close the print dialog after printing a batch (20 secs)

we have been measuring the whole system (desktop, network and server) trying to understand what happens during the delays and this is what we noticed:

- for every ACCPAC workstation running we have 75+ files open on the server, this is probabily due due to the installation model (see above)
- the server CPU is most of the time around 0%, reaching peaks of 20-30% when performing some heavy operations but always for very short time (2 sec max)
- the server network usage is normally using 500bytes/sec (<1% of 100Mbps); it goes up to 34% for about 30 secs for exacmple when a workstation is opening a G/L Batch list
- we found three processes related to PSQL: ntdbsmgr, ntbtrv and w3sqlmgr; while the last two never consume any CPU time, ntdbsmgr.exe uses up to 5% of CPU for few seconds for example, when a client is opening the Batch List; this process has about 500MB of memory allocated.

we are running other programs on the server; mostly web based (ASP) reporting apps accessing medium-sized SQL Server DB (1GB); the performances are great, the network usage is low (<1%) thanks to the web system model; the response times are below 2 secs for the most complex report; the server CPU usage goes up to 50% for around 2 secs when executing the queries.
We have a totla of 18 windows xp workstations which normally access the server where all the shared files are stored (27GB of office, autocad, etc.); there is NO performance issue on accessign shared files.

finally, the questions:
- any idea why ACCPAC would take that long to performs the tasks listed above? the delays are not random, they happen all the times;
- how do I verify which database security model are we using? I believe PSQL can still work as BTrieve; the distributor (who did the ACCPAC/Pervasive installation) said that the workstations need to access the actual database physical files with "full control" permissions; this doesn't sound as a network protocol based communication.

I heard that ACCPAC can work with SQL2005, would this offer better performances?

Any suggestion is kindly appreciated.

 

[Jay Converse]

What happens when you turn off your virus checker?

 

[ettienne]

Sounds like you are running Norton Antivirus. Exclude the Accpac program and data files from real time scanning and you should see a remarkable improvement in speed - do this on both the server and workstations.

There's nothing wrong with the way Accpac has been installed, that is the preferred method used by the experienced consultants.

With the newer versions of Accpac the users no longer require full access to the data files, this is taken care of by Pervasive through the ODBC connection to the database. You can also make use of database security in Pervasive, but there is a performance hit.

Accpac can run with SQL2005, performance will be about the same, slower in some processes and faster in others. Opening screens and posting batches will be slower, printing reports will be faster.

 

[Nibbler01]

Thanks for the answers.

There is no resident antivirus running on our workstations just a scanner we run every night; by the way its' not Norton
Actually the long delays were showing only on one workstation which was using a low profile wireless network card (Dlink); that machine could access any other network based service with no problem but when accessing ACCPAC it was showing its performance limits; I guess because ACCPAC network traffic is somehow "important"...at least this is what we have measured.
Removing the wifi card and using a normal cable reduced the delays for that workstation (e.g. 30 secs) to the "normal" delays we have on the others (e.g. 5 secs) so we are fine for now, we may eventually upgrade the network to giganet.

Etienne, your answer about the full access to data file is making me very happy; we have been looking at that share with a lot of concerns in the last years and always been told that it could not be removed; all the users needed to have full control rights over the share and the fodler storing Pervasive data files.

Can you give me more information on which version of ACCPAC should be used to make this happens? Also can you give me any link to some online document which states that this is possibile? OUr courrent distributor still states this is not :-(

THANKS!

 

[ettienne]

Wireless is BAD news for accounting systems, it's OK for browsing the web and so on. Good move to take that out.

You're on the later greatest version of Accpac, nothing else is needed. Users need access to the shared data folder, this is where the license files and Site folder is. From Accpac 5.4 onwards users no longer need full access to the data folders, in prior versions they did. This is documented in the System Administrators Guide.

 

[ettienne]

Just to clarify the statement about the Accpac data files, the data files do not have to be in a shared folder, but users still need Read/Write/Modify permissions to the folder where the data is located. Users will not be able to open the folder in Windows Explorer, but they can run Accpac.