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'
      ,[ActionName]
      ,[LastStatusMessageIDName]
      ,[ExitCode]
  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
    LEFT OUTER JOIN DATABASE.dbo.v_Package
	    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:- https://docs.universaldashboard.io/components/grids#server-side-processing

I’m curious why you want to load the data from CSV rather than from a direct SQL query? I’m doing something similar reading other ConfigMgr views in SQL, like v_Collection and vWorkstationStatus using the DbaTools module and it seems to work fine.

Hi @skatterbrainz so the reason behind me loading the data from CSV as opposed to directly from SQL, is the database which I have no control over and I have only access to a VIEW takes a long-time to actually run the SQL queries I am using on my dashboard, like sometimes up to 1 minute or more. So instead of the user waiting on my dashboard for the chart to actually show anything, if I already have that same data loaded into a CSV file from a scheduled job then pulling the information into a chart is going to be loads quicker, as the end-user is not waiting 1+ minute for the SQL query to finish, then to load the data. Hence why I went this route. I haven’t used this same approach for all my dashboard, most I do pull directly from SQL.

1 Like

That makes sense, but gadzooks! Something doesn’t sound right with that query. I have queries I use with customers for hundreds of thousands of rows with 10-12 tables/views linked and they run in a few seconds. I’d take the DBA folks out and get them really drunk and discuss indexing or QO,etc. Can you post/share the query that takes that long? I may be able to test in my lab to see what’s going on. I don’t mean to sound snarky, just trying to help.

@skatterbrainz appreciated. Sadly this only seems to happen on the database that runs bespoke software that we paid a stupid amount of money for, and I am limited to only using certain views that the developers create…when I look at the design behind the view I can totally see why performance sucks, but saldy my hands are tied as I am given what I am given to work with. I also create a lot of my own databases, and always run direct SQL to UD from my own DBs and it works great…the dashboard in question I posted in the show off here:-


which is reading from CSV as the queries took to long, as you can see via CSV it was instant load.

1 Like