We are developing a system for the retail market and of its features will enable clients (actually consumer clubs) to go through all transactions made by end-clients.One of the ways to get this information will be via an API.The idea is that there will be requests for reports with a start date and an end date, and a response will have all the transactions between those dates.We worry that some reports will be very large, and that some clients will repeatedly request for reports, in this case the DB and CPU will be very overloaded.The same server that is going to service those requests will also take care of the actual retail transactions (received by proprietary devices) and of the Web application.We are not sure about how to limit the report requests from the API so that it won't affect the system too much.So, how should we deal with this scenario? any thoughts?EDIT:just to make it clear:When I mentioned proprietary devices I meant On-Location devices which are used during sales with end-clients, this means that these requests shouldn't get delayed, and this is the main concern.One more question : Some people here suggested the use of prioritized threads, i.e. - giving lower priority to the threads retrieving the reports, is this a good idea?
How should we deal with multiple transaction-report requests?
database;performance;api;report generation
If you make a business rule such that 1 customer may ask for at most 3 reports a day and the total duration of dates in between is not greater than 30, then you could design a table that the system checks before a report request is processed.A better approach to study the reporting requirement a bit further and consider an Data Mart approach. Move the data to the data mart sever and let the users run the reports as much as they want from the data mart server. Of course this will require more work but it is the better approach from an architecture perspective and it will help your customers with other reporting needs too in the future.