I'm working on an CRM type application(.NET, SQL Server) that needs to generate reports from large datasets, millions of database rows in a dozen different tables with a lot of aggregation and logic. The reports are currently generated by long, complicated stored procedures with lots of joins, temp tables and logic. These reports have to follow a third party specification, and the way they are currently written often produce incorrect data. There are also performance issues as the reports take about 10 times longer to run than they should. I'm faced with either re-writing the stored procedures or finding another way of generating the reports. I have two main requirements, speed and accuracy.
SQL seems like the best choice for speed. The data is relational and it already exists in a database. The downside of SQL is that the reports are very complex and having it all in one/several giant queries makes it hard to test different pieces of the logic.
Doing the calculations in C# seems like a better choice for testability/accuracy, but I don't think it would perform very well due to the memory requirements and the slow nature of procedural code.
What makes me lean towards a non SQL solution is that sometimes our customers think the reports are wrong when it's really their own bad data. Customers will often expect a person record to show up on a report, but according to the rules of the report that record should not be included based on the values of certain columns. Because the logic is so complex and our reports are written so badly we usually can't tell customers that their data is bad and why until we spend a day or two tracing that record through the logic of the report query. It would be nice if we had a rules engine that we could run on any given record to see exactly what data columns are causing it to be filtered out of the report. I'd rather not implement the report logic twice, once in the report query and again in a validation engine.
What is the best choice here? Are there rules engines that can operate on large datasets? I don't know much about big data, but I've heard about map/reduce and Hadoop. Would something like that help? What about a functional language like F#? Any other options?