Document Tree
Document Properties
Kbid
31182N
Last Modified
25-Jul-2024
Added to KB
02-May-2024
Public Access
Everyone
Status
Online
Doc Type
Guidelines
Product
ICM 11
Guide - 11.10.1 - Moving BME_Catalog_ID to Improve BMECatalog Import

Table of Contents

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 &quot;ROWCOUNT&quot;
    FROM
        product p
    WHERE
        p.DomainID=<template-variable value="DomainUUID"/> 
    AND
       p.BMECATALOGID=<template-variable value="CatalogID"/>

Disclaimer
The information provided in the Knowledge Base may not be applicable to all systems and situations. Intershop Communications will not be liable to any party for any direct or indirect damages resulting from the use of the Customer Support section of the Intershop Corporate Web site, including, without limitation, any lost profits, business interruption, loss of programs or other data on your information handling system.
The Intershop Knowledge Portal uses only technically necessary cookies. We do not track visitors or have visitors tracked by 3rd parties. Please find further information on privacy in the Intershop Privacy Policy and Legal Notice.
Home
Knowledge Base
Product Releases
Log on to continue
This Knowledge Base document is reserved for registered customers.
Log on with your Intershop Entra ID to continue.
Write an email to supportadmin@intershop.de if you experience login issues,
or if you want to register as customer.