Hello, I have an indexed view and I query this view to get counts on how many products each seller has in a specific category. This query works perfect and produces results in 45ms. I want to join additional tables and apply additional WHERE filters to the result set. I am not able to do it because I had to get products_pid out of With clause to be able to join other tables after CTE. I couldn't add products_pid to the select list because it would require me to have in the Group By. After reading articles in this forum, I found out that Group By can be replaced by row_number() over (partition by...). I re-wrote my query using row_number(). But this query takes 1100 ms to execute. Is this the write way to get rid of Group By? What am I doing wrong here and why it takes so long to execute?
Original query:
set statistics time on
GO
with keys as (
select getproductssellers.productslocation_cid,
count(distinct getproductssellers.products_pid) as CNT
from GetProductsSellers WITH (NOEXPAND)
WHERE getproductssellers.products_subcategory=26
group by getproductssellers.productslocation_cid
)
select customer.businessname as Seller, productslocation_cid, CNT
from keys
inner join customer on productslocation_cid=customer.cid
order by CNT desc
New query:
set statistics time on
GO
with keys as (
select getproductssellers.products_pid, getproductssellers.productslocation_cid,
count(getproductssellers.products_pid) over (partition by productslocation_cid) as CNT,
row_number() over(partition by productslocation_cid order by products_pid) as test
from GetProductsSellers WITH (NOEXPAND)
WHERE getproductssellers.products_subcategory=26
--group by getproductssellers.productslocation_cid
)
select customer.businessname as Seller, productslocation_cid, CNT
from keys inner join customer on productslocation_cid=customer.cid
where test=1
order by CNT desc
Data in the View:
Products_subcategory Productslocation_cid Products_pid record_count
16 1 8869 1
16 1 8870 1
16 1 12073 1
16 12 12677 1
16 12 14313 1
16 12 18829 1
26 12 21365 1
26 1 22191 1
26 12 24539 1
26 12 24541 1
Results:
Seller ProductsLoaction_cid CNT
StoreA 1 1
StoreB 12 3