[Mono-dev] Performance issue with DataTable.Load on "large" data sets

[Nicklas Overgaard]

Hi again,

Sorry for the spamming. 

Moving out the "Begin" and "End" load methods reduced DataTable.Load
time to 1.7 seconds on my test machine, so we are getting there!

/Nicklas

On Thu, 2011-04-07 at 19:29 +0200, Nicklas Overgaard wrote:
> Hi again,
> 
> I now have a profile log, created with the new mono profiler. It shows,
> that the method "EndLoadData" is using up almost all of the time (16
> minutes of the 17 minutes it took to create the dump).
> 
> When looking in the file "DbDataAdapter.cs" line 355 in current GIT
> head, the "BeginLoadData" and "EndLoadData" methods are called for each
> iteration in the DataReader's data.
> 
> This means that for each row we add to the DataTable, the DataSet is
> begin asked to enforce constraints and other stuff in the datatable.
> 
> According to MSDN:
> http://msdn.microsoft.com/en-us/library/system.data.datatable.beginloaddata.aspx
> 
> "BeginLoadData Turns off notifications, index maintenance, and
> constraints while loading data."
> 
> So would'nt it make sense to move "BeginLoad.." and "EndLoad.." out of
> the loop?
> 
> Well, I'm trying it out :)
> 
> Best regards,
> 
> Nicklas Overgaard
> 
> On Thu, 2011-04-07 at 14:58 +0200, Nicklas Overgaard wrote:
> > Hi mono-devers!
> > 
> > I'm currently working on a rather large webproject, where we are using a
> > combination of mono 2.10.1 and MySQL.
> > 
> > Over the past week, I have observed that loading "large" datasets (5000+
> > rows) from mysql into a DataTable takes a very long time. 
> > 
> > It's done somewhat like this:
> > <code>
> > 
> > comm.CommandText = query;
> > comm.CommandTimeout = MySQLConnection.timeout;
> > MySqlDataReader reader = (MySqlDataReader)comm.ExecuteReader();
> > DataTable dt = new DataTable();
> > dt.Load(reader); // <- this is killing mono
> > reader.Close();
> > 
> > </code>
> > 
> > I have created a small testprogram, compiled it on my linux machine and
> > executed it.
> > 
> > It takes 15 seconds to do such operation under mono - but on windows it
> > takes only 0.4 seconds (with the same executable, fetching the same
> > data). I have profiled the application on windows, and it seems that
> > the .net framework is using specialized methods for loading data from a
> > datareader.
> > 
> > I have been looking through the implementation in mono, in regard to
> > DataTable.Load, and I can see that a lot of validation and other stuff
> > is going on, which could explain the huge difference. I'm also working
> > on a mono log profile trace, to dig a little deeper.
> > 
> > Would it be OK, if I tried to patch the current mono implementation to
> > gain the same speeds as .net? The reason for asking, is that I know that
> > I cannot contribute to Mono if I have seen the actual code in .NET (but
> > does a profile result count as "seeing the code"?)
> > 
> > Best regards,
> > 
> > Nicklas Overgaard
> > 
> > _______________________________________________
> > Mono-devel-list mailing list
> > Mono-devel-list at lists.ximian.com
> > http://lists.ximian.com/mailman/listinfo/mono-devel-list
> 
> _______________________________________________
> Mono-devel-list mailing list
> Mono-devel-list at lists.ximian.com
> http://lists.ximian.com/mailman/listinfo/mono-devel-list