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

رضا جافری 1,291 Reputation points
2022-06-03T19:55:06+00:00

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

.NET
.NET
Microsoft Technologies based on the .NET software framework.
3,264 questions
Windows Presentation Foundation
Windows Presentation Foundation
A part of the .NET Framework that provides a unified programming model for building line-of-business desktop applications on Windows.
2,647 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,483 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,097 questions
XAML
XAML
A language based on Extensible Markup Language (XML) that enables developers to specify a hierarchy of objects with a set of properties and logic.
756 questions
0 comments No comments
{count} votes

Accepted answer
  1. Ken Tucker 5,846 Reputation points
    2022-06-04T10:43:01.323+00:00

    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://learn.microsoft.com/en-us/dotnet/api/system.windows.controls.datagrid.enablerowvirtualization?view=windowsdesktop-6.0

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful