Cart Performance Issues

[Jeffr_ca]

Hi All,

We've been running AC 7.0.3 for a couple years now with really good performance and useability. More recently, I've noticed some performance issues that seem specific to when items are in a cart.

PLATFORM: ASP.NET
VERSION: 7.0.3
BUILD: 12458
MSSQL v2008
AC SCHEMA v2005

When browsing the site with an empty cart, the site response is very good without any delays. As you begin adding items to the cart, you will notice performance begin slowing. By time you get to 5 or 6 items the performance has dropped to where it's annoying. If you begin removing items from the cart, response improves and if you empty the cart entirely, performance is excellent again. The mini-basket is displayed in the sidebar.

Now, this problem seems to be specific to individual sessions, because if you open another browser on the same computer and begin browsing with an empty cart, performance is fine....even though in a parallel browser session with multiple cart items, the performance is slow.

I don't have a huge catalogue or huge number of users, but do use variants on some products. But even if you add cart items that have no variants, the performance problem can be recreated.

I've run through some database cleanup tasks (anonymous users, page views and even deleting abandoned baskets) but the problem persists. The entire database is under 150mb and because the site had worked well in the past, I'm more suspicious of the server environment (shared hosting). I have seen timeout errors with order confirmation emails not being sent and various other timeout issues and this increases my concern with the hosting system. There have been other hosting timeout issues that I've identified aside from the cart that leads me to believe that the server is overloaded with other users/tasks.

Are there any other database particular cleanup or repair tasks that I should undertake to optimize performance before I start considering a different hosting solution?

Thanks for any suggestions!

-Jeff

http://www.woodessence.com

[jmestep]

We have seen slowdowns on sites where a lot of the products have variants and inventory set on the variants, but it has usually come with over 20 products in the cart. We have seen a big slowdown in older versions of Able with a lot of tax rules also. The basket has to go thru a lot of things when it does a recalculate- variants, kits, discounts, coupons, taxes.

[Jeffr_ca]

Thanks Judy,

Relative to the variants showing on some other sites (like Hairbowcenter.com) ours would have a much smaller catalogue and in most cases, fewer variants. We don't have a lot of tax rules either and considering it worked well before, I'm fishing for ideas on something that has maybe gone wrong in the database that is causing every click to take longer than normal.

That or backlog at the server.

-Jeff

http://www.woodessence.com

[AbleMods]

Have your hosting provider run a full SQL 'reorganize' on your Able database. See if that boosts your site speed at all.

A reorganize command tells SQL to compact and re-arrange all your indexes so they are back to optimum efficiency.

If that doesn't show a noticeable improvement, your problem is the environment. Especially since you've already stated you have seen other problems as well.

Your hosting provider should have the db reorganize scheduled to run at least once a week on your store database.

I ran into this so much, I ended up writing a page that analyzes your db and does the reorganize for you. That way you don't even have to involve your hosting provider. http://www.ablemods.com/Clean-Indexes-U ... 48C18.aspx

When indexes get slow, I notice it most on the backend reports. Especially affiliate reports. Some of my clients would wait 3-4 minutes for a report to run. After reorganizing the store db, the exact same report takes 6 seconds. Knocks you right out of the chair the first time it does that to you

[Jeffr_ca]

Thanks for the help Joe,

We're continuing to work on the problem.... which I still believe is host environment related as the performance is so sporadic. One moment it seems fine (or at least tolerable) and then the next moment it's painfully slow. The database (<150mb) is tiny in relative terms to other sites.

-Jeff

[dgoranov]

As Joe pointed out, it is important to have a database maintenance plan set up on the DB server. If your site database is small then most probably the issue is hosting provider related. Most hosting
providers offering VPS Servers use iSCSI attached storage which may cause sporadic performance issues if there is a high private network load. A couple of things that will increase site performance in the
long run.

- disable page tracking : Administration > Configure > Page Tracking
- set low value for Anonymous User Maintenance : Administration > Configure > Maintenance (for low traffic site 7 days is OK)
- add additional indexes to ac_Users table:

CREATE NONCLUSTERED INDEX [idx_ac_Users_LoweredUserName] ON [dbo].[ac_Users]
(
[LoweredUserName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [idx_ac_Users_UserName] ON [dbo].[ac_Users]
(
[UserName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO