none
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

     

     

    Wednesday, October 24, 2007 12:23 PM

All replies

  • 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.

    Wednesday, October 24, 2007 12:37 PM
  • 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.

     

     

    Wednesday, October 24, 2007 12:43 PM