4 ms·
The author didn't mention the reason the query is slow. I dare to challenge that the non-optimal query is something that a person with advanced SQL skills woul
by kroolik 6y ago
The author didn't mention the reason the query is slow.
I dare to challenge that the non-optimal query is something that a person with advanced SQL skills would do. A seasoned engineer knows that by joining two tables, you are looking at, worst case, NxM comparisons.
The problem is that you are joining the customers table with THREE other tables - the merged result of stores, employees and markouts. No matter how hard you try, you can't escape the fact that you are joining way more rows than just stores and customers.
I would call it a layman issue SQL beginners learn the hard way. Advanced SQL users know the very basics of the db they are working with.
- jiggawatts 6y agoI don't even understand the thought process that would have led to the non-optimal query. It would simply never occur to me to use anything other than UNION ALL for this kind of scenario, because it is the only appropriate translation of the english-language business request into a SQL query.
- dwd 6y agoAfter working 20+ years in the industry I'm always surprised by the aversion (or lack of thought) to draw up a Venn diagram on a white board to visualise the relation, let alone then write it out using set notation.
- avereveard 6y agoProbably the result of some early attempt at either sorting or removing duplicates
- robertlagrant 6y agoIt could be that the query was gradually modified. It's pretty simple to spot the union when the problem's being focused on, laid out nicely in a blog post, and the answer already given!
- ncallaway 6y ago> I would call it a layman issue SQL beginners learn the hard way. Advanced SQL users know the very basics of the db they are working with. So? Does every blog post have to be for expert users of the system? What's wrong with a blog post that explains mistakes that a beginner might make, and a method for mitigating it.
- kroolik 6y agoCompletely nothing is wrong with it! That's not what I'm arguing with. I'm challenging the statement in the Conclusion section: > Arriving at query #2 to get the combined results was the intuitive way of thinking through the problem, and something that someone with intermediate or advanced SQL skills could come up with. The issue in question is simple. It's a very good lecture for people early in their SQL journey. It would be a superb read if the author had dug into the details why it's slow. But advanced engineers should know that LEFT JOINing 6+ tables will be huge. I disagree it's a mistake an experienced person would do.
- ncallaway 6y agoAh, fair play. I must’ve missed that part of the article, or just didn’t have it top of mind when reading your comment.
- AmericanChopper 6y agoI think an advanced SQL user would probably notice that the entire problem here is caused by an incorrectly normalized schema. An order has to be placed by a customer, which is usually a type of person, but sometimes the customer person has a status of staff member, which in the example usecase was denormalized into a different table, resulting in the naturally inefficient access pattern.