SQLDataAdapter & Fill Performance (When calling Fill Multiple times)
I have been running into some strange behaviour when using the SQLDataAdapter.
We store documents directly into our database, and in order to improve performance I only return the document when the user requests to view it. Loading the first document works fine, but subsequent document loads are significantly slower.
We thus have some code along the following linesCode Block
Friend NotInheritable Class CandidateDocument
Private dtCandidateDocument as New dsCandidates.CandidateDocumentDataTable()
Private Function LoadDocument(ByVal DocumentID As Integer) As dsCandidates.CandidateDocumentRow
Dim DocumentRow As dsCandidates.CandidateDocumentRow = dsCandidateDocument.FindByDocumentId(DocumentID)
If DocumentRow IsNot Nothing Then Return DocumentRow
Me.daCandidateDocument.ClearBeforeFill = False 'Do not clear any existing documents
Catch ex As Exception
ErrorMsg(ex, "DocumentID: " & DocumentID)
The performance problem can be overcome by filling the document into a temporary table, and then importing this row, as belowCode Block
Dim Test As New dsCandidates.CandidateDocumentDataTable()
To test I added six copies of a 15Mb, and six copies of a 7MB document to my database.
(All times are in milliseconds)
TEST A (15MB) - Standard
TEST A (15MB) – Import Row
TEST B (7MB) – Standard
TEST B (7MB) – Import Row
As you can see, the first document takes about the same amount of time in either the standard or import row tests. However subsequent document loads are significantly longer in the standard test.
Can anyone offer an explanation for this behaviour?
Zenzero Solutions Ltd
does your dataadaptor (daCandidateDocument) and/or dataset (dsCandidates) need to be a global variable? By the fact that it's taking longer after each call, it looks like those objects might be getting bogged down.
If you make one (or both) a local variable to that function, and remember to dispose at the end of the function, you should see some performance improvement.
The dataset does need to be a global variable. I will make the dataAdapter a local variable as you suggest, but I would be surprised if this accounted for the performance difference.
Edit: I tried making the dataAdapter a local variable (encapsulated in a using block), but it did not account for the significant difference I have reported.