Virtual Happy Hour is canceled this month (March) due to Good Friday.
Try our conversational search powered by Generative AI!
AI OnAI Off
Virtual Happy Hour is canceled this month (March) due to Good Friday.
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).
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