I'm presently focusing on a task which will store specific financial details about our clients inside a MS SQL database. Later, our customers have to have the ability to query the database to come back data in the clients according to certain criteria (eg. clients bigger a certain size, clients inside a certain physical location) and total it for a benchmark. The database is going to be utilized by our financial software utilizing a script.
I'm presently writing the saved methods for that database. Things I am thinking about doing is writing a number of different saved methods in line with the various kinds of criteria you can use. They'll return the customer amounts.
The particular question I've is around the approach to locating the information. I have to do a number of different information using the clients data. Is it more beneficial practice to possess different saved methods to complete the calculation in line with the client number and return the end result or is it more beneficial to simply possess a saved procedure return all the details concerning the client and carry out the information within the script?
Performance happens to be an problem because you will see lots of clients within the database and so i want the technique to become reasonably efficient.
Oddly enough, the information warehouse folks do that constantly. They frequently make use of the easiest possible SQL (Choose SUM/COUNT... GROUP BY...) and perform the work outdoors the database in report-writing tools.
I believe you need to get a duplicate from the Data Warehouse Toolkit and find out how you can do this in ways that's a great deal simpler. more flexible and most likely more scalable.
I believe lots of it has related to the information involved and also the procedures that you're doing. I typically discover that when you are performing information that reduce how big the return in the DB (Types and aggregates) that it's a lot more effective to get it done within the DB. While you begin to do other information it is not as obvious cut though.
Generally such as the ones I believe you are explaining it's more effective to make use of saved methods, however you will find edge cases when it isn't. The very best answer is always to check it out for both and perform some load testing to find out for several which technique is most effective.
It truly is dependent around the procedures. It is extremely easy to have this stuff as calculated posts within the database, ask them to pre-calculated in sights or SPs (or use UDFs), ask them to calculated individually and saved throughout the ETL or perhaps a outlining phase or allow the client get it done.
I'd avoid letting the customer do anything whatsoever unless of course you realize you are able to control the calculation consistently, to ensure that they do not get it wrong (report authors who all perform the work individually is really a occur), and particularly if the calculation rules might change.
SQL Server is really a Relational DataStore. I would recommend one Saved Procedure per calculation, which does exactly the same calculation for any client. Just pass within the ID like a parameter to obtain the client's specific result.
"Performance happens to be an problem because you will see lots of clients"....this is dependent how the tables are stabilized and indexed. Don't go indexing every column unless of course you completely understand what indexes actually are.
Edit ~ Consider your information too. Some could be offloaded to front-end clients.
It may sound out of your question that the best way is always to do all of the information within the SP and return the only(?) lead to your script.
Should you choose choose to perform the processing within the script you need to make certain that you are restricting the information you retrieve to simply that's necessary and make certain that you are making as couple of round outings towards the database as you possibly can.
When the business rules that determine the calculated result are vulnerable to change, don't code such in saved methods. A much better spot to do such will be the controller (C in MVC). The guidelines could be pluggable and simple to alter.
Also from the scalability perspective, copying the application-servers isn't as costly as copying database servers.
That does not imply that saved methods can't be used though. Report generation could be one ideal candidate for computing done using saved methods.
Consider if you want to havea data warehouse. A database enhanced for confirming is extremely differnt from the database enhanced for data entry.