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?

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.

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.

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.

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.

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 :)