multi-stringbuilders vs readability

  • Thread starter Thread starter rodchar
  • Start date Start date
R

rodchar

hi all,
i have this dynamic cte sql statement that i'm building with a
StringBuilder. It is a very lengthy sql statement (70 lines of code). So at
different places in the sql string i have these conditional statements for
variability. For instance, at runtime a variable where clause can occur, or
multiple joins could be added, or even different fields can be selected. So,
then even more lines of code get added and less readable.

So I wanted to get some feedback on a direction i think may be more readable
but may degrade performance. i was thinking to put small place holders thru
out the string and then do all the conditioning below the statement and
replace the placeholders when i'm done. However, i've had to create more
instances of StringBuilder (about 5 new instances or more) objects just to
get it to work.

Now would the trade off be worth it (new instances of stringbuilder vs
readability) or are there any other ideas you know of (with the exception of
new technology like linq)?

thanks,
rodchar

thanks,

Now is the trade
 
Go for readability, and don't worry aboout efficiency. Creating five
instances of a StringBuilder is going to require a number of CPU cycles
which is completely insignificant when compared with the amount of time that
the Sql Server is going to need for processing the query that you are
building.
 
is there a performance difference between building the sql dynamically on the
C# side or dynamically on SQL Server side?
 
rodchar said:
is there a performance difference between building the sql dynamically on
the
C# side or dynamically on SQL Server side?
Click to expand...

The cost of *building* the query is negligible compared with the cost of
*executing* the query. After all, the build phase is only string
concatenation in memory, but executing the query requires parsing,
optimizing, cacheing, performing logical and/or physical page reads,
manipulating the resulting data, and delivering the response to the client.
Optimizing and tuning the query itself will have a much greater impact
on application performance than optimizing the code (either C# or T-Sql)
that constructs the query string.
Also, be sure to parameterize the query instead of concatenating into it
anything that is input by the users; this would put you at risk of a SQL
injection attack. This operation can be done either on the C# side or
dynamically in a stored procedure, but in both cases it is important to do
it.
 
Back
Top