Introduction

During BMECat import all imported products get the information of the corresponding BMECat catalog. This information was stored as product custom attribute BME_Catalog_ID, which is stored in an XML structure in the product table attributes column. Executing a SQL query with a condition in an XML structure is slow.

Change

The attribute which stores the BMECatalogID has been moved to a native product table column (DCA) called BMECatalogID. Using a native table column improves the performance of the BMECatalog import. When a new version of the same catalog is imported, the BMECatalog import identifies the products related to the previous BMECatalog import and deletes them. Since the custom attribute BME_Catalog_ID is now a direct custom attribute (DCA), accessing it from the storefront, back office, via REST (via the ORM layer) has not changed. But queries in an ICM customization, which access the value of BME_Catalog_ID directly, must be changed. All ICM standard queries accessing BME_Catalog_ID were changed.

Migration

All existing BMECat products in the database are automatically migrated in the DBPrepare (DBMigrate). Depending on the number of entries, this process might take several minutes.

All standard queries that deal with BMECat imported products have been adjusted. In case there are custom queries which access the BME_Catalog_ID directly in a SQL statement, it is necessary to replace the former custom attribute BME_CATALOG_ID in query conditions with Product.BMECATALOGID.

Example query before migration:

<sql-dialect name="Microsoft">
	DECLARE @queryValue VARCHAR(300);
	SET @queryValue = <template-variable value="CatalogID" />
</sql-dialect>
SELECT
		p.uuid, count(*) over() as &quot;ROWCOUNT&quot;
    FROM
        product p
    WHERE
        p.DomainID=<template-variable value="DomainUUID"/> 
	<sql-dialect name="Oracle">
		AND contains(p.attributes, <template-variable value="CatalogID" />||' INPATH (/attrs/attr[@name="BME_CATALOG_ID"]/string)')>0		
	</sql-dialect>
	<sql-dialect name="Microsoft">
	   AND p.attributes.exist('//attrs/attr[@name="BME_CATALOG_ID"]/string [contains(., sql:variable("@queryValue"))]') = 1
	</sql-dialect>

Example query after migration:

SELECT
        p.uuid, count(*) over() as "ROWCOUNT"
    FROM
        product p
    WHERE
        p.DomainID=<template-variable value="DomainUUID"/> 
    AND
       p.BMECATALOGID=<template-variable value="CatalogID"/>