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

SQL: One of the most valuable skills

craigkerstiens.com

Post reply on HN
Print view View original

321–330 of 390 posts

Re: SQL: One of the most valuable skills

#321

Post by arkh »

beatgammit wrote: post #81
kumarvvr wrote: post #76

SQL is a mind bender for me. I do a lot of work on Data, use python and pandas to do a lot of data magic, but the problem with me is my mind is too procedural in thinking. - Step 1 - Step 2 - Loop through results in Step 2 - Curate and finish output. I try very hard to transform the above steps into an SQL statement spanning multiple tables, but always fail and I usually fallback to python for manually extracting and…

I took a course in prolog, really seemed to help get my mind in the right place. Basically, instead of thinking in terms of how to get to the result, think instead of the result and figure out how to get there. For example, let's say I need to get a mapping of students to their classes. One way would be to get the students, then loop through that to get classes for each one. Another way to do it would be to ask the d…

> I took a course in prolog, really seemed to help get my mind in the right place.

I have the reverse problem: every time I motivate myself to learn Prolog I feel like I could just input the constraints in a db and use SQL to get my results.

Re: SQL: One of the most valuable skills

#322

Post by silasdavis »

nostrademons wrote: post #196
3pt14159 wrote: post #186

Earlier quoted context omitted.

I understand your perspective, but I look at it a different way. SQL is troublesome to some programming types because it seems alien to ask what you want instead of telling the computer what to do and I find most programmers, especially ASD-types (who I think have an edge for some situations, like writing certain code in a huge org like Google) find this an unfamiliar and strange way of thinking. You're right about s…

Most programmers who are at all familiar with functional programming, DSLs, configuration languages, or optimizing compilers are very well versed with asking the computer for what you want rather than telling it what to do . At least when I was there, this was a very large percentage of Googlers. My issue with SQL is that a programming language should allow you to compose and name building blocks, and then recombine…

I think you make a valid point about compositionality. SQL lacks elegant composition at the source/module/syntax level. Views and stored procedures are all stateful ways of achieving something similar by using them also involves migrating that state. I don't think you're saying SQL is not expensive which seems to be the rebuttal offered by siblings.

Re: SQL: One of the most valuable skills

#323

Post by ben7799 »

I learned SQL around 1996 or 1997 and had an absolutely fantastic college course or two involving it.

I've mostly been a backend developer... having the ability to go in and fix SQL and make things run in It is sad that so often "self described 10x programmers" build solutions to go around SQL that are horrible failures. Poor use of ORMs, weird abstractions at the application layer that force developers to use poor data access patterns, unnecessary "locking" at the application layer, unnecessary "existence checks" at the application layer, processing objects 1-by-1 in the application. All these things lead to terrible performance and huge wastes of application memory/IO.

I love some of the NoSQL solutions too as in some cases they can force teams to use better data organizations/patterns and scale so well. Those patterns are often possible in an RDBMS but the system doesn't guide a team to using those patterns. The way CQL in Cassandra forces you to think about data organization is great for example.

Re: SQL: One of the most valuable skills

#324

Post by zzzcpan »

collyw wrote: post #314
zzzcpan wrote: post #292

Earlier quoted context omitted.

People behind NoSQL movement understand SQL better, than any people glorifying SQL here. In fact, I think this is a testament of how poorly most people actually understand SQL, they can't even see basic on the surface problems with it.

All I heard from NoSQL proponents was that "relational databases don't scale". These same people were usually dealing with data sets that would fit easily onto a single server.

No, I'm pretty sure you only heard it from your fellow RDBMS friends. I have never argued for scale myself, but plenty of times tried to debunk this exact stereotype.

Re: SQL: One of the most valuable skills

#325

Post by thom »

nostrademons wrote: post #196
3pt14159 wrote: post #186

Earlier quoted context omitted.

I understand your perspective, but I look at it a different way. SQL is troublesome to some programming types because it seems alien to ask what you want instead of telling the computer what to do and I find most programmers, especially ASD-types (who I think have an edge for some situations, like writing certain code in a huge org like Google) find this an unfamiliar and strange way of thinking. You're right about s…

Most programmers who are at all familiar with functional programming, DSLs, configuration languages, or optimizing compilers are very well versed with asking the computer for what you want rather than telling it what to do . At least when I was there, this was a very large percentage of Googlers. My issue with SQL is that a programming language should allow you to compose and name building blocks, and then recombine…

This is a very real problem I bang my head against regularly. There just seems no way to achieve all three of readability, maintainability and performance in a large enough SQL codebase. You can piece together views right up until the moment the query planner forgets to push where clauses down. You can wrap a query in a function to guarantee the where clause is evaluated then and there, but now you have to maintain that _and_ your view/table. You can slowly rewrite your code bit by bit, adding complexity to force the query planner (in this supposedly declarative language) to behave the way you want it to, for identical results. You can wrap all of it in a materialized view so you don't have to care about performance anymore, right up until the point it takes 12 hours to refresh.

I've be very much in the market for something more modular than SQL, which had a much more customisable query planner so you could say "I don't care if you go away and compile this for an hour as long as you make it as quick as physically possible and then save the query plan forever".

Re: SQL: One of the most valuable skills

#326

Post by jbverschoor »

magicalhippo wrote: post #238
garyclarke27 wrote: post #230

Earlier quoted context omitted.

Views - Create a general top level view, then build more specific views on views using more filters, to go down, ie more granular - then just join these with yet more views to combine, or aggregate to go back up. How is that not composable? If you hit performance issues, they are easily solved by using a few materialized views. Also CTEs and User defined Functions (I use pure SQL functions but in Postgres you can eas…

At least in the DB we're using, Sybase SQLAnywhere, materialized views comes with a hefty price tag. They must be dropped and recreated every time you touch any of the base tables, like adding a column, which in turn requires any indexes on the materialized views to be recreated. For a few of our customers, that meant that a 15 minute DB change (adding a column) turned into a several hour DB change (rebuilding materi…

What's the difference between that and a non sql approach?

Writing a program that makes a temporary table somewhere, and if there's a mistake you have to start all over again?

Re: SQL: One of the most valuable skills

#327

Post by rafiki6 »

The only thing I'll disagree with is "SQL is permanent". Nothing is permanent. SQL will live for a long time, but eventually it will be displaced by something else once something displaces RDBMS as the defacto data storage standard of the world and chooses some other interface. That being said, learn SQL, it really is a very very useful thing to know.

Re: SQL: One of the most valuable skills

#328

Post by comma_at »

chubot wrote: post #178
busterarm wrote: post #144

Earlier quoted context omitted.

This isn't meant to offend, rather as a point of consideration, but seeing your example use case being 10GB and then talk about big data frameworks makes it hard for me to take this advice seriously. I might reach for that kind of tooling at the hundreds of TB to PB scale, but in our production applications we have _tables_ that are multiple terabytes. SQL is just fine. Yes, we also have have queries that run in the…

It seems like you misunderstood what I wrote. I'm saying you should consider using R or Python if your reports are taking a long time, not big data frameworks. Big data was a reference to thinking about the problem in terms of the speed of the hardware. If it's 1000x slower than what the hardware can do, that's a sign you're using the wrong tool for the job. Getting within 10x is reasonable, but not 100x or 1000x, wh…

> These two situations are very common:

> 1) SQL queries that are orders of magnitude slower than a simple offline computation in Python or R (let alone C++)

This is largely anecdotal[1]. Do you have any sources that back up this statement? To say that Python or R is faster than X, well, I've never heard such a statement.

[1] https://yourlogicalfallacyis.com/anecdotal

Re: SQL: One of the most valuable skills

#329

Post by tomaskafka »

cirgue wrote: post #317
grigjd3 wrote: post #297

Earlier quoted context omitted.

As a software engineer who later learned SQL, I could not disagree more. Within the parameters that it is designed for, SQL is a terrific language that makes exploring and manipulating data much easier than tools like python or Scala. That doesn't mean I have no place for python or Scala, but that I definitely see a class of problems where an SQL interface is far superior.

I use python/Pandas every day for data analysis and the like, and I would never dream of not writing most of the aggregation and filtering logic in SQL. If you're working with large datasets, there is absolutely no reason to pull unnecessary data into memory.

Pulling into memory is an attribute of implementation - you could write LINQ in C# (which is a great abstraction too) and not care about the fact that it's translated to SQL that runs server-side.

Re: SQL: One of the most valuable skills

#330

Post by da_chicken »

goto11 wrote: post #268
nostrademons wrote: post #196

Earlier quoted context omitted.

Most programmers who are at all familiar with functional programming, DSLs, configuration languages, or optimizing compilers are very well versed with asking the computer for what you want rather than telling it what to do . At least when I was there, this was a very large percentage of Googlers. My issue with SQL is that a programming language should allow you to compose and name building blocks, and then recombine…

'With'-clauses (aka Common Table Expressions) allows you to name and compose subqueries much more cleanly. Your preferred interface sounds like Linq. An IQueryable interface represent a query and can be composed and assigned to variables, and the type T represent the type of the relation or projection. Linq composes a lot more elegantly than SQL itself. The instances does not represent intermediate results though, th…

> 'With'-clauses (aka Common Table Expressions) allows you to name and compose subqueries much more cleanly.

These are great, but their implementation varies pretty significantly. Notably, MySQL didn't have them at all until v8.0, and PostgreSQL's CTEs are... wonky, for lack of a better term (I believe they use an "evaluate and store" method that's closer to a temp table, and as a result they don't optimize well and have been known to have strange side effects). Not all RDBMSs support recursive CTEs, either.

Post reply on HN