Catalog Code Optimization

  • Article

Several catalog object methods have been optimized to speed performance for a few special cases. The following methods have been optimized for this purpose:

CatalogManager.Search

Category.Products

CatalogManager.FreeTextSearch

CatalogManager.Query

One optimization is in place for the case where the record count is required, but the actual records are not required. Another optimization is in place for the case where the paged records are required in an unsorted manner, but the record count is not required.

Currently, most of the site code (both Retail site and International Retail site code) calls the APIs in the general mode: this means that both the paged records and the record count are requested. However, this method of calling the API is much costlier than the two options just described.

The following code samples demonstrate best practices to take advantage of the optimized features described above:

  • Paging through the products of a catalog
  • Querying catalogs
  • Implementing free text search

Paging through the products of a catalog

The following code sample illustrates catalog product paging that takes advantage of the optimized features:

' This applies only if you do not want the recordset to be sorted
' First get the total number of products under this category
Dim oCatalogManager
Dim oProductCatalog
Dim oCategory
Dim TotalRecords

Set oCatalogManager = CreateObject("Commerce.Catalogmanager")
Call oCatalogManager.Initialize("Provider=SQLOLEDB;Integrated Security='SSPI';Data Source=(local);Initial catalog=catalogdb", True)
Set oProductCatalog = oCatalogManager.GetCatalog("Mycatalog")
Set oCategory = oProductCatalog.getCategory("Mycategory")
'Get the total number of products and families under this category
Set oRs = oCategory.Products(12, , 1, 0, TotalRecords)

'Once you get TotalRecords Cache it and pass NULL for TotalRecords in subsequent calls.
'Use the following call to page through the products
'get the first 10 products. Pass Null to the TotalRecords parameter
Set oRs = oCategory.Products(12, , 1, 10, Null)
'get 10 products starting from the 50th product. Pass Null to the TotalRecords parameter
Set oRs = oCategory.Products(12, , 50, 10, Null)

Querying catalogs

The following code sample illustrates catalog querying that takes advantage of the optimized features:

'This applies only if you do not want the recordset to be sorted
' First get the total number of records 
Dim oCatalogManager
Set oCatalogManager = CreateObject("Commerce.Catalogmanager")
Call oCatalogManager.Initialize("Provider=SQLOLEDB;Integrated Security='SSPI';Data Source=(local);
Initial catalog=catalogdb", True)
Set oRs = oCatalogManager.Search("cy_list_price>0", , "mycatalog", 14, "[ProductId],[VariantId],[cy_list_price],[Name],[Description]", , , 1, 0, "en-us", TotalRecords)
' Cache TotalRecords and do pass NULL for TotalRecords 
' in subsequent calls.
' Use the following call to page through the query results
'get the first 10 records. Pass Null to the TotalRecords parameter
Set oRs = oCatalogManager.Search("cy_list_price>0", , "mycatalog", 14, "[ProductId],[VariantId],[cy_list_price],[Name],[Description]", , , 1, 10, "en-us", Null)
' Get 10 records starting from the 50th product.
' Pass Null to the TotalRecords parameter
Set oRs = oCatalogManager.Search("cy_list_price>0", , "mycatalog", 14, "[ProductId],[VariantId],[cy_list_price],[Name],[Description]", , , 50, 10, "en-us", Null)

Implementing free text search

The following code sample illustrates free text search that takes advantage of the optimized features:

' This applies only if you do not want the recordset to be sorted
' First get the total number of records 
Dim oCatalogManager

Set oCatalogManager = CreateObject("Commerce.Catalogmanager")
Call oCatalogManager.Initialize("Provider=SQLOLEDB;Integrated Security='SSPI';Data Source=(local);Initial catalog=catalogdb", True)
Set oRs = oCatalogManager.Search(,"books" , "mycatalog",_
   14, "[ProductId],[VariantId],[cy_list_price],[Name], _
   [Description]", , , 1, 0, "en-us", TotalRecords)
' Cache TotalRecords and do pass NULL for TotalRecords 
' in subsequent calls.
' Use the following call to page through the query results
' Get the first 10 records. Pass Null to the TotalRecords parameter
Set oRs = oCatalogManager.Search(,"books", "mycatalog", _
  14, "[ProductId],[VariantId],[cy_list_price],[Name],_
   [Description]", , , 1, 10, "en-us", Null)
' Get 10 records starting from the 50th record.
' Pass Null to the TotalRecords parameter
Set oRs = oCatalogManager.Search(,"books", "mycatalog",_
   14, "[ProductId],[VariantId],[cy_list_price], _
   [Name],[Description]", , , 50, 10, "en-us", Null)

Copyright © 2005 Microsoft Corporation.
All rights reserved.