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

Literate SQL

modern-sql.com

Post reply on HN
Print view View original

71–80 of 100 posts

Re: Literate SQL

#71

Post by teej »

bayonetz wrote: post #49

Love the literate sql style. A lot of times we have to use temp tables instead of with statements though. In Vertica, all the withs of a query have fit in memory together. Using temp tables instead to create the logical sequence, you only need enough memory for each temp table and final query on there own. The cognitive effect is still the same. Define A, Define B based on A, Define C based on B, etc. but sequentiall…

We do the same thing with Redshift. Using temp tables also gives us more direct control over the query plan, since we can specify distribution and sort keys. On top of that, we can run unit tests on the temporary table (explained in another comment) and fail the query if tests fail.

Re: Literate SQL

#72

Post by combatentropy »

enjoiful wrote: post #19
caseymarquis wrote: post #8

It's critical to have a good understanding of SQL, but once you do, ORMs with a functional syntax solve this problem for many of us. I know it's not possible for some people to use ORMs as they can't risk the ORM making a performance mistake, but I think the solution is to improve ORMs to the point that writing raw SQL is akin to writing assembly instead of using a higher level language. That said, we're not there ye…

I love Django -- but what it does is mostly magic to me. That being said... I'm fairly competent in being effective in Django -- although not for advanced and efficient querying. I want to get strong in SQL -- where/how do I start?

> I want to get strong in SQL -- where/how do I start?

1. I suggest the PostgreSQL documentation itself (https://www.postgresql.org/docs/current/static/). It's well-written, but still wordier than need be, and if you try to read it from start to finish, you will probably die. However, the beginning chapters are overviews of SQL. Once you feel in the mud, you are probably in very specific, technical chapters. You can just skim these, jump to ones that interest you at the moment, etc.

2. There also a separate wiki (https://wiki.postgresql.org/wiki/Main_Page). I haven't gone there much, but it is a nice complement. It has practical summaries of things that the main documentation spins out of control (like setting up replication). It also has some examples of how to to do really advanced things in SQL.

3. A well-reviewed, short book. The first book I read was SQL Demystified. It's a short and easy intro to SQL in general. Find something like that. I have tried hard to find good books on SQL, but most are huge tomes that will crush your soul.

4. Practice. For the past decade at my job I have been forced to learn complex SQL for various business reports, across a variety of tables. And I still feel like an intermediate.

Re: Literate SQL

#73

Post by Daishiman »

vladsanchez wrote: post #66
caseymarquis wrote: post #8

It's critical to have a good understanding of SQL, but once you do, ORMs with a functional syntax solve this problem for many of us. I know it's not possible for some people to use ORMs as they can't risk the ORM making a performance mistake, but I think the solution is to improve ORMs to the point that writing raw SQL is akin to writing assembly instead of using a higher level language. That said, we're not there ye…

ORMs are for people who don't know SQL.

SQL is for people whose problem domain is so simple that they don't need to do things such as, I dunno, compose queries.

Re: Literate SQL

#74

Post by Daishiman »

3131s wrote: post #38
enjoiful wrote: post #19

Earlier quoted context omitted.

I love Django -- but what it does is mostly magic to me. That being said... I'm fairly competent in being effective in Django -- although not for advanced and efficient querying. I want to get strong in SQL -- where/how do I start?

Write raw SQL queries. My feeling on the Django ORM is that it's useful for defining models and managing migrations, but I prefer to write raw SQL for all queries. The PostgreSQL documentation is really good too.

> but I prefer to write raw SQL for all queries.

If you're doing this you're missing out on the biggest advantage of the ORM, which is that it allows you to compose queries.

Using the Q objects, query expressions, and custom Queryset objects, you can filter objects by pretty much every imaginable criteria out there. This would be horrendously difficult and error-prone writing plain SQL.

The Django ORM, despite having limitations if you want to run analytics (and that's really a use case for with plain SQL excels), generally writes out exactly the same SQL I'd write by hand.

Re: Literate SQL

#75

Post by gfody »

justinclift wrote: post #69
gfody wrote: post #62

Earlier quoted context omitted.

you (and I) are absolutely spoiled by Visual Studio w/SSDT. there is nothing even remotely close to that level of integration for database development in other stacks.

Err... any of the JetBrains IDE's? They have database integration. It's also available standalone as "DataGrip", though I've never used it: https://www.jetbrains.com/datagrip/

the level of integration with SSDT is way beyond that - the database structure and stored procs live side-by-side with your code, are source controlled together, built and deployed together. there's compile-time checks for stored procedures, code analysis, etc. DataGrip is just an alternative for SQL Developer/Management Studio, SSDT is much more.

Re: Literate SQL

#76

Post by ajarmst »

AndrewOMartin wrote: post #39

I see that this is about a way of writing SQL, but as Literate Programming is cited in the title, I wanted to point out that you can already use the true Literate Programming tool noweb to write SQL with or without "with" statements. The advantage is that you'll be able to write full documentation amongst the SQL, present it in any order, and reuse chunks. The disadvantage is that it outputs to stdout, so if that's n…

Well, stdout can be redirected in any sane environment, so that's not much pf a disadvantage. However, thanks for making the point that what they're describing (good variable naming practices, and the use of 'with' to prefix them) is hardly what Knuth was talking about when he coined "Literate Programming".

Re: Literate SQL

#77

Post by ajarmst »

The use of meaningful labels and "with" to prefix them may be good practice, but that's barely related to what Knuth meant by "Literate Programming".

Re: Literate SQL

#78

Post by gfody »

weavie wrote: post #67
gfody wrote: post #60

Earlier quoted context omitted.

I've found views, parameterized-views/table-valued-functions can be a nice compromise to keeping the SQL relatively simple and thus ORM friendly without sacrificing performance.

This can be dangerous if over used. It can be very tempting to quickly pull in a 'left join blah' to grab some fields you may need. But that blah view could be hiding some massive performance sapping beast. When it comes to optimizing a slow running query, having to step through layer after layer of nested view can be incredibly challenging.

I can't speak for other engines but at least SQL Server and Oracle will eliminate unnecessary joins for inlined table-valued functions, e.g.:

create function thing (a, b) returns table as select * from table_1 left join table_2 on ...

select (only cols from table 1) from thing(a, b)

..and table_2 is not accessed

predicates from the topmost query will be pushed down to the function's query as well so the functions performance is generally equivalent to the adhoc version.

on the whole I find this strategy to be very effective at reducing the complexity of adhoc queries without performance penalties. in the case of indexed views it can greatly improve performance.

Re: Literate SQL

#79

Post by DaiPlusPlus »

weberc2 wrote: post #50
paulddraper wrote: post #21

Earlier quoted context omitted.

I see this opinion a lot. I don't quite understand it. Your DBMS isn't some ambivalent data store with simple universal semantics. I'm not just talking about SQL features like complex subselects or CTEs, but index hints, lock order, transactional visibility, non-trivial column constraints, index locks... These matter in appreciably sized public-facing (e.g. web) systems. Granted, with enough transactions and roundtri…

While I prefer vanilla SQL to the current generation of ORMs, I think ORMs are trying to address a real problem--namely that SQL (despite being high level) isn't very abstraction-friendly. Abstractions in SQL can be expensive and often unpredictably so, despite how clever databases are today. I think part of the problem is the mismatch between the largely imperative OO paradigm and the largely declarative relational…

I advocate ORM use for guaranteed type-safety - so long as SQL queries are processed as strings inside your application there's always the risk of column names and types not matching your entity classes - by using an ORM that generates SQL based on your classes (e.g. Entity Framework with Linq) it promotes mismatches to compile-time errors. This alone makes it worthwhile.

Re: Literate SQL

#80

Post by threeseed »

gregmac wrote: post #37
caseymarquis wrote: post #8

It's critical to have a good understanding of SQL, but once you do, ORMs with a functional syntax solve this problem for many of us. I know it's not possible for some people to use ORMs as they can't risk the ORM making a performance mistake, but I think the solution is to improve ORMs to the point that writing raw SQL is akin to writing assembly instead of using a higher level language. That said, we're not there ye…

I disagree about SQL being like assembly. It's a fairly high level language, because underneath the engine is figuring out how to use indexes and actually run your query. If you want to draw parallels, ORMs are more like writing code in a template language that's written in the language you're actually working in. I've spent far too much time fighting with ORMs trying to get the SQL I want generated. Additionally for…

Actually more often than not ORMs are going to provide better performance than having people inexperienced with SQL writing it themselves. Especially when it comes to batching, pagination and joins and the intelligent use of in memory caching rather than doing everything in database.

And the biggest advantage of an ORM is that you can trivially switch between databases which is often required for running automated tests. SQLite or H2 for development and then Oracle, SQL Server or Teradata for production is a very common pattern I've seen at many companies.

Post reply on HN