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

41–50 of 110 posts

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

#41

Post by biorach »

TZubiri wrote: post #29
Quarrelsome wrote: post #10

Religion and engineering do not make good bedfellows. I got into a pointless argument with someone on LinkedIn who was trashing on ORMs, strawmanning them by stating they would all pull the entire data into memory to just perform a count (some ORMs are much more sophisticated than this). It was some sort of weird religious argument because the chap thought every single piece of data processing should be written in a…

I'm not hugely experimented, and SQL has always been enough for me, perhaps due to my simple requirements. But so far I hold the opinion that ORM is always a mistake. I find a lot of programmers don't know how to write an array of floats to disk, if you forced me to choose between an ORM or No DB at all, I would choose no DB all day. ORM feels like an abstraction on top of an abstraction. I don't trust that those who…

> I find a lot of programmers don't know how to write an array of floats to disk

what does that have to do with it?

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

#42

Post by bytefish »

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 am not going to dismiss your experience here. Stored Procedures can turn into wild monsters. Pair it with Triggers and you are in for chasing a noodle.

But it's also a reality, that relational databases often become the main integration point in companies. In those environments it’s hard (next to impossible) and dangerous to use something like ORMs.

Often enough I don't "own the tables" and I don't "own the columns" for lack of a better word. The DBA only gives me SELECT and EXECUTE permissions on the database. How am I going to work here without Stored Procedures?

And while this sounds weird, these limited permissions are to protect me from accidentally writing to wrong columns. Wrong columns that could impact systems, I didn't even know about. Is it possible to write to the same columns with a Stored Procedure? Of course! But it's not as dangerous as giving an application fat UPDATE permissions.

By using SQL Views I can build a data model upon these tables for reading the data, and build a more consumable data model. And you mentioned C#: I can use EF Core to query these Views with LINQ. Stored Procedures are used to store data to multiple tables in a "legacy database" within a transaction.

This could also be done with EF Core, but I need to explicitly log all changes to the data. How should this be done without a Stored Procedure or Triggers? CDC doesn't help a lot here.

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

#43

Post by samwillis »

simonw wrote: post #30
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'm convinced that the solution to stored procedures that people are afraid to update is automated tests . Write unit tests for them just like you would any other complex logic - the tests can evolve over time into a detailed spec for how they should work, and give you the ability to refactor or improve them with confidence later on. For some reason stored procedures and automated testing seem to often not go togethe…

I think a lot of the problems come from the fact testing stored procedures ventures into e2e testing land. You have to stand up infra in order to test them. There's not really been a simple way to unit test stored procedures as part of your application codes testing framework.

(Aside: I think this is something PGlite helps with if your in Postgres land)

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

#44

Post by matkoniecz »

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…

Note that Argentine definitely needed some change in course given their situation.

Not checked their situation much, but their current ideas were clearly not sustainable given repeated defaults. And general bad situation of population.

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

#45

Post by bazoom42 »

Stored procedures are actually great when used for bulk set-oriented data operations. Much simpler and more efficient than passing large datasets back and forth between servers.

The problems arise when processing data row-by-row using cursors or simular procedural approach, or with too much buisness logic in procedual form.

I guess the moral of the story is: use the right tool for the job instead of deciding on the “one true pattern”.

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

#46

Post by withinboredom »

Quarrelsome wrote: post #10

Religion and engineering do not make good bedfellows. I got into a pointless argument with someone on LinkedIn who was trashing on ORMs, strawmanning them by stating they would all pull the entire data into memory to just perform a count (some ORMs are much more sophisticated than this). It was some sort of weird religious argument because the chap thought every single piece of data processing should be written in a…

Haha. I’ve actually had an ORM attempt to load the whole database just to do a count. That was a fun bug to dig into (nobody noticed until we had a big enough customer) and an easy fix. Literally, all I had to do was mark some fields as lazy loaded.

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

#47

Post by jajko »

bazoom42 wrote: post #45

Stored procedures are actually great when used for bulk set-oriented data operations. Much simpler and more efficient than passing large datasets back and forth between servers. The problems arise when processing data row-by-row using cursors or simular procedural approach, or with too much buisness logic in procedual form. I guess the moral of the story is: use the right tool for the job instead of deciding on the “…

The problem I've seen, very few people from Java / C# / other languages actually know plsql or ever bothered to learn it. Its not that hard, but I guess not a cool enough tech to have on resume? When it could massively help with speed of certain use cases, in ways no external code could ever dream of achieving.

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

#48

Post by henrikschroder »

awesome_dude wrote: post #39
zabzonk wrote: post #31

Earlier quoted context omitted.

> 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 applica…

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

Except that really was the original model back in the 90's. All "good" databases had an extensive roles and permissions system, and a point of pride was the number of ODBC connectors for different languages and environments.

You were supposed to have The Database, looked after and controlled by the hallowed DBAs, who had their own hardware budget, their own organization, and who controlled access to The Database, giving trusted applications access to it, but only after they had vetted the schema and queries that those dirty developers wanted to run against it. Trusted users could get SELECT access, but only to the tables they needed to run their custom reports and queries.

It was a whole ass thing that's fallen completely to the wayside as database software went from underpinning Larry's latest mega-yacht, to free as in beer, and we learned how to clone and shard the data instead.

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

#49

Post by t43562 »

withinboredom wrote: post #46
Quarrelsome wrote: post #10

Religion and engineering do not make good bedfellows. I got into a pointless argument with someone on LinkedIn who was trashing on ORMs, strawmanning them by stating they would all pull the entire data into memory to just perform a count (some ORMs are much more sophisticated than this). It was some sort of weird religious argument because the chap thought every single piece of data processing should be written in a…

Haha. I’ve actually had an ORM attempt to load the whole database just to do a count. That was a fun bug to dig into (nobody noticed until we had a big enough customer) and an easy fix. Literally, all I had to do was mark some fields as lazy loaded.

I think the problem is that it's not very obvious to developers that one or the other thing is going wrong until - as you say - you get a big enough customer.

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

#50

Post by t43562 »

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

They are an example where someone decides "we will use jira" but they aren't necessarily the ones using it every day so the **ness doesn't matter to them.
Post reply on HN