Hacker News Reader

Comment by EastSmith | Hacker News Reader

I've wrote CRUD apps for 5 years very long time ago. Then I was spending weeks debugging business logic in stored procedures and views, fine-tuning big query execution plans, you name it.

At my current work nobody talks about SQL, literally every discussion starts with "We will create this new Rails model". Indexes are not created deliberately - we are adding them only when the need is obvious, because some functionality is plain slow. No one talks about SQL, plain SQL queries in code are considered really bad practice - in cases where the query generated by Rails is complex and slow and a plain SQL query is PR-ed, the code reviews discussions are pretty long.

I am not saying this is good or bad, in fact all looks OK, but I was really really surprised at the beginning.

View on HN

I once worked with a guy who would generate SQL queries via a series of multiple regex transformations. Even now that's still up there as one of the most terrifying things I've seen a developer do.

Why terrifying? Sounds like he saw that the structure of much of what he was doing was boilerplate and created a bespoke code generator to save himself time. As long as the resulting queries are readable and effective I don't see a problem. Unless I'm missing something, understanding his code generator is not (necessarily) a pre-requisite for understanding the resulting code.

> Why terrifying?

Because you're leaving an unmaintainable mess. Nobody else can rationalise the code aside himself. It's harder to debug because even he cannot account for every edge case his regex might throw up -- which also makes it less secure. Also regex is comparatively slow so transforming an SQL query using multiple regex search and replace patterns is not the way to write hot code hit by literally millions of visitors each hour.

> understanding his code generator is not (necessarily) a pre-requisite for understanding the resulting code.

Oh it absolutely is if you want maintainable and secure code. I get some situations call for complexity but when you're generating SQL on a public facing web application with a centralised database backend, you want to be damn sure you can rationalise the SQL being generated. The best ways of doing that is to either not to try to be too clever with your generation (KISS) or to put your confidence into an established and well tested existing ORM or equivalent.

> Indexes are not created deliberately - we are adding them only when the need is obvious, because some functionality is plain slow.

Is there anything wrong with this? My thinking is that it could be seen as a premature optimization if an index is created without knowing if it will be used.

In my world, if you don't know whether an index is likely to be used, it indicates a lack of understanding of what your users are likely to be doing, and/or the nature of the data set. Yes, there are marginal use-cases, but overall, you should understand what most of your users/consumers are doing and optimise accordingly.

Reply on news.ycombinator.com