18

I may not be able to give the right title to the question. But here it is,

We are developing financial portal for wealth management. We are expecting over 10000 clients to use the application. The portal calculates various performance analytics based on the the technical analysis of the stock market.

We developed lot of the functionality through Stored procedures, user defined functions, triggers etc. through Database. We thought we can gain huge performance boost doing stuff directly in database than through C# code. And we actually did get a huge performance boost.

When I tried to brag about the achievement to our CTO, he counter questioned my decision of having functionality implemented in database rather than code. According to him such applications suffer scalability problems. In his words "These days things are kept in memory/cache. Clustered data is hard to manage over time. Facebook, Google have nothing in database. It is the era of thin servers and thick clients. DB is used only to store plain data and functionality should be completely decoupled from the database."

Can you guys please give me some suggestions as to whether what he says is right. How to go about architect such an application?

9
  • 3
    "and we actually did get a huge performance boost" compared to what? When you never implemented the same functionality on a client, how do you know?
    – Doc Brown
    Sep 28, 2012 at 10:44
  • 3
    I think it will be the usual - it depends on the project, the data implementation and skill of the team. Sep 28, 2012 at 10:45
  • 1
    You should ask your CTO what makes him thinks databases aren't using his favored techniques and why stored procedures don't qualify as "code."
    – Blrfl
    Sep 28, 2012 at 11:31
  • 3
    Facebook and Google have problems at an entirely different scale to most applications - there may be an issue with amount of data you have to deal with in terms of data from the market but contemporary SQL databases are built to cope with staggering amounts of data.
    – Murph
    Sep 28, 2012 at 12:34
  • 1
    I'd probably think the same way as your CTO unless you could prove performance of his solution was insufficient and there were not other ways to manage it. Stored procedures, especially when their numbers get large, cause a tremendous barrier to move to other DBs if required...can't predict the future.
    – Rig
    Sep 28, 2012 at 12:50

5 Answers 5

24

In short, I would agree with your CTO. You've probably gained some performance at the expense of scalability (if those terms are confusing, I'll clarify below). My two biggest worries would be maintainability and lack of options to scale horizontally (assuming you are going to need that).

Proximity to data: Let's take a step back. There are some good reasons for pushing code into a DB. I would argue that the biggest one would be proximity to the data - for example, if you are expecting a calculation to return a handful of values, but these are aggregations of millions of records, sending the millions of records (on-demand) over the network to be aggregated elsewhere is hugely wasteful, and could kill easily your system. Having said this, you could achieve this proximity of data in other ways, essentially using caches or analysis DBs where some of the aggregation is done upfront.

Performance of code in the DB: Secondary performance effects, such as "caching of execution plans" are more difficult to argue. Sometimes, cached execution plans can be a very negative thing, if the wrong execution plan was cached. Depending on your RDBMS, you may get the most out of these, but you won't get much over parametrised SQL, in most cases (those plans typically get cached, too). I would also argue that most compiled or JIT'ed languages typically perform better than their SQL equivalents (such as T-SQL or PL/SQL) for basic operations and non-relational programming (string manipulation, loops, etc), so you wouldn't be losing anything there, if you used something like Java or C# to do the number crunching. Fine-grained optimisation is also pretty difficult - on the DB, you're often stuck with a generic B-tree (index) as your only data structure. To be fair, a full analysis, including things like having longer-running transactions, lock escalation, etc, could fill books.

Maintainability: SQL is a wonderful language for what it was designed to do. I'm not sure it's a great fit for application logic. Most of the tooling and practices that make our lives bearable (TDD, refactoring, etc) are difficult to apply to database programming.

Performance versus scalability: To clarify these terms, I mean this: performance is how quick you'd expect a single request to go through your system (and back to the user), for the moment assuming low load. This will often be limited by things like the number of physical layers it goes through, how well optimised those layers are, etc. Scalability is how performance changes with increasing number of users / load. You may have medium / low performance (say, 5 seconds+ for a request), but awesome scalability (able to support millions of users). In your case, you will probably experience good performance, but your scalability will be bounded by how big a server your can physically build. At some point, you will hit that limit, and be forced to turn to things like sharding, which may not be feasible depending on the nature of the application.

Premature Optimisation: Ultimately, I think you've made the mistake of optimising prematurely. As others have pointed out, you don't really have measurements showing how the other approaches would work. Well, we can't always build full-scale prototypes to prove or disprove a theory... But in general, I'd always be hesitant to chose an approach which trades maintainability (probably the most important quality of an application) for performance.

EDIT: On a positive note, vertical scaling can stretch quite far in some cases. As far as I know, SO ran on a single server for quite some time. I'm not sure how it matches up to your 10 000 users (I guess it would depend on the nature of what they are doing in your system), but it gives you an idea of what can be done (actually, there are far more impressive examples, this just happens to be a popular one people can easily understand).

EDIT 2: To clarify and comment on a few things raised elsewhere:

  • Re: Atomic consistency - ACID consistency may well be a requirement of the system. The above doesn't really argue against that, and you should realise that ACID consistency doesn't require you to run all your business logic inside the DB. By moving code which does not need to be there into the DB, you're constraining it to run in the physical environment of the rest of the DB - it's competing for the same hardware resources as the actual data management portion of your DB. As for scaling only the code out to other DB servers (but not the actual data) - sure, this may be possible, but what exactly are you gaining here, apart from additional licensing costs in most cases? Keep things that don't need to be on the DB, off the DB.
  • Re: SQL / C# performance - since this seems to be a topic of interest, let's add a bit to the discussion. You can certainly run native / Java / C# code inside DBs, but as far as I know, that's not what was being discussed here - we're comparing implementing typical application code in something like T-SQL versus something like C#. There a number of problems which have been difficult to solve with relational code in the past - e.g. consider the "maximum concurrent logins" problem, where you have records indicating a login or logout, and the time, and you need to work out what the maximum number of users logged in at any one time was. The simplest possible solution is to iterate through the records and keep incrementing / decrementing a counter as you encounter logins / logouts, and keeping track of the maximum of this value. It turns out that unless your DB supports a certain sliding window aggregation (which SQL 2008 didn't, 2012 may, I don't know), the best you can do is a CURSOR (the purely relational solutions are all on different orders of complexity, and attempting to solve it using a while loop results in worse performance). In this case, yes, the C# solution is actually faster than what you can achieve in T-SQL, period. That may seem far-fetched, but this problem can easily manifest itself in financial systems, if you are working with rows representing relative changes, and need to calculate windowed aggregations on those. Stored proc invocations also tend to be more expensive - invoke a trivial SP a million times and see how that compares to calling a C# function. I hinted at a few other examples above - I haven't yet encountered anyone implement a proper hash table in T-SQL (one which actually gives some benefits), while it is pretty easy to do in C#. Again, there are things that DBs are awesome at, and things that they're not so awesome at. Just like I wouldn't want to be doing JOINs, SUMs and GROUP BYs in C#, I don't want to be writing anything particularly CPU intensive in T-SQL.
2
  • One of the reasons I tend to push functionality to the database is that is much less buggy than application level code. SQL is declarative and doesn't suffer from many of the problems that imperative languages do. Mar 23, 2016 at 15:12
  • Regarding maintainability, using SQL Server Data Tools maintainability is a cinch. In fact for any nontrivial database (one with more than 5 tables) I would consider it a requirement.
    – Jon49
    Aug 19, 2017 at 21:34
5

Scalability has nothing to do with where the data sits or how the computation happens. Scalability is all about how you manage global state and data inter-dependence. If your architecture is convoluted with all sorts of data inter-dependencies then it doesn't matter where you put the code for transforming that data. The inter-dependencies are going to force your hand and reduce any potential for scaling things. If on the other hand your data is loosely coupled and there is very little to no global state then once again it doesn't matter where the computation happens. Scaling things is going to be much easier.

I'm not sure where your CTO is getting his information about scalability issues but from what you have said it doesn't sound like he has any real reasons for questioning the current architectural decision other than software fashion trends. Basing architectural decisions on such trends is usually a bad idea.

1
  • 1
    +1 for Scalability is all about how you manage global state and data inter-dependence. Oct 3, 2012 at 18:42
2

And we actually did get a huge performance boost.

I think you need to set a performance benchmark and start building your prototype first. Keeping all logic in DB is an old school (imho, i don't have anything against it) of dealing with client-server architecture. Although, it has its advantages, there are number of drawback that needs to be considered.

Usual approach for this type of salable applications is done through SOA. Because in long run, this is the easiest way to add new client applications to your project.

You have also mentioned triggers. The trigger usage might turn to be a big gotchas later in the application's support life-cycle, i would be double careful with it, and even try to skip its usage.

2

Your CTO is 100% Wrong.

Your Financial numbers MUST add up at all times. That means you need ACID and relational DB are the best place to insure that. The Performance gains of NoSql DB are usually at the Expense of ACID and that is OK for Google and Facebook BUT NOT for a system containing financials.

To say that C# performs better than SQL code is also idiocy…

8
  • To say that C# performs better than SQL code is also idiocy… - But you're not denying that C# code is more scalable, correct?
    – Jim G.
    Sep 30, 2012 at 14:21
  • No its not more scalable, Because that not where the bottle neck is, I can scale the Sql code (not the data) horizontally just as easily as i can horizontally scale the C# code.
    – Morons
    Sep 30, 2012 at 14:24
  • @JimG. Just to clarify, "I can scale the Sql code (not the data) horizontally just as easily as i can horizontally scale the C# code" if it was designed to do so... Same as C# it must be designed to scale. You can't just say C# scales better, it a matter of planning not the language.
    – Morons
    Sep 30, 2012 at 14:51
  • @JimG.: Software that doesn't scale can be written in any language, including C#. Any database worth its salt can have stored procedures written in languages other than their native SQL-ish implementation, and people who go off the deep end with NoSQL in situations that require ACID usually end up re-inventing most of the wheels that have been nicely implemented by the DBMS.
    – Blrfl
    Sep 30, 2012 at 14:52
  • @Morons: I think we agree. I was in fact conflating the data with "SQL". It's much more expensive to scale the database.
    – Jim G.
    Sep 30, 2012 at 15:03
2

Anytime anyone mentions scalability and Google/Facebook/Twitter/etc, it's a red herring. Unless you're providing essentially the same service, what works for them may not be appropriate for you. In general, if you can scale from a single machine to an eight-machine cluster, you've probably covered all your bases. Unless you have a hard business requirement to serve 20M page views a day, don't worry about hyper-scaling. Do what makes sense for your application's real requirements, and worry about scaling up when it becomes obvious you need to. And don't forget, most database servers can be clustered too, so just because it's all in one database doesn't mean it's on one server.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.