Ok so im working on a query and this will be the first time im really testing any thing so i figured i would set up a quick performance test between cross tabs and a pivot to test out a possible methodology for my actual tests. The bellow code shows the timing method ill be using for the test.

Check the logical reads with profiler, it's a good chance that it is the same for all three examples.Check the execution plan and see the differences (if any).The methods you use are all alike, there is almost no difference in performance.You should not measure just one execution, but at least 3 executions per each method and take the average elapsed time and logical reads.Logical reads is more reliable parameter (than elapsed time) you should look at to see if one method is better than other.Other methods of optimization will probably give you a better gain in performance. For example, a covering index.

My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

so its looking that on by dual core box i did not hit any parallelism (ill have to look at the query plans tomorrow) and if i had cross tabs would have been faster. ill also have to add in statistics io to the testing or run the profiler at the same time im running the queries.

glad to know i am at least on the right track for a test methodology as well.

My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.