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

51–60 of 100 posts

Re: Literate SQL

#51

Post by weberc2 »

richardlblair wrote: post #27
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…

I think your post makes a lot of assumptions. I do not believe scale to be a reason not to use an ORM. If you pay attention to what you're doing you can scale a system that uses an ORM just fine. The orm is usually not the problem, it's usually that people don't know what they are doing and introduce serious performance issues.

As I understand it, the point of an ORM is to make relational data look and feel more like the objects in my program; I shouldn't have to think about the consequences of my data living on the other side of a network connection. Even if that isn't the official point, it's at least a widely-held misunderstanding. If you're going to write code that uses the ORM this way, you're going to have a bad time, but if you write your code intelligently, you end up using your ORM as a glorified query builder.

I think an ORM makes more sense in a functional language since the interfaces are more likely to be declarative than imperative. For example, an in-memory List and a SQLQuery can both implement a filter() method that takes a function and applies it in the way most efficient to that data structure; in traditional OO languages, we usually loop over our in-memory structures explicitly, and doing that with a database is prohibitive.

Re: Literate SQL

#52

Post by mtone »

wvenable wrote: post #44
nerdponx wrote: post #5

I wish CTEs ("common table expression", i.e. a "with clause") had the same performance as a subquery in PSQL. I always assumed they'd be implemented as a kind of macro that expanded to a subquery. Is there a good reason for this distinction? For that matter, having some kind of SQL-oriented macro/preprocessor language would be fantastic. I guess GPP (General Preprocessor, https://logological.org/gpp ) is always an op…

Even in databases without the same performance issues as PSQL, like SQL Server, "with clauses" can cause performance issues on their on own. I recently refactored a long SQL query with a half-dozen with-expressions to a single query and increased the performance by an order of magnitude. They had used "with" to build up a query from a bunch of independent sets and then union them together and, while perfectly logical…

The trade-off is modularity. During development working with CTEs paves the way for establishing views later, possibly highlight recurring requirements or gaps in the schema, and the logical separation of general purpose sets (ie functions) from the specifics of a particular query is valuable.

Like in everything, a balance is ideal. For reporting/analysis/testing purposes, I think nesting views and CTEs is more productive in the long run, while production application code is probably best kept as straightforward as possible.

Re: Literate SQL

#53

Post by paulddraper »

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…

Agreed. If anything SQL is declarative and more high level than most application code.

FYI, if you have a 1-2GB database, that whole sucker fits in RAM, so rejoice.

Re: Literate SQL

#54

Post by Nullabillity »

paulddraper wrote: post #40
Asooka wrote: post #35

Earlier quoted context omitted.

The reason for me to use ORM rather than direct SQL queries is so I can keep everything written in a single language, so I can develop with one IDE. Having everything under one roof is a huge boon to productivity, rather than having to edit the logic in the IDE, then edit the database queries in a different editor, and the two knowing nothing about each other. It lets me directly map my source language's type system…

I can sympathize. For me, that's Scala JVM on the server, ScalaTags/ScalaCSS/ScalaJS in the web browser, Slick for the ORM, and SBT for the build system. The bliss of learn once, program anywhere. When this hits reality though, I use Relate https://github.com/lucidsoftware/relate (disclaimer: I'm a contributor) It's SQL, but with minimal syntactic overhead. sql"SELECT * FROM users WHERE email IN ($emails)".asList[Use…

Since you're already using Slick, what's the difference between Relate and Slick's native SQL literals[1]?

[1]: http://slick.lightbend.com/doc/3.2.0/sql.html

Re: Literate SQL

#55

Post by meditationapp »

vinceguidry wrote: post #11
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…

For anybody on Rails, you can call .to_sql on any ActiveRecord::Relation and it will return the exact SQL string that your code is producing. Really handy for troubleshooting, especially for associations.

Of course, not all operations return a relation, such as count, update_all, delete, etc, even if they have a sql equivalent.

Re: Literate SQL

#56

Post by lucio »

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…

>but I think the solution is to improve ORMs

http://blogs.tedneward.com/post/the-vietnam-of-computer-scie...

Not trying to attack you, just a cautionary tale that you should read

Re: Literate SQL

#58

Post by saosebastiao »

nerdponx wrote: post #5

I wish CTEs ("common table expression", i.e. a "with clause") had the same performance as a subquery in PSQL. I always assumed they'd be implemented as a kind of macro that expanded to a subquery. Is there a good reason for this distinction? For that matter, having some kind of SQL-oriented macro/preprocessor language would be fantastic. I guess GPP (General Preprocessor, https://logological.org/gpp ) is always an op…

The reasoning was that it was easier to implement that way. CTEs can contain state changing behavior and for that reason they are treated as optimization fences in order to be as safe as possible.

I believe Tom Lane has since said that there isn't any reason it should stay that way and the community has never received any guarantee that this was going to stay the same. It just hasn't been implemented. In other words, they're seeking contributions. I'd do it myself if I were even a remotely capable C programmer, but I'm not.

Re: Literate SQL

#59

Post by virtualwhys »

mshenfield wrote: post #20
zzzeek wrote: post #13

Earlier quoted context omitted.

You can have functional SQL query languages that map directly to SQL without object oriented row mapping. Everyone can use these tools without performance issues. Disclaimer: i wrote SQLAlchemy.

> You can have functional SQL query languages that map directly to SQL without object oriented row mapping. Well said. I always think of this as the canonical example: http://sqlkorma.com/ I don't know what rock I've been under, but didn't realize SQLAlchemy had the same idea w/ SQLAlchemy Core.

> without object oriented row mapping

Korma seems to do exactly that, N+1 lazy loaded queries included. SQLAlchemy, Esqueleto, Slick, Quill, and the like all generate precisely what you tell them to (i.e. queries semantically the same as what you'd write by hand).

This is not to disparage Korma, it looks pretty cool, I'd certainly give it a look if I worked with Clojure.

Re: Literate SQL

#60

Post by gfody »

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…

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.
Post reply on HN