-
more jdbc.log | egrep "(UPDATE attr_store_product SET p_dayswithoutsale|gather|MERGE)" | awk -F '|' '{print $4" "$6}' | awk -F ' ms ' '{print $1";"$2}' | sort -n > jdbc_sync_sorted.log -
Execution Flow
-
AfterSyncEvent(publish)-
com.ibm.alcampo.event.listener.AfterSyncEventListener#onSiteEvent-
com.ibm.alcampo.event.listener.VisibilityRuleEventListener#onSiteEvent-
com.ibm.alcampo.facade.impl.DefaultStockRuleFacade#flagStockLevelsOnTheFlycom.ibm.alcampo.dao.impl.DefaultCatalogSyncHookDao#updateStockLevelFlags
-
FlaggedForStockUpdateEvent(publish)-
com.ibm.alcampo.event.listener.VisibilityRuleEventListener#onSiteEvent-
com.ibm.alcampo.facade.impl.DefaultStockRuleFacade#createUpdateStockLevelsJobAlcampoUpdateRuptureStockLevelImpexGeneratorAlcampoUpdateRealStockLevelImpexGeneratorAlcampoUpdateCollectionStockLevelImpexGeneratorAlcampoUpdatePlatformStockLevelImpexGeneratorAlcampoResetAMSPRuleFlagByHiperGeneratorAlcampoUpdateWithoutWeightStockLevelImpexGeneratorAlcampoUpdateWithoutPriceStockLevelImpexGenerator
-
-
-
-
-
com.ibm.alcampo.event.listener.AfterSyncEventListener#onSiteEventClick to expand
@Override protected void onSiteEvent(AfterSyncEvent afterSyncEvent) { UpdateMode updateMode= this.resolveUpdateMode(afterSyncEvent); // FULL / SINGLE eventService.publishEvent(new StockUpdateTriggerEvent(Collections.<String>emptyList(), updateMode, stockUpdateProcessConfiguration, afterSyncEvent.getStartTimeCronJob())); }com.ibm.alcampo.event.listener.VisibilityRuleEventListener#onSiteEventClick to expand
@Override protected void onSiteEvent(AbstractVisibilityRuleEvent event) { DefaultStockRuleService.UpdateMode updateMode = null; if (event instanceof StockUpdateTriggerEvent) { long time_start, time_end; time_start = System.currentTimeMillis(); StockUpdateTriggerEvent triggerEvent = (StockUpdateTriggerEvent) event; config.set(triggerEvent.getStockUpdateProcessConfiguration()); updateMode = triggerEvent.getUpdateMode(); try { String jobUid = triggerEvent.getJobsUid(); if (StringUtils.isBlank(jobUid)) { stockRuleFacade.flagStockLevels(triggerEvent.getUids(), updateMode, String.valueOf(System.currentTimeMillis()), triggerEvent.getLasTimeSync()); } else { stockRuleFacade.flagStockLevels(triggerEvent.getUids(), updateMode, jobUid, triggerEvent.getLasTimeSync()); } } catch (UnsupportedEncodingException | InterruptedException e) { LOG.error(e.getMessage()); } time_end = System.currentTimeMillis(); LOG.info("*************************** PROCESS FLAGGING :" + (time_end - time_start) + " milliseconds"); } if (event instanceof FlaggedForStockUpdateEvent) { long time_start, time_end; time_start = System.currentTimeMillis(); FlaggedForStockUpdateEvent flaggedEvent = (FlaggedForStockUpdateEvent) event; try { String jobUid = flaggedEvent.getJobsUid(); //(JCP 17/10/2016) Mark if the flow comes from place order, in that case we will inject reduced service with only PLATFORMA, TEORICO and COLLECCION generators boolean isFromCheckOut = BooleanUtils.toBoolean(flaggedEvent.isFromCheckOut()); if (StringUtils.isBlank(jobUid)) { stockRuleFacade .createUpdateStockLevelsJob(flaggedEvent.getStockLevelFlagJob(), String.valueOf(System.currentTimeMillis()), flaggedEvent.isFullSync(), isFromCheckOut, flaggedEvent.getUpdateMode()); } else { stockRuleFacade.createUpdateStockLevelsJob(flaggedEvent.getStockLevelFlagJob(), jobUid, flaggedEvent.isFullSync(), isFromCheckOut, flaggedEvent.getUpdateMode()); } } catch (IOException | JaloBusinessException e) { LOG.error(e.getMessage()); } time_end = System.currentTimeMillis(); LOG.info("*************************** PROCESS UPDATE STOCKLEVELS :" + (time_end - time_start) + " milliseconds"); } if (event instanceof StockUpdateExportedEvent) { StockUpdateExportedEvent stockUpdateExportedEvent = (StockUpdateExportedEvent) event; long time_start, time_end; time_start = System.currentTimeMillis(); try { stockRuleFacade.executeUpdateStockLevelsJob(stockUpdateExportedEvent.getUpdateStockLevelsCompositeJob(), stockUpdateExportedEvent.getCode(), stockUpdateExportedEvent.getProductCatalogVersion()); } catch (InterruptedException e) { LOG.error(e.getMessage()); } time_end = System.currentTimeMillis(); LOG.info("*************************** PROCESS EXCUTE UPDATE STOCKLEVELS :" + (time_end - time_start) + " milliseconds"); } // JCP - 25/01/2017 Dont invalidate cache, there has been no rules calculation. if (!ObjectUtils.equals(updateMode, DefaultStockRuleService.UpdateMode.PRODUCTS)) { //JCP - 22/07/2016 Force clear all StockLevels cache. If not hybris don't invalidate recalculated stock from cache. this.getCacheInvalidationStrategy().invalidate(STOCK_LEVEL_REGION_KEY_CACHE); //JCP - 05/09/2018 Force clear all AMSP cache. If not hybris don't invalidate recalculated occultation from cache. this.getCacheInvalidationStrategy().invalidate(AMSP_LEVEL_REGION_KEY_CACHE); } if (event instanceof StockUpdateImportedEvent) { StockUpdateImportedEvent stockUpdateImportedEvent = (StockUpdateImportedEvent) event; generateMenuFacade.startGenerateMenuJob(); } }com.ibm.alcampo.facade.impl.DefaultStockRuleFacade#flagStockLevelsOnTheFlyClick to expand
private void flagStockLevelsOnTheFly(Date lasTimeSync, String jobUid, UpdateMode updateMode) { stockServicesMap.get(Config.DatabaseNames.ORACLE.toString()).flagStockLevelsOnTheFly(lasTimeSync, jobUid); eventService.publishEvent(new FlaggedForStockUpdateEvent(true, jobUid, updateMode)); }com.ibm.alcampo.dao.impl.DefaultCatalogSyncHookDao#updateStockLevelFlagsClick to expand
@Override public void updateStockLevelFlags(final Date lastSyncTime, String jobUid) { final String jobCode=AlcampovisibilityrulesConstants.FLAGJOB_ID + jobUid; SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss "); final String queryString = "MERGE INTO stocklevels target USING ( " + "SELECT DISTINCT stocklevel.pk stock_level_pk FROM (" + "SELECT s.PK " + "FROM itemsynctimestamps ist " + "JOIN products p ON ist.p_targetitem = p.PK " + "JOIN stocklevels s ON p.code = s.p_productcode " + "WHERE ( ist.p_lastsynctime > TO_DATE('" + sdf.format(lastSyncTime) + "', 'dd/MM/yyyy HH24:MI:SS')) " + "AND p.p_approvalstatus IN (select pk from enumerationvalues where (code='approved' or code='Estado___5_1'))" + " " + "" + "UNION ALL" + " " + "" + "SELECT s.PK " + "FROM attr_store_product amsp " + "JOIN products p " + "ON amsp.p_product = p.PK " + "JOIN basestore b " + "ON amsp.p_store = b.PK " + "JOIN basestore2warehouserel bwrel " + "ON b.PK = bwrel.SourcePK " + "JOIN stocklevels s " + "ON p.code = s.p_productcode " + "AND bwrel.TargetPK = s.p_warehouse " + "WHERE s.p_lastflaggedtime < amsp.modifiedTS " + "AND p.p_approvalstatus IN (select pk from enumerationvalues where (code='approved' or code='Estado___5_1'))" + " " + "" + "UNION ALL " + " " + "" + "SELECT s.PK " + "FROM attr_store_product amsp " + "JOIN products p " + "ON amsp.p_product = p.PK " + "JOIN basestore b " + "ON amsp.p_store = b.PK " + "JOIN basestore2warehouserel bwrel " + "ON b.PK = bwrel.SourcePK " + "JOIN stocklevels s " + "ON p.code = s.p_productcode " + "AND bwrel.TargetPK = s.p_warehouse " + "WHERE s.p_lastflaggedtime IS NULL " + "AND p.p_approvalstatus IN (select pk from enumerationvalues where (code='approved' or code='Estado___5_1')) " + " " + "" + "UNION ALL " + " " + "" + "SELECT s.PK " + "FROM attr_store_product amsp " + "JOIN products p " + "ON amsp.p_product = p.PK " + "JOIN basestore b " + "ON amsp.p_store = b.PK " + "JOIN basestore2warehouserel bwrel " + "ON b.PK = bwrel.SourcePK " + "JOIN stocklevels s " + "ON p.code = s.p_productcode " + "AND bwrel.TargetPK = s.p_warehouse " + "WHERE s.p_lastflaggedtime < s.modifiedTS " + "AND p.p_approvalstatus IN (select pk from enumerationvalues where (code='approved' or code='Estado___5_1'))" + ") stocklevel)" + " source " + "ON (target.pk = source.stock_level_pk) WHEN matched " + "THEN UPDATE SET target.p_updateStockLevelsCronjobCode = '"+jobCode+"', " + "target.p_lastflaggedtime = SYSDATE"; this.jdbcTemplate.update(new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(final Connection connection) throws SQLException { PreparedStatement ptm = connection.prepareStatement(queryString); return ptm; } }); }com.ibm.alcampo.facade.impl.DefaultStockRuleFacade#createUpdateStockLevelsJobClick to expand
@Override public void createUpdateStockLevelsJob(ImpExImportCronJobModel flagStockImpexJob, String jobUid, boolean isFullSync, boolean isFromCheckOut, UpdateMode updateMode) throws IOException, JaloBusinessException { CatalogVersionModel productCatalogVersion = catalogVersionService .getCatalogVersion(configurationService.getConfiguration().getString("alcampo.productcatalog.name"), "Online"); CompositeCronJobModel updateStockLevelJob; StockRuleService service = Config.isOracleUsed() ? stockServicesMap.get(Config.DatabaseNames.ORACLE.toString()) : stockServicesMap.get(Config.DatabaseNames.MYSQL.toString()); if (BooleanUtils.isTrue(isFullSync)) { if (ObjectUtils.equals(updateMode, UpdateMode.FULLSYNC)) { updateStockLevelJob = service.createUpdateStockLevelsJob(FLAGJOB_ID + jobUid, jobUid, productCatalogVersion); } else { //(JCP 17/10/2016) Get reduced service with for not nightly syncs and preserve platform data service = stockServicesMap.get(ORACLE_NOT_NIGHTLY_GENERATOR_SERVICE); updateStockLevelJob = service.createUpdateStockLevelsJob(FLAGJOB_ID + jobUid, jobUid, productCatalogVersion); } eventService.publishEvent(new StockUpdateExportedEvent(updateStockLevelJob, FLAGJOB_ID + jobUid, productCatalogVersion)); } else { if (isFromCheckOut) { //(JCP 17/10/2016) Get reduced service with only 4 generators service = stockServicesMap.get(ORACLE_REDUCED_GENERATOR_SERVICE); updateStockLevelJob = service.createUpdateStockLevelsJob(flagStockImpexJob.getCode(), jobUid, productCatalogVersion); } else { updateStockLevelJob = service.createUpdateStockLevelsJob(flagStockImpexJob.getCode(), jobUid, productCatalogVersion); } eventService.publishEvent( new StockUpdateExportedEvent(updateStockLevelJob, flagStockImpexJob.getCode(), productCatalogVersion)); } }Queries
AlcampoUpdateRuptureStockLevelImpexGeneratorUpdates stock levels having a different
inStockStatusorlastAppliedRuleonrupture_rules_viewMERGE INTO stocklevels target USING (SELECT DISTINCT STOCK_STATUS, STOCK_LEVEL_PK, RULE_PK, STOCK_FLAG FROM rupture_rules_view WHERE stock_flag = 'Flag stock impex job 1635136617728') SOURCE ON (target.pk = source.stock_level_pk) WHEN matched THEN UPDATE SET target.p_instockstatus = source.stock_status, target.p_lastappliedrulepk = source.rule_pk, target.modifiedts = SYSDATE, target.p_lastflaggedtime = SYSDATE WHERE (target.p_instockstatus != source.stock_status OR target.p_lastappliedrulepk != TO_CHAR(source.rule_pk) OR target.p_instockstatus IS NULL);Select execution time
select * from rupture_rules_view # 20211110 21:00 # Execution time: 395809 ms -> 6.5 min select * from INPUT_RUPTURErupture_rules_viewClick to expand
CREATE OR REPLACE FORCE VIEW "INT_ECOM"."RUPTURE_RULES_VIEW" ("STOCK_STATUS", "STOCK_LEVEL_PK", "RULE_PK", "STOCK_FLAG") AS (SELECT rnv.rule_presentation AS stock_status, ir.stock_level_pk AS stock_level_pk, rnv.rule_pk AS rule_pk, ir.stock_flag AS stock_flag FROM INPUT_RUPTURE ir JOIN v_rupture_rules rnv ON(rnv.c_l4_pk =ir.c_pk AND rnv.rule_isvariableweight=ir.is_variable_weight AND rnv.rule_range =ir.p_range AND rnv.rule_rupturestock =ir.rupture_stock AND rnv.rule_salable =ir.sellable AND rnv.store_uid =ir.store_uid))INPUT_RUPTURETrack Changes Table Column Comments stocklevels pk stocklevels p_productcode stocklevels p_updatestocklevelscronjobcode ? stocklevels p_instockstatus ? stocklevels p_stock_collection ? stocklevels p_stock1 DECODE(sl.p_stock1, NULL, 'NOT_FOR_SALE', 88888888, 'NOT_FOR_SALE', 0, 'forceOutOfStock', 'forceInStock') AS rupture_stockx products p_maxdayswithoutsale DECODE(SIGN((TRUNC(SYSDATE, 'ddd') - TRUNC(amsp.p_lastsaledate, 'ddd')) - COALESCE (p.p_maxdayswithoutsale -1, 0)), -1, 1, 0) AS sellablex products p_isvariableweight x products p_stocktype COALESCE(amspst.code, st.code) AS stock_typex? cat2prodrel Filtered to category type c.p_categoryType = 8797037953115x attr_store_product p_specificstock COALESCE(amspst.code, st.code) AS stock_typex attr_store_product p_lastsaledate DECODE(SIGN((TRUNC(SYSDATE, 'ddd') - TRUNC(amsp.p_lastsaledate, 'ddd')) - COALESCE (p.p_maxdayswithoutsale -1, 0)), -1, 1, 0) AS sellablex attr_store_product p_range basestore p_uid categories pk Click to expand
CREATE OR REPLACE FORCE VIEW "INT_ECOM"."INPUT_RUPTURE" ("STOCK_LEVEL_PK", "PRODUCT_CODE", "CURRENT_STATUS", "STOCK3", "RUPTURE_STOCK", "SELLABLE", "STORE_UID", "P_RANGE", "IS_VARIABLE_WEIGHT", "STOCK_TYPE", "C_PK", "STOCK_FLAG") AS (SELECT "STOCK_LEVEL_PK", "PRODUCT_CODE", "CURRENT_STATUS", "STOCK3", "RUPTURE_STOCK", "SELLABLE", "STORE_UID", "P_RANGE", "IS_VARIABLE_WEIGHT", "STOCK_TYPE", "C_PK", "STOCK_FLAG" FROM (SELECT sl.pk AS stock_level_pk, sl.p_productcode AS product_code, sl.p_instockstatus AS current_status, sl.p_stock_collection AS stock3, DECODE(sl.p_stock1, NULL, 'NOT_FOR_SALE', 88888888, 'NOT_FOR_SALE', 0, 'forceOutOfStock', 'forceInStock') AS rupture_stock, DECODE(SIGN((TRUNC(SYSDATE, 'ddd') - TRUNC(amsp.p_lastsaledate, 'ddd')) - COALESCE (p.p_maxdayswithoutsale -1, 0)), -1, 1, 0) AS sellable, b.p_uid AS store_uid, amsp.p_range AS p_range, p.p_isvariableweight AS is_variable_weight, COALESCE(amspst.code, st.code) AS stock_type, c.pk AS c_pk, sl.p_updatestocklevelscronjobcode AS stock_flag FROM stocklevels sl JOIN warehouses w ON sl.p_warehouse = w.pk JOIN enumerationvalues wt ON wt.pk = w.p_alcampotype JOIN basestore2warehouserel b2w ON w.pk = b2w.targetpk JOIN basestore b ON b.pk = b2w.sourcepk JOIN products p ON p.code = sl.p_productcode JOIN attr_store_product amsp ON p.pk = amsp.p_product AND b.pk = amsp.p_store LEFT JOIN enumerationvalues amspst ON amspst.pk = amsp.p_specificstock JOIN enumerationvalues st ON st.pk = p.p_stocktype JOIN cat2prodrel c2p ON p.pk = c2p.targetpk JOIN categories c ON c2p.sourcepk = c.pk WHERE c.p_categoryType = 8797037953115 AND c.p_hierarchicalcode IS NOT NULL AND c.p_categorylevel = 4 AND wt.code = 'ALMACEN' AND COALESCE (amspst.code, st.code) = 'RUPTURA' AND c.p_catalogversion = 8796093186649 AND p.p_catalogversion = 8796093186649 AND p.p_approvalstatus = 8796099543131 ) WHERE stock_type = 'RUPTURA' );V_RUPTURE_RULESClick to expand
CREATE MATERIALIZED VIEW "INT_ECOM"."V_RUPTURE_RULES" ("C_L4_PK", "STORE_UID", "RULE_PK", "RULE_RANGE", "RULE_ISVARIABLEWEIGHT", "RULE_SALABLE", "RULE_RUPTURESTOCK", "RULE_PRESENTATION") ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "INT_ECOM_TB" BUILD IMMEDIATE USING INDEX REFRESH FORCE ON DEMAND USING DEFAULT LOCAL ROLLBACK SEGMENT USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE AS SELECT DISTINCT c_l4_pk, store_uid, FIRST_VALUE(rule_pk) OVER (PARTITION BY c_l4_pk, store_uid ORDER BY c_l4_level DESC RANGE UNBOUNDED preceding) AS rule_pk, rule_range, rule_isvariableweight, rule_salable, rule_rupturestock, FIRST_VALUE(rule_presentation) OVER (PARTITION BY c_l4_pk, store_uid ORDER BY c_l4_level DESC RANGE UNBOUNDED preceding) AS rule_presentation FROM (SELECT rr.pk AS rule_pk, rr.p_range AS rule_range, rr.p_isvariableweight AS rule_isvariableweight, rr.p_salable AS rule_salable, enum.code AS rule_rupturestock, rr.p_presentation AS rule_presentation, b.p_uid AS store_uid, c_l4.pk AS c_l4_pk, c_rule.p_categorylevel AS c_l4_level FROM categories c_l4, categories c_rule, enumerationvalues enum, basestore b, rupture2basestore r2b, rupture_rule rr WHERE r2b.targetpk = rr.pk AND b.pk = r2b.sourcepk AND enum.pk = rr.p_rupturestock AND c_rule.pk = rr.p_categoryas400 AND c_l4.p_hierarchicalcode LIKE Concat(c_rule.p_hierarchicalcode, '%') AND c_l4.p_categoryType = 8797037953115 AND c_l4.p_categorylevel = 4 AND c_l4.p_hierarchicalcode IS NOT NULL AND c_l4.p_catalogversion = 8796093186649 AND c_rule.p_hierarchicalcode IS NOT NULL AND c_rule.p_catalogversion = 8796093186649 ) ;AlcampoUpdateRealStockLevelImpexGeneratorUpdates stock levels having a different
inStockStatusorlastAppliedRuleonreal_rules_viewMERGE INTO stocklevels target USING (SELECT DISTINCT STOCK_STATUS, STOCK_LEVEL_PK, RULE_PK, STOCK_FLAG FROM real_rules_view WHERE stock_flag = 'Flag stock impex job 1635136617728') SOURCE ON (target.pk = source.stock_level_pk) WHEN matched THEN UPDATE SET target.p_instockstatus = source.stock_status, target.p_lastappliedrulepk = source.rule_pk, target.modifiedts = SYSDATE, target.p_lastflaggedtime = SYSDATE WHERE (target.p_instockstatus != source.stock_status OR target.p_lastappliedrulepk != TO_CHAR(source.rule_pk) OR target.p_instockstatus IS NULL);Select execution time
select * from real_rules_view # 20211110 21:18 # Execution time: 90576 -> 1.5 minreal_rules_viewClick to expand
CREATE OR REPLACE FORCE VIEW "INT_ECOM"."REAL_RULES_VIEW" ("STOCK_STATUS", "STOCK_LEVEL_PK", "RULE_PK", "STOCK_FLAG") AS (SELECT rnv.rule_presentation AS stock_status, ir.stock_level_pk AS stock_level_pk, rnv.rule_pk AS rule_pk, ir.stock_flag AS stock_flag FROM input_real ir JOIN v_real_rules rnv ON(rnv.c_l4_pk =ir.c_pk AND rnv.rule_range =ir.p_range AND rnv.rule_realstock =ir.real_stock AND rnv.rule_salable =ir.sellable AND rnv.store_uid =ir.store_uid)) ;input_realTrack Changes Table Column Comments stocklevels pk stocklevels p_productcode stocklevels p_updatestocklevelscronjobcode ? stocklevels p_instockstatus ? stocklevels p_stock_collection ? stocklevels p_available DECODE(Sign(sl.p_available - sl.p_reserved - COALESCE (p.p_securitystock, 0)), NULL, 'NO', -1, 'NO', 0, 'NO', 'SI') AS real_stock,? stocklevels p_reserved DECODE(Sign(sl.p_available - sl.p_reserved - COALESCE (p.p_securitystock, 0)), NULL, 'NO', -1, 'NO', 0, 'NO', 'SI') AS real_stock,x products p_securitystock DECODE(Sign(sl.p_available - sl.p_reserved - COALESCE (p.p_securitystock, 0)), NULL, 'NO', -1, 'NO', 0, 'NO', 'SI') AS real_stock,x products p_maxdayswithoutsale DECODE(SIGN((TRUNC(SYSDATE, 'ddd') - TRUNC(amsp.p_lastsaledate, 'ddd')) - COALESCE (p.p_maxdayswithoutsale -1, 0)), -1, 1, 0) AS sellablex products p_stocktype COALESCE(amspst.code, st.code) AS stock_typex? cat2prodrel Filtered to category type c.p_categoryType = 8797037953115x attr_store_product p_specificstock COALESCE(amspst.code, st.code) AS stock_typex attr_store_product p_lastsaledate DECODE(SIGN((TRUNC(SYSDATE, 'ddd') - TRUNC(amsp.p_lastsaledate, 'ddd')) - COALESCE (p.p_maxdayswithoutsale -1, 0)), -1, 1, 0) AS sellablex attr_store_product p_range basestore p_uid categories pk Click to expand
CREATE OR REPLACE FORCE VIEW "INT_ECOM"."INPUT_REAL" ("STOCK_LEVEL_PK", "PRODUCT_CODE", "CURRENT_STATUS", "REAL_STOCK", "SELLABLE", "STORE_UID", "P_RANGE", "STOCK_TYPE", "C_PK", "STOCK_FLAG") AS (SELECT "STOCK_LEVEL_PK", "PRODUCT_CODE", "CURRENT_STATUS", "REAL_STOCK", "SELLABLE", "STORE_UID", "P_RANGE", "STOCK_TYPE", "C_PK", "STOCK_FLAG" FROM (SELECT sl.pk AS stock_level_pk, sl.p_productcode AS product_code, sl.p_instockstatus AS current_status, DECODE(Sign(sl.p_available - sl.p_reserved - COALESCE (p.p_securitystock, 0)), NULL, 'NO', -1, 'NO', 0, 'NO', 'SI') AS real_stock, DECODE(SIGN((TRUNC(SYSDATE, 'ddd') - TRUNC(amsp.p_lastsaledate, 'ddd')) - COALESCE (p.p_maxdayswithoutsale -1, 0)), -1, 1, 0) AS sellable, b.p_uid AS store_uid, amsp.p_range AS p_range, COALESCE(amspst.code, st.code) AS stock_type, c.pk AS c_pk, sl.p_updatestocklevelscronjobcode AS stock_flag FROM stocklevels sl JOIN warehouses w ON sl.p_warehouse = w.pk JOIN enumerationvalues wt ON wt.pk = w.p_alcampotype JOIN basestore2warehouserel b2w ON w.pk = b2w.targetpk JOIN basestore b ON b.pk = b2w.sourcepk JOIN products p ON p.code = sl.p_productcode JOIN attr_store_product amsp ON p.pk = amsp.p_product AND b.pk = amsp.p_store LEFT JOIN enumerationvalues amspst ON amspst.pk = amsp.p_specificstock JOIN enumerationvalues st ON st.pk = p.p_stocktype JOIN cat2prodrel c2p ON p.pk = c2p.targetpk JOIN categories c ON c2p.sourcepk = c.pk WHERE c.p_categoryType = 8797037953115 AND c.p_hierarchicalcode IS NOT NULL AND c.p_categorylevel = 4 AND wt.code = 'ALMACEN' AND COALESCE (amspst.code, st.code) = 'TEORICO' AND c.p_catalogversion = 8796093186649 AND p.p_catalogversion = 8796093186649 AND p.p_approvalstatus = 8796099543131 ) WHERE stock_type = 'TEORICO' );V_REAL_RULESClick to expand
CREATE MATERIALIZED VIEW "INT_ECOM"."V_REAL_RULES" ("C_L4_PK", "STORE_UID", "RULE_PK", "RULE_RANGE", "RULE_SALABLE", "RULE_REALSTOCK", "RULE_PRESENTATION") ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "INT_ECOM_TB" BUILD IMMEDIATE USING INDEX REFRESH COMPLETE ON DEMAND USING DEFAULT LOCAL ROLLBACK SEGMENT USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE AS SELECT DISTINCT c_l4_pk, store_uid, FIRST_VALUE(rule_pk) OVER (PARTITION BY c_l4_pk, store_uid ORDER BY c_l4_level DESC RANGE UNBOUNDED preceding) AS rule_pk, rule_range, rule_salable, rule_realstock, FIRST_VALUE(rule_presentation) OVER (PARTITION BY c_l4_pk, store_uid ORDER BY c_l4_level DESC RANGE UNBOUNDED preceding) AS rule_presentation FROM (SELECT rr.pk AS rule_pk, rr.p_range AS rule_range, rr.p_salable AS rule_salable, enum.code AS rule_realstock, rr.p_presentation AS rule_presentation, b.p_uid AS store_uid, c_l4.pk AS c_l4_pk, c_rule.p_categorylevel AS c_l4_level FROM categories c_l4, categories c_rule, enumerationvalues enum, basestore b, real2basestore r2b, real_rule rr WHERE r2b.targetpk = rr.pk AND b.pk = r2b.sourcepk AND enum.pk = rr.p_realstock AND c_rule.pk = rr.p_categoryas400 AND c_l4.p_hierarchicalcode LIKE Concat(c_rule.p_hierarchicalcode, '%') AND c_l4.p_categoryType = 8797037953115 AND c_l4.p_categorylevel = 4 AND c_l4.p_hierarchicalcode IS NOT NULL AND c_l4.p_catalogversion = 8796093186649 AND c_rule.p_hierarchicalcode IS NOT NULL AND c_rule.p_catalogversion = 8796093186649 )AlcampoUpdateCollectionStockLevelImpexGeneratorUpdates stock levels having a different
inStockStatusorlastAppliedRuleoncollection_rules_viewMERGE INTO stocklevels target USING (SELECT DISTINCT STOCK_STATUS, STOCK_LEVEL_PK, RULE_PK, STOCK_FLAG FROM collection_rules_view WHERE stock_flag = 'Flag stock impex job 1634964367141') SOURCE ON (target.pk = source.stock_level_pk) WHEN matched THEN UPDATE SET target.p_instockstatus = source.stock_status, target.p_lastappliedrulepk = source.rule_pk, target.modifiedts = SYSDATE, target.p_lastflaggedtime = SYSDATE WHERE (target.p_instockstatus != source.stock_status OR target.p_lastappliedrulepk != TO_CHAR(source.rule_pk) OR target.p_instockstatus IS NULL);Select execution time
select * from collection_rules_view # 20211110 21:19 # Execution time: 83874 ms -> 1.5 minAlcampoUpdatePlatformStockLevelImpexGeneratorMERGE INTO stocklevels target USING (SELECT DISTINCT STOCK_STATUS, STOCK_LEVEL_PK, RULE_PK, STOCK_FLAG FROM platform_rules_view WHERE stock_flag = 'Flag stock impex job 1635308917632') SOURCE ON (target.pk = source.stock_level_pk) WHEN matched THEN UPDATE SET target.p_instockstatus = source.stock_status, target.p_lastappliedrulepk = source.rule_pk, target.modifiedts = SYSDATE, target.p_lastflaggedtime = SYSDATE WHERE (target.p_instockstatus != source.stock_status OR target.p_lastappliedrulepk != TO_CHAR(source.rule_pk) OR target.p_instockstatus IS NULL);Select execution time
select * from platform_rules_view # 20211110 21:20 # Execution time: 6713 ms -> 6"platform_rules_viewClick to expand
CREATE OR REPLACE FORCE VIEW "INT_ECOM"."PLATFORM_RULES_VIEW" ("STOCK_STATUS", "STOCK_LEVEL_PK", "RULE_PK", "STOCK_FLAG") AS (SELECT rnv.rule_presentation AS stock_status, ir.stock_level_pk AS stock_level_pk, rnv.rule_pk AS rule_pk, ir.stock_flag AS stock_flag FROM input_platform ir JOIN v_platform_rules rnv ON(rnv.c_l4_pk =ir.c_pk AND rnv.rule_range =ir.p_range AND rnv.rule_platformstock =ir.platform_stock AND rnv.store_uid =ir.store_uid));input_platformTrack Changes Table Column Comments stocklevels pk stocklevels p_productcode stocklevels p_updatestocklevelscronjobcode ? stocklevels p_instockstatus ? stocklevels p_stock_collection ? stocklevels p_available decode(Sign(sl.p_available - sl.p_reserved), NULL, 'NO', -1, 'NO', 0, 'NO', 'SI') AS platform_stock? stocklevels p_reserved decode(Sign(sl.p_available - sl.p_reserved), NULL, 'NO', -1, 'NO', 0, 'NO', 'SI') AS platform_stock,x products p_stocktype COALESCE(amspst.code, st.code) AS stock_typex? cat2prodrel Filtered to category type c.p_categoryType = 8797037953115x attr_store_product p_specificstock COALESCE(amspst.code, st.code) AS stock_typex attr_store_product p_range basestore p_uid categories pk Click to expand
CREATE OR REPLACE FORCE VIEW "INT_ECOM"."INPUT_PLATFORM" ("STOCK_LEVEL_PK", "PRODUCT_CODE", "CURRENT_STATUS", "PLATFORM_STOCK", "STORE_UID", "P_RANGE", "STOCK_TYPE", "C_PK", "STOCK_FLAG") AS (SELECT "STOCK_LEVEL_PK", "PRODUCT_CODE", "CURRENT_STATUS", "PLATFORM_STOCK", "STORE_UID", "P_RANGE", "STOCK_TYPE", "C_PK", "STOCK_FLAG" FROM (SELECT sl.pk AS stock_level_pk, sl.p_productcode AS product_code, sl.p_instockstatus AS current_status, decode(Sign(sl.p_available - sl.p_reserved), NULL, 'NO', -1, 'NO', 0, 'NO', 'SI') AS platform_stock, b.p_uid AS store_uid, p.p_range AS p_range, COALESCE (amspst.code, st.code) AS stock_type, c.pk AS c_pk, sl.p_updatestocklevelscronjobcode AS stock_flag FROM stocklevels sl JOIN warehouses w ON sl.p_warehouse = w.pk JOIN enumerationvalues wt ON wt.pk = w.p_alcampotype JOIN basestore2warehouserel b2w ON w.pk = b2w.targetpk JOIN basestore b ON b.pk = b2w.sourcepk JOIN products p ON p.code = sl.p_productcode LEFT JOIN attr_store_product amsp ON amsp.p_product = p.pk AND amsp.p_store = b.pk LEFT JOIN enumerationvalues amspst ON amspst.pk = amsp.p_specificstock JOIN enumerationvalues st ON st.pk = p.p_stocktype JOIN cat2prodrel c2p ON p.pk = c2p.targetpk JOIN categories c ON c2p.sourcepk = c.pk WHERE c.p_categoryType = 8797037953115 AND c.p_hierarchicalcode IS NOT NULL AND c.p_categorylevel = 4 AND wt.code = 'PLATAFORMA' AND COALESCE (amspst.code, st.code) = 'PLATAFORMA' AND c.p_catalogversion = 8796093186649 AND p.p_catalogversion = 8796093186649 AND (p.p_approvalstatus = 8796099543131 OR p.p_approvalstatus = 8796099674203) ) WHERE stock_type = 'PLATAFORMA' );v_platform_rulesClick to expand
CREATE MATERIALIZED VIEW "INT_ECOM"."V_PLATFORM_RULES" ("C_L4_PK", "STORE_UID", "RULE_PK", "RULE_RANGE", "RULE_SALABLE", "RULE_PLATFORMSTOCK", "RULE_PRESENTATION") ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "INT_ECOM_TB" BUILD IMMEDIATE USING INDEX REFRESH FORCE ON DEMAND USING DEFAULT LOCAL ROLLBACK SEGMENT USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE AS SELECT DISTINCT c_l4_pk, store_uid, FIRST_VALUE(rule_pk) OVER (PARTITION BY c_l4_pk, store_uid ORDER BY c_l4_level DESC RANGE UNBOUNDED preceding) AS rule_pk, rule_range, rule_salable, rule_platformstock, FIRST_VALUE(rule_presentation) OVER (PARTITION BY c_l4_pk, store_uid ORDER BY c_l4_level DESC RANGE UNBOUNDED preceding) AS rule_presentation FROM (SELECT rr.pk AS rule_pk, rr.p_range AS rule_range, rr.p_salable AS rule_salable, enum.code AS rule_platformstock, rr.p_presentation AS rule_presentation, b.p_uid AS store_uid, c_l4.pk AS c_l4_pk, c_rule.p_categorylevel AS c_l4_level FROM categories c_l4, categories c_rule, enumerationvalues enum, basestore b, platformrules2basestore p2b, platform_rules rr WHERE p2b.targetpk = rr.pk AND b.pk = p2b.sourcepk AND enum.pk = rr.p_platformstock AND c_rule.pk = rr.p_categoryas400 AND c_l4.p_hierarchicalcode LIKE Concat(c_rule.p_hierarchicalcode, '%') AND c_l4.p_categoryType = 8797037953115 AND c_l4.p_categorylevel = 4 AND c_l4.p_hierarchicalcode IS NOT NULL AND c_l4.p_catalogversion = 8796093186649 AND c_rule.p_hierarchicalcode IS NOT NULL AND c_rule.p_catalogversion = 8796093186649 ) ;AlcampoResetAMSPRuleFlagByHiperGeneratorMERGE INTO attr_store_product ss USING (SELECT DISTINCT amsp.PK AS spk FROM stocklevels s JOIN products p ON p.code = s.p_productcode AND p.p_catalogversion = 8796093186649 JOIN attr_store_product amsp ON amsp.p_product = p.PK JOIN basestore b ON b.PK = amsp.p_store AND amsp.p_product = p.PK WHERE s.p_updatestocklevelscronjobcode = 'Flag stock impex job 1634964367141' AND p_hiddenInStoreByRule = 1) sl ON (ss.PK = sl.spk) WHEN MATCHED THEN UPDATE SET ss.p_hiddenInStoreByRule = 0, ss.p_hiddenInStoreText = '', ss.modifiedts = '23/10/2021 07:08:42'Select execution time
# Flag stock impex job 1636523760729 -> 1206552 stock levels aprox. SELECT DISTINCT amsp.PK AS spk FROM stocklevels s JOIN products p ON p.code = s.p_productcode AND p.p_catalogversion = 8796093186649 JOIN attr_store_product amsp ON amsp.p_product = p.PK JOIN basestore b ON b.PK = amsp.p_store AND amsp.p_product = p.PK WHERE s.p_updatestocklevelscronjobcode = 'Flag stock impex job 1636523760729' AND p_hiddenInStoreByRule = 1 # 20211110 21:24 # Execution time: 2090 ms -> 2"AlcampoUpdateWithoutWeightStockLevelImpexGeneratorTrack Changes Table Column Comments attr_store_product pk x attr_store_product p_variableweight x products p_isvariableweight MERGE INTO attr_store_product ss USING (SELECT DISTINCT amsp.PK AS spk FROM products p JOIN attr_store_product amsp ON amsp.p_product = p.PK JOIN basestore b ON b.PK = amsp.p_store AND amsp.p_product = p.PK JOIN basestore2warehouserel b2r ON b2r.SourcePK = b.PK JOIN warehouses w ON b2r.TargetPK = w.PK JOIN enumerationvalues wt ON wt.pk = w.p_alcampotype AND wt.code = 'ALMACEN' JOIN stocklevels s ON s.p_warehouse = w.PK AND p.code = s.p_productcode WHERE (s.p_updatestocklevelscronjobcode = 'Flag stock impex job 1634964367141' AND p.p_catalogversion = 8796093186649 AND (p.p_isvariableweight = 1 AND (amsp.p_variableweight IS NULL OR amsp.p_variableweight = 0)))) sl ON (ss.PK = sl.spk) WHEN MATCHED THEN UPDATE SET ss.p_hiddenInStoreByRule = 1, ss.p_hiddenInStoreText = 'regla de peso variable cero', ss.modifiedts = '23/10/2021 07:08:42'Select execution time
# Flag stock impex job 1636523760729 -> 1206552 stock levels aprox. SELECT DISTINCT amsp.PK AS spk FROM products p JOIN attr_store_product amsp ON amsp.p_product = p.PK JOIN basestore b ON b.PK = amsp.p_store AND amsp.p_product = p.PK JOIN basestore2warehouserel b2r ON b2r.SourcePK = b.PK JOIN warehouses w ON b2r.TargetPK = w.PK JOIN enumerationvalues wt ON wt.pk = w.p_alcampotype AND wt.code = 'ALMACEN' JOIN stocklevels s ON s.p_warehouse = w.PK AND p.code = s.p_productcode WHERE (s.p_updatestocklevelscronjobcode = 'Flag stock impex job 1636523760729' AND p.p_catalogversion = 8796093186649 AND (p.p_isvariableweight = 1 AND (amsp.p_variableweight IS NULL OR amsp.p_variableweight = 0))) # 20211110 21:27 # Execution time: 4696 ms -> 4.6"AlcampoUpdateWithoutPriceStockLevelImpexGeneratorTrack Changes Table Column Comments attr_store_product pk x pricerows p_price MERGE INTO attr_store_product ss USING (SELECT DISTINCT amsp.PK AS spk FROM stocklevels s JOIN products p ON p.code = s.p_productcode AND p.p_catalogversion = 8796093186649 JOIN attr_store_product amsp ON amsp.p_product = p.PK JOIN basestore b ON b.PK = amsp.p_store AND amsp.p_product = p.PK JOIN basestore2warehouserel b2r ON b2r.SourcePK = b.PK JOIN warehouses w ON s.p_warehouse = w.pk AND b2r.TARGETPK= w.pk JOIN enumerationvalues wt ON wt.pk = w.p_alcampotype AND wt.code = 'ALMACEN' JOIN enumerationvalues ev ON SUBSTR(ev.code, 16, 10) = SUBSTR(w.p_code, 11, 10) LEFT JOIN pricerows pr ON p.code = pr.p_productid AND ev.pk = pr.p_ug WHERE s.p_updatestocklevelscronjobcode = 'Flag stock impex job 1634964367141' AND (pr.p_price = 0 OR pr.p_price IS NULL)) sl ON (ss.PK = sl.spk) WHEN MATCHED THEN UPDATE SET ss.p_hiddenInStoreByRule = 1, ss.p_hiddenInStoreText = 'regla de precio cero', ss.modifiedts = '23/10/2021 07:08:42'Select execution time
# Flag stock impex job 1636523760729 -> 1206552 stock levels aprox. SELECT DISTINCT amsp.PK AS spk FROM stocklevels s JOIN products p ON p.code = s.p_productcode AND p.p_catalogversion = 8796093186649 JOIN attr_store_product amsp ON amsp.p_product = p.PK JOIN basestore b ON b.PK = amsp.p_store AND amsp.p_product = p.PK JOIN basestore2warehouserel b2r ON b2r.SourcePK = b.PK JOIN warehouses w ON s.p_warehouse = w.pk AND b2r.TARGETPK= w.pk JOIN enumerationvalues wt ON wt.pk = w.p_alcampotype AND wt.code = 'ALMACEN' JOIN enumerationvalues ev ON SUBSTR(ev.code, 16, 10) = SUBSTR(w.p_code, 11, 10) LEFT JOIN pricerows pr ON p.code = pr.p_productid AND ev.pk = pr.p_ug WHERE s.p_updatestocklevelscronjobcode = 'Flag stock impex job 1636523760729' AND (pr.p_price = 0 OR pr.p_price IS NULL) # 20211110 21:30 # Execution time: 23122 ms -> 23"Edited by Julio Argüello -
-
Last UpdateFromSQLCronJob Overlaps
SELECT {code}, {startTime}, {endTime} - {startTime} AS duration FROM {UpdateFromSQLCronJob as c1} WHERE EXISTS ( {{ SELECT {pk} FROM {UpdateFromSQLCronJob as c2} WHERE {c2.startTime} BETWEEN {c1.startTime} AND {c2.startTime} }} ) ORDER BY 2 DESCClick to expand
P_CODE P_STARTTIME DURATION AlcampoUpdatePlatformStockLevelImpexGenerator_1636553116838 2021-11-10 15:05:19.004 0 0:0:0.477 AlcampoUpdateCollectionStockLevelImpexGenerator_1636553116749 2021-11-10 15:05:18.176 0 0:0:0.644 AlcampoUpdateRealStockLevelImpexGenerator_1636553116637 2021-11-10 15:05:17.354 0 0:0:0.613 AlcampoUpdatePlatformStockLevelImpexGenerator_1636553094068 2021-11-10 15:04:56.577 0 0:0:0.486 AlcampoUpdatePlatformStockLevelImpexGenerator_1636553094042 2021-11-10 15:04:56.213 0 0:0:0.518 AlcampoUpdateCollectionStockLevelImpexGenerator_1636553093959 2021-11-10 15:04:55.86 0 0:0:0.506 AlcampoUpdateCollectionStockLevelImpexGenerator_1636553093940 2021-11-10 15:04:55.398 0 0:0:0.585 AlcampoUpdateRealStockLevelImpexGenerator_1636553093859 2021-11-10 15:04:54.383 0 0:0:1.235 AlcampoUpdateRealStockLevelImpexGenerator_1636553093845 2021-11-10 15:04:54.362 0 0:0:0.817 AlcampoUpdatePlatformStockLevelImpexGenerator_1636552946668 2021-11-10 15:02:28.696 0 0:0:0.614 AlcampoUpdateCollectionStockLevelImpexGenerator_1636552946569 2021-11-10 15:02:27.834 0 0:0:0.665 AlcampoUpdateRealStockLevelImpexGenerator_1636552946461 2021-11-10 15:02:26.984 0 0:0:0.623 AlcampoUpdatePlatformStockLevelImpexGenerator_1636552941156 2021-11-10 15:02:23.332 0 0:0:0.499 AlcampoUpdateCollectionStockLevelImpexGenerator_1636552941064 2021-11-10 15:02:22.525 0 0:0:0.602 AlcampoUpdateRealStockLevelImpexGenerator_1636552940970 2021-11-10 15:02:21.484 0 0:0:0.771 AlcampoUpdatePlatformStockLevelImpexGenerator_1636552910392 2021-11-10 15:01:52.321 0 0:0:0.427 AlcampoUpdateCollectionStockLevelImpexGenerator_1636552910282 2021-11-10 15:01:51.602 0 0:0:0.538 AlcampoUpdateRealStockLevelImpexGenerator_1636552910183 2021-11-10 15:01:50.696 0 0:0:0.611 AlcampoUpdatePlatformStockLevelImpexGenerator_1636552896545 2021-11-10 15:01:38.777 0 0:0:0.419 AlcampoUpdateCollectionStockLevelImpexGenerator_1636552896457 2021-11-10 15:01:38.088 0 0:0:0.498 AlcampoUpdateRealStockLevelImpexGenerator_1636552896338 2021-11-10 15:01:37.211 0 0:0:0.658 AlcampoUpdatePlatformStockLevelImpexGenerator_1636552878519 2021-11-10 15:01:20.701 0 0:0:0.428 AlcampoUpdateCollectionStockLevelImpexGenerator_1636552878417 2021-11-10 15:01:19.867 0 0:0:0.648 AlcampoUpdateRealStockLevelImpexGenerator_1636552878322 2021-11-10 15:01:18.908 0 0:0:0.671 AlcampoUpdatePlatformStockLevelImpexGenerator_1636552801882 2021-11-10 15:00:03.906 0 0:0:0.465 AlcampoUpdateCollectionStockLevelImpexGenerator_1636552801787 2021-11-10 15:00:03.154 0 0:0:0.528 AlcampoUpdateRealStockLevelImpexGenerator_1636552801671 2021-11-10 15:00:02.219 0 0:0:0.698 AlcampoUpdatePlatformStockLevelImpexGenerator_1636552769615 2021-11-10 14:59:31.69 0 0:0:0.452 AlcampoUpdateCollectionStockLevelImpexGenerator_1636552769512 2021-11-10 14:59:30.915 0 0:0:0.583 AlcampoUpdateRealStockLevelImpexGenerator_1636552769418 2021-11-10 14:59:29.947 0 0:0:0.762 AlcampoUpdatePlatformStockLevelImpexGenerator_1636552685002 2021-11-10 14:58:06.867 0 0:0:0.478 AlcampoUpdateCollectionStockLevelImpexGenerator_1636552684919 2021-11-10 14:58:06.101 0 0:0:0.589 AlcampoUpdateRealStockLevelImpexGenerator_1636552684824 2021-11-10 14:58:05.272 0 0:0:0.604 AlcampoUpdatePlatformStockLevelImpexGenerator_1636552600314 2021-11-10 14:56:42.303 0 0:0:0.482 AlcampoUpdateCollectionStockLevelImpexGenerator_1636552600213 2021-11-10 14:56:41.568 0 0:0:0.531 AlcampoUpdatePlatformStockLevelImpexGenerator_1636552599104 2021-11-10 14:56:41.053 0 0:0:0.545 AlcampoUpdateRealStockLevelImpexGenerator_1636552600105 2021-11-10 14:56:40.626 0 0:0:0.725 AlcampoUpdateCollectionStockLevelImpexGenerator_1636552599008 2021-11-10 14:56:40.331 0 0:0:0.527 AlcampoUpdateRealStockLevelImpexGenerator_1636552598907 2021-11-10 14:56:39.405 0 0:0:0.676 AlcampoUpdatePlatformStockLevelImpexGenerator_1636552559803 2021-11-10 14:56:01.815 0 0:0:0.492 AlcampoUpdateCollectionStockLevelImpexGenerator_1636552559695 2021-11-10 14:56:00.991 0 0:0:0.648 AlcampoUpdateRealStockLevelImpexGenerator_1636552559596 2021-11-10 14:56:00.156 0 0:0:0.634 AlcampoUpdatePlatformStockLevelImpexGenerator_1636552516004 2021-11-10 14:55:17.893 0 0:0:0.398 AlcampoUpdateCollectionStockLevelImpexGenerator_1636552515922 2021-11-10 14:55:17.191 0 0:0:0.523 AlcampoUpdateRealStockLevelImpexGenerator_1636552515831 2021-11-10 14:55:16.311 0 0:0:0.627 AlcampoUpdatePlatformStockLevelImpexGenerator_1636552442521 2021-11-10 14:54:04.327 0 0:0:0.464 AlcampoUpdateCollectionStockLevelImpexGenerator_1636552442421 2021-11-10 14:54:03.66 0 0:0:0.489 AlcampoUpdateRealStockLevelImpexGenerator_1636552442317 2021-11-10 14:54:02.832 0 0:0:0.607 AlcampoUpdatePlatformStockLevelImpexGenerator_1636552432042 2021-11-10 14:53:54.08 0 0:0:0.429 AlcampoUpdateCollectionStockLevelImpexGenerator_1636552431943 2021-11-10 14:53:53.37 0 0:0:0.529 AlcampoUpdateRealStockLevelImpexGenerator_1636552431839 2021-11-10 14:53:52.397 0 0:0:0.751 AlcampoUpdatePlatformStockLevelImpexGenerator_1636552421680 2021-11-10 14:53:43.68 0 0:0:0.541 AlcampoUpdateCollectionStockLevelImpexGenerator_1636552421580 2021-11-10 14:53:42.971 0 0:0:0.527 AlcampoUpdateRealStockLevelImpexGenerator_1636552421479 2021-11-10 14:53:42.026 0 0:0:0.729 AlcampoUpdatePlatformStockLevelImpexGenerator_1636552401975 2021-11-10 14:53:24.055 0 0:0:0.519 AlcampoUpdateCollectionStockLevelImpexGenerator_1636552401885 2021-11-10 14:53:23.233 0 0:0:0.597 AlcampoUpdateRealStockLevelImpexGenerator_1636552401790 2021-11-10 14:53:22.285 0 0:0:0.733 AlcampoUpdatePlatformStockLevelImpexGenerator_1636552382057 2021-11-10 14:53:04.191 0 0:0:0.452 AlcampoUpdatePlatformStockLevelImpexGenerator_1636552381735 2021-11-10 14:53:03.633 0 0:0:0.486 AlcampoUpdateCollectionStockLevelImpexGenerator_1636552381969 2021-11-10 14:53:03.382 0 0:0:0.567 AlcampoUpdateCollectionStockLevelImpexGenerator_1636552381648 2021-11-10 14:53:02.968 0 0:0:0.473 AlcampoUpdateRealStockLevelImpexGenerator_1636552381880 2021-11-10 14:53:02.337 0 0:0:0.706 AlcampoUpdateRealStockLevelImpexGenerator_1636552381569 2021-11-10 14:53:02.02 0 0:0:0.679 AlcampoUpdatePlatformStockLevelImpexGenerator_1636552338922 2021-11-10 14:52:20.904 0 0:0:0.428 AlcampoUpdateCollectionStockLevelImpexGenerator_1636552338823 2021-11-10 14:52:20.113 0 0:0:0.607 AlcampoUpdateRealStockLevelImpexGenerator_1636552338713 2021-11-10 14:52:19.232 0 0:0:0.65 AlcampoUpdatePlatformStockLevelImpexGenerator_1636552321157 2021-11-10 14:52:03.395 0 0:0:0.555 AlcampoUpdatePlatformStockLevelImpexGenerator_1636552321149 2021-11-10 14:52:03.124 0 0:0:0.526 AlcampoUpdateCollectionStockLevelImpexGenerator_1636552321058 2021-11-10 14:52:02.503 0 0:0:0.564 AlcampoUpdateCollectionStockLevelImpexGenerator_1636552321045 2021-11-10 14:52:02.448 0 0:0:0.475 AlcampoUpdateRealStockLevelImpexGenerator_1636552320970 2021-11-10 14:52:01.479 0 0:0:0.839 AlcampoUpdateRealStockLevelImpexGenerator_1636552320938 2021-11-10 14:52:01.465 0 0:0:0.717 AlcampoUpdatePlatformStockLevelImpexGenerator_1636552308756 2021-11-10 14:51:50.958 0 0:0:0.414 AlcampoUpdateCollectionStockLevelImpexGenerator_1636552308662 2021-11-10 14:51:50.194 0 0:0:0.556 AlcampoUpdateRealStockLevelImpexGenerator_1636552308542 2021-11-10 14:51:49.348 0 0:0:0.607 AlcampoUpdatePlatformStockLevelImpexGenerator_1636552253616 2021-11-10 14:50:55.751 0 0:0:0.466 AlcampoUpdateCollectionStockLevelImpexGenerator_1636552253512 2021-11-10 14:50:54.943 0 0:0:0.517 AlcampoUpdateRealStockLevelImpexGenerator_1636552250523 2021-11-10 14:50:53.946 0 0:0:0.728 AlcampoUpdatePlatformStockLevelImpexGenerator_1636552240473 2021-11-10 14:50:42.314 0 0:0:0.568 AlcampoUpdateCollectionStockLevelImpexGenerator_1636552240378 2021-11-10 14:50:41.646 0 0:0:0.481 AlcampoUpdateRealStockLevelImpexGenerator_1636552240279 2021-11-10 14:50:40.776 0 0:0:0.593 AlcampoUpdatePlatformStockLevelImpexGenerator_1636552230827 2021-11-10 14:50:32.893 0 0:0:0.411 AlcampoUpdateCollectionStockLevelImpexGenerator_1636552230731 2021-11-10 14:50:32.058 0 0:0:0.624 AlcampoUpdateRealStockLevelImpexGenerator_1636552230635 2021-11-10 14:50:31.124 0 0:0:0.723 AlcampoUpdatePlatformStockLevelImpexGenerator_1636552228214 2021-11-10 14:50:30.123 0 0:0:0.47 AlcampoUpdateCollectionStockLevelImpexGenerator_1636552228118 2021-11-10 14:50:29.358 0 0:0:0.576 AlcampoUpdateRealStockLevelImpexGenerator_1636552228014 2021-11-10 14:50:28.528 0 0:0:0.581 AlcampoUpdatePlatformStockLevelImpexGenerator_1636552168804 2021-11-10 14:49:30.723 0 0:0:0.449 AlcampoUpdatePlatformStockLevelImpexGenerator_1636552167894 2021-11-10 14:49:30.128 0 0:0:0.412 AlcampoUpdateCollectionStockLevelImpexGenerator_1636552168716 2021-11-10 14:49:29.941 0 0:0:0.549 AlcampoUpdateCollectionStockLevelImpexGenerator_1636552167813 2021-11-10 14:49:29.257 0 0:0:0.673 AlcampoUpdateRealStockLevelImpexGenerator_1636552168623 2021-11-10 14:49:29.112 0 0:0:0.623 AlcampoUpdateRealStockLevelImpexGenerator_1636552167719 2021-11-10 14:49:28.216 0 0:0:0.798 AlcampoUpdatePlatformStockLevelImpexGenerator_1636552118204 2021-11-10 14:48:40.036 0 0:0:0.436 AlcampoUpdateCollectionStockLevelImpexGenerator_1636552118109 2021-11-10 14:48:39.385 0 0:0:0.488 AlcampoUpdateRealStockLevelImpexGenerator_1636552118016 2021-11-10 14:48:38.493 0 0:0:0.652 AlcampoUpdatePlatformStockLevelImpexGenerator_1636552108158 2021-11-10 14:48:30.057 0 0:0:0.481 AlcampoUpdateCollectionStockLevelImpexGenerator_1636552108061 2021-11-10 14:48:29.378 0 0:0:0.484 AlcampoUpdateRealStockLevelImpexGenerator_1636552107974 2021-11-10 14:48:28.519 0 0:0:0.626 AlcampoUpdatePlatformStockLevelImpexGenerator_1636552104990 2021-11-10 14:48:26.853 0 0:0:0.621 AlcampoUpdateCollectionStockLevelImpexGenerator_1636552104893 2021-11-10 14:48:26.098 0 0:0:0.572 AlcampoUpdateRealStockLevelImpexGenerator_1636552104794 2021-11-10 14:48:25.286 0 0:0:0.592 AlcampoUpdatePlatformStockLevelImpexGenerator_1636552094261 2021-11-10 14:48:16.435 0 0:0:0.579 AlcampoUpdateCollectionStockLevelImpexGenerator_1636552094144 2021-11-10 14:48:15.716 0 0:0:0.508 AlcampoUpdateRealStockLevelImpexGenerator_1636552093798 2021-11-10 14:48:14.593 0 0:0:0.614 AlcampoUpdatePlatformStockLevelImpexGenerator_1636552063758 2021-11-10 14:47:45.661 0 0:0:0.464 AlcampoUpdateCollectionStockLevelImpexGenerator_1636552063666 2021-11-10 14:47:44.963 0 0:0:0.5 AlcampoUpdateRealStockLevelImpexGenerator_1636552063560 2021-11-10 14:47:44.085 0 0:0:0.668 AlcampoUpdatePlatformStockLevelImpexGenerator_1636552047263 2021-11-10 14:47:29.275 0 0:0:0.402 AlcampoUpdateCollectionStockLevelImpexGenerator_1636552047168 2021-11-10 14:47:28.406 0 0:0:0.646 AlcampoUpdateRealStockLevelImpexGenerator_1636552047052 2021-11-10 14:47:27.554 0 0:0:0.602 AlcampoUpdatePlatformStockLevelImpexGenerator_1636552034156 2021-11-10 14:47:16.183 0 0:0:0.45 AlcampoUpdateCollectionStockLevelImpexGenerator_1636552034057 2021-11-10 14:47:15.48 0 0:0:0.518 AlcampoUpdateRealStockLevelImpexGenerator_1636552033956 2021-11-10 14:47:14.512 0 0:0:0.742 AlcampoUpdatePlatformStockLevelImpexGenerator_1636552013816 2021-11-10 14:46:55.728 0 0:0:0.411 AlcampoUpdateCollectionStockLevelImpexGenerator_1636552013708 2021-11-10 14:46:55.005 0 0:0:0.527 AlcampoUpdateRealStockLevelImpexGenerator_1636552013614 2021-11-10 14:46:54.117 0 0:0:0.627 AlcampoUpdatePlatformStockLevelImpexGenerator_1636551947247 2021-11-10 14:45:49.437 0 0:0:0.555 AlcampoUpdateCollectionStockLevelImpexGenerator_1636551947121 2021-11-10 14:45:48.63 0 0:0:0.599 AlcampoUpdateRealStockLevelImpexGenerator_1636551947022 2021-11-10 14:45:47.579 0 0:0:0.823 AlcampoUpdatePlatformStockLevelImpexGenerator_1636551932915 2021-11-10 14:45:34.983 0 0:0:0.425 AlcampoUpdateCollectionStockLevelImpexGenerator_1636551932827 2021-11-10 14:45:34.153 0 0:0:0.65 AlcampoUpdateRealStockLevelImpexGenerator_1636551932739 2021-11-10 14:45:33.216 0 0:0:0.674 AlcampoUpdatePlatformStockLevelImpexGenerator_1636551900227 2021-11-10 14:45:02.075 0 0:0:0.412 AlcampoUpdateCollectionStockLevelImpexGenerator_1636551900131 2021-11-10 14:45:01.384 0 0:0:0.501 AlcampoUpdateRealStockLevelImpexGenerator_1636551900044 2021-11-10 14:45:00.531 0 0:0:0.599 AlcampoUpdatePlatformStockLevelImpexGenerator_1636551884631 2021-11-10 14:44:46.793 0 0:0:0.389 AlcampoUpdateCollectionStockLevelImpexGenerator_1636551884531 2021-11-10 14:44:45.989 0 0:0:0.622 AlcampoUpdateRealStockLevelImpexGenerator_1636551884434 2021-11-10 14:44:44.938 0 0:0:0.83 AlcampoUpdatePlatformStockLevelImpexGenerator_1636551876561 2021-11-10 14:44:38.444 0 0:0:0.416 AlcampoUpdateCollectionStockLevelImpexGenerator_1636551876463 2021-11-10 14:44:37.703 0 0:0:0.562 AlcampoUpdateRealStockLevelImpexGenerator_1636551876376 2021-11-10 14:44:36.852 0 0:0:0.64 AlcampoUpdatePlatformStockLevelImpexGenerator_1636551871313 2021-11-10 14:44:33.465 0 0:0:0.467 AlcampoUpdateCollectionStockLevelImpexGenerator_1636551871217 2021-11-10 14:44:32.578 0 0:0:0.642 AlcampoUpdateRealStockLevelImpexGenerator_1636551871119 2021-11-10 14:44:31.613 0 0:0:0.768 AlcampoUpdatePlatformStockLevelImpexGenerator_1636551852983 2021-11-10 14:44:15.064 0 0:0:0.467 AlcampoUpdateCollectionStockLevelImpexGenerator_1636551852881 2021-11-10 14:44:14.247 0 0:0:0.616 AlcampoUpdateRealStockLevelImpexGenerator_1636551852784 2021-11-10 14:44:13.263 0 0:0:0.765 AlcampoUpdatePlatformStockLevelImpexGenerator_1636551803794 2021-11-10 14:43:25.826 0 0:0:0.463 AlcampoUpdateCollectionStockLevelImpexGenerator_1636551803704 2021-11-10 14:43:24.985 0 0:0:0.636 AlcampoUpdateRealStockLevelImpexGenerator_1636551803598 2021-11-10 14:43:24.094 0 0:0:0.649 AlcampoUpdatePlatformStockLevelImpexGenerator_1636551788090 2021-11-10 14:43:10.469 0 0:0:0.529 AlcampoUpdateCollectionStockLevelImpexGenerator_1636551787992 2021-11-10 14:43:09.574 0 0:0:0.678 AlcampoUpdateRealStockLevelImpexGenerator_1636551787887 2021-11-10 14:43:08.404 0 0:0:0.927 AlcampoUpdatePlatformStockLevelImpexGenerator_1636551724621 2021-11-10 14:42:06.859 0 0:0:0.426 AlcampoUpdateCollectionStockLevelImpexGenerator_1636551724428 2021-11-10 14:42:06.023 0 0:0:0.631 AlcampoUpdateRealStockLevelImpexGenerator_1636551724331 2021-11-10 14:42:04.967 0 0:0:0.784 AlcampoUpdatePlatformStockLevelImpexGenerator_1636551705563 2021-11-10 14:41:47.453 0 0:0:0.44 AlcampoUpdatePlatformStockLevelImpexGenerator_1636551705354 2021-11-10 14:41:47.316 0 0:0:0.527 AlcampoUpdateCollectionStockLevelImpexGenerator_1636551705451 2021-11-10 14:41:46.771 0 0:0:0.506 AlcampoUpdateCollectionStockLevelImpexGenerator_1636551705222 2021-11-10 14:41:46.509 0 0:0:0.613 AlcampoUpdateRealStockLevelImpexGenerator_1636551705365 2021-11-10 14:41:45.861 0 0:0:0.735 AlcampoUpdateRealStockLevelImpexGenerator_1636551705106 2021-11-10 14:41:45.635 0 0:0:0.665 AlcampoUpdatePlatformStockLevelImpexGenerator_1636551672633 2021-11-10 14:41:14.675 0 0:0:0.474 AlcampoUpdateCollectionStockLevelImpexGenerator_1636551672539 2021-11-10 14:41:13.77 0 0:0:0.703 AlcampoUpdateRealStockLevelImpexGenerator_1636551672450 2021-11-10 14:41:12.93 0 0:0:0.58 AlcampoUpdatePlatformStockLevelImpexGenerator_1636551651904 2021-11-10 14:40:54.191 0 0:0:0.418 AlcampoUpdatePlatformStockLevelImpexGenerator_1636551652085 2021-11-10 14:40:54.169 0 0:0:0.443 AlcampoUpdatePlatformStockLevelImpexGenerator_1636551651759 2021-11-10 14:40:53.882 0 0:0:0.422 AlcampoUpdateCollectionStockLevelImpexGenerator_1636551651968 2021-11-10 14:40:53.39 0 0:0:0.549 AlcampoUpdateCollectionStockLevelImpexGenerator_1636551651803 2021-11-10 14:40:53.325 0 0:0:0.672 AlcampoUpdateCollectionStockLevelImpexGenerator_1636551651664 2021-11-10 14:40:52.953 0 0:0:0.709 AlcampoUpdateRealStockLevelImpexGenerator_1636551651853 2021-11-10 14:40:52.424 0 0:0:0.787 AlcampoUpdateRealStockLevelImpexGenerator_1636551651706 2021-11-10 14:40:52.234 0 0:0:0.884 AlcampoUpdateRealStockLevelImpexGenerator_1636551651543 2021-11-10 14:40:52.075 0 0:0:0.671 AlcampoUpdatePlatformStockLevelImpexGenerator_1636551638566 2021-11-10 14:40:40.847 0 0:0:0.443 AlcampoUpdateCollectionStockLevelImpexGenerator_1636551638457 2021-11-10 14:40:39.916 0 0:0:0.701 AlcampoUpdateRealStockLevelImpexGenerator_1636551638351 2021-11-10 14:40:38.87 0 0:0:0.741 AlcampoUpdatePlatformStockLevelImpexGenerator_1636551601256 2021-11-10 14:40:03.358 0 0:0:0.518 AlcampoUpdateCollectionStockLevelImpexGenerator_1636551601157 2021-11-10 14:40:02.541 0 0:0:0.628 AlcampoUpdateRealStockLevelImpexGenerator_1636551601060 2021-11-10 14:40:01.565 0 0:0:0.741 AlcampoUpdatePlatformStockLevelImpexGenerator_1636551595143 2021-11-10 14:39:57.06 0 0:0:0.434 AlcampoUpdateCollectionStockLevelImpexGenerator_1636551595059 2021-11-10 14:39:56.286 0 0:0:0.546 AlcampoUpdateRealStockLevelImpexGenerator_1636551594966 2021-11-10 14:39:55.459 0 0:0:0.59 AlcampoUpdatePlatformStockLevelImpexGenerator_1636551537508 2021-11-10 14:38:59.606 0 0:0:0.507 AlcampoUpdateCollectionStockLevelImpexGenerator_1636551537384 2021-11-10 14:38:58.762 0 0:0:0.655 AlcampoUpdateRealStockLevelImpexGenerator_1636551537291 2021-11-10 14:38:57.822 0 0:0:0.705 AlcampoUpdatePlatformStockLevelImpexGenerator_1636551494848 2021-11-10 14:38:18.434 0 0:0:0.445 AlcampoUpdateCollectionStockLevelImpexGenerator_1636551494755 2021-11-10 14:38:17.728 0 0:0:0.524 AlcampoUpdateRealStockLevelImpexGenerator_1636551494630 2021-11-10 14:38:15.95 0 0:0:1.525 AlcampoUpdatePlatformStockLevelImpexGenerator_1636551474293 2021-11-10 14:37:56.413 0 0:0:0.498 AlcampoUpdateCollectionStockLevelImpexGenerator_1636551474180 2021-11-10 14:37:55.528 0 0:0:0.688 AlcampoUpdateRealStockLevelImpexGenerator_1636551474087 2021-11-10 14:37:54.606 0 0:0:0.696 AlcampoUpdatePlatformStockLevelImpexGenerator_1636551442427 2021-11-10 14:37:24.542 0 0:0:0.466 AlcampoUpdateCollectionStockLevelImpexGenerator_1636551442336 2021-11-10 14:37:23.711 0 0:0:0.632 AlcampoUpdateRealStockLevelImpexGenerator_1636551442248 2021-11-10 14:37:22.723 0 0:0:0.747 AlcampoUpdatePlatformStockLevelImpexGenerator_1636551432707 2021-11-10 14:37:15.046 0 0:0:0.442 AlcampoUpdateCollectionStockLevelImpexGenerator_1636551432602 2021-11-10 14:37:14.191 0 0:0:0.656 AlcampoUpdateRealStockLevelImpexGenerator_1636551432487 2021-11-10 14:37:13.144 0 0:0:0.814 AlcampoUpdatePlatformStockLevelImpexGenerator_1636551387286 2021-11-10 14:36:29.418 0 0:0:0.411 AlcampoUpdateCollectionStockLevelImpexGenerator_1636551387190 2021-11-10 14:36:28.611 0 0:0:0.59 AlcampoUpdateRealStockLevelImpexGenerator_1636551387077 2021-11-10 14:36:27.578 0 0:0:0.765 AlcampoUpdatePlatformStockLevelImpexGenerator_1636551368332 2021-11-10 14:36:10.154 0 0:0:0.448 AlcampoUpdateCollectionStockLevelImpexGenerator_1636551368209 2021-11-10 14:36:09.474 0 0:0:0.487 AlcampoUpdateRealStockLevelImpexGenerator_1636551368114 2021-11-10 14:36:08.647 0 0:0:0.612 AlcampoUpdatePlatformStockLevelImpexGenerator_1636551360984 2021-11-10 14:36:03.103 0 0:0:0.415 AlcampoUpdateCollectionStockLevelImpexGenerator_1636551360887 2021-11-10 14:36:02.302 0 0:0:0.576 AlcampoUpdateRealStockLevelImpexGenerator_1636551360782 2021-11-10 14:36:01.277 0 0:0:0.729 AlcampoUpdatePlatformStockLevelImpexGenerator_1636551348481 2021-11-10 14:35:50.673 0 0:0:0.506 AlcampoUpdateCollectionStockLevelImpexGenerator_1636551348382 2021-11-10 14:35:49.841 0 0:0:0.621 Edited by Julio Argüello -
Flag Stats
SELECT {updateStockLevelsCronjobCode}, count({pk}) FROM {StockLevel} GROUP BY {updateStockLevelsCronjobCode} ORDER BY 2 DESCP_UPDATESTOCKLEVELSCRONJOBCODE COUNT(ITEM_T0.PK) Date Approved Percentage Approved Accumulated Percentage 3.437.503 1970-01-01 00:00:00 80% 0% Flag stock impex job 1636523760729 1.206.552 2021-11-10 05:56:01 28% 28% Flag stock impex job 1636439643968 529.539 2021-11-09 06:34:04 12% 41% Flag stock impex job 1636265556354 253.753 2021-11-07 06:12:36 6% 47% Flag stock impex job 1635745548927 191.909 2021-11-01 05:45:49 4% 51% Flag stock impex job 1636179483587 151.812 2021-11-06 06:18:04 4% 55% Flag stock impex job 1636352442929 117.483 2021-11-08 06:20:43 3% 57% Flag stock impex job 1636092101131 110.241 2021-11-05 06:01:41 3% 60% Flag stock impex job 1635052678671 109.341 2021-10-24 05:17:59 3% 62% Flag stock impex job 1636005665216 103.134 2021-11-04 06:01:05 2% 65% Flag stock impex job 1635659846065 82.693 2021-10-31 05:57:26 2% 67% Flag stock impex job 1635483858468 80.570 2021-10-29 05:04:18 2% 69% Flag stock impex job 1633929647742 75.375 2021-10-11 05:20:48 2% 70% Flag stock impex job 1634275922963 73.518 2021-10-15 05:32:03 2% 72% Flag stock impex job 1635918657544 73.208 2021-11-03 05:50:58 2% 74% Flag stock impex job 1635831220104 58.990 2021-11-02 05:33:40 1% 75% Flag stock impex job 1635227126856 51.057 2021-10-26 05:45:27 1% 76% Flag stock impex job 1635569280025 45.853 2021-10-30 04:48:00 1% 78% fixPRO_ocultacion_11072917 43.255 #VALUE! 1% 79% Flag stock impex job 1634446727522 40.946 2021-10-17 04:58:48 1% 80% Flag stock impex job 1636552597229 38.204 2021-11-10 13:56:37 1% 80% updateAMSPLastDateFromSQLCronJobaffected amspsselect count (*) from v_last_sale_date # 84 # 2021110_16:20 and 2021110_21:00 # Execution time: 388215 ms -> 6 min to execute select * from v_last_sale_date # Execution time: 66966 ms -> 1 min to executeQueries
V_LAST_SALE_DATEClick to expand
CREATE OR REPLACE FORCE VIEW "INT_ECOM"."V_LAST_SALE_DATE" ("PK", "PRODUCT_PK", "P_CATALOGVERSION", "LAST_SALE_DATE") AS SELECT amsp.p_store pk, amsp.p_product product_pk, amsp.p_catalogversion p_catalogversion, cast(st.fecha_ult AS timestamp(3)) last_sale_date FROM alc_stock_tmp st, basestore b, products p, attr_store_product amsp WHERE p.code = to_char(st.etiqueta) AND p.p_catalogversion = 8796093186649 AND b.p_uid = 'basestore_'||st.hiper AND amsp.p_product = p.pk AND amsp.p_store = b.pk AND amsp.p_catalogversion = p.p_catalogversion AND (st.fecha_ult <> trunc(cast(amsp.p_lastsaledate AS date)) OR amsp.p_lastsaledate IS NULL)Edited by Julio Argüello -
Flagged stock levels
Case 1
Products approved or 5_1 that has been recently synced
-
itemsynctimestamps->products->stocklevels
+ "SELECT s.PK " + "FROM itemsynctimestamps ist " + "JOIN products p ON ist.p_targetitem = p.PK " + "JOIN stocklevels s ON p.code = s.p_productcode " + "WHERE ( ist.p_lastsynctime > TO_DATE('" + sdf.format(lastSyncTime) + "', 'dd/MM/yyyy HH24:MI:SS')) " + "AND p.p_approvalstatus IN (select pk from enumerationvalues where (code='approved' or code='Estado___5_1'))"Sync Impact
ABOUT 1/4 M STOCK LEVELS:
Product Catalog Sync Cron Job
000CAXFJ-
11/11/2021 06:38:17 -> 08:40:16
-
(4570 + 106) * 50 ≈ 4600 * 50 = 230.000 stock levels
Entity Action From To Count Category UPDATE 7 5686 5680 Product CREATE 5693 5798 106 Product UPDATE 5800 10369 4570 BrochureRow CREATE 10376 29339 18964 MediaContainer REMOVE 29346 29385 40 MediaContainer CREATE 29387 30038 652 Media UPDATE 30048 32815 2768 Media UPDATE2 32818 32822 5 Case 2
Products approved or 5_1 whose amsp has been changed after last flag
-
attr_store_product->products->basestore->basestore2warehouserel->stocklevels
+ "SELECT s.PK " + "FROM attr_store_product amsp " + "JOIN products p " + "ON amsp.p_product = p.PK " + "JOIN basestore b " + "ON amsp.p_store = b.PK " + "JOIN basestore2warehouserel bwrel " + "ON b.PK = bwrel.SourcePK " + "JOIN stocklevels s " + "ON p.code = s.p_productcode " + "AND bwrel.TargetPK = s.p_warehouse " + "WHERE s.p_lastflaggedtime < amsp.modifiedTS " + "AND p.p_approvalstatus IN (select pk from enumerationvalues where (code='approved' or code='Estado___5_1'))"Amsp Date Impact
ABOUT 1.1 M AMSP
select to_char(amsp.modifiedTS,'DD-MM-YYYY'), count(amsp.pk) from attr_store_product amsp JOIN products p ON amsp.p_product = p.PK WHERE p.p_approvalstatus IN (select pk from enumerationvalues where (code='approved' or code='Estado___5_1')) group by to_char(amsp.modifiedTS,'DD-MM-YYYY') order by 2 descTO_CHAR(AMSP.MODIFIEDTS,'DD-MM-YYYY') COUNT(AMSP.PK) 11-11-2021 1160014 10-11-2021 335398 07-11-2021 229908 09-11-2021 228867 06-11-2021 116467 31-10-2021 114562 ... ... Case 3
Products approved or 5_1 with amsp whose stock level has never been flagged
-
attr_store_product->products->basestore->basestore2warehouserel->stocklevels
+ "SELECT s.PK " + "FROM attr_store_product amsp " + "JOIN products p " + "ON amsp.p_product = p.PK " + "JOIN basestore b " + "ON amsp.p_store = b.PK " + "JOIN basestore2warehouserel bwrel " + "ON b.PK = bwrel.SourcePK " + "JOIN stocklevels s " + "ON p.code = s.p_productcode " + "AND bwrel.TargetPK = s.p_warehouse " + "WHERE s.p_lastflaggedtime IS NULL " + "AND p.p_approvalstatus IN (select pk from enumerationvalues where (code='approved' or code='Estado___5_1')) "Stock Level Flag is Empty
TRENDING 0 SL
Case 4
Products approved or 5_1 with amps whose associated stock level has been modified after been flagged
-
attr_store_product->products->basestore->basestore2warehouserel->stocklevels
+ "SELECT s.PK " + "FROM attr_store_product amsp " + "JOIN products p " + "ON amsp.p_product = p.PK " + "JOIN basestore b " + "ON amsp.p_store = b.PK " + "JOIN basestore2warehouserel bwrel " + "ON b.PK = bwrel.SourcePK " + "JOIN stocklevels s " + "ON p.code = s.p_productcode " + "AND bwrel.TargetPK = s.p_warehouse " + "WHERE s.p_lastflaggedtime < s.modifiedTS " + "AND p.p_approvalstatus IN (select pk from enumerationvalues where (code='approved' or code='Estado___5_1'))" + ") stocklevel)" + " source " + "ON (target.pk = source.stock_level_pk) WHEN matched "Stock Modification Impact
ABOUT 200 k
select count({pk}) from {StockLevel} where {modifiedTime} > TO_DATE('11/NOV/2021 07:00:00', 'dd/mon/yyyy HH24:MI:SS') # 161027 at 14:26 (after 7 hours)Edited by Julio Argüello -
-
Summary: Action Plan
Following points and suggestions are -on every case- based on the analysis and conclusions extracted from real data and commented out on previous comments on this snippet.
Facts
- Post-sync cron jobs -intended for filtering per store assortment- take a long time to execute (about 1 hour)
- It is based on a 2 step process: flagging + consecutive
MERGEoperations to keepin_stock_statusup to date. - Those
MERGEsentences affect to a wide number of stock levels (>1.5 M) after full sync and could co-exist with narrower executions triggered after placing any order. - Most of these
MERGEoperations depend on an OracleViewand aMaterialized View(refreshed once per week)
Suggested Optimizations
- Is "Collection stock" still needed? If not then let's remove it.
- Reduce the total amount of stock levels affected by these operations
-
Avoid concurrency while updating stock levels.
- Either during reserve / release phase
- Or during stock rule recalculation
- A DBA to optimize queries and/or database setup to speed up affected queries and sentences
[1] Avoid calculating "Collection Stock"
AFAIK
AlcampoUpdateCollectionStockLevelImpexGeneratoris not required and takes 10% of time.So, let's validate it can be safely removed and proceed.
[2] Optimize Flagging Stage
OBJECTIVE: Reduce the total amount of stock levels affected by these operations
Every night about 1.2M of stock levels are flagged:
- Either because related products has been synced...
- ...or
AMSPhas been modified after last flag round... - ...or stock levels are new... (have never been flagged before)
- ...or stock levels have been modified after last flag round.
Most of them are caused by [1], [2] and [4] (in such an order) whilst we are only interested on keeping track of changes over:
Table Column Comments productsp_maxdayswithoutsaleproductsp_isvariableweightproductsp_stocktypecat2prodrel(exclusively for AS400 categories) attr_store_productp_specificstockattr_store_productp_lastsaledateattr_store_productp_rangestocklevelsp_stock_collection?? stocklevelsp_instockstatusstocklevelsp_stock1stocklevelsp_availablestocklevelsp_reservedstocklevelsp_securitystockSo we can adjust
com.ibm.alcampo.dao.impl.DefaultCatalogSyncHookDao#updateStockLevelFlagsto affect less items on [1] and [2] and even [4] (less prior) based on a new introduced field onproductandamsp(or even on an auxiliar table). It can be implemented with a Hybris prepare interceptor or directly at database level with a trigger.In such a way the coarse grained timestamps (
itemsynctimestamps.p_lastsynctimeandattr_store_product.modifiedTS) would be replaced by respective timestamps that only changes when anyone of meaningful columns are modified.[2BIS] Optimize Flagging Stage for Case [4]
If optimizing steps [1] and [2] is not enough then we can always apply the same technique for case [4].
I wouldn't implement it by now. Based on the reports above cases [1] and [2] should filter out at least 60% of selected stock levels.
[3] Avoid concurrency over
stocklevelsoperationsIf previous actions does not speed up processes as expected then we could give it a chance to avoid concurrency.
There are two levels of complexity:
- On one hand we could just reject small
MERGEoperations when the huge (after sync)MERGEis in progress.- Current behaviour in unpredictable and could be a reason for slowness.
- It is up to DBA determine whether we could give it a chance or not.
- We could even go further by serializing any stock level operation. I wouldn't do it by now till getting DBA feedback not getting results of previous actions. But it is of course an option.
Resources
- Sync_Analysis: Excel file with data extracted from real system.
- Reglas_de_presentación.docx
Edited by Julio Argüello
Please register or sign in to comment