We recently upgraded to Accpac 5.5a on MSSQL 2005.  The server has dual 4-core processors with 8GB of RAM.  The NIC is gigabit.  There is nothing else running on the server.  All workstations have at least 2GB of RAM.

My users are saying the new system is slower than the 7-year old Pervasive implementation.  It's inconsistent and not always reproducable. They're telling me everything is slow: posting invoices, adding payments and loading customers to name a few things.

Our databases are not huge.  The biggest one is 13GB.  I ran an audit on MSSQL and noticed Accpac produces a suprising # of transactions for simple tasks, but that shouldn't be bringing performance down.  I only have 8 users.

Any ideas?

Thanks.
Brian

13 GB is actually a good sized database for Accpac.

What modules are installed?  If you're using PJC, then the speed decrease is expected.  It's slower (by a lot) on MS-SQL than on Pervasive.

I did some more testing and found posting an invoice for 1 users took 20 seconds.  For another user it took about 3 seconds.  The faster user was plugged into a 1GB network port.  The slower one was in a 100MB port.  We've decided to upgrade everyone to 1GB nics and replace the switch.

However, this should not be necessary!  SQL Server 2005 ships with an auditing tool that captures the SQL statements executed in a specific time period.  I audited the posting of an invoice.  The results are staggering.

-There were over 3000 commands sent to MSSQL during the post
-1150 of the results were the command "execute sp_bindsession null"
-This SELECT statement was executed 374 times: 

select top 1 "HOMECUR",...,"RATESRCE" from "CSCRH"  with (index(CSCRH_KEY_0) /*,updlock*/)  where  "HOMECUR"='USD' and "RATETYPE"='SP' option(keep plan)

I'm hoping there are some ACCPAC technical people reading this.  Is it really necessary to execute the exact same query 374 times?  Surely the post can be written to perform less than 1150 sp_bindsession calls!  No wonder you're maxing out our 100MB NIC.  It's a good thing I don't have 100 users or I'd have to team 3 NICs on the server just to keep up with the workstations.

This is very disappointing.  I understand that sometimes a simple record update can cascade into this situation, but it something is very wrong with this code.  I hope ACCPAC plans to resolve this problem in version 6.

Brian

Depending on the complexity of the setup, it may be needed.

I've done lots of installs with 100MB connection.  It is slower than a 1 GB one, but shouldn't be that bad.  Odds are you have something else happening on the network.  (The last couple of times I saw a performance drop like that, it was a bad/failing switch).

We're using AR,AP,OE and IC.  The switches are relatively new and are Cisco, so it's good equipment.  Plus it's the same switch we used with the Pervasive implementation.

Have you seen an MSSQL implementation on 100MB?

There are a number of things you could/should do:

1. Slap in another 8GB (or more) of RAM. Cheap and effective.

2. I am assuming you installed and are running 64 bit versions? If not, even the 8GB of RAM is useless.

3. Seperate the data and log files into two drives.A typical SATA drive will only give you 70-100 IOPM's and can be a limiting factor in SQL performance.

4. Where is your Accpac installed - server or local?

> 1. Slap in another 8GB (or more) of RAM. Cheap and effective.
We have 8GB now.  We're ordering another 6 or 16GB.  I noticed it's using everything it has now for cache.

> 2. I am assuming you installed and are running 64 bit versions? If not, even the 8GB of RAM is useless.
We're running 64-bit.

> 3. Seperate the data and log files into two drives.A typical SATA drive will only give you 70-100 IOPM's and can be a limiting factor in SQL performance.
All drives are 15k RPM SAS.  C drive is on a mirror.  Data and logs are on separate logical drives on a single RAID 10.

> 4. Where is your Accpac installed - server or local?
Local. We're using SQL Native Client ODBC connections to connect.  We were using the SQL connection built in to Accpac, but I prefer the ODBC connections because they work with MSSQL integrated security instead of funneling everyone through the same user.

No one has responded to my concerns about the redundant SQL calls Accpac makes.  Can I get a Sage response on this?  (Do Sage techs read this forum?)  Can I expect this to be resolved in a future release?

Thanks.
Brian

I doubt anyone from Sage will reply, what you describe is how it's always worked.  Accpac's legacy is from the transactional model of Btrieve and Pervasive, and is very single row/cursor heavy.  Even though great strides have been made in the last few years, the Pervasive pieces are still there.  That's a key reason why WebAccpac runs so poorly. 

Version 6 is a major rewrite, but none of us in the field have gotten our hands on it yet to give you an answer.  All we know is that it will be a thin client, which implies drastically reduced network traffic.

 you can try to double check the transaction log of your cisco switch. Any error detected. A bad switch will casued ACCPAC retry the operation. I have an experience that my cisco switch (10/100) setting losted. ACCPAC performance (18GB database size) drop from 2-3 sec per invoice post to 40sec per invoice post.