9 ms·
Common Mistakes and Missed Optimization Opportunities in SQL
- Svip 7y agoWhile counting columns will not include NULL columns, how about counting joined tables? SELECT a.id, COUNT(b.*) FROM a JOIN b ON b.a_id = a.id GROUP BY a.id is not permitted in Postgres. Sure, I could just use COUNT(b.a_id) since that's what I join on, but a more complicated example might not allow for that. For instance if it was a virtual table.
- andreareina 7y agoAny reason a regular count() wouldn't work? SELECT a.id, count() FROM ...
- Svip 7y agoIt would, I realised after I read the replies, that I should have used the two joined table scenario as per my reply to your sibling.
- gnud 7y agoI'm sorry, if you want to count NULL-rows in b.* , how will it ever be different from just COUNT( * )? Maybe I'm misunderstanding what you're after?
- Svip 7y agoYou're right, it's a bad example, imagine if I joining two tables: SELECT a.id, COUNT(b.*), COUNT(c.*) FROM a JOIN b ON b.a_id = a.id JOIN c ON c.a_id = a.id GROUP BY a.id I want to know how many occurrences a_id has in both table b and c. Again in this simple example, I could just count on b.a_id and c.a_id, respectively, but imagine if b and c were complex virtual tables: JOIN (SELECT NULL AS foo, 1 AS bar UNION SELECT 1 AS foo, NULL AS bar) b ON b.foo = a.id OR b.bar = a.id This would be useful if we are aggregating data together, where essentially, there are two ways to join the data with the main table, and both columns can be null. Of course, in this example, you could count by going COUNT(b.foo) + COUNT(b.bar), but that's a bit awkward, or a column in table b you know to never be null. But what if you don't? And still have table c next to it? Yes, in all cases, there would be a way out. In the extreme case, you could wrap it in a virtual table, where you add a column that is just always 0 (not null), so you can count on it. It would just be neat if b.* was possible.
- gnud 7y agoI guess I didn't make myself clear either. If there's an inner join, then there is a matching row. It will be counted by a normal COUNT( * ). On an outer join, columns in a 'missing' row will be represented as NULL. You're saying you wish there were two NULLS, 'missing value in table null' and 'missing row in join null', and that you could count the first one?
- Svip 7y agoNo, not that complex. Just that I want to be able to specify which of my joined tables I am counting on in a null-safe way, in case the joining table could have null values in all its columns.
- deleted 7y ago[deleted]
- sixbrx 7y agoI might misunderstand, but I think the join just isn't the right approach for that sort of child record counting, ie. counting records from two or more independent child tables associated with your rows (if that is what you're wanting). You're grouping and counting after the three way join. That join will involve all combinations of child records between the two child tables associated with any given parent row (almost never what is wanted). So any given non-null thing you're counting from one child record will appear multiple times, = the number of child records in the other table associated with the parent row. I think you just want to use correlated subqueries to count the child records: select a.id, (select count(whatever) from child1 c1 where c1.a_id = a.id), (select count(whatever) from child2 c2 where c2.a_id = a.id) ... TLDR: You almost never want to join independent children to a common parent, use independent correlated subquery expressions instead.
- Svip 7y agoExcept, that's potentially super slow if the optimiser does not realise what to do. In its default state, it will make two table look ups for each row in table a. So that's 1+N*2 look ups compared to 3 look ups in my example. For little data, that's probably fine, but for a big database, it will be slow. However, the optimiser may be able to handle that? I know Sybase's and MSSQL's had trouble with it, but I've heard Postgres' might be able to.
- irrational 7y agoIn regards to formatting sql, I used to do it the way shown, but a coworker formatted the columns in the select with the commas in front. This seemed strange to me until I tried it. I realized that this solved the problem of sometimes a query would be changed and the last item in the select list would be removed, but the last comma would not be removed. Or, a new item was added to the end of the select list, but they neglected to add in a comma at the end of the previous last item. SELECT col1 ,col2 ,COUNT(col3) FROM t1 JOIN t2 ON ta.pk = t2.fk WHERE col1 = col2 AND col3 > col4 GROUP BY col1 ,col2 HAVING COUNT(col3) > 1
- lmkg 7y agoI hate hate hate the way it looks, but unfortunately it is objectively superior. This is why I prefer more languages go out of their way to make their syntax tolerant of optional trailing commas.
- robert_tweed 7y agoI don't bother with this but instead try to keep column names alphabetical. I try to do the same thing in other languages with property names, etc. This gives greater stability in version control, as it removes the temptation to change the order for subjective reasons. Even better if it can be automated with linting. This "fix" only helps with the case where new items are inserted at the end. By using alphabetic ordering you increase the chances that a new item will be inserted at the beginning or the middle, in which case it makes no difference where you put the commas. It only really helps at all because inserting new items at the end is common, whereas removing items from the start is rare, at least in SQL (all it really does is displace the dangling comma problem from the former case to the latter). However, always inserting new items at the end tends to lead to unintuitive ordering, which in turn leads to additional VCS churn when that becomes seen as technical debt. Commas at the start does give objectively better readability in one sense: it ensures they are aligned vertically. That makes it easier to spot errors at a glance. You might call this "ease of formal reviewability". However, in practice it seems to be worse for general readability, for the entirely subjective reason you already pointed to. Since code is typically read more often than it is written, it's important to optimise for that first. Since an error here will always cause a hard fail, it falls into a different category than say, omitting braces in a C-style if statement, which can introduce subtle bugs through bad merges. In the latter case, ease of formal reviewability has to take precedence over subjective aesthetics.
- tempguy9999 7y agoThis is a pretty trivial list. Useful for beginners I guess. I seriously take issue with "Reference Column Position in GROUP BY and ORDER BY" though. If it is restricted to ad-hoc (AKA messing-about) queries I'd be fine with it, but it won't be. Just don't do it.
- wfriesen 7y agoIt's especially egregious in the ORDER BY, since there you have the option of using column aliases.
- tempguy9999 7y agoI always, always forgot what column aliases I can use where. Thanks for the reminder.
- commandlinefan 7y agoAre you saying you can't use column aliases in group by? What version of Postgres are you using? I just tried it in 11.5 and it worked: # select cust_id as c, sum(avail_balance) as b from account group by c order by b;
- tempguy9999 7y agoInteresting. It doesn't work in MSSQL, and I understand that's correct (ie. isn't allowed) per the standard.
- commandlinefan 7y agoHuh - I guess I never thought about it. It makes sense to disallow it, though - column aliases are there to rename complex expressions, which you probably _shouldn't_ be grouping on anyway.
- tempguy9999 7y agoI think it's for other reasons (and grouping on expressions is quite reasonable anyway). It's (IIRC!) something to do with the situation of select x + y as x from ... group by x which x are we talking about? (Logically that example is crap because only the alias x makes sense, but something like that anyway).
- esnard 7y agoIn the "Avoid Transformations on Indexed Fields" part, I fail to understand how the example can work if you're applying the timezone computation on the right-hand side. I'm not familiar with MS SQL (I've only worked with MySQL / PostgreSQL), can someone explain me how it works?
- moron4hire 7y agoYour only failure is because it's just wrong. It's about the same as trying to change "if((a + b) > c)" to "if(a > (c + b))". If this weren't time zones, it'd obviously be "if(a > (c - b))", because you have to balance the equation by applying the same operation to boths sides. But because this is dealing with timezones, the offset of "b" is different depending on the value of "a", so we won't know what to subtract from "c" to get the right comparison. So the right transformation for this "gotcha" is not even possible.
- paulclinger 7y agoI think the advice will still work, but you'd need to switch from "named" timezones to number-specific one, so for example replace `PST` with `-08:00` and then apply the opposite conversion on the right side (as you and I suggested).
- paulclinger 7y agoI don't think it works the way author expects it to work, as the math is not correct. Think about `a+1 < 2` comparison. To remove +1, you need to change it to `a < 2-1`, not to `a < 2+1`; the operation needs to be transformed to the opposite one, which in this case would imply shifting the timezone in the opposite direction. If you are asking about the timezone shift applied to a date, I think the engine converts the date to 00:00:00 timestamp and then does the timezone conversion.
- jlarocco 7y agoI think the advice is correct, but the examples are not. When the transformation switches to the other side of the comparison it has to be inverted.
- Foobar8568 7y agoI would add to the common mistakes (should be generic, but I have more xp with sql server) : not indexing, most often, tables are not or poorly indexed. Implicit conversion can generate a lot of io/leads to poor perf or just not using indexes. Sql function:sorry but they are most often crap and useless, better to in-line or use TVF, and no its not code logic duplication. Read uncommitted unless you enjoy not reading rows, multiple times or half of a value (page split and/or LOB values)
- jve 7y ago> Read uncommitted unless you enjoy not reading rows, multiple times or half of a value (page split and/or LOB values) Please elaborate.
- Foobar8568 7y agohttps://www.mssqltips.com/sqlservertip/6072/sql-server-nolock-anomalies-issues-and-inconsistencies/ https://www.mssqltips.com/sqlservertip/6072/sql-server-noloc... The first time I experienced the lob issue was in 2009. At that time, I didn't know how to really reproduce the issue due to my lack of knowledge (which triggered also deep diving will to sql server internals _not my article,nor his but more generally Paul White /sql kiwi has published a load of great articles on internals, maybe some of the best technical articles I have read. )
- castorp 7y agoSQL functions can be inlined in Postgres And Postgres does not support read uncommitted to begin with. But I agree that implicit conversion is the root of a lot of evil
- SigmundA 7y ago> Sql function:sorry but they are most often crap and useless, better to in-line or use TVF, and no its not code logic duplication. Functions have helped me tremendously in SQL server, but you do have to know the issues, which can be taken advantage of to some degree. Code reuse is the obvious use case, but due to lack of inlining up to SQL Server 2019 meant you could reduce performance compared to hand inlined case statement or whatever. Hopefully now that functions can be inlined in 2019 this will be a non issue going forward They are an optimization barrier which can be a good thing. I have used to this my advantage to stabilize tricky queries that where using views for code reuse. The performance becomes consistent and predictable rather than going pathological on some databases even though it may be slightly slower on others.
- adamiscool8 7y agoSome of these have been learned through trial and error over the years, but a few were new and great to know. On a related note, is the MCSE the gold standard for SQL education? Have been looking for a way to brush up and formalize my SQL skills.
- gigatexal 7y agoEdit: “Don’t use an ORM” should be point 1
- ars 7y agoThe opposite. Point 1 should be don't use an ORM unless you don't know SQL. But you should know SQL so don't use an ORM. An ORM only works until the point where you need to join tables. As soon as that's needed the ORM just causes you endless trouble.
- gigatexal 7y agoEdited, i meant don’t use one.
- godshatter 7y agoI'd never run across coalesce before. I usually end up doing nested NVL calls if I'm trying to find the first non-null in a series of expressions (I'm on Oracle, btw). I've now added this function to my toolbox.
- oarabbus_ 7y agoCoalesce and NVL are synonyms for each other.
- godshatter 7y agoThey don't seem to be in oracle. Giving more than two parameters to nvl gives me an error but works fine with coalesce. Granted they are basically the same thing if you are giving both two parameters.
- kbenson 7y ago> 2019-22-11: Fixed the examples in the "Faux Predicate" section after several keen eyed readers noticed it was backwards. What abomination of a date format is this? I can only assume this is a bug, a typo, or an easter egg for those paying attention. Please let it be one of those. The last thing the world needs is people pushing yet another crazy date format into use.
- jandrese 7y agoIt looks like a typo. Hebrew date style is yyyymmdd[1]. [1] https://www.ibm.com/support/knowledgecenter/en/SSS28S_8.1.0/XFDL/i_xfdl_r_formats_he_IL.html https://www.ibm.com/support/knowledgecenter/en/SSS28S_8.1.0/...
- Dowwie 7y agoWould someone please confirm whether this article is misrepresenting a subquery as an inline CTE? It is my understanding that as of Postgresql 12, a programmer denotes a CTE as "AS MATERIALIZED", "AS NOT MATERIALIZED", or neither and allow the default operation to happen: the CTE subquery will default to inline if its result is used once. for reference: https://sudonull.com/posts/998-Important-changes-in-the-CTE-in-PostgreSQL-12 https://sudonull.com/posts/998-Important-changes-in-the-CTE-... Generally speaking, some clarification would be helpful!
- rgharris 7y agoI think the article and you are correct - the article is worded a little oddly though and ignores the fact that in Postgres 12 CTEs that are referenced multiple times are MATERIALIZED by default. Before Postgres 12 CTEs were always materialized so you did not get any query optimization benefits of CTEs acting like inline subqueries. After Postgres 12 all CTEs default to NOT MATERIALIZED if only referenced once or MATERIALIZED if referenced more than once. You can override via MATERIALIZED or NOT MATERIALIZED when defining the CTE. Their example is showing that you can let Postgres (before 12) optimize a CTE for you by writing it as an inline subquery instead of a CTE: SELECT * FROM ( SELECT * FROM sale ) AS inlined WHERE created_by_id = 1 But with Postgres 12 their "don't" example would result in an index scan without refactoring to the "do" example. Basically their advice on do vs don't applies to before Postgres 12. https://www.postgresql.org/docs/12/queries-with.html https://www.postgresql.org/docs/12/queries-with.html is pretty thorough on this
- Dowwie 7y agoThanks for confirming
- monkeycantype 7y agoI wish I could use ON for the selection criteria for the first table instead of a where clause: Select A.value, B.valuue from tableA A on A.id = 77 join tableB B on B.id = A.bId