Virtual Catalogs : To materialize or not to materialize

Commerce Server 2002 introduced a feature which allowed content from one or more base catalogs to be aggregated into a virtual catalog using inclusion and exclusion rules. The values for the properties of the products and categories came from the base catalogs. The virtual catalog simply contains references to these included products and categories. In sql terms this means that the virtual catalog is defined as a view which is a union of the included base catalog tables. If the virtual catalog contains a number of base catalogs and/or the base catalogs themselves contain a lot of products and categories executing sql queries on this view can result in poor performance. Given the limitations of indexed views we could not index the virtual catalog view. In order to address performance issues without restricting the number of base catalogs that can be added,  CS2002 allowed virtual catalogs to be materialized. In sql terms this means that the virtual catalog view is now materialized to a physical table.Since the queries now run against a physical table the run time queries execute much faster.

Things you should know about virtual catalogs and materializing:

  1. Materializing a virtual catalog improves runtime performance. This also results in the data from the base catalog being duplicated in the virtual catalogs containing that base catalog. 
  2. By default virtual catalogs are not materialized. You should use the MaterializeVC.vbs script in the “Program Files\Microsoft Commerce Server 2002\Support” folder of your Commerce Server installation to materialize or non-materialize one or all virtual catalogs. The virtual catalogs will be materialized into a physical table the next time you rebuild the virtual catalog. You can also rebuild the virtual catalog using the MaterializeVC.vbs script.
  3. If you materialize a virtual catalog, you will have to rebuild the virtual catalog after any change you make to the included base catalogs, for the change to take effect in the virtual catalog. For eg if you include a base catalog in a virtual catalog, materialize the virtual catalog and change the description of a product in the base catalog you have to rebuild the virtual catalog to see the changed description of the product in the virtual catalog.
  4. Limit the number of rules in the virtual catalog. As a general rule you should rely on catalog and category level rules.

So when should you materialize a virtual catalog?
The answer to this question depends on a number of factors:

  1. The number and size of base catalogs. If the virtual catalog contains a number of base catalogs and/or the base catalogs contain a lot of products and categories then materializing the virtual catalog can result in better runtime performance.
  2. The frequency with which the content in the base catalogs and virtual catalog change. If the content of the base catalogs changes frequently then materializing the virtual catalogs will mean frequent rebuilds which might offset the performance gain.
  3. If the desired performance without materializing a virtual catalog meets your needs then you should not materialize the virtual catalog.
  4. The tradeoff between duplicating base catalog data and improving virtual catalog performance.

        As a general rule of thumb you should start without materializing a virtual catalog and determine based on the operations that you wish to perform on the virtual catalog, if the desired performance is acceptable or not. If the desired performance is not acceptable then you should consider materializing the virtual catalog.
       On a related note, we did make a number of performance improvements for virtual catalogs in the upcoming Commerce Server Feature Pack to address the current rebuild and runtime performance issues for virtual catalogs.
        And finally, this webcast on Virtual Catalogs.