November Happy Hour will be moved to Thursday December 5th.
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