CLR Aggregate shows an expensive sort

• David.Poole

  SSC Guru

  Points: 76140

  More actions

  November 5, 2010 at 8:32 am

  #228807

  I've been testing CLR Vs T-SQL versions of linear regression aggregates in SQL2008.

  The gist is that CLR is slightly slower if I aggregate an entire table however when I introduce a GROUP BY statement the execution plan shows that the entire set is sorted prior to aggregation so the CLR:T-SQL Cost ratio for the Aggregate goes from being 49:51 to 89:11

  Has anyone come across this problem and if so how did you solve it?

  LinkedIn Profile

• thierry.claveau

  SSC Enthusiast

  Points: 101

  More actions

  February 15, 2016 at 10:05 am

  #1858815

  Hi David,

  Many years later... I'm interested in iteratively using regressions over up to 1000 lines within a table that contains millions lines. I'm wondering if you achieved to explain why SQL uses the SORT? Also, were you able to achieve better performance with your CLR-aggregate function?

  Thank you !

• Solomon Rutzky

  SSCoach

  Points: 16430

  More actions

  April 30, 2016 at 2:53 pm

  #1875769

  Just FYI, there is an oddly similar thread on the MSDN forums ;-):

  https://social.msdn.microsoft.com/Forums/sqlserver/en-US/957a5b94-c7d0-49d8-928d-7cccff14b0c6/clr-aggregate-performs-an-expensive-sort

  It does not seem as though there is a fix as I tested with SQL Server 2014 and the same error occurs when attempting OPTION (HASH GROUP).

  SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
  Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
  Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
  Info sites — Collations     •     Module Signing     •     SQLCLR

• Solomon Rutzky

  SSCoach

  Points: 16430

  More actions

  February 15, 2019 at 8:03 am

  #2022151

  Now that Microsoft Connect has been moved over to UserVoice, the new URL for the request to fix this is:

  Allow OPTION(HASH GROUP) with SQLCLR UDAs

  Please support that request by voting for it. Thanks!

  Take care, Solomon..

  SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
  Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
  Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
  Info sites — Collations     •     Module Signing     •     SQLCLR