The stored procedure is slow when you do a generic filter in administration section or you use "Search" on the home page,
i had problems in that kind of navigation, this is the reason i tried to optimize the query.
You are right, the query as proposed can return duplicate values if products have meny variants (or products associated to more than one category), i didn't noticed it ;P thanks for your suggestion.
It's better to do a DISTINCT in the select that retrieves results, i got 8.254 seconds against 10.367 (I have more than 120.000 products with no variants)
so the QUERY that populates the temporary table becomes :
---------------- Create query that retrieve Products and populate Temporary table with results --------
set @sql =
N'INSERT INTO #tmpPage SELECT res.* FROM Nop_Product nopp inner join
(SELECT Distinct COUNT(*) OVER() as tot,
ROW_NUMBER() OVER
(' + @orderbysql +
') as ROWNUMBER,
p.ProductId,
isnull(p.Name,pl.name) as pname,
p.ShortDescription,
..............................................
