Slowness pinpointed during POST to SpecialPrices Service Layer endpoint

[azaleamaemae]

We are about to go live with our new SAP Business One (using Service Layer SLD Version: 1000200)

We are transitioning our pricing (over 700,000 records) from an old ERP into the /b1s/v2/SpecialPrices endpoint

At first, the performance is just fine, but as our custom integration logic continues to push prices, we noticed a slowdown after a few thousand price records were seeded into the system. We have eliminated various possible causes (looking at Service Layer server, restarting the database, index maintenance, etc) and believe there may be a possible bug (or at least room for improvement) in the inner workings of how SAP B1 processes incoming records for this endpoint.

This is what we think is the problem:

Even when POST'ing one new SpecialPrice, if the customer has several other existing prices, they all get updated to have their 'Valid' field set to 'Y' - now many folks may not notice this if all their customers only have a few pricing records.

But, for our company, one of our largest customers has thousands of pricing records (over 500,000) and thus, on each new POST to the /b1s/v2/SpecialPrices endpoint, we notice a significant lag in response time due to the processing burden experienced inside the database as it touches every SpecialPrice for that customer.

This screenshot shows the results of a process-monitoring-query where we can see an UPDATE statement occurring against OSPP, and the only thing in the WHERE clause is the reference to BusinessPartner

(@P1 nvarchar(254))UPDATE T0 SET T0.[Valid] = N'Y'  FROM  [dbo].[OSPP] T0  WHERE T0.[CardCode] = (@P1)