Share via

Virtual Catalogs : To materialize or not to materialize

  • Article

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.

Comments

  • Anonymous
    April 28, 2004
    I don't know what MaterializeVC.vbs does, but my experience is that you should not use virtual catalogs to drive a site unless you have absolutely no other option.


    Use them to slice, dice and mix your base catalogs then export the virtual and then reimport it.

    It makes many annoying problems (such as the base catalog names being prefixed to the SKU) go away.

  • Anonymous
    April 28, 2004
    This is great, Vinayak! Thanks for the excellent post. I hope that you continue to share your experience with us all.

  • Anonymous
    May 30, 2006
    Virtual catalogs is a key feature we improved upon in Commerce Server 2007 to allow you to further customize...

  • Anonymous
    April 05, 2008
    I was working with a client and they run into an error that they had reached their Virtual Catalog limit.

  • Anonymous
    June 24, 2008
    PingBack from http://rafael.ranchcredit.com/materialize.html

  • Anonymous
    June 17, 2009
    PingBack from http://patioumbrellasource.info/story.php?id=277

  • Anonymous
    February 10, 2011
    Pingback from  Virtual catalog rebuild hangs | Commerce Server Blog