5 ms·

These arguments are correct and repeated for many many years. Nevertheless in reality most of the stated reasons have almost no real practicability. They re co

• teraku 6y ago
  This is supported by some databases and DWHs. I work with BigQuery and they do have that syntax
• pqb 6y ago
  As other commenter mentioned the BigQuery supports the `SELECT * EXCEPT(column names...) FROM mytable` syntax. Also, it is worth noting the `SELECT COUNT(*) FROM mytable` in BigQuery is faster and cheaper than `SELECT COUNT(always_truish_column) FROM mytable`.
  • tpetry 6y ago
    This performance optimization is valid for most databases: COUNT(column) means in reality COUNT(column is NOT NULL), so if the column can have nulls the values need to be checked. But in most databases there is no difference in performance because the query optimized is intelligent enough to check whether the column can have null by definition and switches to COUNT(*) if there can't be nulls. In some databases COUNT(primarykey) is even faster, but these are all optimizations most often not needed, so micro optimizations as the query optimizer is intelligent enough to choose the best logic ;)
    • Zobat 6y ago
      I was told years ago that count(1) was faster than count(*) and have been using that blindly and without questioning since (MSSQL user), although I can't remember last time I submitted code that did that. Often write it when examining something in the database though.
      • jeltz 6y ago
        In PostgreSQL count(1) is slower since PostgreSQL does not optimize count(constant) to count(*). EDIT: PostgreSQL might be able to optimize that with LLVM but for short running queries it won't.
      • tpetry 6y ago
        These specific tricks do exist for every database. But there is no reason for mssql to not handle count(*) and count(1) the same, they are the same. Maybe they are already the same and it‘s just a very old trick which has been replaced by a better query optimizer.
  • londons_explore 6y ago
    Bigquery is surprisingly dumb when it comes to rearranging your query to be able to execute it faster. It seems to entire focus of the bigquery team is being able to parallelize all the work to be done, rather than seeing how they could do less work in the first place. The former is clearly interesting when you bill per Gigabyte of data processed... I suspect that stems from the fact it doesn't have the pregenerated statistics that other databases have, and therefore there isn't much scope to make a smart query optimizer.
    • deleted 6y ago
      [deleted]
    • _dark_matter_ 6y ago
      Just to be clear, BigQuery bills per byte scanned. So how much work they have to do on the data is almost irrelevant from a cost perspective. There are improvements to decrease data scanned, for example, good sort orders and partitioning. BQ supports predicate pushdown for both of these.
  • jeltz 6y ago
    The second part is due to that count(*) would actually be better written as count() if it was not for the weirdness of the SQL standard.
    • jimktrains2 6y ago
      Count(*) makes sense as you can count(column), count(column1, column2, ...), Count(distinct column), and count(distinct column1, column2,...) Just like in a select.
      • ants_a 6y ago
        I don't know of any database that supports multi column count. But even if one did, count(*) does not have the semantics that you would expect from that. If the semantics were equivalent to expanding the column list then count(*) with a single nullable column would return the number of non-null values. But instead it is defined to return the number of rows regardless of context, being equivalent to count() or count(1).
        • jimktrains2 6y ago
          I'm not entirely sure why I thought I've done a count with multiple columns before because it does indeed seem not to be a thing.
      • deleted 6y ago
        [deleted]
  • deleted 6y ago
    [deleted]
• meepmorp 6y ago
  >most applications are CRUD and based on an ORM Do you actually believe that most CRUD apps use an ORM?
  • stevenhuang 6y ago
    Yes? Most web frameworks do include some form of ORM.
    • hamburglar 6y ago
      The existence of an ORM in your framework doesn’t mean you necessarily use it. I’ve worked on several projects where the ORM is acknowledged as very handy when you are doing a prototype/poc and for really simple things, but for anything even a little complex you should be writing your own SQL by hand so you know exactly what the db is doing and the cost of it. I personally have come to the conclusion that letting an ORM generate nontrivial SQL for you is actually harder than just writing the SQL yourself, because you have to think “how do I get this higher-level abstraction to generate the actual SQL I want?” and at that point you may as well just write SQL.
      • acidbaseextract 6y ago
        I mean, 90% of my application is simple things. I mostly use the Django ORM and even more complicated joins, grouping and subqueries are predictable. I'm curious what ORM people are using that obscures the queries that are being emitted so much. For the 10% where things are complicated I will happily write SQL. I wouldn't say the ORM is "for prototypes/pocs", but rather it should be the default and raw SQL is "complicated stuff only".
      • dodobirdlord 6y ago
        I've become convinced that the best ORM approach is for the ORM to generate code from SQL and not the other way around.
  • zbentley 6y ago
    Anecdotally yes, most small to medium sized CRUD apps do use either an ORM or a query builder so abstracted as to be effectively an ORM.
• EugeneOZ 6y ago
  My experience is quite contrary :) I prefer to not use ORM (even if I wouldn’t mind, clients often ask me to work with the DB directly, without ORM), and every selected column matters - in terms of performance and amount of transferred data. Some fields are TEXT/BLOB and sending thousands of them “just because” is not cool.
• branko_d 6y ago
  It's not jut about large columns. When fetching from views, selecting unneeded columns can trigger unneeded JOINs. Assuming no JOINed column is mentioned in the select list (either explicitly or through *), the query optimizer can eliminate the OUTER JOIN, or INNER JOIN when it is known that the referenced row exists (because there is a FOREIGN KEY). This is mentioned in the article as "Oracle’s join elimination transformation", but (most?) other databases can do it too.
  • reshlo 6y ago
    Wouldn’t that only apply if the query or the view features a DISTINCT or GROUP BY, and no COUNT()? Otherwise you’d still need to know how many rows in the other table match the predicate, so you’d still need to do the join, right?
    • branko_d 6y ago
      I guess it depends on the join predicate. For a vanilla equi-join, no grouping is required. Let's say we have the following two tables and a view that joins them: CREATE TABLE PARENT ( PARENT_ID int PRIMARY KEY, PARENT_NAME varchar(255) ); CREATE TABLE CHILD ( CHILD_ID int PRIMARY KEY REFERENCES PARENT, CHILD_NAME varchar(255) ); CREATE VIEW PARENT_CHILD AS SELECT * FROM PARENT JOIN CHILD ON PARENT_ID = CHILD_ID; Now if you select any fields from PARENT, the query plan will physically access both PARENT and CHILD. For example: SELECT * FROM PARENT_CHILD; SELECT PARENT_ID, PARENT_NAME FROM PARENT_CHILD; But the following would physically access only CHILD (because it knows that for any existing CHILD row, the corresponding PARENT row must also exist): SELECT CHILD_ID, CHILD_NAME FROM PARENT_CHILD;
      • reshlo 6y ago
        What I’m suggesting is that the following still has to access CHILD despite only projecting columns from PARENT. If I understand it correctly, not projecting any columns from a joined table doesn’t mean it won’t be accessed, even for vanilla equi-joins. SELECT PARENT_ID FROM PARENT_CHILD;