Hi there,
I have a solution with 190K+ orders (many were imported from an external system for marketing purposes). With this change, the backend order list now takes 10+ seconds to load. I turned on a profiler and found that the select statement for the orders does something like this:
-- Build CTE (code skipped)
-- Select from CTE
SELECT * FROM OrdersCount, OrderedOrders WITH (NOLOCK) WHERE RowNumber BETWEEN @p5 AND @p6 ORDER BY RowNumber',N'@p0 datetime,@p1 nvarchar(5),@p2 datetime,@p3 nvarchar(5),@p4 int,@p5 int,@p6 int',@p0='2023-11-22 19:26:50.940',@p1=N'LANG3',@p2='2008-01-01 00:00:00',@p3=N'SHOP1',@p4=0,@p5=1,@p6=2147483647
The last two params basically tell it to fetch all rows.
Isn't the idea of this CTE to do paging so we get one page full of orders at a time instead of all 190K every time we load a page?
Tested on 9.15.14 if that matters.
Imar
