We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
Sorry, I forgot to mention. I need to do this using a CROSS APPLY. I cannot use any windowed functions to perform this task due to performance reasons.
slightwv (䄆 Netminder)
How about doing the cross apply in an inner query and using row_number on an outer query?
pseudo code:
select element_id, property_id, valuefrom ( select element_id, property_id, value, row_number() over(partition by property_id order by property_id, element_id desc) rn from ( select blah, blah blah from junk1 cross apply junk2 ) t2) t1where rn=1
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
slightwv (䄆 Netminder)
As you know, I'm not really a SQL Server person. I was thinking by moving the window function to an outer level and keeping the cross join inside, performance might not be an issue.
Guess a temp table or materialize view from the cross join then using row_number isn't an option either since it uses row_number?
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
Unlimited question asking, solutions, articles and more.
Russ Suter
ASKER
Thanks all!
So it would appear that I was operating under a false assumption that ROW_NUMBER() is slow. I had an interesting few hours last weekend figuring out why my view was running so slow. It turned out that my view uses an APPLY clause to call a user defined function. It's the UDF that is using the ROW_NUMBER() function. When using CROSS APPLY, performance of the view is about twice as slow as when using OUTER APPLY. Furthermore, if I filter the view with any kind of WHERE clause, performance of the view drops even further when using CROSS APPLY. My solution was to just use OUTER APPLY for the view and now performance is great. The UDF is still using ROW_NUMBER(). Thanks all for your contributions.
PortletPaul
What does that UDF return?
A scalar value? e.g. RETURN INT
A table?
I suggest you consider possible alternatives for that function.