A critical vulnerability was discovered in React Server Components (Next.js). Our systems remain protected but we advise to update packages to newest version. Learn More.
AI OnAI Off
Slow SQL for stock quantity check (Add to cart) - ecf_WarehouseInventory_GetInventories
If i trace... i think "Get":
var inventory = inventoryService.Get(new CatalogKey(catalogEntry), warehouse);
calls "List":
return Enumerable.FirstOrDefault<IWarehouseInventory>(this.List((IEnumerable<CatalogKey>) new CatalogKey[1]
{
catalogKey
}, warehouse));
}
which invokes the slow one instead of:
[ecf_WarehouseInventory_GetInventory] which i guess would be more appopriate in that case. And is a lot faster (more like my fix).
Apr 09, 2014 17:12
This topic was created over six months ago and has been resolved. If you have a similar question, please create a new topic and refer to this one.
Hello
We have a problem with "add to cart" (slow). So we debugged and found that 1 SP took about 4 s (real time) to finish. We then changed the SQL to use JOIN instead of select in there where-clause and the performance were x20.
Can I get some opinion on how you think this will work and if there are any problem. Maybe change the code if its good?
The SQL:
declare @p2 dbo.udttCatalogKey
insert into @p2 values('8E90AF84-13FA-4388-A93B-DF5296C5ADFF',N'1002')
declare @p3 dbo.udttWarehouseCode
insert into @p3 values(N'8e90af84-13fa-4388-a93b-df5296c5adff')
insert into @p3 values(N'01')
exec ecf_WarehouseInventory_GetInventories @ApplicationId='8E90AF84-13FA-4388-A93B-DF5296C5ADFF',@CatalogKeys=@p2,@WarehouseCodes=@p3
NEW
SELECT
WI.WarehouseCode,
WI.CatalogEntryCode,
WI.InStockQuantity,
WI.ReservedQuantity,
WI.ReorderMinQuantity,
WI.PreorderQuantity,
WI.BackorderQuantity,
WI.AllowPreorder,
WI.AllowBackorder,
WI.InventoryStatus,
WI.PreorderAvailabilityDate,
WI.BackorderAvailabilityDate,
WI.ApplicationId
FROM [WarehouseInventory] AS WI
JOIN [Warehouse] AS W ON WI.WarehouseCode = W.Code
LEFT JOIN @CatalogKeys as CK ON CK.CatalogEntryCode = WI.CatalogEntryCode
LEFT JOIN @WarehouseCodes as WC ON WC.WarehouseCode = WI.WarehouseCode
WHERE WI.ApplicationId = @ApplicationId
AND (@filterCatalogKeys = 0 OR CK.CatalogEntryCode is not NULL)
AND (@filterWarehouseCodes = 0 OR WC.WarehouseCode is not NULL)
ORDER BY W.SortOrder, WI.CatalogEntryCode
Changed from old:
FROM [WarehouseInventory] AS WI
JOIN [Warehouse] AS W ON WI.WarehouseCode = W.Code
WHERE WI.ApplicationId = @ApplicationId
AND (@filterCatalogKeys = 0 OR WI.CatalogEntryCode IN (SELECT CatalogEntryCode FROM @CatalogKeys))
AND (@filterWarehouseCodes = 0 OR WI.WarehouseCode IN (SELECT WarehouseCode FROM @WarehouseCodes))
ORDER BY W.SortOrder, WI.CatalogEntryCode
Time take:
Before:
SQL Server Execution Times:
CPU time = 6563 ms, elapsed time = 2432 ms.
After:
SQL Server Execution Times:
CPU time = 327 ms, elapsed time = 177 ms.
No indexes or anything else were changed.
/Daniel