replacing Group By with row_number()

Question

replacing Group By with row_number()

elayevskiy

SSC Veteran

Points: 295
More actions
September 23, 2010 at 7:47 pm

#148707

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?
Thank you,
Eugene.
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
16188691
16188701
161120731
1612126771
1612143131
1612188291
2612213651
261221911
2612245391
2612245411
Results:
Seller ProductsLoaction_cid CNT
StoreA 1 1
StoreB 12 3

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

Even i have seen that behaviour.. GROUP BY beats ROW_NUMBER by comfortable margin when it is run over large dataset.. if you could provide samples of your environment, it would be helpful...

elayevskiy SSC Veteran Points: 295 More actions · Answer 2

I have SQL 2005. This test is run on Developers Edition, 2GB memory. Indexed View has about 500,000 records. Production system runs on Workgroup edition (this is why I am using WITH (NOEXPAND)), has 4GB memory, and about 4,000,000 records in the VIEW.

Thank you.