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.)