Passing DataTables by ReferencePossible Performance Hit |   | 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.
|
