Take a fairly typical SQL insert query with parameters in a C# project using NHibernate; it might be written like this:
Session
.CreateSQLQuery(
@"INSERT INTO my_table(COL_A, COL_B, ...,
..., COL_M, COL_N)
VALUES (:VAL_A, :VAL_B, ....,
..., :VAL_M, :VAL_N)")
.SetParameter("VAL_A", "input for A")
.SetParameter("VAL_B", "input for B")
(...)
.SetParameter("VAL_N", "input for N")
.ExecuteUpdate();
This feels fairly well organized and easily readable to me, which I like, but I'm curious about the whitespace included with the query itself. We could remove it by writing some variation of the following instead, which I have seen in a few cases. This requires a little more effort to write though, and may affect readability:
Session
.CreateSQLQuery(
@"INSERT INTO my_table(COL_A, COL_B, ..., " +
@" COL_M, COL_N)" +
@" VALUES (:VAL_A, :VAL_B, ....," +
@" :VAL_M, :VAL_N)")
.SetParameter(...)
My question then is whether there is any point in doing anything like this at all?
I have a vague recollection of hearing about this from years back; the idea that we should limit the amount of whitespace as it might affect the performance of queries. My hunch is that the effect of this (if any) would be negligible, and that it would not be worth the cost, but it would be interesting to gain a little more insight.
Changing whitespace does not directly affect Oracle SQL performance, but it can indirectly affect performance by avoiding SQL performance features. But even if there is a weird reason why this one query suffers due to adding space, in general you should not stop formatting your SQL.
Some SQL optimization features, like SQL profiles and outlines, are based on an MD5 hash of the SQL statement text. Any change to the statement, even adding or removing a space, will generate a different hash. If a DBA or the tuning advisor task has created a SQL profile that improves the performance of a statement, that profile will no longer work after any trivial change to the query.
A related problem occurs with SQL reoptimization. The optimizer learns from its mistakes and can change the execution plan the second or third time the statement runs. Rarely, the optimizer will learn the wrong lesson and a SQL statement will be slower on the second or third execution. In that case, adding or removing a space will make the query run faster - at least initially.
These behind the scense optimizations are powerful tools for improving query performance without changing queries. Unfortunately, they also tend to create cargo cult programming myths; someone makes a trivial change, performance improves, and they think they have stumbled upon a cryptic performance secret. This is probably how the whole "use count(1) instead of count(*)" nonsense started.
Keep using all the whitespace you need to properly format your queries. If you run into a real, measurable problem, compare the execution plans to see what changed and why. If a coworker insists on removing whitespace as a general rule, ask them for a reproducible test case to prove their claims.
No. SQL statements are effectively "compiled" before they are run and whitespace has no tangible effect on the outcome, unless the lack of it introduces a syntax error