How to manage data access / preloading efficiently using web services in C#?

Ok, this is very "generic" question. We currently have a SQL Server database for which we need to develop an application in ASP.NET with will contain all the business logic in C# Web Services.

The thing is that, architecturally speaking, I'm not sure how to design the web service and the data management. There are many things to consider :

1. We need have very rapid access to data. Right now, we have over a million "sales" and "purchases" record from which we need to often calculate and load the current stock for a given day according to a serie of parameter. I'm not sure how we should preload the data and keep the data in the Web Service. Doing a stock calculation within a SQL query will be very lengthy. They currently have a stock calculation application that preloads all sales and purchases for the day and afterwards calculate the stock on the code-side.

2. We want to develop powerful reporting tools. We want to implement a "pivot table" but not sure how to implement it and have good performances.

3. For the reasons above, I'm not sure how to design the data model.

4. How would you manage the display of the current stock, considering that "stock" is actually purchases - sales and that you have to consider all rows to calculate it ? Would you cache "stock" data in the database to optimize开发者_StackOverflow performances, even though its redundant data ?

Anybody can give me any guidelines on how to start, or from their personnal experiences (what have you done in the past ?)

I'm not sure if it's possible to make a bounty even though the question is new (I'd put 300 rep on it, since I really need something). If you know how, let me know.

Thanks

---

The first suggestion is to not use legacy ASMX web services. Use WCF, which Microsoft says should be used for all new web service development.

Second, are you sure you can't optimize the database, or else place it on faster hardware, or nearer to the web server?

I don't know that you're going to get that much data in memory at once. If you could, then you could use a DataSet and use LINQ to DataSets for queries against it.

---

I hope I hope I'm misunderstanding what you wrote, but if by

contain all the business logic in C# Web Services

you mean something like this then you're already headed in the anti-pattern direction. Accessing your data from an ASP.NET application over web-services would just cause you to incur the serialization/deserialization penalty for pretty much no gain.

A better approach would be to organize services you want to make available into a common layer that your applications are built on and access them directly from your ASP.NET application and maybe also expose them as Web Services to allow external sources to consume this data.

You could also look into exposing data that is expensive to compute using a data warehouse that is updated at regular intervals (once or a couple of times/day). This would help with getting better read performance out of data (as long as you're willing to accept data being a bit stale).

Is that the kind of information you're looking for?