Posted Date Published 8/13/2009 2:23:16 PM
Catalog Performance Tip
You may notice over time that your site is slow specifically the Catalog calls. In this post I hope to address some performance improvements by doing adding a maintenance task.
Catalog System
I am not going to tell you what the Catalog System is you can always find info in the help files but I will explain some of the internals that slows down over time. Looking at how Commerce Server retrieve data is a bit interesting. The Catalog System has two databases one Your_SiteName_ProductCatalog and another MCSC_CatalogScratch.
All of the Catalogs, Categories, Products and their relationship is held in Your_SiteName_ProductCatalog database. The MCSC_CatalogScratch is a persisted temporary storage. Some Catalog APIs when called internally need to store their data into temp tables and then do a select of that. Any time you perform a search or use paganation among other calls the temporary location is used.
MSCS_CatalogScratch Database
So what is this database? The thought behind this database was the right idea but used incorrectly. If you have written advanced stored procedures you have at one time or another used temp tables. The creation of temp tables are a performance hit. So what the Commerce Server product did was to create a temp database and create it’s temp table in that database. So a table is created when a query is made and the next request that comes a long will do a quick search of system tables to see if a table already exists if so then it will use it else it will create a new table. Over time you have have thousands of tables but that’s not the only bad part. In some Catalog System stored procedures (which I have done sql traces) I have realized that they used hints like KEEPFIXED PLAN. Since multiple queries of different type use these tables it’s possible that you get a plan that is not very optimized and slow down the execution of the stored procedure.
You are a diseases and I am the cure
So how do you fix this. Well you really can’t touch the Commerce Server stored procedures but you can delete the temp tables to help performance. You should ideally have a job\task that periodically goes and deletes these tables. The VBScript that cleans out these tables is located in “C:\Program Files\Microsoft Commerce Server 2007\Tools\CatalogCleanup.vbs” if Commerce Server was installed using defaults. But you don’t have to use the VBScript just use the stored procedure that it calls.
EXEC dbo.ctlg_DropPersistentTablesInScratchDB 0
EXEC dbo.ctlg_DropPersistentTablesInScratchDB 1
There are two values you can pass it zero and one. I can’t remember exactly but if you use the zero value it will only delete tables that are not currently being used. If you pass one then all tables are deleted regardless if they are being used or not. I have done this with some customers who have had over several thousand tables and the stored procedure returned errors not to worry just keep running the stored procedure until you have not errors.
Comments disabled.