In light of the class Debugging section we just wrapped up, I think it’s good to point out the CLR Profiler as an excellent tool for examining what your .Net application is doing under the hood. The CLRProfiler takes an invasive approach to monitor what your program is doing on the managed heap – check out Gregor Noriskin’s articles to download the tool and for specifics.
I was using CLRProfiler to explore my data access code, and I set up the following test to compare OLEDB and SQLClient data provider performance:
const SQL as String = "SELECT ContactName FROM Customers"
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'OleDB
for x as integer = 0 to 10
listbox1.Items.Clear()
dim cn as new OleDbConnection( AppSettings( "DSNOLEDB" ).ToString() )
cn.Open()
dim cmd as New OleDbCommand( SQL, cn )
dim reader as OleDbDataReader = cmd.ExecuteReader( CommandBehavior.CloseConnection )
do while( reader.Read() )
listbox1.Items.Add( reader( 0 ).ToString() )
loop
reader.Close()
x+=1
next
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
'SQLClient
for x as integer = 0 to 10
listbox1.Items.Clear()
dim cn as new SqlConnection( AppSettings( "DSNSQLSERVER" ).ToString() )
cn.Open()
dim cmd as New SqlCommand( SQL, cn )
dim reader as SqlDataReader = cmd.ExecuteReader( CommandBehavior.CloseConnection )
do while( reader.Read() )
listbox1.Items.Add( reader( 0 ).ToString() )
loop
reader.Close()
x+=1
next
End Sub
Nothing fancy, just a good ol’ datareader iterating over results of a SQL query and filling a listbox. Everything you read praises the SQLClient for it’s efficiency . . . so when I ran the CLR Profiler I figured I’d see SQLClient looking better than OLEDB. Here is a glimpse of what I initially saw (numbers averaged over many repetitions):
- OLEDB method allocates 514 KB to the managed heap, 2 generation 2 garbage collections runs
- SQLClient method allocates 722 KB to the managed heap, 3 generation 2 garbage collections runs
On first glance, one might say the smaller heap allocated memory for OLEDB and the fewer GC cycles (which follows from consumption of less heap space!) paints OLEDB in the more favorable light. SQLClient more efficient? Does the SQLClient data provider, the figurative Emperor, have “no clothes?” On closer examination, I found the following in the CLRProfiler’s Histogram for Allocated Objects (over 91KB is the largest metric the grid displayed):
- OLEDB: 13% of objects exceed 91 KB.
- SQLCLIENT: 8% of objects exceed 91 KB.
A significantly higher percentage of OLEDB objects are of the large variety, and I'll make a supposition about this later. The CLRProfiler just tells us the managed footprints our program creates, so I added a timing mechanism to find the average duration the methods took:
- OLEDB: 43 Milliseconds
- SQLCLIENT: 31 Milliseconds
This is for a simple query, one can imagine how much faster SQLClient is when we attack a more complicated data operation and we bring Stored Procedures into the picture, maybe even some advanced T-SQL . . .
Let’s review. If I’m reading the CLRProfiler and Performance Timing tea leaves correctly, the SQLClient provider has a larger managed heap memory footprint (about 1.5 times larger) than the OLEDB provider. This is understandable since OLEDB is an older technology implemented before the days of the CLR; the OLEDB Provider calls more to the unmanaged OLEDB Windows code and has a smaller managed codebase. SQLClient, on the other hand, takes advantage of the CLR and is implemented more in managed code than OLEDB. The smaller OLEDB Garbage Collection runs are a direct result of the smaller CLR footprint created by OLEDB. These two points alone would make the SQLClient hype look a bit unwarranted . . . until we factor in time. SQLClient does indeed perform faster, much faster, even in this simple example than OLEDB.
If we look at the objects allocated by each approach, we see OLEDB creates objects greater than 91 KB in size over 50% more often than SQLOLEDB. Larger objects are slower to work with than small ones, and perhaps (I don’t know for certain) the OLEDB calls to unmanaged code account for this statistical point.
The morals of this story are:
1) SQLClient certainly is faster than OLEDB; the Emperor has clothes after all
2) SQLClient has a larger managed heap footprint
3) A larger managed heap footprint does not necessarily mean a slower performing application: in this case, it means the opposite (but this isn’t always the case – try a String vs StringBuilder example)
4) The CLRProfiler is a powerful tool for inspecting your application’s internal behaviour
Check out CLRProfiler and Happy .Netting!