3 ms·

I understand the utility and have used linq to do this type of thing now and again, but I like to do this kind of labor in SQL. Working with collections is wha

I understand the utility and have used linq to do this type of thing now and again, but I like to do this kind of labor in SQL. Working with collections is what it's suited for, so why try to do this anywhere else?

jordanlev 11y ago
I am a big fan of SQL myself, but sometimes data doesn't live in a database. (In this case, it looks like his sample data is coming from a JSON API call). I suppose you could create a database to store the data and then query it, but that might be adding more overall complexity to your system than would be saved by using SQL instead.
cjefferies 11y ago
I am a proponent of moving as much complex logic away from SQL and into the application layer as possible. I'm not talking about ordering and grouping, but truly complex business logic. I have seen tremendous benefits over the years in following this pattern where applicable. To name a few: [0] Application code is much easier to test. This is by far the biggest factor. I recently rewrote a legacy application that was mostly driven by stored procedures hundreds of lines long, to a service driven application with database interaction limited to lookups and saves. We were able to wrap hundreds of automated tests around that process. The confidence we gained and freedom to improve upon it down the road was unbelievable. [1] High level application code is much easier to read and understand than really complex SQL; and therefore is much easier to support. This is extremely important when you have a large team of developers. Of course, bad code is bad code, whether it's in the application or database, however I find this rule is still generally true. [2] Depending on your database vendor, the application layer is usually much cheaper to scale horizontally. In the enterprise, I've worked mostly with SQL Server and there is no comparison in the licensing cost involved in standing up a new database server vs an application server. Of course, this is not always the case but from my experience this approach has made the most sense.
- falcolas 11y ago
  One downside to doing these calculations in the application, vs the DB: You have to transfer more data from the DB over a finite sized connection than you do if it's in SQL (stored procs or no). For better or worse, the DB is well tuned for these forms of workflows, most general application programming languages are not. This doesn't apply to data which is being manipulated before stored, of course.
  - cjefferies 11y ago
    Yes, absolutely. That is one downside to this approach (and there are more). Even though you are moving the data to the calculations, I would still contend that this approach can be as efficient, if not more efficient, depending on how well your app layer is scaled. However, this is the challenge of designing software, weighing the pros and cons and making the best decision based on your situation.

jordanlev 11y ago

I am a big fan of SQL myself, but sometimes data doesn't live in a database. (In this case, it looks like his sample data is coming from a JSON API call). I suppose you could create a database to store the data and then query it, but that might be adding more overall complexity to your system than would be saved by using SQL instead.

cjefferies 11y ago

I am a proponent of moving as much complex logic away from SQL and into the application layer as possible. I'm not talking about ordering and grouping, but truly complex business logic. I have seen tremendous benefits over the years in following this pattern where applicable. To name a few: [0] Application code is much easier to test. This is by far the biggest factor. I recently rewrote a legacy application that was mostly driven by stored procedures hundreds of lines long, to a service driven application with database interaction limited to lookups and saves. We were able to wrap hundreds of automated tests around that process. The confidence we gained and freedom to improve upon it down the road was unbelievable. [1] High level application code is much easier to read and understand than really complex SQL; and therefore is much easier to support. This is extremely important when you have a large team of developers. Of course, bad code is bad code, whether it's in the application or database, however I find this rule is still generally true. [2] Depending on your database vendor, the application layer is usually much cheaper to scale horizontally. In the enterprise, I've worked mostly with SQL Server and there is no comparison in the licensing cost involved in standing up a new database server vs an application server. Of course, this is not always the case but from my experience this approach has made the most sense.

falcolas 11y ago
One downside to doing these calculations in the application, vs the DB: You have to transfer more data from the DB over a finite sized connection than you do if it's in SQL (stored procs or no). For better or worse, the DB is well tuned for these forms of workflows, most general application programming languages are not. This doesn't apply to data which is being manipulated before stored, of course.
- cjefferies 11y ago
  Yes, absolutely. That is one downside to this approach (and there are more). Even though you are moving the data to the calculations, I would still contend that this approach can be as efficient, if not more efficient, depending on how well your app layer is scaled. However, this is the challenge of designing software, weighing the pros and cons and making the best decision based on your situation.