none
My code "da.Fill(dataSet)" Needs to Complete Faster RRS feed

  • Question

  • I wrote this code about 5 years ago at a time the client application and SQL server were in the same building.  The time to complete the line "da.Fill(dataSet)" was about 3 seconds - not good, but tolerable.  Now that the SQL server is a "cloud" virtural server, the time has increased to over 30 seconds to complete this line of code. The table "MyTable" is purged of rows older that 180 days, so the maximum size is somewhat finite at 2,600 rows.  The table has 202 fields.

    I am looking for a course of direction I can take to shorten the time with revised code.  As an aside, my reports on current applications have been coded using DevExpress, and I have had excellent response time using a cloud server. I would prefer to modify the old code than rewrite the 20 or so applications using DevExpress.

    Any direction is sincerely appreciated. 

    Private Sub RunMyReport()
     
           Dim report As LocalReport = New LocalReport()
           report.ReportPath = "..\..\rptMyReport.rdlc"
           report.DataSources.Add(New ReportDataSource("DataSet", LoadReportData()))
           Export(report)
           m_currentPageIndex = 0
           print()
     
       End Sub

       Private Function LoadReportData() As DataTable
     
           Dim cnSQL As SqlConnection = dbLIMS.GetSQLConnection
           Dim dataSet As New DataSet()
           Dim da As New SqlDataAdapter() 
           da.SelectCommand = New SqlCommand("SELECT * FROM MyTable")
           da.SelectCommand.Connection = cnSQL
     
           da.Fill(dataSet)
           Return dataSet.Tables(0)
     
       End Function

    Wednesday, May 13, 2015 12:47 PM

Answers

  • And, it would have been better if I had written this code in VS instead of off the top of my head (plus, I don't use VB and I just realized that "var" is a C# thing, not a VB thing). I've revised my original post (note that I changed it to use DataTable.Load() instead of DataSet.Load()):

    Private Function LoadReportData() As DataTable
      
        Dim cnSQL As SqlConnection = dbLIMS.GetSQLConnection
        Dim command As New SqlCommand("SELECT * FROM MyTable", cnSQL)
    
        cnSql.Open()
        Dim reader As SqlDataReader = command.ExecuteReader()
        Dim dt As New DataTable()
        dt.Load(reader)
        cnSql.Close()
        Return dt
    
    End Function
    


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    • Marked as answer by LHendren Wednesday, May 13, 2015 3:59 PM
    Wednesday, May 13, 2015 3:29 PM

All replies

  • SqlCommand.ExecuteReader is supposed to be faster than SqlDataAdapter.Fill ... something like this:

    Private Function LoadReportData() As DataTable
      
        Dim cnSQL As SqlConnection = dbLIMS.GetSQLConnection
        Dim dataSet As New DataSet()
        Dim command As New SqlCommand("SELECT * FROM MyTable")
        command.Connection = cnSQL
    
        cnSql.Open()
        var reader = command.ExecuteReader()
        dataSet.Load(reader)
        cnSql.Close()
        Return dataSet.Tables(0)
    
    End Function
    UPDATE: Not correct VB code (use of "var") ... changed it (and other things) in new reply below.


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com


    • Edited by BonnieBMVP Wednesday, May 13, 2015 3:30 PM
    Wednesday, May 13, 2015 3:08 PM
  • And, it would have been better if I had written this code in VS instead of off the top of my head (plus, I don't use VB and I just realized that "var" is a C# thing, not a VB thing). I've revised my original post (note that I changed it to use DataTable.Load() instead of DataSet.Load()):

    Private Function LoadReportData() As DataTable
      
        Dim cnSQL As SqlConnection = dbLIMS.GetSQLConnection
        Dim command As New SqlCommand("SELECT * FROM MyTable", cnSQL)
    
        cnSql.Open()
        Dim reader As SqlDataReader = command.ExecuteReader()
        Dim dt As New DataTable()
        dt.Load(reader)
        cnSql.Close()
        Return dt
    
    End Function
    


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    • Marked as answer by LHendren Wednesday, May 13, 2015 3:59 PM
    Wednesday, May 13, 2015 3:29 PM
  • @LHendren, I see that you marked my reply as an answer ... I'm just curious to know how much that speeds up your process?

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Wednesday, May 13, 2015 4:10 PM
  • Bonnie, thank you SO much! 

    This reduced the report print time from 34 seconds down to just less than 3 seconds which is very manageable given the Main Office to Cloud setup.  Changing from filling a DataSet to reading a DataTable is the answer.

    Wednesday, May 13, 2015 4:11 PM
  • Awesome! You're welcome! Three seconds is soooo much better!  Glad I could help ...   =0)

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Wednesday, May 13, 2015 4:56 PM
  • Thank you So much BonnieB

    I used .fill before but because of this i will now use  datatable.load(DataReader)

    in all programs i will make.

    P.S.

    Proven and tested Faster.

    2,400 rows and Above  for 3 - 10 secs than .fill which is about a min. before the debugger proceeds

    to the next line

    More power :)





    Wednesday, August 17, 2016 3:35 AM
  • LosMuertos --- Yes, the .Load() appears to be faster than the .Fill() ... however, if you're SELECTing into more than one DataTable (like a muliple SELECT query, or a Stored Procedure that has several SELECTs in it), then it gets more complex (I believe that you have to include an array of DataTables as one of the parameters). I haven't used the .Load() in those situations, so I don't know if the performance is still faster than the .Fill(). Maybe I can find some time tomorrow to give it a test ... (or maybe you could try a test and see what kind of speed results you get).

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Wednesday, August 17, 2016 5:09 AM
  • there is a thing came up in my mind about the process between .fill() and .Load()

    is it right that the difference between them is in .fill() it needs to complete first before it show the rows while in .load() it allows to show the rows partially?

    Wednesday, August 17, 2016 5:41 AM
  • I don't think that using the .Load() allows you to partially load the Rows. Is this something you need to do?

    Also, I'm pretty sure that the speed of the .Load() vs the .Fill() does depend somewhat on your query, but that's just anecdotal evidence that I've read here and there. I haven't done any extensive testing at all. I always use .Fill().


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Wednesday, August 17, 2016 4:48 PM