Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
denis_the_thief
Aged Yak Warrior
596 Posts |
 Posted - 2015-01-29 : 16:32:41
|
| Our department is wantning to Get Rid of Stored Procedures, or at least remove all Business Logic from them and move them to a middle-tier Application Service.They claim this is to make it independant from the Data Source.I'm familiar with some of the pros and cons of Stored Procedures. But this debate is not wether one or the other is better. More like if having Data Access in the Application middle-tier is so much better than Stored Procedures that it is worth doing this type of an enourmous conversion.1. Has anyone been involved with something like this, was there a net cost or net benefit afterward?2. I'm not even sure what Data Source intependance would look like? I think the only Data Source independance would be, if the Database strucutre is identical, we could move to Oracle or something. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2015-01-29 : 17:42:59
|
The following is my very biased opinion.First off, getting rid of stored procedures is not a good idea. If they do, I would assume they are going to use embedded ad-hoc SQL statements to interact with the database using insert/update/delete statements. Unless they stick to ANSI SQL in doing so, moving database to Oracle or another RDBMS will require a rewrite of the middle-tier code as well.Second, while there is some rationale for moving some of the business logic out of the database the question is how do you define "business logic"?As an example, let us say you have a million orders in your database, and for whatever reason, the middle tier needs to know the average order amount (or the top 100 order amount, or the earliest or latest 100 orders, or number of orders less than a certain amount, or the standard deviation of the order amount - you get the idea). Doing those calculations in the middle-tier would require loading all the orders into memory and then doing the computations. It would be much faster and economical to do those computations in SQL. In fact, it's almost trivial for SQL to do those types of computations in comparison to doing it in the middle tier.Now, if you are using Newton-Raphson method to solve an inverse admittivity problem in electrical impedance tomography, perhaps SQL server is not the place to do that. But, even in those cases I can make a case why it is more efficient and more maintainable to do it in SQL - but that is because I am a hammer and everything looks like a nail to me  |
 |
|
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2015-01-30 : 06:34:42
|
| 1. There may be a cost benefit if your company can negotiate a substantial discount on Microsoft licenses by threatening to go elsewhere. (I once worked for a company where the accounts system used Oracle. At the time the Oracle license was about three times the cost of a SQL Server license. By saying we were going to move the accounts system to SQL Server we got the Oracle license for the same cost as a SQL Server license.)2. I doubt if one can ever get complete data source independence but software companies like keeping differences between the SQL Server/Oracle versions of their software to a minimum. |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2015-02-11 : 11:01:01
|
Thank you both.quote:
Originally posted by James K
...Second, while there is some rationale for moving some of the business logic out of the database the question is how do you define "business logic"?...
Ya, that's a good point. |
|
|
|
|
|
|
|
|
Getting Rid of Stored Procedures