Factors that may affect site performance and cause SQL Server errors
If you add too many base catalogs into a virtual catalog, you may affect site performance in the following areas:
- Web site browsing
- Virtual catalog rebuilding
- Staging service
Therefore, you must balance overall site performance and business needs.
The following factors may affect site performance or cause SQL Server errors to occur
- The number of base catalogs that are included in a virtual catalog
- The number of base catalog categories and products that are included in the virtual catalog
- The numbers of categories and the hierarchy structure of these categories and products
- The numbers of the properties for these categories and products.
- The numbers of the relationships among categories and products
- Virtual catalog materialization
Note Sometimes, virtual catalog materialization is the only option to avoid SQL Server limitations.
You may have to adjust these factors accordingly when site performance decreases.
Issues that may occur
When you include lots of base catalogs in a virtual catalog, you may experience one of the following issues.
You receive error message 4414
If you run SQL Server 2000 Service Pack 3 (SP3) or an earlier service pack of SQL Server 2000, you may receive the following error message:
Msg 4414, Level 16, State 1, Line 1
Could not allocate ancillary table for view or function resolution. The maximum number of tables in a query (256) was exceeded.
If you run SQL Server 2000 Service Pack 4 (SP4), you may receive the following error message:
Msg 4414, Level 16, State 1, Line 1
Could not allocate ancillary table for view or function resolution. The maximum number of tables in a query (260) was exceeded.
This issue occurs because of a limitation in SQL Server 2000 that only enables a maximum of 256 tables joining for a database view. A Commerce Server virtual catalog is implemented as the database view before the virtual catalog is materialized.
To resolve this issue, use one of the following methods:
- Materialize the virtual catalog.
- Migrate to SQL Server 2005, and then install the latest service pack for SQL Server 2005.
- Adjust the factors that are listed in the "Factors that may affect site performance and cause SQL Server errors" section.
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
828269
FIX: A Transact-SQL query that uses views may fail unexpectedly in SQL Server 2000 Service Pack 3
You receive error message 511 or error message 8618
You may receive one of the following error messages in Catalog Manager and site browsing:
Error message 1
Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8081 which is greater than the allowable maximum of 8060.
Error message 2
Msg 8618, Level 16, State 2, Line 1
Could not get the root products for the catalog. ---> System.Data.SqlClient.SqlException: The query processor could not produce a query plan because a worktable is required, and its minimum row size exceeds the maximum allowable of 8060 bytes. A typical reason why a worktable is required is a GROUP BY or ORDER BY clause in the query. If the query has a GROUP BY or ORDER BY clause, Consider using prefix (LEFT()) or hash (CHECKSUM()) of fields for grouping or prefix for ordering. Note however that this will change the behavior of the query.
To resolve this issue, use one of the following methods:
- Decrease the number of custom properties that are used for categories and for products that are included into the virtual catalog from base catalogs.
- Materialize the virtual catalog.
- Adjust the factors that are listed in the "Factors that may affect site performance and cause SQL Server errors" section.
A time-out occurs when you try to access the virtual catalog
When you try to access the virtual catalog and browse the Web site, a time-out may occur.
This issue occurs because the virtual catalog may include categories and products from lots of base catalogs. When this behavior occurs, access to the virtual catalog may be slow, and you exceed the time-out period that is set in the Web site.
To resolve this issue, increase the time-out value in the Web.config file of the Web site. For example, modify the following code example.
<system.web>
<httpRuntime executionTimeout="190" maxRequestLength="4096" useFullyQualifiedRedirectUrl="false" minFreeThreads="8" minLocalRequestFreeThreads="4" appRequestQueueLimit="100" />
</system.web>