31–40 of 98 posts
Wow this just highlights why I absolutely hate working with raw SQL be it Postgres or MSSQL.
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.
Earlier quoted context omitted.
That was my reasoning when I answered, but I had missed the fact it was a GROUP BY, which means you can't just filter after the fact. Edit: In other words it was 100 or 10 aggregated rows. A extra WHERE clause will change the values of each of the rows rather than just filter the rows from 100 to 10. (Which a HAVING clause would do.)
It's much simpler than that. The first query only has to reference the index because the data is IN the index. The second query has to access the table. That's it. It's called a covering index.
The trick is the fact that the GROUP BY means that "It used to return 100 it now returns 10" is a red herring, it still has to read every row to make up those 10.
Wow this just highlights why I absolutely hate working with raw SQL be it Postgres or MSSQL.
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.
Earlier quoted context omitted.
It's much simpler than that. The first query only has to reference the index because the data is IN the index. The second query has to access the table. That's it. It's called a covering index.
But if it wasn't for the GROUP BY, filtering 100 results of a million down to 10 results wouldn't change performance much even if you read every column of every row of those 100. The trick is the fact that the GROUP BY means that "It used to return 100 it now returns 10" is a red herring, it still has to read every row to make up those 10.
SELECT date_column, count(*)
FROM tbl
WHERE a = @a
AND b = @b
GROUP BY date_column;
SELECT a, date_column
FROM tbl
WHERE a = @a
SELECT a, date_column
FROM tbl
WHERE a = @a
AND b = @b
The fact that something like this is necessary just goes to show you how much of a failure SQL is compared to what it was supposed to be. Remember the promise of a "declarative language" where you just had to tell it what you want, and it took care of the details?
I reach the opposite conclusion. These are premature optimization tweaks which means tons of people are using SQL successfully without knowing them. You could argue CSS is a failure, because designers don't know that #sidebar .widget is slower than .widget I'd argue that it is evidence CSS is a success because it makes no real difference, and people opt for the more readable solution.
The fact that something like this is necessary just goes to show you how much of a failure SQL is compared to what it was supposed to be. Remember the promise of a "declarative language" where you just had to tell it what you want, and it took care of the details?
The fact that something like this is necessary just goes to show you how much of a failure SQL is compared to what it was supposed to be. Remember the promise of a "declarative language" where you just had to tell it what you want, and it took care of the details?
Think of the first question. If you're going to use a function to transform data, you're going to take a performance hit vs. just doing and indexable operation against the data.
If you want a facility that will magically figure out what you want when you are unable to express what you want, good luck. That's unicorn territory.
Wow this just highlights why I absolutely hate working with raw SQL be it Postgres or MSSQL.
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!)
Earlier quoted context omitted.
I reach the opposite conclusion. These are premature optimization tweaks which means tons of people are using SQL successfully without knowing them. You could argue CSS is a failure, because designers don't know that #sidebar .widget is slower than .widget I'd argue that it is evidence CSS is a success because it makes no real difference, and people opt for the more readable solution.
Do you have a good link for numbers on this css point? I would expect speed differences, but my naive view would be that they wouldn't be much.
I'm surprised people didn't score better on this, it's a very simple concept. :-/