Simplify: move code into database functions

Post by spacemanmatt » Mon, May 04, 2015, 1:34 PM UTC

zamalek wrote: post #68

jconley wrote: post #13

Stored procedures, functions, triggers, etc, are very useful tools. They are used regularly in Microsoft SQL Server and Oracle systems. But, one has to be very careful to use them when warranted. The out-of-the-box tooling for debugging, maintaining, versioning, and testing code that lives in your database is not nearly as robust as what you have for most other development environments. The key here is to use the too…

Nice write-up. > This code has to be maintained, versioned, etc. Specifically to a Microsoft stack this specific pain point is outright negated, in fact, Microsoft's answer (Visual Studio SQLPROJ/Data Dude) exposes migrations for the horrible metahistory hack that they are. SQL projects are stored in source control as though they were C++/C#/what-have-you. Just like the rest of our code, the 347kloc SQL portion is ha…

Post by specialist » Mon, May 04, 2015, 3:38 PM UTC

PaulHoule wrote: post #46

jconley wrote: post #13

Stored procedures, functions, triggers, etc, are very useful tools. They are used regularly in Microsoft SQL Server and Oracle systems. But, one has to be very careful to use them when warranted. The out-of-the-box tooling for debugging, maintaining, versioning, and testing code that lives in your database is not nearly as robust as what you have for most other development environments. The key here is to use the too…

You commonly encounter "legacy" code that uses this design and often people who have cut their teeth on currently fashionable techniques put it down. If you want to construct high performance applications, however, moving code close to data solves a lot of problems, particular those involving the reconciliation of durability and scalability. (i.e. transactions work a lot better if you can get all the coordinated part…

Post by straws » Tue, May 05, 2015, 12:52 PM UTC

y0ghur7_xxx wrote: post #38

BurningFrog wrote: post #8

A serious problem with this approach is scaling. When your code is in Ruby/JS/whatever on separate servers, and your traffic increases, you can easily scale up by adding more stateless servers. Going from 1 to 2 DB servers is much harder, and can easily mean a major rewrite, during which your site may not really work.

> A serious problem with this approach is scaling. Stack overflow runs off one SqlServer. Do you think your app is going beyond that? If yes, maybe this approach will not work for you. If no, you are in the 99,99999% of all other sites/apps that are not as big as SO, FB or google and you can use this simple way to make your site or app.

Post by radicalbyte » Mon, May 11, 2015, 8:35 PM UTC

hobs wrote: post #24

radicalbyte wrote: post #18

Earlier quoted context omitted.

The other side of the coin is that you probably won't need to scale. The performance differences can be staggering: I've replaced Java-based procedures which took more than an hour to execute with Stored Procedures which completed within milliseconds. That's an extreme example - the original code was simply incompetent - but I regularly see significant (100x) performance increase in core functions through the switch.…

I see databases in general get a lot of flack from developers for this reason, when on the other side of the fence I see a lot of really smart people misunderstanding the tool (as your example alludes to.) I have often come to a stored procedure and not returned 100x, but more like 50,000x by simply changing a few lines of code. tl;dr anyone can write slow code in any language.