Given: A C# calculation engine that loads an object model, crunches huge amounts of numbers, and saves the results to a couple of gigantic mega-indexed database tables in SQL Server. Those tables provide data to web interfaces, other software modules, and SQL Server Reporting Services 2005 reports.
I managed to make the engine a lot faster in the latest version of the software, fast enough now that it can provide the data on request - sometimes even faster than the time it takes to query the database for the pre-calculated numbers. I am very happy about this.
That breakthrough means that we can generate data on request for the web interfaces and other software modules. But the cache tables cannot die yet, because they're consumed by the SSRS reports (or more specifically, by stored procedures that query the tables and provide the data to SSRS.)
The cache tables are a pain, in much the same way that any cache is a pain in the world of software. Without going into too much detail, they have sync'ing problems, locking problems, etc etc. The software would work so much more nicely if I didn't have to worry about keeping those darned tables up to date.
But how else can I get the data into SSRS? I've done a fair bit of research and nothing looks too promising:
- We could provide the data via a web service and use the SSRS XML DPE. But that looks kind of hideous - am I right that you have to parse your SOAP envelope yourself?! And it doesn't support XPath, but a proprietary XPath-y dialect?? Our report writers know T-SQL, and that's what they're best at.
- Using the SQL CLR to host our API is not desirable - it's a big app and you can't do anything without creating an application object and logging in, etc.
- Using the SQL CLR to contact a web service on the web application - this is the most promising so far (this article was helpful c# sql web-services reporting-services