SQLQueryStress

I want to recommend a good tool when you are optimizing your queries.

Often after a trace you will find that only a couple of your queries ends up taking the majority of the cpu.

Sometimes it's as easy to solve the problems by just adding an index, and sometimes there will be other solutions.

Whatever solution you choose, it can be a good thing to test your query.

The SQLQueryStress tool written by Adam Machanic, can be downloaded here http://www.datamanipulation.net/SQLQueryStress/.

SQLQueryStress is very simple although very powerful. You can use parameter substitution so you can execute your queries with different parameters. You can set the number of iterations but also the number of threads (clients).

The output from this is "cpu seconds", "elapsed time", "client seconds", "logical reads", and more.

So when you compare two queries and booth queries takes the same time to execute, you can easily see in this program if one of them use less cpu or maybe less IO over several executions and with different parameters.

Are you still using the repeater?

When asp.net first was released we quickly adapted to an object oriented way of creating and manage controls, instead of the old procedural asp ways. We started to use literals, datagrid and repeaters.

All the good functionality that came with the data binding controls also introduced performance problems. So – for example - instead of using the datagrid we started to use the repeater. However it’s easy to forget that even the repeaters data binding functionality comes with performance problems.

The repeater uses reflection internally, and for each object (text or image or whatever) in the itemtemplate it will create a new control, and if you have a rather complex repeater it will quickly add up and create a lot of controls. The creation of a control and all it’s rendering means extra cpu time, and also extra work for GC.

I have made some performance tests and found out that the repeater can be 30 times slower than using string concatenation with stringbuilder and then use response.write to ouput the result. And this is only the rendering time, we also have saved a lot of CPU time and therefore the application will scale better when we stress test it.

The problem with this solution is that it will give us headache when we need to add/edit the html or add an attribute of a control or something. What we can do is to create a usercontrol that has a public collection (of some sort), that we can set, and then use inline c# with other custom controls. This solution will still give us the same performance benefits.

Example
<%
for (int i = 0; i < myCollection.Count; i++){
myCustomControl.myAttribute = myCollection[i].SomeProperty;
%>
<div class="<%=myCollection[i].myDivClassProperty %>">some html</div>
<uc:myUc id="myCustomControl" runat="server" />
<%
}
%>

This way we will keep the rest of our code clean and use this optimized version of data rendering where you have a lot of posts to render or is one of the most viewed pages in your application.