April 22, 2015 at 6:26 pm
teodorom (4/22/2015)
There is another point worth considering: this blog has an international audience.My English is only technical, and I have to say that I'm unable to solve crosswords, I have a lot of problems with the Woody Allen movies and it's very hard to me to understand the Frank Zappa's songs.
So, to be able to understand that paper, I had to read it two times. And still then I'm sure I have not captured all the subtleties.
Apart that I am an (exclusively) strong supporter of the stored procedures.
That is, given any business problem, I've never seen a situation where it was worthwhile not to use them.
Don't feel bad. I have heard that English is one of the hardest languages to learn if you were not born with it. This was told to me by persons who learned English and other languages who related their experiences to me.
As a native speaker of American English I have problems with Woody Allen movies.
Courage
ATBCharles Kincaid
April 22, 2015 at 7:13 pm
teodorom (4/22/2015)
Apart that I am an (exclusively) strong supporter of the stored procedures.That is, given any business problem, I've never seen a situation where it was worthwhile not to use them.
I too am an advocate of stored procedures, and prefer a database where the only access applications have is through stored procedures (or whatever the equivalent is called in the RDBMS used).
But there are things for which stored procedures shouldn't be used.
At least there are certainly things that I wouldn't do in a stored procedure, and those things sometimes turn up as business problems. In those cases I will use stored procedures for access to and manipulation of data but will not use SQL to solve the problems for which SQL is a thoroughly inefficient language (non-trivial constraint problems; various types of simulation; computing matrix inverses or eigenvectors or determinants; doing tensor manipulation; numerical integration; numerical differentiation; numerical solutions of differential equations; doing best fit of data to some set of models). So there are certainly some business problems where it is best not to use stored procedures for anything but data access.
I hope this post won't provoke a load of responses telling me that the problems I listed aren't business problems because they aren't about customer relations or accountancy from people who think that business has no use for the things I have mentioned. 😉
Tom
April 23, 2015 at 1:31 am
TomThomson (4/22/2015)
But there are things for which stored procedures shouldn't be used.
I'm a Mathematician and I understand perfectly what you say.
Even though I haven't yet made any detailed "experiment" on those topics.
Mathcad is a good candidate for doing those things: it can easily interfaced with OLE Automation, and so id should be easy to write CLR code that incorporates such "difficult" tasks.
I guess that re-writing Levenberg–Marquardt in T-SQL would be a desperate task.
'R' seems another interesting environment for doing statistical analysis.
A have no such itegration experience for Mathemathica and Matlab (I hate Matlab !).
Performances ? I don't know. I have only some limited experience in doing reporting.
April 23, 2015 at 1:33 am
The problems you listed aren't business problems because they aren't about customer relations or accountancy. Business has no use for the things you have mentioned.
Just kidding (obviously I hope!) business problems are any encountered in the path of capital generation.
Seriously though I don't understand why everyone says the only way is the way they like things to be. It appears to me to bely a lack of broad experience. In our team we do not have access to a DBA let alone a database team, the way our structure works it is as easy to regenerate and roll out the middle layer or the UI as it is to rework a proc. Simple access via ORM is a productivity no-brainer, a battle won here many years back despite many initial misgivings. POCO wrapping and caching honestly make performance super slick. When it's better to use a proc we use one. I understand the arguments for mandated proc usage and they may apply to some companies but that is just not right for every case.
Despite thinking in this case the author is quite wrong, so far off as to be not even worth refuting, as he has clearly never seen ORM based development done right, I enjoy reading strong language in people's personal posts and am very happy to see lots more. I never use such in any technical work myself admittedly - it just makes life easier, however swearing at computers when they don't work how you want is definitely part of the steam valve I have always employed in my career - of course not in company that might be offended etc. It can just be a spice that enriches and flavours the perspective you are projecting.
April 24, 2015 at 10:00 am
There is a senior who wants to replace all the inline sql statements with sprocs. Well fine, the application will stop looping through 1000 inline statements and now will loop through 1000 sproc calls. This so called "senior" seems to have missed the boat..
April 24, 2015 at 11:17 am
Steve JP (4/24/2015)
... the application will stop looping through 1000 inline statements ...
The fact that you need to do so makes me think that someone, at least during the database design, has missed something.
April 24, 2015 at 11:45 am
Steve JP (4/24/2015)
There is a senior who wants to replace all the inline sql statements with sprocs. Well fine, the application will stop looping through 1000 inline statements and now will loop through 1000 sproc calls. This so called "senior" seems to have missed the boat..
Replacing the inline sql with a stored proceure is the first step in getting rid of the looping. It's the looping that kills you.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 24, 2015 at 12:10 pm
bdenning (4/22/2015)
I in no way stated "don't read this article" but instead it was a soft plea to SQL Server Central to not reference such articles. Doing so increases the "hits" on the site, and indicates acceptance of the language. By posting my comment (my first on this site), I've been notified of updates and have enjoyed the discussion. It is obvious that there are very intelligent people out there from whom I can learn, even new words and phrases ("ad hominem" -- thanks Jeff, I had to look that one up! :-)). I was mostly interested in how Steve as editor might respond (or not respond). This is my 2nd post (at the risk of being flamed further) -- others have expressed their opinions and others have been "off subject". I apologize to those who've thought I've been "passive aggressive" and "more offensive" as I never wanted to offend anyone, but wanted to express my personal opinion to SQL Server Central. If there is a better venue for doing so, please let me know (Steve?) -- otherwise, end of discussion.
Welcome to SQL Server Central! This is a fantastic community of ridiculously helpful, intelligent, spirited, and experienced people. I've found this to be a very welcoming place, though you occasionally need to put on your asbestos suit and shrug off a comment or two.
I've twice been taken to the woodshed by two separate, very talented frequent posters here; afterwards, both of them apologized to me for over-reacting. You won't find that too many places online!
I'm still amazed that contributors here will take your crappy code, point out the errors in it, fix the errors, run the fixed code to return the desired data, and then refactor that slightly-less-crappy code to give you more performant code. For free. For fun.
Stick around -- you will learn scads!
Rich
April 24, 2015 at 12:46 pm
teodorom (4/23/2015)
I'm a Mathematician and I understand perfectly what you say.
I helped a friend debug and test a program that took a free form trinomial and factor it into it's two component binomials. It was written in COBOL. Not even COBOL 90. No libraries at all. All the string handling was handled internally. We parsed the input line character by character.
Just because you can do something with <technology X> does not mean that it is the best choice for your circumstance.
I use stored procedures a lot. Putting procedural logic in a database layer may be the best choice wherein that logic needs to be available to multiple classes on consuming logic. For example my desktop and web application both need identical logic. Under the idea of "do it once in one place and use it all over" the database is the lowest common point.
Stored procedures for everything all the time is a much faulty thinking as stored procedures are just bad and should be avoided altogether.
In any decision what is the worst choice? Dogma.
ATBCharles Kincaid
April 25, 2015 at 8:49 am
Steve Jones - SSC Editor (4/20/2015)
RonKyle (4/20/2015)
...But the number crunching belongs somewhere else. I have seen first-hand the improvements in performance that comes from moving business logic into the middle tier, and in my view it is irresponsible to advocate this view. This issue was settled a long time ago.
I think this really depends on what's being crunched and how. There are some logical items in SQL Server that are very efficient and certainly you may want some calculations to be consistent no matter how the data is calculated.
Also, plenty of systems don't have middle tiers. We could argue about whether that's a failing of the design or architecture, but I'd say that many applications don't need this complexity.
Here is a good example I think where the number crunching cannot exist in the database.
I worked in the video game industry, specifically in Massively Multiplayer Online Games. This is a type of video game genre where thousands of players play a single game simultaneously.
In the games I've worked on, a lot of our games were driven by RDBMS such as Oracle. Each game had 2 databases, one for the game resources and the other for the end users (players).
When you think about this at a high-level, every player in a virtual world has to have their exact position logged. Every step you make has to be relogged. This is so the game knows where you are and so the game can communicate your position to other players in your general vicinity.
The amount of traffic (transactions) is insane, even for a small game. Players are moving around, fighting objects, receiving objects, dropping objects, deleting objects and the works.
Now think about how you would handle the game logic and mechanics of the video game here. Think about everything I said before in terms of just the game object transactions and now also having to handle the game mechanics of the actual game.
Think about a game where you have character progression and character skills. Think about how a player may be a deadly Wizard with a spell that casts down fire from the skies and burns everything in his path. Not only are you having to keep track of the objects states in the game world, you also have to figure out if the player has that spell, how much damage it has at the given moment, what objects that spell impacts in the game world and any final resolves.
Again, pretty insane amount of checks and balances that could be made to a database that could also bring it tumbling down to it's knees.
So, to my point. A lot of what I said is actually handled on the client end. That is, the game logic (business logic) is handled by the client and not the database. Unfortunately, in this scenario, the database is already doing so much. The idea that the database is figuring out how much damage you are doing to the 10 players around you and also keeping track of the object states in the entire game world is sometimes too much for the backend to bear. The same argument is the same reason why stored procedures are ignored just on simple checks and balanced to pass onto the client to drive it home in number crunching. So, why not pass some of that onto the client to handle and just let the database manage the data.
See attached screenshot for reference of a online game where hundreds of players are in combat in one game instance online. Imagine what that database has to handle in an application like that.
(Note: Game is EVE Online. Each red flag and blue flag is a player in 3D space. It's a view with the camera scaled as far back as possible to get everyone in the shot.)
April 25, 2015 at 9:16 am
xsevensinzx (4/25/2015)
Here is a good example I think where the number crunching cannot exist in the database.
I worked in the video game industry, specifically in Massively Multiplayer Online Games. This is a type of video game genre where thousands of players play a single game simultaneously.
Absolutely. This is certainly a place you'd struggle with anything, even recording actions. I worked in the financial industry at one point and we couldn't update pricing every minute and have our application work.
However, that doesn't mean no business logic works. Plenty of situations can do better, not sending sets of data to a client to process rather than making a decision in the database.
As with most things, it depends.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 25, 2015 at 5:53 pm
Steve Jones - SSC Editor (4/25/2015)
xsevensinzx (4/25/2015)
Here is a good example I think where the number crunching cannot exist in the database.
I worked in the video game industry, specifically in Massively Multiplayer Online Games. This is a type of video game genre where thousands of players play a single game simultaneously.
Absolutely. This is certainly a place you'd struggle with anything, even recording actions. I worked in the financial industry at one point and we couldn't update pricing every minute and have our application work.
However, that doesn't mean no business logic works. Plenty of situations can do better, not sending sets of data to a client to process rather than making a decision in the database.
As with most things, it depends.
Sure, but it's not the point of saying that business logic can and cannot exist in the database. It's about what makes sense in the use case. At the end of the day, front-end and back-end applications can process logic. But, in almost all situations, only the back-end can store physical data. This is why that business logic is not solely owned by the back-end such as the database.
That does not mean that the back-end is not the best place for business logic, it just means that you can't make an argument that database engines specifically, is the sole owner of the logic or the main role by default because it's not. It would be a secondary role at best where the primary role is always going to be about the data, not the logic.
I would say in most cases though, it's going to live in the back-end, especially in the database. But you have to remember, if someone came to me and said that the database can store no logic, then it's not the end of the world. If someone came to me that I could also store no persistent data on the back-end, then that may be world destroying.
April 26, 2015 at 2:46 pm
xsevensinzx (4/25/2015)
...if someone came to me and said that the database can store no logic, then it's not the end of the world.
BWAAAHAAAA!!! If someone gave me such ridiculous requirements (and please don't take that personally... I know you'd not be the driver of such ignorant requirements), then it would be the end of the job for me because I'd find a place where people actually understand the correct interplay between front-ends and databases as well as the correct design and use of databases. 😛
Steve said the only correct thing about where business logic needs to be applied at... "It Depends".
--Jeff Moden
April 26, 2015 at 4:02 pm
Jeff Moden (4/26/2015)
xsevensinzx (4/25/2015)
...if someone came to me and said that the database can store no logic, then it's not the end of the world.
BWAAAHAAAA!!! If someone gave me such ridiculous requirements (and please don't take that personally... I know you'd not be the driver of such ignorant requirements), then it would be the end of the job for me because I'd find a place where people actually understand the correct interplay between front-ends and databases as well as the correct design and use of databases. 😛
Steve said the only correct thing about where business logic needs to be applied at... "It Depends".
For sure, but it depends applies to everything you do in this world.
April 26, 2015 at 4:40 pm
xsevensinzx (4/26/2015)
Jeff Moden (4/26/2015)
xsevensinzx (4/25/2015)
...if someone came to me and said that the database can store no logic, then it's not the end of the world.
BWAAAHAAAA!!! If someone gave me such ridiculous requirements (and please don't take that personally... I know you'd not be the driver of such ignorant requirements), then it would be the end of the job for me because I'd find a place where people actually understand the correct interplay between front-ends and databases as well as the correct design and use of databases. 😛
Steve said the only correct thing about where business logic needs to be applied at... "It Depends".
For sure, but it depends applies to everything you do in this world.
Heh... even that "depends". For example, if I jab myself in the eye with a large sharp stick, there's no "It Depends" about it... it's going to hurt and the eye isn't going to work so well after that. 😛
--Jeff Moden
Viewing 15 posts - 61 through 75 (of 77 total)
You must be logged in to reply to this topic. Login to reply