Passing DataTables by Reference

Possible Performance Hit

by Les Smith

Passing a DataTable by reference, to have a data access method fill it for you can be very costly, especially if you are going against an Access database.

I recently wrote a fairly complex database application against an Access database and I was experiencing a three second performance hit every time I attempted to fill a grid, regardless of the number of rows returned in the DataTable.  I discovered that passing a DataTable by reference was causing the problem.  Notice the following code example:

Dim dt As New DataTable       Sql = "select * from equipment"       Dim iRows As Integer = SendQuery(Sql, dt, ConnectString)

This is the function that I was calling to retrieve the data.

Public Function SendQuery(ByVal Sql As String, _       ByRef dt As DataTable, _       ByVal ConnectString As String) As Integer       ' Returns number of rows affected       Dim cmdOLE As New OleDbCommand       Dim daOLE As New OleDbDataAdapter       If Not MeOpenConnection(ConnectString, ConnOle) Then          Return -1 ' can't connect to db       End If       cmdOLE.CommandText = Sql       cmdOLE.CommandType = CommandType.Text       cmdOLE.Connection = ConnOle       daOLE = New OleDbDataAdapter(cmd_OLE)       daOLE.Fill(dt)       Return dt.Rows.Count    End Function

This simple query was consistantly taking 3 seconds to execute the daOLE.Fill(dt) statement.  Finally, I changed the code in the SendQuery function to the following and found that the response, from the Fill statement was instantaneous!.  

Public Function SendQuery(ByVal Sql As String, _       ByRef dt As DataTable, _       ByVal ConnectString As String) As Integer       ' Returns number of rows affected       Dim localDT As New DataTable       Dim cmdOLE As New OleDbCommand       Dim daOLE As New OleDbDataAdapter       If Not MeOpenConnection(ConnectString, ConnOle) Then          Return -1 ' can't connect to db       End If       cmdOLE.CommandText = Sql       cmdOLE.CommandType = CommandType.Text       cmdOLE.Connection = ConnOle       daOLE = New OleDbDataAdapter(cmd_OLE)       daOLE.Fill(localDT)       dt = localDT       Return localDT.Rows.Count    End Function

The only changes that I made were to create a local datatable, fill it, and set the datatable passed by reference to the local datatable and the result was astounding.  The three second delay was gone and the performance of my app was immediately improved.  Obviously, passing a datatable by reference can be very expensive.  By the way, doing the same thing using the SqlClient did not result in a noticable delay.  Possibly the SqlClient is optimized in a way that the OleDB Client is not.  I just know that the change did wonders for my app.