Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question
    
The objection may be to the use of interpolated quotes (double quotes) and concatenation (.), which I've seen some programmers blame for performance costs. –  Bruce Alderson Nov 22 '10 at 5:34
    
Everything is required to be on 1 line? Hello scroll bar, good bye readability. –  mike30 Mar 5 '13 at 13:57
    
@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. –  Sean McSomething Mar 5 '13 at 17:29
    
Are any “in house” tools used to process the files? –  Ian Dec 10 '13 at 13:38
add comment

7 Answers

up vote 25 down vote accepted

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.

share|improve this answer
2  
Good idea, this would make a small change stand out very nicely in a source control diff. –  Carson63000 Nov 22 '10 at 3:45
    
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) –  Dean Harding Nov 22 '10 at 5:41
4  
Similar layout here, only difference being the leading comma, we have it at the end. –  G3D Nov 22 '10 at 12:02
3  
@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. –  Jon Hopkins Nov 23 '10 at 0:11
1  
This format also makes it easy to comment out single items during development and to use cut and paste to change the ordering. –  Chris Nava Feb 14 '11 at 5:30
show 6 more comments

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.

share|improve this answer
1  
Agreed, it all comes down to readability really. Performance etc doesnt get affected by this at all, its all just aesthetic. –  Christian Nov 22 '10 at 1:15
    
Agree that performance can't be a good argument. –  the Tin Man Nov 22 '10 at 1:17
    
I dont know.. just told me to keep it in one line ,maybe because they do –  Parhs Nov 22 '10 at 2:09
    
They're probably afraid to touch it if it's "legacy" code. Just slowly back away and everything will be fine. –  the Tin Man Nov 22 '10 at 2:32
    
Its fresh code ... –  Parhs Nov 22 '10 at 4:07
show 4 more comments

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.

share|improve this answer
add comment

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.

share|improve this answer
    
+1 for mention of heredoc –  Larry Coleman Nov 22 '10 at 19:53
1  
For PHP, nowdocs are the single-quoted variety (i.e. no variable substitution). –  Alan Pearce Nov 22 '10 at 19:57
add comment

My 2p worth:

SELECT
    a.field1
    ,a.field2
    ,b.field1
    ,c.field1
FROM
    tablea a
    INNER JOIN tableb b ON
        a.key = b.fkey
    LEFT JOIN tablec c ON
        b.key = c.fkey
ORDER BY
    a.field1 ASC
    ,b.field1 ASC
share|improve this answer
add comment

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

share|improve this answer
add comment

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.

share|improve this answer
    
+1, and I'd also consider making the query into a stored procedure –  Larry Coleman Nov 22 '10 at 19:54
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.