New-UDGrid - Getting data from sql

Universal Dashboard Universal Dashboard Help
1

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"
2

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

3

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

4

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

5

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.

6

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
7

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.

8

@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