Welcome to MSDN Blogs Sign in | Join | Help
Office Accounting - performance tips

I promised in my first post that I would talk a bit about performance, I do have some tips and the list will probably grow over time, however a lot of things will affect the performance on your individual PC, so it is very hard to give hard advice on this. At the same time there is a lot of information online about how to make your PC run faster, so as you probably guessed already I am not going to even try to cover everything here, instead I will try to mention a few of the things I have seen have an effect on Office Accounting … enough disclaimer J here it goes ….

 

If your Office Accounting experience feels very slow there are a couple of things you should check (most tips would have affect on Small Business Accounting if that is the accounting program of your choice).

 

Running processes: Are you running a lot of programs and / or processes on your machine at the same time? A lot of people like to run a lot of programs at the same time (I am definitely one of them), and while that is very convenient it will take up a lot of resources on your PC! Your experience in Office Accounting may improve if you try to limit the number of applications that you are running. The same thing goes for the processes on your machine! And you may not realize that you are running a lot; often when you buy a new PC it will be pre installed with all kinds of programs and some of them may be running as processes on your machine. I highly recommend removing these programs if you don’t use them anyway.

 

Enough RAM? This one is a bit like the one above, one of the important resources on your PC is your RAM, and so another way to go rather than closing down everything before using Office Accounting could be to add a bit more memory to your machine (we have seen this have a huge impact before). Yeah, yeah… I know you will be thinking that developers just user more and more RAM without thinking about it, but we are actually using a lot less memory in this version of the application than the last one and we hope to improve even more in the future.   

 

Running old SQL database instances; if you were using Small Business Accounting 2006 before upgrading to Office accounting 2007 there is a good chance that you now have 2 SQL instances on your machine. If you are no longer using SBA 2006 you should uninstall the SQL server for it. In Add or Remove Programs it will be called “Microsoft SQL Server Desktop Engine (MICROSOFTSMLBIZ)”. Same thing goes if you have been experimenting with installing to different SQL instance names, you should uninstall the ones you are no longer using.

 

SQL needs “Repair”: Sometimes the database that Office Accounting is running on gets in a state where it can be helpful to run some optimization scripts on the database. We have build this into Data Utilities in Office Accounting, in this dialog go to the “Advanced Tools” tab and click “Repair” (Data Utilities can be found under File/Utilities/Data Utilities… in the program or as a separate program in your start menu). In some situations this can magically have a huge impact on the performance that you experience in the product. It will mostly affect time used in the database such as time used while saving.

 

Cold performance: After each restart of the machine (or if it has been a while since you used Office Accounting), the Office Accounting files will be removed from the cache of your PC and the application will feel somewhat slower, this is normal. This also goes for the speed of the database… you will feel this the most if the first document that you are creating is a quick small document, as you will get to saving the document before we have been able to fully cache the program in the database. (You may notice that CPU usage goes up for a while after the first time you open any kind of document, this is because we are doing the caching). I know that you are probably thinking; “why should I care about stuff like this”, and naturally you shouldn’t. Just think happy thoughts if you get into this situation often (or simply give the application a few seconds to put stuff in the cache - let the CPU usage settle - before you hit save the first time), and you now know that the next saves should be faster.

 

First install: You might feel a similar thing after the first time you install the program. To speed up the install process we moved some of the optimization of our files out of setup and we are now doing it at a later. This means that the experience that you get the very first time you run the program may be a bit slower than what you will see later in your everyday use of the program (if you are the technical type I am talking about running ngen on our files).

 

If any of this (or anything else for that sake) made your experience running Office Accounting better, please let me know! We may be able to use your feedback to make future versions even better…
Posted: Monday, November 13, 2006 7:01 PM by Jorn

Comments

shoregl said:

Thanks for the tips, Jorn.  Can you (or anyone else) offer some guidelines regarding the number of customers that 2007 Pro can support with satisfactory UI performance?  1,000?  10,000?  Actually, I'd be interested in other performance metrics you can share too.

Thanks,

Garrett

# November 16, 2006 12:44 PM

Jorn said:

Well as you probably guessed I can’t give you a very specific answer here as everything depends on the machine you are running on, if you have multiple machines going against the same company (over a network) and so forth.

There is no hard limit but you will see a difference in performance from having 10 customers to having 10000. I can tell you that we have been testing with thousands of customers, vendors, items etc and it works absolutely fine.

Jorn

# November 16, 2006 5:44 PM

Jorn Lindhard Mortensen's blog said:

Previously I did a post with a few general performance tips for Office Accounting . I just saw a question

# March 28, 2007 4:16 PM

ITGuyt said:

If you remove MICROSOFTSMLBIZ will that affect business contact manager for Outlook?? Thanks!

# June 14, 2007 7:42 PM

Jorn said:

It can, it depends on the version of BCM. Both of these products has moved to the MSSMLBIZ instance in the new versions.

If you do not know, you can test this by stopping the service and checking if everything still works, if you run into problems you can just start the service again.

/Jorn

# June 14, 2007 7:51 PM

ITGuyt said:

Thanks Jorn,

I was able to get rid of the other instance and everything is still working.

On another note, we are still having performance issues after following your tips. The biggest problems are when trying to link contacts to accounting from Outlook the screen takes anywhere from 3-5 minutes to come up. Also, when more than one user runs accounting at the same time it gets sluggish and usually results in some of the machines needing to restart SBA. Otherwise when one person is connected everything works fine.

We are running the server which is a Windows XP Professional P4 2.6 GHz with 1GB of RAM and its sole purpose is a server. We have 5 other workstations that have the accounting software and ship rush add-in software. Out of the five machines 2-3 of them run SBA at the same time. The company file (.sba) is stored on a different machine, not sure if that would make any difference in performance. If you need any more information on our setup let me know. I would really appreciate any feedback that you can provide. It would make the users of the program a lot happier. Thanks!

# July 19, 2007 3:48 PM

Jorn said:

Hi ITGuyt

Wow, 3 to 5 minutes? That’s really bad! Let me try to ask you a few follow up questions to help me understand what’s going on…

- I’m not sure I understand your scenario fully, could you try to describe step by step what you are doing and when you experience the delay?

- Also do you see the same delay when you try to do the same thing from within Office Accounting

- You mention that your server is only a server, does that mean that you are only running SQL on that machine? Or are you running other server apps on the box? If you are running other things you may want to check how much resources the SQL process is using when you experience the slow performance. (Did you see my post on “Performance tip for Office Accounting on SQL Server ”?)

- When you mention the company file you mean the .sbc file, correct? If so that should not affect performance…

- Do you know how many contacts / customers you handle in your company?

- I might be interesting to take a look at the “What is taking time in my MS SQL database?” post to see if there is any specific SQL statement that is taking a long time – if that is the case that might help me understand the problem.  

# July 19, 2007 4:41 PM

ITGuyt said:

Thanks for the fast response. Yes, I know… I have been on the Microsoft message board and it seems to have a lot of people complaining about performance. It was just that no one seemed to have any ideas on how to make it better.

What we are doing when it takes a long time to open the screen is in BCM in Outlook is linking to accounting. When you go to Contacts in business contact manager in Outlook and open any contact there is a link to accounting button when the contacts window appears. When you click that button the following screen can take anywhere from 3-5 minutes to appear which can be very frustrating when this is used a lot. The screen of course is link account to customer.

I am not sure what you meant by “- Also do you see the same delay when you try to do the same thing from within Office Accounting” I am unaware that there was a linking feature from Accounting to BCM. I just thought you could only link from BCM to accounting

The current numbers of contacts / customers are as follows:

.Accounts in BCM -- 955

Business Contacts in BCM -- 1865

Customers in Accounting -- 3135

The only things that I can see running on that machine are SQL Server (MSSMLBIZ) and SQL Server Browser. Other than that there is nothing else running on that machine except McAfee Virus scan and whatever is standard for a Windows XP machine with accounting and BCM on it.

That would be great if you could take a look at what is taking time in my database. Just let me know what is information you need and I will provide. I pulled up the Object Execution Statistics report and have no idea what I am looking for. Thanks again for trying to help.

# July 20, 2007 2:56 AM

Jorn said:

I see, I do not know a lot about that particular area of the application so I may have to ask a few people around the team for specifics.

Regarding the Object Execution Statistics. If you see anything that takes a lot of CPU time or that has a lot of logical reads I would loie to know those Object names…

# July 20, 2007 1:35 PM

Jorn said:

I just talked to one of the developers on the BCM side of things.  And he came up with a couple of extra questions:

- Do you see this issue all the time or only the first time a user does this?

- What are the versions of Office Accounting and BCM that you are running on?

The reason for the version question is this comment form the BCM side:

The “link” button only shows up in version 1 integration. In version 2 integration every Account is automatically linked to a SBA customer, so there’s no UI to manually link records.

This COULD mean that an upgrade would fix your problem…

# July 20, 2007 4:36 PM

ITGuyt said:

The linking to accounting from Outlook takes a long time every time it is initiated. We are running BCM in Outlook 2003 with SP4 and Accounting 2007 with SP1 (ver 2.0.7503.0).

Here are the items I think you may be interested in from the database:

Object # 243 dbo]..sproc_CustomerAccountView has high cpu usage with Avg. CPU at 4,850.74 and reads at 337,981.45. Object # 338 dbo]..sproc_ReCalcConsumptionsLinks has high reads at 226,354.96  and writes at 351.69.

I have exported everything in a PDF document. If you have a way for me to send it to you I will. Thanks Again!

# July 23, 2007 4:39 PM

Jorn said:

We have been drilling into your problem. It looks like there is a problem in the way the link form is coded, unfortunately that means I will not be able to fix the problem for you by running anything on the database or similar.

I only see one way of fixing this right now: Upgrade your BCM / Outlook.

Sorry!!

# July 24, 2007 11:49 AM

ITGuyt said:

We already have all the latest updates available for Office 2003 and accounting. I think what you mean by updates you mean that we should upgrade all machines to Office 2007, which comes with the latest BCM??? This would not have an effect on the other problem where multiple people in accounting at the same time results in crashes/slowness would it?? We seemed to be running pretty smoothly until we upgraded to Accounting 2007. Anyway, thanks for all of your efforts Jorn!!! If we decide to upgrade our office software I will let you know the results.

# July 24, 2007 3:47 PM

Jorn said:

Hang on, does that mean that you had this running with the same BCM version against SBA 2006? and you did not have this problem?

# July 24, 2007 9:30 PM

ITGuyt said:

Right, we started out with BCM around October of last year and then added accounting 2006 in January. I received no complaints until we upgraded to 2007 in March or April of this year.

# July 26, 2007 1:09 PM

pappel said:

We are seeing exponential slow downs for Saves of Vendor Bills and Customer Invoices as we are loading the last 2 years of history in the tables. We are probably stretching the intended capacity for this system in that we have around 100,000 Vendor Bills and about 30,000 customer invoices. We do a lot of detailed shipments for the military, so even though we are not a medium-large comapny, our document volume is much larger than most small companies. New documents are running very fast, but if we try to pull up document from more than a few months back and make changes, it takes between 5-10 minutes to save and soemtimes just errors out and shuts down the system. I have tried some of the SQL memory settings and other things that I have found, but I wondered if you had any information on how we might speed up these saves.

Thanks, Phil Appel

# November 5, 2007 1:33 PM

Jorn said:

Well, I’m glad to hear that saving your new documents is fast :o)

Based on what you are describing I’m guessing that you are using inventory items in your invoices. (naturally this is a guess)

When you are saving bills / invoices for inventory items back in time we will recalculate the FIFO queue for transactions using that item after that date (basically we are updating the cost).

How can you solve this? Well depends on your situation… Maybe you can stop using inventory items, maybe you can start using add more item  

Hope it helps...

# November 5, 2007 5:13 PM

ITGuyt said:

Hello Jorn,

It is me again. I am in need of serious help. The problem that we spoke about has never gone away. The repair utility does not work anymore and the database size appears to be 1.7GB. The people I support using this program have a lot of patience but it is really starting to wear thin. Only one person can get into accounting at any given time and it is still extremely slow for that person. This is making it extremely difficult for anyone to get work done within a decent amount of time. I believe that the problem is that the database is so large, which I think is because of duplicate entries and that the repair utility does not work anymore. I also do not think that it has anything to do with the machine the data is served on either. I suppose that the only solution that we have is to Start over with a new database, clean up unnecessary data from the old database, and then import it into the new one. Is something like this possible? I would need some decent instructions on how to do this if so. If you cannot help me with this then maybe you can point me to someone who think may. I would be eternally grateful and so would some other people around here. If you need to know anything else from me please let me know. Thanks again for all the time you spend addressing my issues.

“Server Specs”:

Windows XP Professional

RAM 1GB

Processor P4 2.6Ghz

Hard Drive 40GB

This machines only function is to server SBA data

# March 8, 2008 8:03 PM

Jorn said:

Hi

What do you mean "The repair utility does not work anymore" does it fail to run or does it just not help?

# March 8, 2008 9:30 PM

ITGuyt said:

Well, it runs for a while and then locks up when a large box opens that display a bunch of coding that I do not understand. Also, when this happens I have to go into SQL studio manager and change the database back to multiuser mode from single user mode because no one can log in anymore.

# March 10, 2008 1:04 PM

Jorn said:

It is possible to run the repair directly in SQL, before doing this please make sure no one is actually using the database, for example by setting it into single user mode. These are the commands that are executed when you run a repair...

DBCC checkDB ([SampleProductCompany2008], REPAIR_REBUILD) WITH NO_INFOMSGS

EXEC dbo.sproc_DeleteInvalidDataFromSBA

EXEC dbo.sproc_RefreshDatabase

EXEC dbo.sproc_ClearRefreshTables

I have this theory that dbo.sproc_DeleteInvalidDataFromSBA may be what is causing problems for you.

# March 10, 2008 2:22 PM

ITGuyt said:

So I need to set the database to single user mode. Then run these commands in SQL Studio Express and omit the  EXEC dbo.sproc_DeleteInvalidDataFromSBA. I am not exactly sure how I navigate in the program to execute these commands, could you provide that information? I was also wondering if you think that database size I mentioned in my previous post is an issue? I am pretty sure that we have a lot of duplicate entries again that is making the database large in size.

# March 10, 2008 2:35 PM

Jorn said:

No, I'm sorry I was not clear, you should run all of the statements (I think sproc_DeleteInvalidDataFromSBA may fix the actual problem)

Also you want to change the sampleProductCompany2008 with your own company name...

To set the database to single user you can run this command:

ALTER DATABASE [SampleProductCompany2008] SET SINGLE_USER

It sounds like you know how to set it back to multi user already, but you can run this command:

ALTER DATABASE [SampleProductCompany2008] SET MULTI_USER

Unfortunately I do not have access to a machine with  SQL Studio Express. But if I remember correctly you have to navigate to the database, then click [New query] either by right clicking or in the menu. The new query should be connected to the correct database. In this Query window you can execute the commands…

Hope it helps

# March 10, 2008 3:35 PM

Jorn said:

I do think the database size you are mentioning COULD be an issue (it is hard to say for sure). If the database has a lot of dublicate entries that could definately be an issue.

It would be interseting for me to actually see this database if the people you support would agree to it. Let me know if that is an option...

# March 10, 2008 3:46 PM

ITGuyt said:

Jorn,

Thanks for the commands to run. I used them last night and it actually completed after about 15 to 20 minutes. I still have not heard a report from the users yet to see if it made a difference. The .sbd file for the database is still at 1.4GB though. I also tried to defragment the hard drive, even stopping the sql service while it ran and it would still not defragment the .sbd file. Last time I got it to work and that seemed to improve things as well.

I would like for you to have a look at our database too. However, I am not sure if that is an option but I will let you know.

Anyway, are there any commands I can run like the ones above that will let me see duplicate items or items that are taking up a lot of room in the database? Thanks!

# March 11, 2008 1:33 PM

Jorn said:

I did not expect the database to get smaller, as we did not shrink the files (separate command)

I do believe that what you call duplicate rows should be gone after running the scripts I gave you.

You should be able to run something like this to see if the database has invalid rows:

select count (*) from AccountTable where IsValid = 0

select count (*) from DocumentTable where IsValid = 0

# March 12, 2008 1:12 AM

ITGuyt said:

I tried those and it returned nothing. Do you have anything else I can try?

# March 12, 2008 10:07 PM

Jorn said:

That is a good thing. It is supposed to return zero or a low number :o)

What do you mean anything else you can try? I may have lost track, what are we trying to figure out now?

# March 12, 2008 10:32 PM

ITGuyt said:

I am trying to get to the bottom of what is taking up the most room in the database. Whether that is identifying large sections or finding entries that have been duplicated. I have done the repair functions per your post but I hope I can do more. This recent activity has been the most progress I have made on this in a while.

# March 13, 2008 11:21 AM

Jorn said:

OK – got it

Two comments:

- As I said we did not do anything that would make the file smaller, again I do not have access to a SQL Studio Express but in the full SQL Server Management Studio you can right click the database and under tasks you will find shrink. If you don’t have that option in the Express version you can shrink by running these commands:

DBCC SHRINKDATABASE(N'SampleProductCompany2008' )

DBCC SHRINKFILE (N'SampleProductCompany2008', 0, TRUNCATEONLY)

- To find out what is taking up space in SQL I would normally use some build in reports in SQL Server Management Studio, I do not know if you have access to those reports in the Express version. If you don’t you can run this query and it should give you the same result

SELECT TOP 10

(row_number() over(order by (a1.reserved + ISNULL(a4.reserved,0)) desc))%2 as l1,

a3.name AS [schemaname],

a2.name AS [tablename],

a1.rows as row_count,

(a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved,

a1.data * 8 AS data,

(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS index_size,

(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS unused

FROM

(SELECT

ps.object_id,

SUM (

CASE

WHEN (ps.index_id < 2) THEN row_count

ELSE 0

END

) AS [rows],

SUM (ps.reserved_page_count) AS reserved,

SUM (

CASE

WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)

ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)

END

) AS data,

SUM (ps.used_page_count) AS used

FROM sys.dm_db_partition_stats ps

GROUP BY ps.object_id) AS a1

LEFT OUTER JOIN

(SELECT

it.parent_id,

SUM(ps.reserved_page_count) AS reserved,

SUM(ps.used_page_count) AS used

FROM sys.dm_db_partition_stats ps

INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)

WHERE it.internal_type IN (202,204)

GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)

INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id )

INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)

WHERE a2.type <> N'S' and a2.type <> N'IT'

# March 13, 2008 12:11 PM

ITGuyt said:

Thanks! I will give these a try. I do have a question about shrinking the database. I thought about it but is it really worth it. I mean would it actually improve preformance and are there any "side effects" or down sides in doing this?

# March 13, 2008 12:18 PM

Jorn said:

It can negatively affect your performance as SQL will have to grow the database file when you add rows to the database.

Will it improve performance? Not sure, I don’t think so.

# March 13, 2008 12:25 PM

Jorn said:

btw, did the repair help at all?

# March 13, 2008 12:25 PM

ITGuyt said:

It seems to have made somewhat of a difference. The program is locking up less and the server does not need a hard reset. It is hard to tell the full impact of the repair as they have had someone working on things in the database this week. I am planning on running the repair next week and having them check it right away.

What do you think about the specs of the machine in my earlier post? Do you think it is adequate for SBA?

“Server Specs”:

Windows XP Professional

RAM 1GB

Processor P4 2.6Ghz

Hard Drive 40GB IDE

This machines only function is to server SBA data

# March 14, 2008 5:23 PM

Jorn said:

The specs? well it is not easy for me to answer, I basically do not know what the bottleneck is...

You could take a look at how much memory pressure and CPU pressure the server is experiencing. The best way to answer it for sure would be to TRY to setup a faster machine with more memory as the server and see if there is a difference (if you have another machine available).

There is a bunch of other things that could also be the bottleneck; network, client machines, other things on the server, slow hard drives etc etc.

# March 14, 2008 6:33 PM
Anonymous comments are disabled
Page view tracker