WPF VB.NET中DataGridView加载大量SQL数据性能优化求助

问题背景 #

从本地SQL Server存储过程取2000行123列数据仅需500-800ms，但填充到DataGrid（代码中写为DataGridView，WPF标准控件为DataGrid）时耗时长达约2分钟，现有代码如下：

数据访问层代码

Public Class SQL
    Private Const ConnectionString As String = "Data Source=(localdb)\TestVFE;Initial Catalog=testVFE;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"

    Private Shared Connection As New SqlConnection(ConnectionString)

    Public Shared Sub OpenConnection()
        If Connection.State = ConnectionState.Closed Then
            Connection.Open()
        End If
    End Sub

    Public Shared Sub CloseConnection()
        If Connection.State = ConnectionState.Open Then
            Connection.Close()
        End If
    End Sub

    Public Shared Function GetLast1000Rows() As DataTable
        OpenConnection()
        Dim cmd As New SqlCommand("GetLast1000Rows", Connection)
        cmd.CommandType = CommandType.StoredProcedure
        Dim adapter As New SqlDataAdapter(cmd)
        Dim results As New DataTable()
        adapter.Fill(results)
        CloseConnection()
        Return results
    End Function
End Class

主窗口代码

Class MainWindow
    Private Sub ResultsGrid_Loaded(sender As Object, e As RoutedEventArgs) Handles ResultsGrid.Loaded
        Dim dataTable As DataTable = SQL.GetLast1000Rows
        ResultsGrid.RowHeaderStyle = DataGridViewRowHeadersWidthSizeMode.EnableResizing
        ResultsGrid.ItemsSource = dataTable.DefaultView
    End Sub
End Class

优化方案 #

1. 异步加载数据，避免UI线程阻塞

UI线程被同步数据操作占用会导致界面假死，感知耗时变长，改用异步模式：

修改数据访问层为异步方法：

Public Shared Async Function GetLast1000RowsAsync() As Task(Of DataTable)
    ' 使用Using自动管理连接生命周期，避免共享连接的池问题
    Using conn As New SqlConnection(ConnectionString)
        Await conn.OpenAsync()
        Using cmd As New SqlCommand("GetLast1000Rows", conn)
            cmd.CommandType = CommandType.StoredProcedure
            Dim adapter As New SqlDataAdapter(cmd)
            Dim results As New DataTable()
            adapter.Fill(results)
            Return results
        End Using
    End Using
End Function

主窗口异步处理：

Private Async Sub ResultsGrid_Loaded(sender As Object, e As RoutedEventArgs) Handles ResultsGrid.Loaded
    ResultsGrid.IsEnabled = False ' 可选：禁用控件避免用户操作
    Try
        Dim dataTable As DataTable = Await SQL.GetLast1000RowsAsync()
        ResultsGrid.ItemsSource = dataTable.DefaultView
        ResultsGrid.RowHeaderStyle = DataGridViewRowHeadersWidthSizeMode.EnableResizing
    Finally
        ResultsGrid.IsEnabled = True
    End Try
End Sub

2. 优化DataGrid渲染性能

禁用不必要的自动布局和视觉效果，减少UI重绘：

Private Async Sub ResultsGrid_Loaded(sender As Object, e As RoutedEventArgs) Handles ResultsGrid.Loaded
    ' 加载前暂停布局，避免频繁重绘
    ResultsGrid.SuspendLayout()
    
    Dim dataTable As DataTable = Await SQL.GetLast1000RowsAsync()
    ResultsGrid.ItemsSource = dataTable.DefaultView
    
    ' 优化渲染设置
    ResultsGrid.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.None ' 禁用自动列宽（或按需指定Fill/固定宽度）
    ResultsGrid.RowHeadersWidthSizeMode = DataGridViewRowHeadersWidthSizeMode.DisableResizing ' 不需要行头调整则禁用
    ResultsGrid.DoubleBuffered = True ' 开启双缓冲减少闪烁
    
    ResultsGrid.ResumeLayout() ' 恢复布局一次性渲染
End Sub

3. 开启UI虚拟化

WPF DataGrid支持UI虚拟化，仅渲染可见区域的行/列，大幅降低大数量数据的渲染压力，在XAML中配置：

<DataGrid x:Name="ResultsGrid" 
          EnableRowVirtualization="True"
          EnableColumnVirtualization="True"
          VirtualizingPanel.IsVirtualizing="True"
          VirtualizingPanel.VirtualizationMode="Recycling"/>

4. 精简返回数据

检查存储过程是否返回了非必要的列，仅返回DataGrid需要显示的字段，减少数据传输量和UI渲染负担。

5. 修复连接管理问题

原代码使用共享SqlConnection可能导致连接池异常，改用Using语句自动释放连接，确保连接正确回收。

内容的提问来源于stack exchange，提问作者Milos Dimitrijevic