Thanks Quassnoi, after considering it for a while that's the conclusion I came to, although I didn't realize that the CROSS APPLY would actually perform worse for high-cardinality groupings. I thought it would gradually worsen as the cardinality got higher until it eventually ended up about the same as the ranking functions performance wise. I guess I was wrong.
Your articles are great. I read the one you put in your reply here and I was pretty amazed at some of those queries. I thought SQL Server would be smart enough that you wouldn't have to use tricky recursive CTEs and double-APPLYs to explicitly force efficient usage of the index. When I have some time, I'm going to try testing some of the queries in your articles to see the results firsthand.
Here's a question related to the ProductVersion queries in this discussion:
... analytic functions (ROW_NUMBER or DENSE_RANK) are more efficient due to the overhead of building a DISTINCT list ...
You're saying that the CROSS APPLY query takes a performance hit for high-cardinality data because of the overhead of building a list of DISTINCT ProductIds right? If that's the case, what if there was a separate table that stored each product (lets say dbo.Products) and instead of querying DISTINCT values from the ProductVersions table, we used all the ProductIds from the Products table.
SELECT version.*
FROM dbo.Products product
CROSS APPLY (
SELECT TOP(1) *
FROM dbo.ProductVersions
WHERE ProductId = product.Id
ORDER BY Version DESC, MinorVersion DESC, ReleaseVersion DESC
) version
This shouldn't change the resultset at all since products without versions will be eliminated by the CROSS APPLY, but lets assume anyway that every Product has at least one associated ProductVersions record. If I'm understanding things correctly, this should perform equivalent to the ranking function query for high-cardinality ProductVersions(ProductId), and much better with low-cardinality, since we're avoiding having to build a list of DISTINCT ProductIds. Is this correct?
-- Edit --
Also, in this article:
http://explainextended.com/2009/11/30/sql-server-selecting-records-holding-group-wise-maximum/
at the bottom it says:
To select records holding group-wise maximums in SQL Server, the following approaches can be used:
* Analytic functions
* Using DISTINCT / CROSS APPLY / TOP
* Using recursive CTE’s / CROSS APPLY / TOP
These methods are arranged in order of increasing complexity and decreasing efficiency (for low-cardinality groupers).
Didn't you mean increasing efficiency or maybe decreasing cost? As the queries became more complex they also executed a lot faster.