question

RezaJaferi1992 avatar image
0 Votes"
RezaJaferi1992 asked RezaJaferi1992 edited

RAM overflows and the application slows down, when the application is loaded with a large amount of Microsoft Access data (ADO.NET)

Hi
I use the following algorithm to load data into my application.
208278-data-sources.gif

XAML:

 <Window.Resources>
     <local:DatabaseDataSet x:Key="Database_DataSet"/>
     <CollectionViewSource x:Key="BookTableViewSource" Source="{Binding BookTable, Source={StaticResource Database_DataSet}}"/>
 </Window.Resources>
 <Grid>
     <GroupBox x:Name="Books_GroupBox" Header="Books" Background="Transparent" Visibility="Hidden" HorizontalAlignment="Center" Width="500" Height="400" VerticalAlignment="Center">
         <GroupBox.Style>
             <Style TargetType="{x:Type GroupBox}">
                 <Setter Property="BorderBrush" Value="DarkGray"/>
                 <Setter Property="BorderThickness" Value="1"/>
                 <Setter Property="Template">
                     <Setter.Value>
                         <ControlTemplate TargetType="{x:Type GroupBox}">
                             <Grid SnapsToDevicePixels="true">
                                 <Grid.ColumnDefinitions>
                                     <ColumnDefinition Width="*"/>
                                     <ColumnDefinition Width="*"/>
                                     <ColumnDefinition Width="*"/>
                                     <ColumnDefinition Width="*"/>
                                 </Grid.ColumnDefinitions>
                                 <Grid.RowDefinitions>
                                     <RowDefinition Height="*"/>
                                     <RowDefinition Height="*"/>
                                     <RowDefinition Height="*"/>
                                     <RowDefinition Height="*"/>
                                 </Grid.RowDefinitions>
                                 <Border BorderBrush="DarkGray" 
                                 BorderThickness="1"
                                 CornerRadius="9"
                                 Grid.ColumnSpan="4" 
                                 Grid.RowSpan="4">
                                     <Border.OpacityMask>
                                         <MultiBinding ConverterParameter="9">
                                             <MultiBinding.Converter>
                                                 <BorderGapMaskConverter/>
                                             </MultiBinding.Converter>
                                             <Binding ElementName="Header" Path="ActualWidth"/>
                                             <Binding Path="ActualWidth" RelativeSource="{RelativeSource Self}"/>
                                             <Binding Path="ActualHeight" RelativeSource="{RelativeSource Self}"/>
                                         </MultiBinding>
                                     </Border.OpacityMask>
                                 </Border>
                                 <Border x:Name="Header" Grid.RowSpan="1" Grid.ColumnSpan="2"  Margin="9,-11.5,0,0" HorizontalAlignment="Left" VerticalAlignment="Top">
                                     <ContentPresenter  ContentSource="Header"
                                               RecognizesAccessKey="True" 
                                               SnapsToDevicePixels="{TemplateBinding SnapsToDevicePixels}"/>
                                 </Border>
                                 <ContentPresenter  Grid.ColumnSpan="2" Grid.Column="1" Grid.Row="1" 
                                           Margin="{TemplateBinding Padding}"
                                           SnapsToDevicePixels="{TemplateBinding SnapsToDevicePixels}"/>
                             </Grid>
                         </ControlTemplate>
                     </Setter.Value>
                 </Setter>
             </Style>
         </GroupBox.Style>
         <ItemsControl Width="486" Height="386" Margin="-118,-93,-118,-193">
             <Grid DataContext="{StaticResource BookTableViewSource}" Width="486" Height="386">
                 <DataGrid x:Name="BookDataGrid" HeadersVisibility="Column" EnableRowVirtualization="False" CanUserAddRows="False" AutoGenerateColumns="False" ItemsSource="{Binding}" HorizontalAlignment="Left" VerticalAlignment="Top" Width="486" Height="386" Margin="0">
                     <DataGrid.Columns>
                         <DataGridTextColumn x:Name="BookName" Binding="{Binding BookName}" Width="SizeToHeader"/>
                         <DataGridTextColumn x:Name="Publisher" Binding="{Binding Publisher}" Width="SizeToHeader"/>
                         <DataGridTextColumn x:Name="Category" Binding="{Binding Category}" Width="SizeToHeader"/>
                         <DataGridTextColumn x:Name="BookCode" Binding="{Binding BookCode}" IsReadOnly="True" Width="SizeToHeader"/>
                         <DataGridTextColumn x:Name="Inventory" Binding="{Binding Inventory}" IsReadOnly="True" Width="SizeToHeader"/>
                         <DataGridTextColumn x:Name="DateTaken" Binding="{Binding DateTaken}" Width="SizeToHeader"/>
                         <DataGridTextColumn x:Name="RecipientName" Binding="{Binding RecipientName}" Width="SizeToHeader"/>
                         <DataGridTextColumn x:Name="ReturnDate" Binding="{Binding ReturnDate}" Width="SizeToHeader"/>
                         <DataGridTextColumn x:Name="ReleaseDate" Binding="{Binding ReleaseDate}" Width="SizeToHeader"/>
                         <DataGridTextColumn x:Name="Language" Binding="{Binding BookLanguage}" Width="SizeToHeader"/>
                         <DataGridTextColumn x:Name="Length" Binding="{Binding Length}" Width="SizeToHeader"/>
                         <DataGridTextColumn x:Name="Form" Binding="{Binding Form}" Width="SizeToHeader"/>
                         <DataGridTextColumn x:Name="Translator" Binding="{Binding Translator}" Width="SizeToHeader"/>
                         <DataGridTextColumn x:Name="Narrator" Binding="{Binding Narrator}" Width="SizeToHeader"/>
                         <DataGridTextColumn x:Name="ISBN" Binding="{Binding ISBN}" Width="SizeToHeader"/>
                         <DataGridTextColumn x:Name="Location" Binding="{Binding Location}" Width="SizeToHeader"/>
                         <DataGridTextColumn x:Name="Price" Binding="{Binding Price}" Width="SizeToHeader"/>
                         <DataGridTemplateColumn x:Name="BookImage" Width="SizeToHeader">
                             <DataGridTemplateColumn.CellTemplate>
                                 <DataTemplate>
                                     <Image x:Name="BookImg" Source="{Binding BookImage}"/>
                                 </DataTemplate>
                             </DataGridTemplateColumn.CellTemplate>
                         </DataGridTemplateColumn>
                     </DataGrid.Columns>
                 </DataGrid>
             </Grid>
         </ItemsControl>
     </GroupBox>
 </Grid>

Code Behind (C#):

 public void BookDatagridRefresh()
 {
     DatabaseDataSet Database_DataSet = ((DatabaseDataSet)TryFindResource("Database_DataSet"));
     DatabaseDataSetTableAdapters.BookTableTableAdapter BookTable_TableAdapter = new DatabaseDataSetTableAdapters.BookTableTableAdapter();
     BookTable_TableAdapter.Fill(Database_DataSet.BookTable);
     BookDataGrid.ItemsSource = Database_DataSet.Tables["BookTable"].DefaultView;
 }

When I use the BookDatagridRefresh function (the algorithm described above), RAM overflows and the program becomes very slow.
Pay attention to the following images (before running the program and after running the program):
The book table is around 60.02 MB in size and includes a total of 7500 rows (for example, if 1000 more rows are added, the application will get slower).
208300-size.png

Before running the program (without Visual Studio):
208361-before-running-the-program.png

After running the program (without Visual Studio):
208315-after-running-the-program.png

I use the following tools:
1- Visual Studio 2017 and Microsoft .NET Framework 4.5.2
2- Access 2010 and Access Database Engine 2010x86

What solution do you recommend for the program to consume the least amount of RAM while also increasing its speed?
Please provide an answer by supplying output.
Thanks



dotnet-csharpwindows-wpfdotnet-wpf-xamldotnet-adonetdotnet-sqlclient
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

vb2ae avatar image
1 Vote"
vb2ae answered RezaJaferi1992 edited

I would turn Row virtualization back on

          EnableRowVirtualization="True" 


When the EnableRowVirtualization property is set to true, the DataGrid does not instantiate a DataGridRow object for each data item in the bound data source. Instead, the DataGrid creates DataGridRow objects only when they are needed, and reuses them as much as it can. For example, the DataGrid creates a DataGridRow object for each data item that is currently in view and recycles the row when it scrolls out of view.

https://docs.microsoft.com/en-us/dotnet/api/system.windows.controls.datagrid.enablerowvirtualization?view=windowsdesktop-6.0

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

For some reason, I had to set EnableRowVirtualization to False in order to utilize the loop in the DataGrid, but your solution is correct and I tested it.
The result:
208358-result.gif
Thank you a lot.





0 Votes 0 ·
result.gif (2.4 MiB)