Code formatting SQL queries

Question

Should I break SQL queries in different lines? For example in the project I am working on, we have a query that is taking 1600 columns! 1600 + tab chars. I wrote queries like this:

 "SELECT bla , bla2 , bla FROM bla " . 
 "WHERE bla=333 AND bla=2" . 
 "ORDER BY nfdfsd ...";

But they demanded me to put them in one line and said that my style is bad formatting. Why it is bad practice?

• Bruce Alderson: The objection may be to the use of interpolated quotes (double quotes) and concatenation (.), which I've seen some programmers blame for performance costs.
• mike30: Everything is required to be on 1 line? Hello scroll bar, good bye readability.
• Sean McSomething: @BruceAlderson Sounds like one of those early 2000s "Housewife discovers 3 simple tips to optimize your PHP" articles. The real red flag with double quotes and/or concatenation comes when you start inserting variables without properly escaping them creating SQL injection attacks.
• Ian: Are any "in house" tools used to process the files?
• Tulains Córdova: Why is it so hard to understand that as long as you are getting paid to code, you shold write, clean, neat, orderly code ?

Answer 1

For source control reasons, we have linebreaks after every where clause, or comma. So your above turns into

SELECT bla 
 , bla2 
 , bla 
FROM bla 
WHERE bla=333 
 AND bla=2
ORDER BY nfdfsd
 , asdlfk;

(tabbing and alignment has no standard here, but commas are usually leading)

Still, makes no performance difference.

• Carson63000: Good idea, this would make a small change stand out very nicely in a source control diff.
• Dean Harding: Pretty much the same formatting as I use, though I usually put all of the select list on a single line (or multiple lines if there's a lot of columns)
• DBlackborough: Similar layout here, only difference being the leading comma, we have it at the end.
• Jon Hopkins: @m.edmondson - Diff-ing between version in source control highlights changes on a line by line basis. With this format each line contains a single bit of information - a column name, a table name, a join or order clause - which means that the diff will point right at what's changed, not just to a line with many things on and leave you to work out what's different.
• Chris Nava: This format also makes it easy to comment out single items during development and to use cut and paste to change the ordering.

Answer 2

A query that is 1600 columns sounds like it needs some serious review by a good DBA.

If a query is complex I'll wrap it. If it's straightforward I'll leave it as a single line unless it's going to be too long, then I'll start wrapping it again.

It's all about manageability and comprehending what it is supposed to do so wrapping or not wrapping can be decided on the fly, unless your organization has some code formatting rules about it.

Re: it being bad coding practice. Hardly! It's very good practice. There are no good reasons I know of to use a query that long, and many good reasons to reformat it. As I said before, a skilled DBA probably needs to work on it.

• Christian: Agreed, it all comes down to readability really. Performance etc doesnt get affected by this at all, its all just aesthetic.
• the Tin Man: Agree that performance can't be a good argument.
• GorillaApe: I dont know.. just told me to keep it in one line ,maybe because they do
• the Tin Man: They're probably afraid to touch it if it's "legacy" code. Just slowly back away and everything will be fine.
• GorillaApe: Its fresh code ...

Answer 3

The only advantage of single line queries that comes to mind is that those queries may be somewhat easier to grep for. Other than that, though, I am stumped. Personally, I prefer the more readable, split up queries.

Answer 4

Multiline comments are good, almost vital when dealing with large volumes of SQL. And if your programming language has heredoc quotes, it's even better (as many editors can highlight SQL syntax in them).

Example:

$a = SQL<<<
 SELECT a, b, c, d
 FROM Foo f
 WHERE f.a = ?
SQL;

When working with queries of dozens of lines (or hundreds) both the indentation and whitespace make the text workable.

• Alan Pearce: For PHP, nowdocs are the single-quoted variety (i.e. no variable substitution).

Answer 5

It seems this is specifically about defining a big query inside a programming language of sorts, seeing you put the query inside a string literal and concatenate it.

If it's a compiled language, it should make no difference at all - one of the first optimizations the compiler would do is to automatically concatenate the string literals together, so you end up with a big string anyways.

As for the syntax, you should actually consider moving the query outside of your code - store it in a separate .sql resource file, and have your software read that file. Use prepared statements for the variables, if it's not a query that's built dynamically (i.e. where-clauses etc added depending on certain parameters). If it is built dynamically, you could add in replacement variables of your own, inserting extra parameters where and when needed.

As for the 1600 columns, I seriously recommend building a view for that, so instead of

SELECT column1, column2, .... column1600 from X where Y

you'd get

SELECT * FROM viewX WHERE y

Much more concise in your own code.

• Larry Coleman: +1, and I'd also consider making the query into a stored procedure

Answer 6

I often use the format put forth by @glasnt to troubleshoot a complicated query, however usually have queries in a single line.

This might not answer your question, but I'd also strongly suggest breaking down your query into smaller queries. Obviously this depends on the query, but the more clauses and joins you add to your query - the less the SQL engine is able to optimise your query.

Your database vendor should have tools like MySQL's EXPLAIN (or MSSQL's SHOWPLAN_ALL setting) which will show you what the database is doing behind the scenes to optimise your query, every time the database has to create a temporary table or some such, you're adding huge delays when you're talking about multiple concurrent users.

By moving what might seem like trivial logic out of the SQL and into your code, you can provide dramatic performance increases - SQL is great at simple operations.

The obvious benefit to this as it might relate to you, is that your queries are much less complex and easy to read - easy to manage (not>1600 columns), and faster. Definately an all-round win.

Hope this helps :)