Very slow table rendering

PowerShell Universal
1 
Product: PowerShell Universal
Version: 4.3.0

Hi,
I need to render a table that always grows.
Data is populated via function that query the DB.
(Used this post to create the table: Build Server-Side tables with PowerShell Universal Dashboard
)

Here is my code:

New-UDDynamic -Id 'dynExtendProviders' -Content {
    $ExtendProviderList = (Get-ProvidersList -SQLServerName $SQLServerName | Select-Object @{n="Provider";e={"$($_.COMPANY_NAME) - $($_.ACCOUNT)"}}, @{n="ID";e={"$($_.PROVIDER_ID)"}}, @{n="Account";e={"$($_.ACCOUNT)"}}, @{n="Sysaid";e={"$($_.SYSAID_ID)"}}, @{n="Lockout";e={"$($_.FUTURE_LOCKING_DATE)"}} | Sort-Object -Property 'Provider')
    #(Get-Date -Date $($_.FUTURE_LOCKING_DATE) -Format 'dd/MM/yyyy')
    $Data = $null


 New-UDTable -Id "tblExtendProviders" -Title 'Providers List' -ShowFilter -ShowSelection -Dense -MaxHeight:600 -LoadData {
     $TableData = ConvertFrom-Json $Body
 
     <# $Body will contain
         filters: []
         orderBy: undefined
         orderDirection: ""
         page: 0
         pageSize: 5
         properties: (2) ["name", "host"]
         search: ""
         totalCount: 0
     #>
 #Validate that both server and filename are filled by the user
        #Check If table was filterd by username
             if ($TableData.Filters.Id -eq "Provider") {
                 $Data = $ExtendProviderList | Where-Object { $_.Provider -match $TableData.Filters.value}
             }
             else {
                #Filter only by filename
                 $Data = $ExtendProviderList
             }
                 $Data = $Data | ForEach-Object {
                   if($_.Lockout){
         @{
            ID = $_.ID
            Provider = $_.Provider
            Account = $_.Account
            Sysaid = $_.Sysaid
            Lockout = $_.Lockout
            #Lockout = (Get-Date -Date $($_.Lockout) -Format 'dd/MM/yyyy')
         }
                   } else {
        @{
            ID = $_.ID
            Provider = $_.Provider
            Account = $_.Account
            Sysaid = $_.Sysaid
         }
                   }
     }
     #Drop the data to the table if it has values
     if ($Data.Count -gt 0) {
       $Data | Out-UDTableData -Page $TableData.page -TotalCount $Data.Count -Properties $TableData.properties
     } else {
     #Handler for empty table
       $null | Out-UDTableData -Page $TableData.page -TotalCount $Data.Count -Properties $TableData.properties -ErrorAction SilentlyContinue
 }
 #Table Culmns
 } -Columns @(
     New-UDTableColumn -Property 'ID' -Title 'ID' -Hidden
     New-UDTableColumn -Property 'Provider' -Title 'Provider' -ShowFilter -FilterType text
     New-UDTableColumn -Property 'Account' -Title 'Account'-Hidden
     New-UDTableColumn -Property 'Sysaid' -Title 'SysAid ID' -OnRender { New-UDTextbox -Id ('Sysaid_' + $Data.ID) -Minimum 5 -Maximum 6 -Mask '00000' -Variant outlined -Value $EventData.Sysaid }
     New-UDTableColumn -Property 'Lockout' -Title 'Lockout Date' -OnRender {
       if ($EventData.Lockout) {
         New-UDDatePicker -Id ('lockout_' + $Data.ID) -Format 'DD/MM/YYYY' -MinimumDate (Get-Date).AddDays(1) -MaximumDate (Get-Date).AddDays(7) -Views "day" -Value $EventData.Lockout
         } else {
           New-UDDatePicker -Id ('lockout_' + $Data.ID) -Format 'DD/MM/YYYY' -MinimumDate (Get-Date).AddDays(1) -MaximumDate (Get-Date).AddDays(7) -Views "day"
          }
      }
 ) -OnRowSelection {
          #$Item = $EventData

 }
 } -LoadingComponent { new-udprogress -Circular -Color "#1890ff" }
2

I was able to solve it by rebuilding the table with pages, there is a better way to handle data rendering?

3

I may be wrong, but looking at your code I think you’re missing the point in the server side rendering. The idea is that the table data is paginated and therfore only makes the necessary call to get only that page of data from your source system, when a user clicks to view the next page, an additional call is made at that point, vastly reducing the overhead, because you’re only pulling x records at a time.
If you’re creating server side processing tables for the purpose of optimizing the responsiveness you should never pull all the data in one go.
Am i right in thinking that you are doing this in your $ExtendProviderList variable in the second line of your code?
If so you will need to make some adjustments to ensure that the call to obtain the data is within the -LoadData script block, and you are utilizing pagination in those calls.

Also the article you have referenced is a very old version of powershell universal - it’s not relevant to the version you’re using, it might be best to refer to documentation here instead:
Table | PowerShell Universal

1 Like
4

@dor_g

In general, you are using the right approach: you need to use the -LoadData parameter and add a scriptblock to add where you specify how your data is loaded with filtering, pageination …

You can find a similar example also on the demo page: PowerShell Universal

Note:
What I had to understand at the beginning is the fact that with LoadData you have to do the filtering, pagination, sorting etc. yourself in the LoadData script block. So everything that a user specifies/does in the table in the browser (entering filters, sorting a column, etc.) must be done in LoadData itself. If you enter a filter in a column, you have to calculate how many values match this filter and adjust the Pages and TotalSize etc. accordingly. If a user clicks on a column to sort it, you have to recognize this and sort your PowerShell object yourself.

The most important thing is that you pass a PowerShell object with, for example, 50000 data records. With the pagination that you calculate yourself, however, only e.g. 15 entries (PageSize) are obtained from the object and displayed. Therefore the initial loading is much faster, but it could take a little longer when changing pages.

Here is an example from one of our custom functions:

The code creates a hashtable with all parameters later used with splatting for New-UDTable.

$InputData or $Data contains your data for the table to show (in this example).


# Example $Data
$Data = [System.Collections.ArrayList]::new()
$Data = $InputData | ForEach-Object {
    [PSCustomObject]@{
        'Column1'   = [float]$_.column1
        'Column2'   = [string]$_.column2
        'Column3'   = [Int]$_.column3
        'Column4'   = [Int]$_.column4
        'Column5'   = [string]$_.column5
        'Column6'   = [Int]$_.column6
        'Column7'   = [Int]$_.column7
    }
}

#region New-UDTable parameter
$params_table = @{
    Id                   = "table_$($UniqueId)_table_data"
    Columns              = $objColumns
    PaginationLocation   = $PaginationLocation
    PageSize             = $PageSize
    ShowPagination       = $ShowPagination
    ShowFilter           = $ShowFilter
    ShowSearch           = $ShowSearch
    ShowExport           = $ShowExport
    ShowSort             = $true
    DefaultSortDirection = $DefaultSortDirection
    TextOption           = $Options
}

if ($Dense) {
    $params_table['Dense'] = $true
}


$params_table['LoadData'] = {
       # if something was added to the filter on top of the columns, filter the data
        foreach ($Filter in $EventData.Filters) {
            $Data = $Data | Where-Object -Property $Filter.Id -Match -Value $Filter.Value
        }

        # Get TotalCount of the input data to calculate pagination
        $TotalCount = $Data.Count

        # if one table column will be sorted (someone clicked on the sorting in the table, sort also the content
        if (-not [string]::IsNullOrEmpty($EventData.OrderBy.Field)) {
            $Descending = $EventData.OrderDirection -ne 'asc'
            $Data = $Data | Sort-Object -Property ($EventData.orderBy.Field) -Descending:$Descending
        }
        # calculate page amount, each page size (default 5) based on the total objects
        $Data = $Data | Select-Object -First $EventData.PageSize -Skip ($EventData.Page * $EventData.PageSize)
        # output the data to the table
        $Data | Out-UDTableData -Page $EventData.Page -TotalCount $TotalCount -Properties $EventData.Properties
    }
#endregion

New-UDTable @params_table