Write stored procedures in Linq/Lambda (Unit-testable but performant stored procs). Possible???

Question

I am creating a data source for reporting model (SQL Server Reporting Services). The reports requires a lot of joins and calculations (let's say, calculating financial parameters like money spent on this, that, amount A vs amount B)...all this involves subobjects.

It makes a lot of sense to me to write unit tests for this code (i.e. walking through order collection, aggregating info based on business rules and subobjects etc.) To do this properly, I would expect my code to look approx. like this

foreach (IOrder in Orders)
   foreach (IOrderLine in IOrder.Orderlines) 
     ...

   return ...

and then test the return value.

But this code is not the SQL which is going to be used in the reporting view...of course... So I am thinking, I could plug-in a .NET assembly in the database. The issue here is, of course, performance...I don't want to loop all these objects in C#...too slow.

So, naturally, Linq/Lambda/Expression trees seem to be the answer to me. As we know, when you are doing Linq to SQL, expression trees are built, and then proper SQL is generated based on them.

So, I could write my code in Linq to Objects, using lambda expressions, unit test this code on sample collections (having expressions compiled to .net), and reuse the same code as Linq to SQL in the DB stored procedure, so that inside SQL Server it would generate proper SQL for me (as Linq to SQL already does)...

Then I could get benefits of both unit-tests and writing domain logic code in C# and high-performing stored procedures for reports.

Possible? Can I use Linq/Lambda in SQL Server CLR Stored procedures? Anyone did it or knows how to make it work? Am I crazy? Do you know a better way of doing it?

Thanks

P.S. I think now I figured out how this should be done properly. According to Udi Dahan, if I understand him right. Database should be denormalized, and all the calculated fields should be on the objects in the table. When something is happening on the subobject (OrderLine added), my Customer object should receive an event and recalculate the smart value (cache it and persist).

Then reports go straight-forward, without logic and work fast...

Answer 1

No, you cannot use LINQ/Lambda in SQL CRL Procs - it is based on a different version of .NET and does not support those namespaces.

Answer 2

So, I could write my code in Linq to Objects, using lambda expressions, unit test this code on sample collections (having expressions compiled to .net), and reuse the same code as Linq to SQL in the DB stored procedure, so that inside SQL Server it would generate proper SQL for me (as Linq to SQL already does)...

This plan was fine until you suggested the CLR code be called from your stored procs. Running CLR code from the database process itself creates a lot of problems with regards to versioning, configuration and database stability... Too many problems if you do that.

Your motivation was to have the benefit of using stored procs, which are faster in general. If those stored procs are in turn running CLR code, they're not going to be faster than the CLR code running in the local process.

Using the LINQ generated expressions technically consumes more CPU cycles than stored procs. This is because the database engine has to regenerate the execution plan each time a query is ran. Typically your database server is on a separate machine though that is not CPU bound (it will be limited instead by disk or network capacity), so this is not a real performance issue. It could be if you run the database server on the same machine as everything else, but don't try to fix this with something so convoluted until its a real issue.

Udi's suggestion may be appropriate, if you want to decrease the overhead of generating the reports. There are two important side effects though to consider first. First, can afford to increase the performance overhead of the operations that pregenerate the reported fields? A bigger problem is that it couples your reporting logic with the code that runs the target system. This prevents you from being able to update the reporting code without also updating the business code, and presumes the reporting code being running as soon as the reported code is put into production.