Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Wrong ways to use the databases, when the pendulum swung too far

luu.io

Post reply on HN
Print view View original

31–40 of 110 posts

Re: Wrong ways to use the databases, when the pendulum swung too far

#31

Post by zabzonk »

freetime2 wrote: post #27

Anecdotally, the worst codebase I ever worked on made heavy use of stored procedures. Over the years people couldn’t be bothered or were afraid to update them - which really was the root of the problem. This led to all kinds of crazy patterns in the application code like calling things in a loop where a bulk operation was needed. Or stringing together several stored procedure calls to get the desired outcome, when re…

> you probably want to have the bulk of your business logic written in C#

Perhaps, if the C# applications are the only ones accessing the database. But suppose multiple applications written in different languages (and for different purposes) need the database access? You can do this via stored procs or (better in my experience) by adding an intermediate server process which the applications use, via a publicly documented API, and which then talks to the the database which may provide stored proc access to the intermediate server.

Equally anecdotally:

An example is a server me and a couple of colleagues worked on at a big investment bank. The underlying databases were mostly proprietary from suppliers or written by other teams in the bank. And versioned (badly, eek!). We wrote the SQL queries/procs to access the data that the VB (now C# I guess) would not have been capable of (without enormous waste of effort), and then provided COM interfaces to our server that ran our queries. Mercifully, we didn't have to provide updates, but the same idea would have applied. This gives you more or less complete isolation between app and database.

Anyway, SPs can certainly fit into such an architecture, but might not be first (or second) choice.

Re: Wrong ways to use the databases, when the pendulum swung too far

#33

Post by luuio »

TZubiri wrote: post #28

"They wanted everything to be written into simple primitive Key-Value stores for their new design." I feel this is a very political phenomenon that is very poignant in democracy, both Argentina and the US have elected executives that promise to chainsaw most of the government bureocracy for cost and streamlining reasons. There's a chapter of south park where the kids buy a roller coaster park and make it their own wa…

> there is always more wisdom in what we throw away than what we innovate

i have found this to be true throughout my career. not that things cannot improve, they can always improve. but assuming the original work wasn't grossly incompetent, anything "new" tend to make the same [undocumented] mistakes

Re: Wrong ways to use the databases, when the pendulum swung too far

#34

Post by branko_d »

luuio wrote: post #26
branko_d wrote: post #24

I never quite understood these kinds of arguments, event though they seem to be repeated a lot: > 1. Many critical APIs call would spend most of its CPU time inside the finite database servers. New APIs, or updating existing APIs meant you were hogging the DB/CPU time from every one else. It's not that easy for a stored procedure to spend CPU on anything other than data processing. If you are going to do that data pr…

1. Beyond just querying, the stored proc spent a lot of time processing data. As in, looping through cursors, making business logic decisions, calculating things, etc. 2. Having the business logic (not just loading the data) inside the stored procs meant that a change in business logic that would normally only need to update application code, now invalidates the stored procedure's cached execution plan.

> looping through cursors, making business logic decisions, calculating things, etc.

Interesting. Can you share more details about the "non-data" processing that was done? Were they doing heavy mathematical calculations and such?

> change in business logic that would normally only need to update application code, now invalidates the stored procedure's cached execution plan

As for plan cache invalidation - the most extreme case I saw was on the order of 5-10s. Basically, it depends on the size of that one stored procedure - not on all other stored procedures that may call it or be called by it. What was the actual time that they got?

Re: Wrong ways to use the databases, when the pendulum swung too far

#35

Post by jbverschoor »

freetime2 wrote: post #27

Anecdotally, the worst codebase I ever worked on made heavy use of stored procedures. Over the years people couldn’t be bothered or were afraid to update them - which really was the root of the problem. This led to all kinds of crazy patterns in the application code like calling things in a loop where a bulk operation was needed. Or stringing together several stored procedure calls to get the desired outcome, when re…

I’d rewrite the complete team instead.

Re: Wrong ways to use the databases, when the pendulum swung too far

#36

Post by TZubiri »

luuio wrote: post #33
TZubiri wrote: post #28

"They wanted everything to be written into simple primitive Key-Value stores for their new design." I feel this is a very political phenomenon that is very poignant in democracy, both Argentina and the US have elected executives that promise to chainsaw most of the government bureocracy for cost and streamlining reasons. There's a chapter of south park where the kids buy a roller coaster park and make it their own wa…

> there is always more wisdom in what we throw away than what we innovate i have found this to be true throughout my career. not that things cannot improve, they can always improve. but assuming the original work wasn't grossly incompetent, anything "new" tend to make the same [undocumented] mistakes

An interesting mathematical caveat is that regardless of whether the majority of the work thrown out is correct, throwing the baby with the bathwater may still be optimal, as it might be the only way to affect change. It's not like decreasing the rate of change would decrease the amount of false positives discarded.

Re: Wrong ways to use the databases, when the pendulum swung too far

#37

Post by t0mas88 »

marc_abonce wrote: post #11

> The company is still standing and seems to be doing well financially, so I guess things turned out well enough, or maybe some of the technical decisions started trending more reasonable. Perhaps I've been lucky or I haven't been observant enough, but I've never seen a company suffer financially because of inefficient code. Don't get me wrong, I still value good code for its own sake, but in my experience there is n…

Only if the bad code affects customer experience significantly. That only happens to a big enough amount of you really let things grow out of contol. At some point you'll get bugs that take forever to solve and angry customers as a result.

Re: Wrong ways to use the databases, when the pendulum swung too far

#38

Post by hnaccount_rng »

wongarsu wrote: post #14
marc_abonce wrote: post #11

> The company is still standing and seems to be doing well financially, so I guess things turned out well enough, or maybe some of the technical decisions started trending more reasonable. Perhaps I've been lucky or I haven't been observant enough, but I've never seen a company suffer financially because of inefficient code. Don't get me wrong, I still value good code for its own sake, but in my experience there is n…

I bet Atlassian could make even more money with Jira if it wasn't this slow. They are not struggling as it is, but it's bad enough that it is costing them customers But generally I would agree

Do you have any source for the "they are loosing customers"? I always thought, that they consciously decided to have that shit of an interface because no one relevant to them (i.e. purchasing departments) cares?

Re: Wrong ways to use the databases, when the pendulum swung too far

#39

Post by awesome_dude »

zabzonk wrote: post #31
freetime2 wrote: post #27

Anecdotally, the worst codebase I ever worked on made heavy use of stored procedures. Over the years people couldn’t be bothered or were afraid to update them - which really was the root of the problem. This led to all kinds of crazy patterns in the application code like calling things in a loop where a bulk operation was needed. Or stringing together several stored procedure calls to get the desired outcome, when re…

> you probably want to have the bulk of your business logic written in C# Perhaps, if the C# applications are the only ones accessing the database. But suppose multiple applications written in different languages (and for different purposes) need the database access? You can do this via stored procs or (better in my experience) by adding an intermediate server process which the applications use, via a publicly docume…

> Perhaps, if the C# applications are the only ones accessing the database. But suppose multiple applications written in different languages (and for different purposes) need the database access?

Without wishing this to sound like a personal attack, YUCK

A database that's accessed by multiple applications, regardless of the number of languages, is a really bad smell.

If I have a user db, and hundreds of other applications want to make use of the data in it, they come through MY application. I own the db, I decide what's in it, how the data is shaped, and when it changes.

Multiple applications accessing a single database is asking for a bureaucratic nightmare - "App A wants to change the first name field to UTF-8" - EVERY other user, of that database needs to be informed (best of luck figuring out what other apps are, and who owns them). If you are tempted to say "Those other apps should support UTF-8 as well" then that's the sound of someone that's never had legacy code in the environment.

Re: Wrong ways to use the databases, when the pendulum swung too far

#40

Post by ako »

luuio wrote: post #26
branko_d wrote: post #24

I never quite understood these kinds of arguments, event though they seem to be repeated a lot: > 1. Many critical APIs call would spend most of its CPU time inside the finite database servers. New APIs, or updating existing APIs meant you were hogging the DB/CPU time from every one else. It's not that easy for a stored procedure to spend CPU on anything other than data processing. If you are going to do that data pr…

1. Beyond just querying, the stored proc spent a lot of time processing data. As in, looping through cursors, making business logic decisions, calculating things, etc. 2. Having the business logic (not just loading the data) inside the stored procs meant that a change in business logic that would normally only need to update application code, now invalidates the stored procedure's cached execution plan.

If you’re going to process a lot of data, doing that in the database is usually faster, as you avoid moving all the data into a separate server process and then moving it back again. For many queries the round trip to the database server from the application server takes longer that the query itself.
Post reply on HN