4

I'm using Visual Studio 2010's built-in profiler to look at a section of poorly performing code. However, I'm seeing some results that don't quite make sense. Here is a shot of the report:

report screenshot

This seems to indicate that Regex.Replace is the bottleneck (and I should therefore try to reduce or eliminate this use as much as possible). However, this feels inaccurate, as I know that this particular section of code is making heavy use of the database, and thus I would expect the SqlCommand.ExecuteNonQuery to be at least a little higher in this report, if not more dominant than the Regex use.

So, my question is: is this profiler tool useless for anything involving database access, since the SQL work is being done by another process (i.e. the SQL server), and therefore I have to measure it some other way?

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
Matthew Groves
  • 25,181
  • 9
  • 71
  • 121

4 Answers4

3

The Visual Studio profiler has two modes of operation, sampling and instrumentation. In sampling mode, it does not draw samples when it is blocked, as for I/O. Because of that, it cannot show you any part of the call tree except that in which the leaves are doing raw CPU processing.

You are using sampling mode. Try the instrumentation mode, which operates on wall-clock time, thus it includes I/O.

And whatever you do, please ignore exclusive time. Only pay attention to inclusive time, as a percent of total time. You are looking for routines in your code that are active a large fraction of time, most of it spent in calling other routines, and you are looking for the calls you can do without.

P.S. I do this, which always works.

Community
  • 1
  • 1
Mike Dunlavey
  • 40,059
  • 14
  • 91
  • 135
  • "please ignore exclusive time". When I'm in instrumentation mode, or always? – Matthew Groves May 25 '11 at 16:02
  • 1
    @mgroves: Always. It's included in inclusive time. In real software like yours (as opposed to little 1-page algorithms that don't call functions) all the routines in your code will have negligible exclusive time. It's just eye-candy. The time-takers in your code are practically all function calls of various sorts. You need to find the ones that are active a large fraction of wall clock time. Time-takers not in your code you can't do anything about. – Mike Dunlavey May 25 '11 at 16:17
  • 1
    @mgroves: That, by the way, is why the compiler optimization will have negligible effect on your code :-) – Mike Dunlavey May 25 '11 at 16:32
  • 1
    Well, that explains one thing: when I do Project Euler problems, I find the exclusive samples to be very helpful, but Euler solutions are not "real" programs :) – Matthew Groves May 26 '11 at 14:32
  • 1
    @mgroves: Right. Theory and practice are both important, and are quite different. – Mike Dunlavey May 26 '11 at 14:47
2

In my opinion, your assumption about Visual Studio's profiler is correct, regarding the SQL work.

In order to check your SQL work, a better option would be to run SQL Profiler and track your queries. You can setup the SQL profiler, to show the execution plans of all incoming queries (...for the relevant database(s) ) from your application. That will indicate whether it's your SQL work or as the snapshot suggests Regex.Replace.

Hasanain

Hasanain
  • 925
  • 8
  • 16
1

The Visual Studio Profiler is a CPU Profiler. CPU Profilers are useful for analyzing CPU-bound programs or CPU-bound portions of IO-bound programs. Here are introductions to those terms:

Since your process is I/O-bound, a CPU profiler will not help you make your program run faster. As you suspected, you will have to profile the SQL query using other tools such as the:

or one of many other tools.

Rick Sladkey
  • 33,988
  • 6
  • 71
  • 95
1

You might also find the 'Tier Interaction Profiling' option useful for investigating SQL issues with the Visual Studio 2010 profiler. You can enable this option on page 3 of the Performance Wizard or as part of the session properties (see our blog for screenshots).

Once you have collected data using this mode, switch to the 'Tier Interactions' view and you will see detailed information about SQL calls, including call counts and elapsed times (picture from profiler blog)Tier Interaction View

Colin Thomsen
  • 1,806
  • 15
  • 11