Importing products hangs on many products

Description

Trying to import about 70,000 products hangs because of an inefficient sql statements in ImportProduct.java.

Original statement
UPDATE I_Product i
SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=UPC not unique,'
WHERE I_IsImported<>'Y'
AND UPC IN (SELECT UPC FROM I_Product ii WHERE i.AD_Client_ID=ii.AD_Client_ID GROUP BY UPC HAVING COUNT > 1)
this statement uses hours and then terminates with error.
Running in less than a second is

UPDATE I_Product i
SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=UPC not unique,'
WHERE I_IsImported<>'Y'
AND exists (SELECT 1 FROM I_Product ii WHERE i.AD_Client_ID=ii.AD_Client_ID and i.i_product_id <> ii.i_product_id and i.upc = ii.upc)

The same is true for the statement checking for duplicate values in 'value' which runs better as
UPDATE I_Product i
SET I_IsImported='E', I_ErrorMsg=I_ErrorMsg||'ERR=Value not unique,'
WHERE I_IsImported<>'Y'
AND exists (SELECT 1 FROM I_Product ii WHERE i.AD_Client_ID=ii.AD_Client_ID and i.i_product_id <> ii.i_product_id and i.value = ii.value)

Environment

None

Status

Assignee

Unassigned

Reporter

Martin Schönbeck

Labels

None

Tested By

None

Components

Affects versions

Priority

Critical
Configure