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

51–60 of 110 posts

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

#51

Post by sdeframond »

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…

In good resource on testing stored procedures ?

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

#52

Post by tanelpoder »

The reluctance of using stored procedures where they’d be valuable is also a skill + will issue. I do get the non-database-developer view that if 98% of your app is gonna be written in some other language anyway, why complicate your app layers by adding a bit of PL/SQL code + testing infra here and there.

But for processing (and validating) various data loads and incoming data streams, by looking up matching values in existing reference tables, stored procedures can increase performance/efficiency and reduce complexity (of having to maintain connection pools and caching layers just for validating incoming record batches).

As far as the unwillingness to even evaluate stored procedures issue goes, I sometimes joke that maybe the DB vendors should start calling stored procedures as “database lambdas” instead…

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

#53

Post by t43562 »

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…

On the other hand...... if you look at software as a thing that is mostly about people's heads rather than code.

I mean that in a way I think it's more important to have a team that understands the problem domain fully than to have the code itself. Obviously the business might not think so but as a software process, the costly thing to do IMO is get a group of people to a point of common understanding where they can work productively.

When your developers didn't write the code they're unlikely to understand all the reasons why it is the way it is. How do you get them trained up to that level again? One way is a rewrite. The end result may be no better or even worse but now you have people who know the system, the business and they can look after it efficiently....until they all leave and you start again.

This is my devil's advocate answer to my own feeling that one should never rewrite.

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

#54

Post by beng-nl »

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…

Chesterton’s traditions.

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

#55

Post by t43562 »

So far in my career, such as it is, I have been on lots of rewrite projects and not one of them was a good idea.

There were one or two outright failures (new code abandoned) but the more subtle ones were just efforts that took so long to deliver value that the whole market opportunity was lost.

In every single case it was possible to take the existing system and gradually morph it towards something better - but the desire for revolution prevented evolution and forced everyone to live with shit in the hope of a future that didn't arrive.

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

#56

Post by bob1029 »

tanelpoder wrote: post #52

The reluctance of using stored procedures where they’d be valuable is also a skill + will issue. I do get the non-database-developer view that if 98% of your app is gonna be written in some other language anyway, why complicate your app layers by adding a bit of PL/SQL code + testing infra here and there. But for processing (and validating) various data loads and incoming data streams, by looking up matching values i…

> But for processing (and validating) various data loads and incoming data streams, by looking up matching values in existing reference tables, stored procedures can increase performance/efficiency and reduce complexity

Performing operations on data directly in the SQL provider is the peak of human enlightenment. It takes a lot of leadership or wisdom to push a modern team away from using crap like EF to process everything, but 100x slower.

In exactly 0% of cases of cycling records through code will you see a higher performance result than executing a single T-SQL or PL/SQL script against the provider.

The procedural SQL languages are Turing complete. SQL itself is as of recursive common table expressions. There's not any reasonable argument for not trying this if all the information is already in the SQL store.

Moving information is way more expensive than processing information that is already in place (cache). Your SQL server process can iterate on items in L1 millions of times before a packet makes it across the data center one time.

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

#58

Post by sdeframond »

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…

> ORM feels like an abstraction on top of an abstraction. I don't trust that those who chose ORMs have studied and exhausted the possibilities of the two underlying layers, I feel more often than not they recourse to higher layers of technology without understanding the lower level.

I agree with that. However I feel that teams that choose not to use an ORM end up having one somehow reimplemeted by the seniors, and just used as you describe by the juniors.

I'd rather have the seniors master an existing ORM and spend their time elsewhere.

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

#59

Post by [deleted] »

bytefish wrote: post #42
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…

[deleted]

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

#60

Post by MaxBarraclough »

sdeframond wrote: post #51
simonw wrote: post #30

Earlier quoted context omitted.

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…

In good resource on testing stored procedures ?

There are unit-testing frameworks for the purpose. For SQL Server there are several, [0] I've worked briefly with one called tSQLt [1] and was quite impressed. Frameworks also exist for other RDBMSs. [0]

[0] https://en.wikipedia.org/wiki/List_of_unit_testing_framework...

[1] https://tsqlt.org/user-guide/quick-start/

Post reply on HN