SQLDataAdapter & Fill Performance (When calling Fill Multiple times)

Question

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 lines

 

Code Block

Friend NotInheritable Class CandidateDocument

             Private dtCandidateDocument as New dsCandidates.CandidateDocumentDataTable()

 

            Private Function LoadDocument(ByVal DocumentID As Integer) As dsCandidates.CandidateDocumentRow

        Try

            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

            Me.daCandidateDocument.FillByDocumentID(dsCandidateDocument, DocumentID)

 

            Return dsCandidateDocument.FindByDocumentId(DocumentID)

        Catch ex As Exception

            ErrorMsg(ex, "DocumentID: " & DocumentID)

           Return Nothing

        End Try

    End Function

 

End Class

 

The performance problem can be overcome by filling the document into a temporary table, and then importing this row, as below

 

Code Block

Dim Test As New dsCandidates.CandidateDocumentDataTable()

Me.daCandidateDocument.FillByDocumentID(Test, DocumentID)

dsCandidateDocument.ImportRow(Test(0))

 

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    

1373

8772

8787

8772

8772

8863

 

TEST A (15MB) – Import Row

1389

1514

1389

1389

1404

1389

 

TEST B (7MB) – Standard

655

4308

4276

7292

4339

4388

 

TEST B (7MB) – Import Row

686

671

671

671

686

686

 

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?

 

Thanks,

 

Darren Barrick

Zenzero Solutions Ltd

 

 

Answer 1

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.

Answer 2

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.