Calculating report data - C# via Collections or SQLite

I'm currently working on a reporting tool for data which is stored in a MSSQL database. The database is huge and as it is a producti开发者_开发知识库on database and I'm not the owner of it I can't extend any functionality (add tables/functions and so on).

To get my report numbers I would need to use expensive queries (cursors) and at the same time I don't want to cause unnecessary strain to the db. It's also quite complex stuff like analyzing inventory movements and usages, not just simple Sums and Counts.

So my current approach is to load the data in small chunks using single queries, loading them into Collections and then run a couple of foreach iterations to calculate the reported data. Basically I'm rebuilding the database in memory, of which I then calculate.

I currently have quite a lot of code (and objects for the entities in the db) in there and I'm beginning to think that maybe it would be a better solution to create a local simplified copy of the necessary data via for example a separate SQLLite-DB.

I also considered linq to objects but essentially I still need to create and fill the objects... And a DB would even give the capability to just create new queries for new reports instead of hard-coding them. On the other hand it might be slower than to do all in Collections...

So, the question (finally): What do you think, would it be better to manually replicate a simplified and reduced version of the data to a local db or would you go with other solutions?

If your queries are really that expensive, then your best option is to load them in a local database and do your processing there. I'd suggest SQL Server Express Edition as you'll be able to take advantage of t-sql (link).

As for loading the data, you could use your queries, BCP, DTS packages, or about 50 other options.

It is generally acceptable to take snapshots of your Prod DB and replicate them to another DB where you can perform reporting queries on. This way you will not be adding more load to the Prod DB. There are a few ways to do this. Hardware level replication (fastest usually), software level replication (might be expensive), using views, scheduled jobs that will populate your local DB with live data from Prod. Some reporting analysis would take from minutes to hours based on your data model due to joins and the sheer amount of data. So reporting is recommended to be done on a separate non production DB.