How SQL Database Engines Work, by the Creator of SQLite (2008) [video] • Hacker News Board

Re: How SQL Database Engines Work, by the Creator of SQLite (2008) [video]

#111

Post by Amezarak » Mon, Jun 25, 2018, 12:26 PM UTC

dicroce wrote: post #51

so I was trying to figure out why a query was slow the other day... it was a nasty query with like 14 joins... I used explain and saw that it was a mess... now in my case I was able to switch to outer joints and nest related joins and got it fast.. but I had some interesting thoughts. In SQL, indexes are implicit.. they are used if available but it's easy get a large query to scan sometimes when it shouldnt... what i…

I would suggest that there's potentially something you need to look at with your database schema - a couple dozen joins shouldn't be causing any problems you have to think about.

Part of this is because of the way a well-normalized database is organized. Most databases have a few large tables and many smaller tables. So in the general case, most of your joins will be against smaller tables. Joins with larger tables are usually very fast, as long as the fields you join on are indexed (and you're not doing a CROSS JOIN or something.) The other thing that helps (which it sounds like you did by "nesting related joins") is to always think about limiting (filtering) the datasets you're joining against at as many stages as possible; that way you're always doing the least amount of work necessary, and it's usually conceptually simpler to read and understand.

As others have said, most databases do have index hinting as part of the query language. However, in my (long) experience, you should almost never use it. Index hints should be a huge code smell.

Re: How SQL Database Engines Work, by the Creator of SQLite (2008) [video]

#112

Post by dustingetz » Mon, Jun 25, 2018, 12:35 PM UTC

wvenable wrote: post #42

ebikelaw wrote: post #19

Earlier quoted context omitted.

And the query plan can literally change out from under you at any time. SQL sucks. You should be able to dictate the query plan to the engine directly. If SQL exists as a tool to create and serialize such plans via exploration and experimentation, that’s fine. As a runtime query system it is completely unsuitable.

> You should be able to dictate the query plan to the engine directly. That's like arguing you should be able to dictate the assembly that your compiler produces. The entire point of SQL (and most compilers) is that they can use their knowledge to optimize the result in ways that are too difficult or too involved for humans to do. Most people cannot out-optimize a compiler in the general case. And most people cannot…

You're both right, there is a values mismatch here. The reason we need optimizing compilers to target modern CPUs is because the hardware architecture is so complicated. The root cause of the complexity is less essential concerns and more an artifact of the history of how things unfolded, compounded by the difficulty of disrupting the current local maximas that we're stuck in.

Re: How SQL Database Engines Work, by the Creator of SQLite (2008) [video]

#113

Post by kimdotcom » Mon, Jun 25, 2018, 2:02 PM UTC

Matt3o12_ wrote: post #103

brianwawok wrote: post #55

Earlier quoted context omitted.

Or oracle has it A DBA can even sit there as queries fly past, and add hints on the fly. And then you change a query from "select" (lowercase) to "SELECT" (uppercase), and query plans break and you break production. Fun times

Could you elaborate why changing select from lowercase to uppercase would break anything?

Stored procedures where master and slave are on different OSs and/or case-sensitivity settings are not the same?

Just a guess.

Re: How SQL Database Engines Work, by the Creator of SQLite (2008) [video]

#114

Post by kimdotcom » Mon, Jun 25, 2018, 2:03 PM UTC

Amezarak wrote: post #111

dicroce wrote: post #51

so I was trying to figure out why a query was slow the other day... it was a nasty query with like 14 joins... I used explain and saw that it was a mess... now in my case I was able to switch to outer joints and nest related joins and got it fast.. but I had some interesting thoughts. In SQL, indexes are implicit.. they are used if available but it's easy get a large query to scan sometimes when it shouldnt... what i…

I would suggest that there's potentially something you need to look at with your database schema - a couple dozen joins shouldn't be causing any problems you have to think about. Part of this is because of the way a well-normalized database is organized. Most databases have a few large tables and many smaller tables. So in the general case, most of your joins will be against smaller tables. Joins with larger tables a…

I use UUIDs as primary keys, you insensitive clod!

Re: How SQL Database Engines Work, by the Creator of SQLite (2008) [video]

#115

Post by kimdotcom » Mon, Jun 25, 2018, 2:06 PM UTC

A_Person wrote: post #93

Gosh, I must say there seems to be some misunderstanding of RDBMS concepts in some posts in this thread! I was writing database systems professionally, back in the days before the RDBMS concept was even a thing. So here's my (enormously long and convoluted) 2 cents worth. Please be sure to pack a sandwich and get hydrated before you continue. Say you were dealing with doctors and patients, and needed to store that in…

So, were you writing DB software before Codd's research at IBM was available?

Re: How SQL Database Engines Work, by the Creator of SQLite (2008) [video]

#116

Post by manigandham » Mon, Jun 25, 2018, 2:10 PM UTC

PuercoPop wrote: post #73

dmoreno wrote: post #36

Earlier quoted context omitted.

I completely disagree. Unless your data requirements are very specific, mem only, or not adapted to the relational paradigm any SQL engine will provide you with the best and more efficient algorithms to manipulate your data in the most common situations. I think that if any, databases should be used more.

> Unless your data requirements are very specific, mem only, or not adapted to the relational paradigm any SQL engine will provide you with the best and more efficient algorithms to manipulate your data in the most common situations. Too bad that Michael Stonebraker, Turing Award winner, disagrees with you. SQL are not the best solution for any common use case from the performance perspective. Nevermind what they do…

You missed the "in the most common situations" part.

Modern RDBMS can handle millions of ops/sec and terabytes of data. They do the job fine 99% of the time and are constantly adding new features.

If you have the 1% need for another data store, there are hundreds of options, and interestingly many of them are also starting to implement SQL as an interface now.

Re: How SQL Database Engines Work, by the Creator of SQLite (2008) [video]

#117

Post by kimdotcom » Mon, Jun 25, 2018, 2:16 PM UTC

slow_donkey wrote: post #22

greenyoda wrote: post #17

Earlier quoted context omitted.

> I really wonder who, today, have data that cannot fit in RAM, apart from big actors. Keeping all your data in RAM has significant problems, even if it all fits. For example, would you want to lose all your customers' orders and billing information if your code crashed? In addition to the relational database model, SQL databases offer ACID transactions, which are useful if you want to have consistent and reliable da…

To be fair, using redis or elasticsearch as a main datastore is doable. Although I'm not sure they're much better choices in terms of understanding how they work. You could summon Antirez I guess

Doesn't redis by default have recovery via the filesystem enabled?

Re: How SQL Database Engines Work, by the Creator of SQLite (2008) [video]

#118

Post by A_Person » Mon, Jun 25, 2018, 3:15 PM UTC

kimdotcom wrote: post #115

A_Person wrote: post #93

Gosh, I must say there seems to be some misunderstanding of RDBMS concepts in some posts in this thread! I was writing database systems professionally, back in the days before the RDBMS concept was even a thing. So here's my (enormously long and convoluted) 2 cents worth. Please be sure to pack a sandwich and get hydrated before you continue. Say you were dealing with doctors and patients, and needed to store that in…

So, were you writing DB software before Codd's research at IBM was available?

Yes indeed. I haven't checked his research dates, but I was writing database software in the late (uh) 60s and early 70s. I think that was even before "goto harmful". I still remember our standards officer saying, let's try some of this structured programming stuff!

Re: How SQL Database Engines Work, by the Creator of SQLite (2008) [video]

#119

Post by A_Person » Mon, Jun 25, 2018, 3:22 PM UTC

justinclift wrote: post #101

A_Person wrote: post #98

Earlier quoted context omitted.

Nope! I've never used postgres at all. Most of my RDBMS work was done on HN NSFW ALERT - oracle :-) But your comment supports my general point, which is, that data modelling and relational schema design skills are product agnostic; normalization is normalization, as it were.

Just to point out, "PROGRESS 4GL" is a different thing, not PostgreSQL based. It seems to be called OpenEdge Advanced Business Language these days: https://en.wikipedia.org/wiki/OpenEdge_Advanced_Business_Lan... And yeah, the terms you mentioned are foundational RDBMS terms, common to all reasonable implementations of RDBMS's. :)

Ok. Oracle had/has a presumeably similar thing (PL/SQL). I misread his comment to mean, postgres SQL.

Re: How SQL Database Engines Work, by the Creator of SQLite (2008) [video]

#120

Post by mmjaa » Mon, Jun 25, 2018, 3:28 PM UTC

A_Person wrote: post #119

justinclift wrote: post #101

Earlier quoted context omitted.

Just to point out, "PROGRESS 4GL" is a different thing, not PostgreSQL based. It seems to be called OpenEdge Advanced Business Language these days: https://en.wikipedia.org/wiki/OpenEdge_Advanced_Business_Lan... And yeah, the terms you mentioned are foundational RDBMS terms, common to all reasonable implementations of RDBMS's. :)

Ok. Oracle had/has a presumeably similar thing (PL/SQL). I misread his comment to mean, postgres SQL.

Yes indeed, I meant PROGRESS 4GL, which was sort of around before all the "RDBMS" hoopla congealed all this wonderful info into a single art. Its just that your last paragraph really sounded like a quote, near-verbatim, from the very guides published by PROGRESS just for the purposes of educating people on how to make their data relatable .. but of course that is because these are natural laws for databases.