News for hackers. Stuff that matters.
Re:Results of the SQL Performance Quiz by sergiotapia on Thursday February 20, 2014 @11:14AM (#7271548)
Wow this just highlights why I absolutely hate working with raw SQL be it Postgres or MSSQL.
Re:Results of the SQL Performance Quiz by sz4kerto on Thursday February 20, 2014 @11:48AM (#7271803)
SQL is extremely powerful. Whenever something comes up and says that it's going to hide the complexity of SQL/raw DB access, it's lying. :) I can work well in many cases, then it bites you in the ass later. That's not a reason to not use ORMs for example, but without knowing how the underlying DB works you can have problems. Abstractions are leaky, always. Same applies if you work in a high level language and you don't care/know how caches work; if you use HTTP requests and you have no idea how HTTP works, and so on.
Re:Results of the SQL Performance Quiz by einhverfr on Friday February 21, 2014 @03:09AM (#7275928)
Indeed. I would actually go further and say it can be a reason not to use ORMs though. But the rest of this is to bolster your point and hopefully give you some more ammo for your case.

One of the reasons I started working on the PGObject Perl framework[1] was because I found that if I hand-coded the SQL and programmed around that, I was more productive, and had fewer bugs.

The approach taken (based on our experience with LedgerSMB) is to focus on rules for mapping object oriented calls to stored procedure calls. The framework is of course PostgreSQL-only (and relies a lot on system catalog lookups).

My experience, backing what you are saying, is that if you know what you are doing, and you write good, maintainable stored procedures/UDF's then the power is totally worth it. Unfortunately, there are still some obstacles, and most of these are tooling.

I think the hatred of SQL from the parent (in the context of this quiz) is that if you separate performance from result, as SQL does, then you have to think about performance differently. In theory you code for results first and then tune performance later. In practice, this only gets you so far and you need to code with some knowledge of performance limitations. I know I have been bitten as badly as anyone else.

But, the tradeoff is that when I write more of my code into my queries, I get better performance, fewer bugs, and a significantly smaller codebase. The knowledge is worth it warts and all.

(When I have some really nice code samples, I will submit to HN)

[1] The official repos are the repos with names starting with "PGObject" at https://github.com/ledgersmb

Re:Results of the SQL Performance Quiz by daigoba66 on Thursday February 20, 2014 @12:17PM (#7272064)
Actually I think this highlights why it is important to understand and work with raw SQL when critical performance matters.

I'll let my ORM generate basic CRUD statements; the kind of statement which typically only involves PK lookups. But I find it better to write my own SQL statements for important queries, and of course let the ORM map the result set into objects. In fact, with enough experience, I've also found it _faster_ to just write a query in SQL versus learning yet another DSL or query builder API.

Re:Results of the SQL Performance Quiz by bhaak on Thursday February 20, 2014 @11:30AM (#7271665)
How do you think an ORM will help you avoid those performance issues? Unless the ORM isn't very feature-rich but then you've got other problems anyway.

SQL's syntax is ugly because it was designed in the 70s where some people had quite different ideas what a DSL should look like (hey, COBOL, you are guilty, too!)

Re:Results of the SQL Performance Quiz by sergiotapia on Thursday February 20, 2014 @11:46AM (#7271792)
Oh I don't have any false illusions. I know ORMs fail most of the time when you need very specialized queries - and I know how to do them - but I still hate them.

They're a pain in the ass.

Re:Results of the SQL Performance Quiz by dreamdu5t on Thursday February 20, 2014 @12:45PM (#7272307)
Ugly compared to what query language of equal power and expressiveness?
Re:Results of the SQL Performance Quiz by saosebastiao on Thursday February 20, 2014 @01:32PM (#7272646)
Datalog.

I honestly love SQL...I consider it a noble language:) It is the only declarative language out there that is still used today, and there are some solid reasons for that.

However, I believe the biggest mistake that was made in the creation of SQL was to try to emulate natural language flow patterns. This makes it hard to format and edit, and when people optimize towards writability, they kill readability (such as the use of leading commas. The lack of any real standard indentation practice makes it cooperative work frustrating. And the natural language flow actually confuses the writer/reader into thinking that order of operations is linear, as opposed to the FROM->WHERE->GROUP BY->HAVING->SELECT->ORDER BY->LIMIT order. This makes for really annoying logical bugs, such as how filter conditions in the where clause on a left-joined table can effectively turn your left join into an inner join.

Re:Results of the SQL Performance Quiz by einhverfr on Friday February 21, 2014 @03:13AM (#7275938)
The biggest mistake in SQL was in using NULLs to mean three different things (no record found in an outer join, vs not applicable, vs not yet known). Fortunately with some types (varchar) on sane db's you can use dedicated empty values ('' for varchar) for not applicable, but you are still stuck with the other two as possibly conflicting.

This is actually a big issue because your ability to have complex declarative constraints goes up with table width, so breaking off potentially nullable columns also removes them from being available for cross-column check constraints.

Re:Results of the SQL Performance Quiz by bhaak on Thursday February 20, 2014 @01:27PM (#7272622)
Expressiveness? SQL is quite verbose for what it does achieve and if you look at an SQL with some JOINs, I'm not sure you want to uphold that opinion of its expressiveness.

Don't get me wrong, I have no problem with SQL as such but it has some shortcomings. Probably most things can be blamed on its age.

Why is the syntax of INSERT and UPDATE statements unnecessarily distinct? They do almost the same thing. Why are the values and column names in the INSERT statements' syntax separated and not with an UPDATE statement.

Why are there keywords that consists of more than one word? (Maybe the same ill-guided idea as with COBOL that being able to "read" SQL would make it easier to write/maintain?)

The syntax of SQL is in many places not tight enough, so you often have to hunt down a missing comma or typo, because the SQL parser is just not able to find out what you've done wrong.

Re:Results of the SQL Performance Quiz by 9876543210 on Friday February 21, 2014 @05:06AM (#7276208)
So, yeah, but, like UPDATE can employ a WHERE clause, to apply conditional logic, whereas INSERT, not so much. You insert one record to a table. As long as you know the table? Boom. Done. Oh, but now you'd like to change some records in the table? Well, that's great, but which ones? Tell me where?

I think the whole readable natural language premise is a neat idea, but much in the same way that natural language can be easily complicated, so too, with SQL. Heap abstractions of logic and arithmetic on top, and it gets even worse. But it's still a cool idea. If it works out in your favor, you'll get self documenting code. But alas, there is no floor or cieling to the complexity one can introduce, be it deliberately or accidentally. And not only is it the query itself (or the author) that might over complicate matters. The data schema, defined by the DDL, can easily induce unwanted complexity in subtle ways, and this might be a problem deliberately placed beyond the reach of the person attempting to read or change the data locked within.

As for complaints about parsers, that's not a deficiency of the language. It's a deficiency of the tools used. If the development environment isn't presenting problems to the developer in a convenient manner, then the utility sucks. Get a new one. Don't blame the text-mode clients though. They are just as bare-bones as a command line, and deliberately so. At a certain level, SQL can be regarded the same way a shell script might be regarded, in terms of possessing the convenience of (or lack thereof) a basic TTY command interpretter. But there's no real reason why it should be impossible to create a SQL script authoring tool, which provides syntax validation as convenient as any compiled language might employ, when communicating compilation errors to the developer.

Re:Results of the SQL Performance Quiz by marcosdumay on Thursday February 20, 2014 @12:01PM (#7271926)
What do you prefer? ORMs? Those keep the same set of problems, and add an entire new class of them.

The reason that you don't notice is that most of the times this stuff does not matter at all. Not because ORMs solve anything.

Re:Results of the SQL Performance Quiz by walshemj on Thursday February 20, 2014 @11:28AM (#7271654)
Really :-) try working with the preceding tech which in my case was raw ISAM where you had to build your own ACID on top of a very basic API.

doing Map reduce with non of your fancy pants hadoop malarky

Ps and we had to build our own build system using CPL (prime JCL)