3679180 - Sub-optimal SQL Query Performance after upgrade to Commerce Cloud 2211.42 or later

After upgrading to Commerce Cloud 2211.42 or above, some queries are taking longer to execute when comparing the execution time to previous Commerce versions. 


When comparing the query text from before and after the upgrade, there is a change in whitespace surrounding query parameters.

eg.
<=2211.41

SELECT  item_t0.PK  FROM products item_t0 WHERE ( item_t0.modifiedTS  >=  @P0) AND (item_t0.TypePkString IN  (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9))

>=2211.42

SELECT  item_t0.PK  FROM products item_t0 WHERE ( item_t0.modifiedTS  >=  @P0 ) AND (item_t0.TypePkString IN  ( @P1 , @P2 , @P3 , @P4 , @P5 , @P6 , @P7 , @P8 , @P9 ) )

In the HAC -> Azure SQL -> Top Long Running Queries -> Download, comparing the query before and after upgrade you may find that the query has a different plan_id.

Commerce Cloud >=2211.42

Database, DB, Execution Plan, query optimizer, query_id, plan_id, SQLServer, Azure SQL, spacing, whitespace , KBA , CEC-SCC-PLA-PL , Platform , CEC-HCS-CCAZ-CO-DBA , DB , Problem