I give a lot of talks about performance and in those talks, I tend to emphasize the architectural impact of your choices. There is a huge tendency to focus on micro optimizations to get the performance you need, even though you can usually get orders of magnitude higher performance by making architectural changes.
Good architecture can still benefit from micro optimizations, however, and it is sometimes really surprising to see by how much. During a routine performance review, we identified a particular scenario as a performance issue. Here is the code in question:
This is being triggered when you are using a parameterized query, like this one:
And here is the profiler trace for that:
That is kind of ridiculous, to be honest. About 18% of the client side query process went into generating the name of the query. Opps, that is not really something that I expected.
And here is the optimized version:
Basically, we prepare, in advance, the most likely names, so we can find them as cheaply as possible. The result for that particular operation is impressive:
So we almost halved the costs that we have here, but what is more interesting is what happens at higher level of the stack…
This is the query processing portion, and you can see that the pretty minimal saving of 187 ms in the AddQueryParameter method is translated to a far greater saving down the line. The overall cost went down by almost 30%.
The probable reason is that we are now allocating slightly less, we saved a few allocations for each query parameter, and that in turn translated to a far better overall performance.