Allow OPTION(HASH GROUP) with SQLCLR UDAs
Currently, it appears that when SQLCLR UDA are used with a GROUP BY clause, a StreamAggregate operation must always be used. Attempting to specify OPTION(HASH GROUP) on a query like this:
SELECT dbo.SomeUDA(col1) FROM someTable GROUP BY someColumn OPTION(HASH GROUP)
produces an "Msg 8622, Query processor could not produce a query plan..."
I only have a single test case, but because there is no special considerations in the grouping column (someColumn) in this case, I can only guess its because you'd need to allocate N UDA instances where N is the number of groups.
This should be permitted (indeed a HashAggregate should be considered by the query optimizer), because, unless the appropriate index exists, an expensive sort operator will always be introduced, decreasing the performance of the UDA.
Upvotes: 24<=-=Jan 4 2011 4:40PM=-=>
Thanks for the feedback. We’ll consider fixing this in a future release. Can you tell me more about the scenario? What UDAgg were you implementing and why? Feel free to contact me by email if you want.
Program Manager, SQL Server Query Processing
Sure Eric, there are a few that I can think of. This actually came about because of the following forum question:
http://social.technet.microsoft.com/Forums/en-US/sqlnetfx/thread/957a5b94-c7d0-49d8-928d-7cccff14b0c6. I realized that the sort was required because of choice of stream aggregate. And he can’t put on every index possible to get rid of the sort.
Second is that the spatial aggregates in Denali would need this funtionality. Related to that is that there’s a vendor product that consists of a library of UDAs, Fuzzy Logix (http://www.fuzzyl.com/in-database_analytics.php#) that could benefit from this flexibility as well.
Hope this helps,
Cheers, and thanks,
I can confirm that this is happening to us as well. We needed a custom aggregate function, and we were very surprise to find out that there was an unnecessary sort happening that was hogging the query. It’s very unfortunate that the OPTION doesn’t work. Please fix that.
Solomon Rutzky commented
This is still an issue in SQL Server 2017 CU12 and SQL Server 2019 CTP 2.2 😿.