5 ms·

I'm not impressed for two reasons: 1. Anyone striving to build a better SQL should make a comprehensive list of common (but difficult!) database tasks for OLTP

I'm not impressed for two reasons: 1. Anyone striving to build a better SQL should make a comprehensive list of common (but difficult!) database tasks for OLTP and OLAP workloads. This will expose the weakness of their language. SQL has had 50 years and myriads of improvements to cover all these common cases. This is not a fair fight, so come prepared. 2. It's not enough to be just "better than SQL" to replace it. SQL has such a huge momentum that a new language needs to be absolutely better _and_ it should have many features that SQL cannot possibly have. My nice-to-have list would contain predictable performance, lock ordering, ownership relations (for easy data cleanup), and a standard low level language which the query optimizer would output.

GeneralMayhem 6y ago
Not disagreeing with your point (if purist language nerds had their way over practicality, we'd all be writing Haskell and Prolog) but most of your nice-to-haves strike me as properties of the database engine, not SQL itself. Predictable performance - this will always be not only implementation-dependent but data-dependent as well. In order to know whether a join will be efficient or not, you need to know things like relative sizes of the tables, which is not necessarily a language problem. I have worked with SQL implementations that had extensions to let the user annotate joins with relative sizes on each side, but I don't think that's quite what you mean. Lock ordering - again, good databases should have defined semantics (Postgres, for instance, does take locks in order when using ORDER BY), but I'll grant that this one could be stronger. That said, I think this is pretty niche. How often are you doing large multi-row transactions where lock order is a serious problem? If I have enough volume that deadlock is likely, I probably have enough volume that I want to be breaking up the process into a sharded or two-phase commit anyway. Ownership relations - I think this is a DDL problem rather than a SQL problem. Low-level language - I don't think you'll get a portable low-level language here (at least not for any definition of "low level" that's much lower than the SQL AST) because, again, the basics are implementation-dependent. What kind of scan is the base atom of a query? Well, it depends - is your database distributed? sharded? row-store-based? column-store-based? I do wish more open source database drivers would let you play with the AST in memory (Postgres has ways to print it out, but I don't think there's a good API). That would tend to solve the most significant problem raised in the article (composability) - plugging together SQL clauses automatically is hard, but plugging together subtrees can be much easier.
- AmericanChopper 6y ago
  > Predictable performance - this will always be not only implementation-dependent but data-dependent as well This immediately jumped out at me from the parent comment. It would be entirely possible to implement a query language where you specify a plan for your query. But then you’d immediately lose the “better than SQL” competition, because your complexity and maintainability problems would skyrocket. I’ve had to deal with this problem as an Oracle DBA, and it’s a complete nightmare. It starts with a statistics refresh ruining a couple of execution plans, so you start specifying them manually with the plan manager. Then it gets worse over time, because stats refreshes become a big risk and you don’t want to do them anymore. Eventually you get to the point where you pretty much only run verified plans. Then your verified plans slowly degrade overtime, because the underlying cardinality of every table is constantly changing. You’ve replaced the query optimiser with yourself, which is not only tedious work, but it’s simply not possible to do the job as well as any mainstream DB engine could.
  - CaptainZapp 6y ago
    I wish that I could upvote your comment more than once, because this rings so true. There certainly are (rare) situations, where you need to provide hints in one form or another, but it's really a bloody nightmare to maintain and may completely bork, when you - say - upgrade to a new version of the database engine. I work with relational databases since the early 90s and can give you a no-bullshit money back guarantee that you (not you personally, obviously) are not smarter than the optimizer. Usually there are weird data patterns involved if you absolutely must provide hints. But basically: Don't do it!
    - mnsc 6y ago
      I think one of the challenges with sql is that beginner developers can create naive sql queries that "work" but are extremely complicated for the optimizer to "get right". So in some cases (talking from own experience) the developer can, with the use of hints, "be better" than the optimizer when the problem all along was the overall structure of the query. Edit: don't do it
JoshuaDavid 6y ago
Alternatively you can make a compiler from sql to your alternative, which would make it relatively easy to get the "I just want a familiar thing that works" people on board.
- virgilp 6y ago
  At that point you no longer provide a new language, but a new SQL query engine.
ridaj 6y ago
I don't know if it's necessary for a better language to do both OLAP and OLTP; it seems that they are used by different classes of users with relatively low overlap in language features. But I definitely agree that such a test as (1) would be a critical prerequisite to an improvement over SQL.
thanpolas 6y ago
3. The evolution of SQL should be an open standard, backed by academics and the community. Not a proprietary solution proposed by a company.

GeneralMayhem 6y ago

Not disagreeing with your point (if purist language nerds had their way over practicality, we'd all be writing Haskell and Prolog) but most of your nice-to-haves strike me as properties of the database engine, not SQL itself. Predictable performance - this will always be not only implementation-dependent but data-dependent as well. In order to know whether a join will be efficient or not, you need to know things like relative sizes of the tables, which is not necessarily a language problem. I have worked with SQL implementations that had extensions to let the user annotate joins with relative sizes on each side, but I don't think that's quite what you mean. Lock ordering - again, good databases should have defined semantics (Postgres, for instance, does take locks in order when using ORDER BY), but I'll grant that this one could be stronger. That said, I think this is pretty niche. How often are you doing large multi-row transactions where lock order is a serious problem? If I have enough volume that deadlock is likely, I probably have enough volume that I want to be breaking up the process into a sharded or two-phase commit anyway. Ownership relations - I think this is a DDL problem rather than a SQL problem. Low-level language - I don't think you'll get a portable low-level language here (at least not for any definition of "low level" that's much lower than the SQL AST) because, again, the basics are implementation-dependent. What kind of scan is the base atom of a query? Well, it depends - is your database distributed? sharded? row-store-based? column-store-based? I do wish more open source database drivers would let you play with the AST in memory (Postgres has ways to print it out, but I don't think there's a good API). That would tend to solve the most significant problem raised in the article (composability) - plugging together SQL clauses automatically is hard, but plugging together subtrees can be much easier.

AmericanChopper 6y ago
> Predictable performance - this will always be not only implementation-dependent but data-dependent as well This immediately jumped out at me from the parent comment. It would be entirely possible to implement a query language where you specify a plan for your query. But then you’d immediately lose the “better than SQL” competition, because your complexity and maintainability problems would skyrocket. I’ve had to deal with this problem as an Oracle DBA, and it’s a complete nightmare. It starts with a statistics refresh ruining a couple of execution plans, so you start specifying them manually with the plan manager. Then it gets worse over time, because stats refreshes become a big risk and you don’t want to do them anymore. Eventually you get to the point where you pretty much only run verified plans. Then your verified plans slowly degrade overtime, because the underlying cardinality of every table is constantly changing. You’ve replaced the query optimiser with yourself, which is not only tedious work, but it’s simply not possible to do the job as well as any mainstream DB engine could.
- CaptainZapp 6y ago
  I wish that I could upvote your comment more than once, because this rings so true. There certainly are (rare) situations, where you need to provide hints in one form or another, but it's really a bloody nightmare to maintain and may completely bork, when you - say - upgrade to a new version of the database engine. I work with relational databases since the early 90s and can give you a no-bullshit money back guarantee that you (not you personally, obviously) are not smarter than the optimizer. Usually there are weird data patterns involved if you absolutely must provide hints. But basically: Don't do it!
  - mnsc 6y ago
    I think one of the challenges with sql is that beginner developers can create naive sql queries that "work" but are extremely complicated for the optimizer to "get right". So in some cases (talking from own experience) the developer can, with the use of hints, "be better" than the optimizer when the problem all along was the overall structure of the query. Edit: don't do it

JoshuaDavid 6y ago

Alternatively you can make a compiler from sql to your alternative, which would make it relatively easy to get the "I just want a familiar thing that works" people on board.

virgilp 6y ago
At that point you no longer provide a new language, but a new SQL query engine.

ridaj 6y ago

I don't know if it's necessary for a better language to do both OLAP and OLTP; it seems that they are used by different classes of users with relatively low overlap in language features. But I definitely agree that such a test as (1) would be a critical prerequisite to an improvement over SQL.

thanpolas 6y ago

3. The evolution of SQL should be an open standard, backed by academics and the community. Not a proprietary solution proposed by a company.