New-UDGrid - Getting data from sql

Maybe I’m missing something but if I have an sql query to pull some data then want to feed it to a grid, the grid just loads forever. But if I take that same sql query and export it to csv, then in my grid import that csv data and out put it to the grid it works just fine. When i check the sql version the UD.exe on the server loads over 1GB in ram and just sits there.

If I sub OSD_Data with my sql query is where it fails/hangs.

New-UDRow -Columns {
    New-UDColumn -Size 12 -Endpoint  {
        $osd_data = import-csv D:\ud\osd_status.csv
        New-UdGrid -Title "OSD Status" -PageSize 25 -Headers @("TS","ComputerName","ExecutionTime","ActionName","Status","ExitCode") -Properties @("Name","ComputerName","ExecutionTime","ActionName","LastStatusMessageIDName","ExitCode") -Endpoint {
            $osd_data | Out-UDGridData
        $osd_data = $null

Here is the sql query version.

$osd_query = Invoke-Sqlcmd -ServerInstance SERVER -Database DATABASE -Query "
SELECT DATABASE.dbo.v_package.Name
      ,[DATABASE].[dbo].[v_R_System].Name0 as 'ComputerName'
      ,[ExecutionTime] AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time' as 'ExecutionTime'
  FROM [DATABASE].[dbo].[v_TaskExecutionStatus]
    LEFT OUTER JOIN [DATABASE].[dbo].[v_R_System]
	    ON [DATABASE].[dbo].[v_TaskExecutionStatus].[ResourceID] = [DATABASE].[dbo].[v_R_System].[ResourceID]
    LEFT OUTER JOIN [DATABASE].[dbo].[v_Advertisement]
	    ON [DATABASE].[dbo].[v_TaskExecutionStatus].[AdvertisementID] = [DATABASE].[dbo].[v_Advertisement].AdvertisementID
	    ON DATABASE.dbo.v_Advertisement.PackageID = DATABASE.dbo.v_Package.PackageID

  where ExecutionTime >= DATEADD(day, -2, GETDATE())
  order	by ExecutionTime Desc"

Hello fella,
I use a lot of SQL and loading this into Grids, and unless your looking at thousands and thousands of rows then normally I do not have any issues. I see I am doing an additional select where you don’t:-

> New-UdGrid -Title "OSD Status" -PageSize 25 -Headers @("TS","ComputerName","ExecutionTime","ActionName","Status","ExitCode") -Properties @("Name","ComputerName","ExecutionTime","ActionName","LastStatusMessageIDName","ExitCode") -Endpoint {
>             $osd_data | SELECT_OBJECT "TS","ComputerName","ExecutionTime","ActionName","Status","ExitCode" | Out-UDGridData

Also have you tried using the “server side processing” switch to see if that improves performance? Finally to prove this is not a 2.5.2 bug / issue have you downloaded an older version of UD from powershellgallery and tried same code on older dashboard…I just found my nivo heat maps do not work in 2.5.2

hmm could be cause its 13k rows. >.<

Most likely the issue is the 13k rows…lol…I loaded all my nivo charts from CSV as they seem to load better from CSV. I remember I used that -ServerSideProcessing parameter, which did improve the load time of the grid, but it brought back all the rows in one grid with no NEXT buttons. That was 2.4.0 I tried that with…there is some info on the server-side-processing parameter here:-