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

21–30 of 100 posts

Re: Literate SQL

#21

Post by paulddraper »

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 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 roundtrips to application logic, an ORM can mimic these.

Now, I do know YAGNI; you might never get to a scale where an ORM poses a limitation.

I just suppose I don't see large enough benefits of learning and using an ORM in the early stages, to outweigh the cost of switching to SQL in the (admittedly hypothetical) later stages.

Re: Literate SQL

#22

Post by NumberCruncher »

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…

Even if you work with an ORM instead of writing raw SQL you should know when single row processing is OK and when bulk load / data set processing is a must.

My last employer lost 5M+ euros (not reveue but profit) every month because of excessive use of PL/SQL single row processing.

Re: Literate SQL

#23

Post by wesd »

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?

Start by having a strong understanding the various joins first: http://www.sql-join.com/sql-join-types/

Re: Literate SQL

#24

Post by zamalek »

It's great to see highly legible SQL in a tutorial. These days it seems as though everyone on the internet is secretly running a SQL obfuscation competition.

Re: Literate SQL

#25

Post by zamalek »

mshenfield wrote: post #2

I came to this page looking for ways to parameterize and re-use raw SQL. with (and create view for multiple queries) fulfill the case where the unit of reuse is a filtered view on one or more tables.

With SQL Server you can create TVFs (table-valued functions). So long as they are 'inline' (lacking BEGIN and END) they will be inlined into any query that uses them. If your SQL fu is up to scratch they are extremely powerful - e.g. CROSS APPLYing them is one of the best ways I've seen to do reusable row-level filtering.

Re: Literate SQL

#26

Post by NumberCruncher »

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 used w3schools for learning webdev related things but their SQL tutorial seems to be good as well.

[1] https://www.w3schools.com/sql/default.asp

Re: Literate SQL

#27

Post by richardlblair »

paulddraper wrote: post #21
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 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.

Re: Literate SQL

#28

Post by mistermann »

NumberCruncher wrote: post #22
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…

Even if you work with an ORM instead of writing raw SQL you should know when single row processing is OK and when bulk load / data set processing is a must. My last employer lost 5M+ euros (not reveue but profit) every month because of excessive use of PL/SQL single row processing.

How could they lose that much $ due to single row processing? Having to buy that much more processing power?

Re: Literate SQL

#29

Post by mistermann »

teej wrote: post #16
mshenfield wrote: post #2

I came to this page looking for ways to parameterize and re-use raw SQL. with (and create view for multiple queries) fulfill the case where the unit of reuse is a filtered view on one or more tables.

To make progress on this problem, I wrote a light SQL preprocessor to support INCLUDE statements. It enables me to write code like this: WITH frequently_bought_together AS ( INCLUDE frequently_bought_together.sql ) SELECT ... This allows way better isolation and reuse of business logic than before. In Redshift, I combine this with an assert user-defined function to enable writing unit tests in raw SQL. With all that…

It warms my heart to see people in the wild doing smart things like this (after getting laughed at by moron managers who wouldn't authorize any good ideas like this for years).

Re: Literate SQL

#30

Post by MarkusWinand »

theprotocol wrote: post #15
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…

Regarding postgres `with`: I thought the same as you, that it'd be implemented as a macro, and only just found out that it's not the case thanks to your comment. I'd love to hear more about this decision.

Watch out—it's not that easy: http://modern-sql.com/feature/with/performance
Post reply on HN