3 ms·

The tests in this read like someone who has only used one SQL dialect, and wanted another project to show off C++ abilities. Its full of bad advice that does li

by bsg75 9y ago

The tests in this read like someone who has only used one SQL dialect, and wanted another project to show off C++ abilities. Its full of bad advice that does little to help developers write better code. CheckJoinCount: “Consider splitting up the complex query into many simpler queries, and reduce the number of JOINs” - And offload the additional work to where, to the client? CheckSpaghettiQuery: Character count > 500, again "Split up a complex spaghetti query into several simpler queries" "Rewriting the query’s HAVING clause into a predicate will enable the use of indexes during query processing." - On what engines, and in what circumstances?

extrapickles 9y ago
The join one is particularly bad as it can make your query run much slower as running multiple queries is almost never a win. A check for the opposite (multiple queries when one is needed) would be better as then the query planner in the SQL engine can do a better job and you have fewer round trips. While the tool is a good idea, there need to be some proof that each of the patterns it's looking for are actually bad.
Twirrim 9y ago
> CheckJoinCount: “Consider splitting up the complex query into many simpler queries, and reduce the number of JOINs” - And offload the additional work to where, to the client? I'm sure, like many, I've seen both extremes of this. It seems like most developers I've worked with have stopped and generally thought about what they were doing with their queries, but there are those few that leave you bewildered. 1) Developer who produces a software package used by a fair number of counties across the US. They're essentially one of, if not the only, company working in that field. We acted as a go-between between the state and the software company. There are a set of standard reports included with the software, each of which amounts to little more than a very basic PDF wrapper around an SQL query. A huge, ugly turd of a query. One that they created through some SQL GUI, whenever they created the report. They hadn't profiled any of them or spent any time tuning them. For a large number of them it took quite a while, even with relatively small numbers of records. The state my employers worked with didn't do things on a small scale. In one query's case it would take upwards of 45 minutes to produce a report under optimal conditions. The indexes were great for the general workload, but lousy for producing the report via that query. They also clearly hadn't thought about the end user experience. Nothing on the page to indicate progress (hard to demonstrate progress when you're waiting on one monolithic query). It wasn't set up as a nice asynchronous report production either. While I didn't really have time for it, we wanted to be as helpful to the agency in question as possible, so I took a stab at one report, using a quick python script. Breaking the underlying query apart into a few distinct components, and paying attention to the indexes resulted in 1) producing the report in under a minute. 2) progress bars. Trying to wrestle that change in approach into the monolithic query was way too complicated for pretty much zero benefit. 2) At another company, a co-worker kept having to reject a deployment request from one particular developer (this was quite a while ago, the company used the waterfall method). Littered throughout their code was "SELECT * FROM table", followed by implementing all filtering logic inside their code. It got to the point where we celebrated the dev at least putting code forwards that included a WHERE clause: "SELECT * FROM table WHERE", even if we still had to reject it (due to the breaks-on-every-schema-change "SELECT *"). Over and over again we demonstrated that the database server was quicker than his code, and really hoped he'd eventually learn. That was one of the few times I really, really, really wished a team would either use an ORM, or fire the developer.
- jarulraj 9y ago
  Thanks for sharing these interesting anecdotes.
kuschku 9y ago
Yup, the "CheckSpaghettiQuery" is a recipe for disaster. I have a query here that runs on a raspberry pi running postgresql with 7 GB of data (chat logs in a postgres table, with fulltext indexing) in about 60ms. After splitting it up far enough that the system doesn’t complain anymore, the query takes around 30 hours (I know because that’s what I started with before I merged the queries). This is just a dumb idea in general. You can always filter the data faster in the database, and you reduce unnecessary network round-trips.
jarulraj 9y ago
I agree that executing fewer SQL queries is often better for performance. However, it is possible that an elaborate SQL query that has to use many joins, correlated subqueries, and other operations can be harder for the SQL engine to optimize and execute quickly than a set of more straightforward queries. Furthermore, these queries are simply hard to write, hard to modify, and hard to debug. I am hoping that these checks would serve as hints to developers to simplify and modularize complex SQL queries.

by bsg75 9y ago

extrapickles 9y ago

The join one is particularly bad as it can make your query run much slower as running multiple queries is almost never a win. A check for the opposite (multiple queries when one is needed) would be better as then the query planner in the SQL engine can do a better job and you have fewer round trips. While the tool is a good idea, there need to be some proof that each of the patterns it's looking for are actually bad.

Twirrim 9y ago

> CheckJoinCount: “Consider splitting up the complex query into many simpler queries, and reduce the number of JOINs” - And offload the additional work to where, to the client? I'm sure, like many, I've seen both extremes of this. It seems like most developers I've worked with have stopped and generally thought about what they were doing with their queries, but there are those few that leave you bewildered. 1) Developer who produces a software package used by a fair number of counties across the US. They're essentially one of, if not the only, company working in that field. We acted as a go-between between the state and the software company. There are a set of standard reports included with the software, each of which amounts to little more than a very basic PDF wrapper around an SQL query. A huge, ugly turd of a query. One that they created through some SQL GUI, whenever they created the report. They hadn't profiled any of them or spent any time tuning them. For a large number of them it took quite a while, even with relatively small numbers of records. The state my employers worked with didn't do things on a small scale. In one query's case it would take upwards of 45 minutes to produce a report under optimal conditions. The indexes were great for the general workload, but lousy for producing the report via that query. They also clearly hadn't thought about the end user experience. Nothing on the page to indicate progress (hard to demonstrate progress when you're waiting on one monolithic query). It wasn't set up as a nice asynchronous report production either. While I didn't really have time for it, we wanted to be as helpful to the agency in question as possible, so I took a stab at one report, using a quick python script. Breaking the underlying query apart into a few distinct components, and paying attention to the indexes resulted in 1) producing the report in under a minute. 2) progress bars. Trying to wrestle that change in approach into the monolithic query was way too complicated for pretty much zero benefit. 2) At another company, a co-worker kept having to reject a deployment request from one particular developer (this was quite a while ago, the company used the waterfall method). Littered throughout their code was "SELECT * FROM table", followed by implementing all filtering logic inside their code. It got to the point where we celebrated the dev at least putting code forwards that included a WHERE clause: "SELECT * FROM table WHERE", even if we still had to reject it (due to the breaks-on-every-schema-change "SELECT *"). Over and over again we demonstrated that the database server was quicker than his code, and really hoped he'd eventually learn. That was one of the few times I really, really, really wished a team would either use an ORM, or fire the developer.

jarulraj 9y ago
Thanks for sharing these interesting anecdotes.

kuschku 9y ago

Yup, the "CheckSpaghettiQuery" is a recipe for disaster. I have a query here that runs on a raspberry pi running postgresql with 7 GB of data (chat logs in a postgres table, with fulltext indexing) in about 60ms. After splitting it up far enough that the system doesn’t complain anymore, the query takes around 30 hours (I know because that’s what I started with before I merged the queries). This is just a dumb idea in general. You can always filter the data faster in the database, and you reduce unnecessary network round-trips.

jarulraj 9y ago

I agree that executing fewer SQL queries is often better for performance. However, it is possible that an elaborate SQL query that has to use many joins, correlated subqueries, and other operations can be harder for the SQL engine to optimize and execute quickly than a set of more straightforward queries. Furthermore, these queries are simply hard to write, hard to modify, and hard to debug. I am hoping that these checks would serve as hints to developers to simplify and modularize complex SQL queries.