Pagination through large data set with New-UDDataGrid

PowerShell Universal
1 
Product: PowerShell Universal
Version: 2026.1.5

I have a large MySql data set of about 17000 records. As you can imagine, this is not a quick query, especially as the query has 2 columns where a stored function/procedure is called on them. That function is called clean_html
The process to cleanup this data on the DB itself is something that will happen in the future but not yet, so I have to clean the text fields like this.

I am using New-UDDataGrid to display this data.

It also feels like when I press the ‘go to next page’ button this whole LoadRows is executed again?

Roughly it looks like this:

New-UDDataGrid -LoadRows {

  $Cred = New-Object System.Management.Automation.PSCredential ($MySql_User,(ConvertTo-SecureString $Secret:MySql_Password -AsPlainText -Force))

  $Database = 'dbname'

  $Conn = [guid]::NewGuid().ToString()

  Open-MySqlConnection -ConnectionName $Conn -Server $MySql_Host -Database $Database -Credential $Cred -Port $MySql_Port

  $Sql = "SELECT id, title, `
                clean_html(introtext) as introtext_clean, `
                clean_html(``fulltext``) as fulltext_clean `
                from dbname.tablename `
                order by title"

  $Data = Invoke-SqlQuery -ConnectionName $Conn -Query $Sql -Stream

  Close-SqlConnection -ConnectionName $Conn
  Out-UDDataGridData -Data $Data -Context $EventData -TotalRows $Data.Length

} -Columns @(
        New-UDDataGridColumn -Field id
        New-UDDataGridColumn -Field title -Flex 5
        New-UDDataGridColumn -Field introtext_clean -Flex 10
        New-UDDataGridColumn -Field fulltext_clean -Flex 15

) -AutoHeight $true -Pagination -HeaderFilters -PageSize 10 -Density 'Compact'

As you can see, the complete data set is retrieved from the table. This takes to long and it feels inefficient to do this.

The question is: is there a better way to go through this data?

2

You need to use pagination in your sql calls: Data Grid | PowerShell Universal

For example, I use a similar mechanism with a new-udtable, and use this in my sql query:

Invoke-SQLCmd2 @SQLConfig -query "
    SELECT 
        *
    FROM 
        Table 
        $WHEREFILTERS
    ORDER BY [$OrderByField] $($EventData.OrderDirection)
    OFFSET     $($EventData.Page * $EventData.PageSize) ROWS       
    FETCH NEXT $($EventData.PageSize) ROWS ONLY
"
2 Likes
3

Thanks. I thought this was going to be the case. Well, off I go and dive into SQL once more :slight_smile: