none
C# 2.0 DataAdapter.Fill takes forever

    Question

  • I am experiencing major speed issues with C# and the .net framework 2.0 and I am hoping that someone has encountered this and can help out.

     

    Here is a very simple example.

     

    Here I return 3400 records from a Microsoft Access Database. I am using a DataAdapter to fill a DataTable with the 3400 records.

     

    System.Data.OleDb.OleDbConnection cn = DataHelper.GetConnection();

    System.Data.OleDb.OleDbCommand cmd = cn.CreateCommand();

     

    cmd.CommandText = "Select orderid, customerid from orders";

     

    OleDbDataAdapter da = new OleDbDataAdapter(cmd);

    DataTable dt = new DataTable();

     

    //fill the data table

    da.Fill(dt);  <-- heres the speed problem

     

    //return the datatable

    return dt;

     

     

    In this example, It is taking .NET  almost 11 seconds to fill the DataTable with the records from the database. I then bind the dataTable to a grid control and it binds instantly but I am not able to overcome this dataAdapter speed issue.

     

     

    Comparatively, If I hit the same database with  Visual Basic 6 I can return 100,000 + records in a recordset and bind to a grid control in the same time that it is currently taking .net to return a dataTable with 3400 records and that is just not going to work out.

     

     

    A few more notes..

    1)

    I have tried to retrieve the data with a DataReader and it returns instantaneously (like lightning) however today I do not use a DataReader in the UI at all and was kind of hoping that I could get the DataTable performance up high enough where I could always reuturn a dataTable from our data layer and not a DataReader.

     

    2)

    I have tried to populate the DataTable with a DataReader and bypass the dataAdapter using DataTable.Load(DataReader).

    There was no speed improvement in this method at all. If anything it may have been about 1/2 second slower

     

    3) I have tried to call the DataTable.BeginLoadData and DataTable.EndLoadData methods as the MSDN help suggests which turns off table indexes and other advanced features while the data is being populated.  I saw no performance increase at all here.

     

     

    If anyone knows how to quickly load a DataTable I would love to learn how.

     

    thanks a lot!

    Tuesday, October 16, 2007 12:43 PM

Answers

  • I found the problem. Apparently this is a bug /defect with the visual studio 2005 IDE when  trying to populate a DataTable.

     

    If you use Dataadapter.fill or DataTable.Load while debugging in the visual studio 2005 IDE  the DataAdapter.Fill or DataTable.Load ()  methods perform exponentially slower than when you run the actual compiled EXE (debug or release doesn't matter).

     

    For me it takes 11 seconds to load 3,000 records. For the gentleman who made the post below it took 24 seconds to load 7,000 records.

     

    I ran a debug version of my exe and In the Executable the 3,598 records load instantaneously versus the 11 seconds it takes to load in the IDE.

     

    This post talks about the defect

    http://www.devnewsgroups.net/group/microsoft.public.dotnet.framework.adonet/topic34540.aspx

     

     

     

     

     

    Tuesday, October 16, 2007 5:02 PM

All replies

  • I would have a hard time believing that this is a problem with the fill method. Have you observed this just a few times, or can you really consistently reproduce this at any time? I suspect your machine, and/or if this database is remote (over a network path), something in the network resources getting to it, was busy enough to not give your app the responsiveness you expected at the time.

     

    Tuesday, October 16, 2007 3:46 PM
  • No this is not a single case at all and I am working local on my development machine with a local database.

     

    As far as I can tell in my readings on the web,  nobody seems to be able to get a DataTable to load with any type of respectable performance anytime you have a few thousand records. I have been searching on the net for hours now and everybody says the same thing..DataTable is slow..DataReader is fast but it can't run disconnected.

     

    Don't get me wrong, DataTables load like lightning when there is 1 record or 2 or 3 but if there are 3,000 records its pretty much rediculously slow. My DataReader (with the exact same database connection and select statement) returns in basically milliseconds compared to the 11 seconds that my datatable takes to loade.

     

    In my readings thus far, some people say ADO.NET is not meant to handle large amounts of data and cannot handle a few thousands rows efficiently and  others recommend that you do not fill a DataTable with a data adapter at all but instead recommend that you abandon this idea and return an arraylist of business objects populated with a DataReader to bypass the problem.

     

     

    I have tried to load the DataTable with DataReader using DataTable.Load(). I have tried to fill it using the DataAdapter. I have tried to use BeginLoad and EndLoad methods which is suppose to speed up the process but nothing seems to enhance performance. I wrap the Fill satement in Console.WritelInes to write out the time it takes and each time it takes 11 seconds to load my 3,000 records.

     

     

    heres a few links showing other people with the problem

     

    this link recommends 3rd party tool that allows user to utilize older ADO technology to solve problem

    http://www.developerfood.com/very-slow-loading-a-datatable/microsoft-public-dotnet-framework-performance/5a44afef-654a-41cb-bf19-74ff69357e90/article.aspx

     

    this link recommended to switch to business objects because datatable cannot perform

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=195863&SiteID=1

    Tuesday, October 16, 2007 4:54 PM
  • I found the problem. Apparently this is a bug /defect with the visual studio 2005 IDE when  trying to populate a DataTable.

     

    If you use Dataadapter.fill or DataTable.Load while debugging in the visual studio 2005 IDE  the DataAdapter.Fill or DataTable.Load ()  methods perform exponentially slower than when you run the actual compiled EXE (debug or release doesn't matter).

     

    For me it takes 11 seconds to load 3,000 records. For the gentleman who made the post below it took 24 seconds to load 7,000 records.

     

    I ran a debug version of my exe and In the Executable the 3,598 records load instantaneously versus the 11 seconds it takes to load in the IDE.

     

    This post talks about the defect

    http://www.devnewsgroups.net/group/microsoft.public.dotnet.framework.adonet/topic34540.aspx

     

     

     

     

     

    Tuesday, October 16, 2007 5:02 PM