BigYellowZone

One of the most common causes for vpasp sites running slowly is that the "prodcategories" table is not properly indexed. By indexing relevant columns on that table you can sometimes speed your site (especially product-search aspects) 10-fold or more. It's surprising how something so minor can have such a positive/large effect.

The prodcategories table is a matrix table that holds the cross-referenced list of products and categories (ie it records which products are in which categories) and it's potentially a very large table because of this matrix aspect.

If you've got an access database, then you need to ensure that you set the following index options on these 2 prodcategories columns:

1) intcategoryid - indexed=yes(duplicates allowed)

2) intcatalogid - indexed=yes(duplicates allowed)

To do that you will need to open your cart's .mdb (access database) file in access itself (as you can't add access indexes via sql statements) and set those options manually on the prodcategories' table's column definitions.

If you're using a sqlserver database, then you need to do the same thing, except you'd use your host's web-based front-end to add 2 new indexes to the table, one new index on each of those columns, set to non-unique.

There are various other options which can be done to help tune things, but those would just be looked at on a site-by-site basis; everyone will have a different setup/situation, so it's hard to have generic fixes for tuning.

If you have a large number of records (especially in the prodcategories table or oitems table) then you should consider moving from access to sqlserver.

Multiple concurrent visitors is another potential problem for access databases; if you have a large number of concurrent (simultaneous) visitors then you should definitely consider moving to sqlserver, as access was never really designed for more-than-one user (it was just bolted-on as an afterthought as time went on in the past)