DataSet Clear() performance problem
-
Hi, I am developing a Windows.Forms app. I am using a multi tier architecture with databinded strongly typed datasets . I am using a data access component called Facade where I have a method: public Sub LoadCompany(ByVal companyID as Integer, ByRef ds as CompaniesDataSet) ds.Clear() ...code to set the parameter in the select command DACompanies.Fill(ds) End Sub It seems that ds.Clear() takes more and more time to complete , from a few miliseconds at start to 4-5 seconds after 100 method invokes. Anyone has an answer to this? Thank you, Dan Bunea
-
Hi, I am developing a Windows.Forms app. I am using a multi tier architecture with databinded strongly typed datasets . I am using a data access component called Facade where I have a method: public Sub LoadCompany(ByVal companyID as Integer, ByRef ds as CompaniesDataSet) ds.Clear() ...code to set the parameter in the select command DACompanies.Fill(ds) End Sub It seems that ds.Clear() takes more and more time to complete , from a few miliseconds at start to 4-5 seconds after 100 method invokes. Anyone has an answer to this? Thank you, Dan Bunea
It would be helpful if you could post approximate sizes: how many tables are in your DataSet? How many rows in the DataTables? How big is a row? I suspect you're suffering from a mid-life crisis[^]: the garbage collector is having to collect a lot of medium-aged objects. Instead of passing in a reference to a
DataSet, you should probably just create a new one withinLoadCompanyand return it. Turn LoadCompany into a function:Public Function LoadCompany(ByVal companyID As Integer) As DataSet
Dim ds As New DataSet
' ...
DACompanies.Fill(ds)
Keeping the dataset around typically does you no favours, it just wastes memory. A DataSet object is not terribly expensive to create. Earlier today I was looking at System.Data.dll using Lutz Roeder's Reflector[^], which seems to indicate that
Cleardoesn't delete any tables already contained in the dataset. It empties them, yes, but doesn't delete them. The DataTable keeps its DataRows in an array, whose size doesn't change when Clear is called (although the rows are freed). You might find that memory is being wasted if a large number of rows have been used in the past. If you do decide to keep the DataSet around,Resetwill clear any contained tables. Don't fight the garbage collector. On a final note, there's no need to pass a reference type by reference, unless you want to make the original reference (in the calling function) point to a different object. It's important to understand the difference between a value and reference type. I simply think of a reference type as being the same as a C++ pointer. Stability. What an interesting concept. -- Chris Maunder -
It would be helpful if you could post approximate sizes: how many tables are in your DataSet? How many rows in the DataTables? How big is a row? I suspect you're suffering from a mid-life crisis[^]: the garbage collector is having to collect a lot of medium-aged objects. Instead of passing in a reference to a
DataSet, you should probably just create a new one withinLoadCompanyand return it. Turn LoadCompany into a function:Public Function LoadCompany(ByVal companyID As Integer) As DataSet
Dim ds As New DataSet
' ...
DACompanies.Fill(ds)
Keeping the dataset around typically does you no favours, it just wastes memory. A DataSet object is not terribly expensive to create. Earlier today I was looking at System.Data.dll using Lutz Roeder's Reflector[^], which seems to indicate that
Cleardoesn't delete any tables already contained in the dataset. It empties them, yes, but doesn't delete them. The DataTable keeps its DataRows in an array, whose size doesn't change when Clear is called (although the rows are freed). You might find that memory is being wasted if a large number of rows have been used in the past. If you do decide to keep the DataSet around,Resetwill clear any contained tables. Don't fight the garbage collector. On a final note, there's no need to pass a reference type by reference, unless you want to make the original reference (in the calling function) point to a different object. It's important to understand the difference between a value and reference type. I simply think of a reference type as being the same as a C++ pointer. Stability. What an interesting concept. -- Chris MaunderHi, Thank you for your answer. The DataSet is bound to very many controls (about 50) so reinitializing it is not a solution, because I lose my bindings (of course during load they are suspended, throught their binding context). I have posted a more detailed explanation about this (with screenshots and code) at: http://www.geocities.com/danbunea/posts/DataSetClear.htm Could you tell me more about using Reset() because MSDN documentation doesn't seem to offer too much info. About ByRef you are right, I could have used ByVal. Thank you very much for your answer. Dan Dan
-
Hi, Thank you for your answer. The DataSet is bound to very many controls (about 50) so reinitializing it is not a solution, because I lose my bindings (of course during load they are suspended, throught their binding context). I have posted a more detailed explanation about this (with screenshots and code) at: http://www.geocities.com/danbunea/posts/DataSetClear.htm Could you tell me more about using Reset() because MSDN documentation doesn't seem to offer too much info. About ByRef you are right, I could have used ByVal. Thank you very much for your answer. Dan Dan
I see you're using a typed dataset generated by Visual Studio from an .xsd file. Using
Reseton a typed data-set doesn't help, it just breaks the association between the typed DataTable-derived classes and the DataSet's Tables property. It therefore clears the data the first time, but subsequent calls to Reset do nothing. My bad. I wonder whether retaining the complete set of data (i.e. for all companies) and using a CurrencyManager object to manage the currently displayed company would work better than clearing the dataset when fetching the next company's data? Looking at your code again, I note that you're passing the dataset to Fill, not the dataset's typed data tables (objects derived from DataTable). If you don't have a DataTableMapping assigned to the DataAdapter, I believe the new data will be added to the DataSet as 'Table' (if you have multiple result sets returned from the query, the second result set will be returned as Table1, the third as Table2, etc). I don't know if this is happening, but it may be better to bind directly to the tables.Me.DACompanyContacts.Fill(ds**.CompanyContacts**)
Me.DAGrades.Fill(ds**.Grades**)
Me.DAQuestionsLookup.Fill(ds**.QuestionsLookup**)
Me.DAEmployees.Fill(ds**.Employees**)
Me.DAInvoices.Fill(ds**.Invoices**)
Me.DATrainingCourses.Fill(ds**.TrainingCourses**)
Me.DATrainingNeeds.Fill(ds**.TrainingNeeds**)
Me.DATrainingPlans.Fill(ds**.TrainingPlans**)
Me.DACompany.Fill(ds**.Company**)This may be worthwhile anyway because the work to go through the schema mappings is quite extensive. If it is happening, you may have a lot of stray DataTable objects hanging around. I can't see any other problems with this code, so I'm assuming that it's a memory problem. You may want to try the CLR Profiler[^] to study the memory behaviour of your program. Stability. What an interesting concept. -- Chris Maunder
-
I see you're using a typed dataset generated by Visual Studio from an .xsd file. Using
Reseton a typed data-set doesn't help, it just breaks the association between the typed DataTable-derived classes and the DataSet's Tables property. It therefore clears the data the first time, but subsequent calls to Reset do nothing. My bad. I wonder whether retaining the complete set of data (i.e. for all companies) and using a CurrencyManager object to manage the currently displayed company would work better than clearing the dataset when fetching the next company's data? Looking at your code again, I note that you're passing the dataset to Fill, not the dataset's typed data tables (objects derived from DataTable). If you don't have a DataTableMapping assigned to the DataAdapter, I believe the new data will be added to the DataSet as 'Table' (if you have multiple result sets returned from the query, the second result set will be returned as Table1, the third as Table2, etc). I don't know if this is happening, but it may be better to bind directly to the tables.Me.DACompanyContacts.Fill(ds**.CompanyContacts**)
Me.DAGrades.Fill(ds**.Grades**)
Me.DAQuestionsLookup.Fill(ds**.QuestionsLookup**)
Me.DAEmployees.Fill(ds**.Employees**)
Me.DAInvoices.Fill(ds**.Invoices**)
Me.DATrainingCourses.Fill(ds**.TrainingCourses**)
Me.DATrainingNeeds.Fill(ds**.TrainingNeeds**)
Me.DATrainingPlans.Fill(ds**.TrainingPlans**)
Me.DACompany.Fill(ds**.Company**)This may be worthwhile anyway because the work to go through the schema mappings is quite extensive. If it is happening, you may have a lot of stray DataTable objects hanging around. I can't see any other problems with this code, so I'm assuming that it's a memory problem. You may want to try the CLR Profiler[^] to study the memory behaviour of your program. Stability. What an interesting concept. -- Chris Maunder
Hi, Thanks again for your answer. I decompiled the Reset methods and saw that it uses Clear internally. Probably I'll just move to .NET 1.1 and see what happens. The loading of the tables works nice so I do not have to Me.DACompanyContacts.Fill(ds.CompanyContacts) , just using it as I did works fine. Thanks again, Dan