Thanks Sam! For the record, I tested your procedure (briefly) in my CU6 lab system and it returned data. I can't speak to the performance because something is bogging down my infrastructure right now and everything is performing quite slowly to begin with but I can at least confirm that it drew the charts.
-------------------------------------------
Original Message:
Sent: Dec 29, 2025 05:37 AM
From: Sam Green
Subject: PRD Slow Performance
/****** Object: StoredProcedure [dbo].[prdReportData_Raw] Script Date: 15/12/2025 11:05:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[prdReportData_Raw]
@INinterval int,
@INStartDate datetime,
@INEndDate datetime,
@INTableName varchar(255),
@INTableId varchar (MAX),
@INAggregate varchar(100),
@Interpolate INT
AS
BEGIN
DECLARE @duration int = 0;
DECLARE @filedname varchar(100) = 'samplevalue';
DECLARE @rnTable varchar(200)
DECLARE @hnTable varchar(200)
DECLARE @dnTable varchar(200)
SET NOCOUNT ON;
SET @rnTable = @INTableName
SET @hnTable = REPLACE(@INTableName,'RN','HN')
SET @dnTable = REPLACE(@INTableName,'RN','DN')
IF OBJECT_ID('tempdb..#temptbl') IS NOT NULL
DROP TABLE #temptbl
CREATE TABLE #temptbl (startdate datetime,enddate datetime)
IF OBJECT_ID('tempdb..#temptb2') IS NOT NULL
DROP TABLE #temptb2
CREATE TABLE #temptb2 (id INT IDENTITY(1,1), table_id int, tz_offset int, samplerate int)
DECLARE @Var VARCHAR(4000);
DECLARE @offsetQry NVARCHAR(1000);
DECLARE @interpolationNeeded INT;
SET @interpolationNeeded = 0
--Check if RN Table last sample collection is beyond the start date
DECLARE @SqlStartDates NVARCHAR(MAX);
DECLARE @rnstartdate as datetime
DECLARE @hnstartdate as datetime
DECLARE @dnstartdate as datetime
DECLARE @dnDataIncluded TINYINT
DECLARE @hnDataIncluded TINYINT
DECLARE @rnDataIncluded TINYINT
DECLARE @dnstartdate1 as datetime
DECLARE @dnenddate1 as datetime
SET @dnDataIncluded = 1
SET @hnDataIncluded = 1
SET @rnDataIncluded = 1
--PRINT 'DN Table: '+ @dnTable
--PRINT 'RN Table: '+ @rnTable
--PRINT 'HN Table: '+ @hnTable
SET @SqlStartDates = 'SELECT @rnstartdate = MIN(sampletime) FROM ' +(@rnTable)+' WITH(NOLOCK) '
+N' where table_id in ' +@INTableId+' ';
--PRINT 'rn query'+ @SqlStartDates
EXECUTE sp_executesql @SqlStartDates , N'@rnstartdate datetime OUTPUT',@rnstartdate = @rnstartdate OUTPUT
SET @SqlStartDates = 'SELECT @hnstartdate = MIN(sampletime) FROM ' +(@hnTable)+' WITH(NOLOCK) '
+N' where table_id in ' +@INTableId+' ';
--PRINT 'hn query'+ @SqlStartDates
EXECUTE sp_executesql @SqlStartDates , N'@hnstartdate datetime OUTPUT',@hnstartdate = @hnstartdate OUTPUT
SET @SqlStartDates = 'SELECT @dnstartdate = MIN(sampletime) FROM ' +(@dnTable)+' WITH(NOLOCK) '
+N' where table_id in ' +@INTableId+' ';
--PRINT 'dn query'+ @SqlStartDates
EXECUTE sp_executesql @SqlStartDates , N'@dnstartdate datetime OUTPUT',@dnstartdate = @dnstartdate OUTPUT
--PRINT ' @rnstartdate = '+ CONVERT(VARCHAR,@rnstartdate, 21)
--PRINT ' @hnstartdate = '+ CONVERT(VARCHAR,@hnstartdate, 21)
--PRINT ' @dnstartdate = '+ CONVERT(VARCHAR,@dnstartdate, 21)
SET @Var = substring(@INTableId,2,len(@INTableId)-2)
IF OBJECT_ID('tempdb..#ResltsTab1') IS NOT NULL
DROP TABLE #ResltsTab1
CREATE TABLE #ResltsTab1(
ID INT IDENTITY(1,1),
table_id INT ,
averageValue numeric(28, 2),
tz_offset INT ,
updatedTime DATETIME,
table_type INT
)
-- Check if Start date less than HN start date
IF @dnstartdate IS NOT NULL AND @INStartDate < @hnstartdate
BEGIN
--PRINT 'PROCESSING DN TABLES'
SET @dnstartdate1 = @INStartDate
IF @INEndDate >= @hnstartdate
SET @dnenddate1 = dateadd(minute, -60, @hnstartdate)
ELSE IF @INEndDate < @hnstartdate
BEGIN
SET @dnenddate1 = @INEndDate
--No need to collect HN & RN data
SET @hnDataIncluded = 0
SET @rnDataIncluded = 0
END
--PRINT 'DN: @dnstartdate1 = '+ CONVERT(VARCHAR,@dnstartdate1, 21)
--PRINT 'DN: @dnenddate1 = '+ CONVERT(VARCHAR,@dnenddate1, 21)
INSERT INTO #temptb2(table_id) SELECT * FROM dbo.splitstring(@Var, ',')
SET @offsetQry = N'UPDATE T1 '
+N'SET tz_offset = (SELECT TOP 1 TZ_OFFSET FROM '+ @dnTable +' WITH(NOLOCK) '
+N' where table_id = T1.table_id '
+N' and sampletime >= convert(datetime,''' + CONVERT(VARCHAR,@dnstartdate1, 21) + ''',21)'
+N' and sampletime <= convert(datetime,''' + CONVERT(VARCHAR,@dnenddate1, 21) + ''',21)), '
+N' samplerate = 86400 '
+N' FROM #temptb2 T1 '
--PRINT 'DN: '+ @offsetQry
EXECUTE sp_executesql @offsetQry
DELETE FROM #temptb2 WHERE tz_offset IS NULL
--SELECT * FROM #temptb2;
--------Changes for PRD RAW DATA--------------------------
if (@INinterval =0)
BEGIN
select @INinterval = MAX(samplerate) from #temptb2;
set @INinterval = @INinterval/60;
END;
if (@INinterval =0) set @INinterval =1;
--set @INStartDate =cast(@INStartDate as datetime);
DECLARE @date1 as datetime = dateadd(minute, -@INinterval, @dnstartdate1);
DECLARE @tempStartDate as datetime = @date1;
DECLARE @date2 as datetime = @dnstartdate1;
set @duration = DATEDIFF(minute, @dnstartdate1, @dnenddate1);
DECLARE @Counter int = @duration/@INinterval;
set @Counter = @Counter +1;
DECLARE @rem int =0;
set @rem = @duration % @INinterval
---------------END Changes-----------------------------------
--- Logic for HN AND RN Tables filed change
set @filedname = 'sampleavg';
TRUNCATE TABLE #temptbl;
WHILE @Counter > 0
BEGIN
SET NOCOUNT ON;
insert into #temptbl values(@date1,@date2);
set @date1 = dateadd(SECOND,1,@date2);
set @date2 = dateadd(MINUTE,@INinterval,@date2);
set @Counter = @Counter -1
END;
DECLARE @Sql1 NVARCHAR(MAX);
DECLARE @Sql2 NVARCHAR(MAX);
DECLARE @enddate1 as datetime
DECLARE @startdate1 as datetime
set @Sql1 = '(select @enddate1=max(enddate) from #temptbl where enddate < = (select max(sampletime) from ' +(@dnTable)+' WITH(NOLOCK) '
+N' where table_id in ' +@INTableId+' '
+N' and ( sampletime >= convert(datetime,''' + CONVERT(VARCHAR,@dnstartdate1, 21) + ''',21)'
+N' and sampletime <= convert(datetime,''' + CONVERT(VARCHAR,@dnenddate1, 21) + ''',21))))';
EXECUTE sp_executesql @Sql1 , N'@enddate1 datetime OUTPUT',@enddate1 = @enddate1 OUTPUT
set @Sql2 = '(select @startdate1=max(startdate) from #temptbl where startdate < = (select min(sampletime) from ' +(@dnTable)+' WITH(NOLOCK) '
+N' where table_id in ' +@INTableId+''
+N' and ( sampletime >= convert(datetime,''' + CONVERT(VARCHAR,@dnstartdate1, 21) + ''',21)'
+N' and sampletime <= convert(datetime,''' + CONVERT(VARCHAR,@dnenddate1, 21) + ''',21))))';
EXECUTE sp_executesql @Sql2 , N'@startdate1 datetime OUTPUT',@startdate1 = @startdate1 OUTPUT
-- print '@enddate1 ='+ CONVERT(VARCHAR,@enddate1, 21) + ' ** @startdate1='+ CONVERT(VARCHAR,@startdate1, 21)
--SELECT * FROM #temptbl
DECLARE @Sql NVARCHAR(MAX);
DECLARE @NoData NVARCHAR(MAX);
IF NOT( @enddate1 IS NULL and @startdate1 IS NULL)
BEGIN
SET @Sql = N'INSERT INTO #ResltsTab1(table_id, averageValue, tz_offset, updatedTime, table_type) SELECT table_id, avgvalue, tz_offset, enddate, 1 ' +
N' FROM ( ' +
N' SELECT a.tz_offset, a.table_id, b.enddate, ( ' +
N' Select ' + @INAggregate + '(' + @filedname + ' ) avgvalue ' +
N' FROM ' + @dnTable + ' WITH(NOLOCK) ' +
N' WHERE table_id = a.table_id AND sampletime >= b.startdate and sampletime <= b.enddate ' +
N' ) avgvalue ' +
N' FROM #temptbl b, #temptb2 a ' +
N' ) TAB1 ' +
N' order by table_id, enddate ';
--PRINT 'DN : '+ @Sql
EXECUTE sp_executesql @Sql
--SELECT * FROM #ResltsTab1;
--RESET Start date to DN end date
SET @INStartDate = @dnenddate1
IF @Interpolate = 1
BEGIN
--PRINT 'INTERPOLATING DATA -- DN'
EXEC [prdInterpolateData] @INinterval = @INinterval,@INAggregate = @INAggregate,@filedname =@filedname,@QTable = @dnTable, @TableType = 1
END
END
--SELECT * FROM #ResltsTab1
END
DECLARE @hnstartdate1 as datetime
DECLARE @hnenddate1 as datetime
--PRINT ' Current @INStartDate = '+ CONVERT(VARCHAR,@INStartDate, 21)
-- Check if Start date less than HN start date
IF @hnstartdate IS NOT NULL AND @INStartDate < @rnstartdate AND @hnDataIncluded > 0
BEGIN
--PRINT 'PROCESSING HN TABLES'
SET @hnstartdate1 = @INStartDate
IF @INEndDate >= @rnstartdate
SET @hnenddate1 = dateadd(minute, -60, @rnstartdate)
ELSE IF @INEndDate < @rnstartdate
BEGIN
SET @hnenddate1 = @INEndDate
--No need to collect RN data
SET @rnDataIncluded = 0
END
--PRINT ' @hnstartdate1 = '+ CONVERT(VARCHAR,@hnstartdate1, 21)
--PRINT ' @hnenddate1 = '+ CONVERT(VARCHAR,@hnenddate1, 21)
TRUNCATE TABLE #temptb2;
INSERT INTO #temptb2(table_id) SELECT * FROM dbo.splitstring(@Var, ',')
SET @offsetQry = N'UPDATE T1 '
+N'SET tz_offset = (SELECT TOP 1 TZ_OFFSET FROM '+ @hnTable +' WITH(NOLOCK) '
+N' where table_id = T1.table_id '
+N' and sampletime >= convert(datetime,''' + CONVERT(VARCHAR,@hnstartdate1, 21) + ''',21)'
+N' and sampletime <= convert(datetime,''' + CONVERT(VARCHAR,@hnenddate1, 21) + ''',21)), '
+N' samplerate = 3600 '
+N' FROM #temptb2 T1 '
--PRINT @offsetQry
EXECUTE sp_executesql @offsetQry
DELETE FROM #temptb2 WHERE tz_offset IS NULL
--SELECT * FROM #temptb2
--------Changes for PRD RAW DATA--------------------------
if (@INinterval =0)
BEGIN
select @INinterval = MAX(samplerate) from #temptb2;
set @INinterval = @INinterval/60;
END;
if (@INinterval =0) set @INinterval =1;
set @INStartDate =cast(@hnstartdate1 as datetime);
set @date1 = dateadd(minute, -@INinterval, @hnstartdate1);
set @tempStartDate = @date1;
set @date2 = @INStartDate;
set @duration = DATEDIFF(minute, @hnstartdate1, @hnenddate1);
set @Counter = @duration/@INinterval;
set @Counter = @Counter +1;
set @rem =0;
set @rem = @duration % @INinterval
---------------END Changes-----------------------------------
--- Logic for HN AND RN Tables filed change
set @filedname = 'sampleavg';
TRUNCATE TABLE #temptbl;
WHILE @Counter > 0
BEGIN
SET NOCOUNT ON;
insert into #temptbl values(@date1,@date2);
set @date1 = dateadd(SECOND,1,@date2);
set @date2 = dateadd(MINUTE,@INinterval,@date2);
set @Counter = @Counter -1
END;
DECLARE @Sql1HN NVARCHAR(MAX);
DECLARE @Sql2HN NVARCHAR(MAX);
DECLARE @enddatehn1 as datetime
DECLARE @startdatehn1 as datetime
set @Sql1HN = '(select @enddatehn1=max(enddate) from #temptbl where enddate < = (select max(sampletime) from ' +(@hnTable)+' WITH(NOLOCK) '
+N' where table_id in ' +@INTableId+' '
+N' and ( sampletime >= convert(datetime,''' + CONVERT(VARCHAR,@hnstartdate1, 21) + ''',21)'
+N' and sampletime <= convert(datetime,''' + CONVERT(VARCHAR,@hnenddate1, 21) + ''',21))))';
EXECUTE sp_executesql @Sql1HN , N'@enddatehn1 datetime OUTPUT',@enddatehn1 = @enddatehn1 OUTPUT
set @Sql2HN = '(select @startdatehn1=max(startdate) from #temptbl where startdate < = (select min(sampletime) from ' +(@hnTable)+' WITH(NOLOCK) '
+N' where table_id in ' +@INTableId+''
+N' and ( sampletime >= convert(datetime,''' + CONVERT(VARCHAR,@hnstartdate1, 21) + ''',21)'
+N' and sampletime <= convert(datetime,''' + CONVERT(VARCHAR,@hnenddate1, 21) + ''',21))))';
EXECUTE sp_executesql @Sql2HN , N'@startdatehn1 datetime OUTPUT',@startdatehn1 = @startdatehn1 OUTPUT
--print '@startdatehn1 ='+ CONVERT(VARCHAR,@startdatehn1, 21) + ' ** @enddatehn1='+ CONVERT(VARCHAR,@enddatehn1, 21)
--SELECT * FROM #temptbl
IF NOT ( @enddatehn1 IS NULL and @startdatehn1 IS NULL)
BEGIN
SET @Sql = N'INSERT INTO #ResltsTab1(table_id, averageValue, tz_offset, updatedTime, table_type) SELECT table_id, avgvalue, tz_offset, enddate, 2 ' +
N' FROM ( ' +
N' SELECT a.tz_offset, a.table_id, b.enddate, ( ' +
N' Select ' + @INAggregate + '(' + @filedname + ' ) avgvalue ' +
N' FROM ' + @hnTable + ' WITH(NOLOCK) ' +
N' WHERE table_id = a.table_id AND sampletime >= b.startdate and sampletime <= b.enddate ' +
N' ) avgvalue ' +
N' FROM #temptbl b, #temptb2 a ' +
N' ) TAB1 ' +
N' order by table_id, enddate ';
-- PRINT @Sql
EXECUTE sp_executesql @Sql
--SELECT * FROM #ResltsTab2;
--RESET Start date to DN end date
SET @INStartDate = @hnenddate1
IF @Interpolate = 1
BEGIN
--PRINT 'INTERPOLATING DATA -- HN'
EXEC [prdInterpolateData] @INinterval = @INinterval,@INAggregate = @INAggregate,@filedname =@filedname,@QTable = @hnTable, @TableType = 2
END
END;
END
--Processing RN tables
IF @rnDataIncluded > 0
BEGIN
--PRINT 'PROCESSING RN Tables...'
--PRINT ' @INStartDate = '+ CONVERT(VARCHAR,@INStartDate, 21)
--PRINT ' @INEndDate = '+ CONVERT(VARCHAR,@INEndDate, 21)
TRUNCATE TABLE #temptb2;
INSERT INTO #temptb2(table_id) SELECT * FROM dbo.splitstring(@Var, ',')
SET @offsetQry = N'UPDATE T1 '
+N'SET tz_offset = (SELECT TOP 1 TZ_OFFSET FROM '+ @INTableName + ' WITH(NOLOCK) ' +
+N' where TABLE_ID = T1.table_id '
+N' and sampletime >= convert(datetime,''' + CONVERT(VARCHAR,@INStartDate, 21) + ''',21)'
+N' and sampletime <= convert(datetime,''' + CONVERT(VARCHAR,@INEndDate, 21) + ''',21)), '
+N' samplerate = (SELECT MAX(samplerate) FROM '+ @INTableName
+N' where TABLE_ID = T1.table_id '
+N' and sampletime >= convert(datetime,''' + CONVERT(VARCHAR,@INStartDate, 21) + ''',21)'
+N' and sampletime <= convert(datetime,''' + CONVERT(VARCHAR,@INEndDate, 21) + ''',21)) '
+N' FROM #temptb2 T1 '
--PRINT @offsetQry
EXECUTE sp_executesql @offsetQry
DELETE FROM #temptb2 WHERE tz_offset IS NULL
--SELECT * FROM #temptb2
--------Changes for PRD RAW DATA--------------------------
if (@INinterval =0)
BEGIN
select @INinterval = MAX(samplerate) from #temptb2;
SET @INinterval = @INinterval/60;
END;
if (@INinterval =0) set @INinterval =1;
-- IF aggregation interval is less than sample time and Interpolation is not enabled - Treat it as RAW Data
DECLARE @minSampleRate as INT
SELECT @minSampleRate = MIN(samplerate) FROM #temptb2;
--PRINT 'MINIMUM SAMPLE RATE : '+ convert(varchar(20), @INinterval)
--PRINT 'INTERVAL : '+ convert(varchar(20), @minSampleRate)
IF (@INinterval * 60) < @minSampleRate AND @Interpolate = 0
BEGIN
SET @INinterval = @minSampleRate/60
END
SET @INStartDate =cast(@INStartDate as datetime);
SET @date1 = dateadd(minute, -@INinterval, @INStartDate);
SET @tempStartDate = @date1;
SET @date2 = @INStartDate;
SET @duration = DATEDIFF(minute, @INStartDate, @INEndDate);
SET @Counter = @duration/@INinterval;
SET @Counter = @Counter +1;
SET @rem =0;
SET @rem = @duration % @INinterval
---------------END Changes-----------------------------------
--- Logic for HN AND RN Tables filed change
set @filedname = 'samplevalue';
--PRINT ' @date1 = '+ CONVERT(VARCHAR,@date1, 21)
--PRINT ' @date2 = '+ CONVERT(VARCHAR,@date2, 21)
TRUNCATE TABLE #temptbl;
WHILE @Counter > 0
BEGIN
SET NOCOUNT ON;
insert into #temptbl values(@date1,@date2);
set @date1 = dateadd(SECOND,1,@date2);
set @date2 = dateadd(MINUTE,@INinterval,@date2);
set @Counter = @Counter -1
END;
set @Sql1 = '(select @enddate1=max(enddate) from #temptbl where enddate < = (select max(sampletime) from ' +(@rnTable)+' WITH(NOLOCK) '
+N' where table_id in ' +@INTableId+' '
+N' and ( sampletime >= convert(datetime,''' + CONVERT(VARCHAR,@INStartDate, 21) + ''',21)'
+N' and sampletime <= convert(datetime,''' + CONVERT(VARCHAR,@INEndDate, 21) + ''',21))))';
EXECUTE sp_executesql @Sql1 , N'@enddate1 datetime OUTPUT',@enddate1 = @enddate1 OUTPUT
set @Sql2 = '(select @startdate1=max(startdate) from #temptbl where startdate < = (select min(sampletime) from ' +(@rnTable)+' WITH(NOLOCK) '
+N' where table_id in ' +@INTableId+''
+N' and ( sampletime >= convert(datetime,''' + CONVERT(VARCHAR,@INStartDate, 21) + ''',21)'
+N' and sampletime <= convert(datetime,''' + CONVERT(VARCHAR,@INEndDate, 21) + ''',21))))';
EXECUTE sp_executesql @Sql2 , N'@startdate1 datetime OUTPUT',@startdate1 = @startdate1 OUTPUT
--SELECT * FROM #temptbl
IF NOT ( @enddate1 IS NULL and @startdate1 IS NULL)
BEGIN
-------------------------------------------------------------------------
/*Changed by NB 20230913 - Improve performance on large table scans*/
IF OBJECT_ID('tempdb..#temptb3') IS NOT NULL
DROP TABLE #temptb3
CREATE TABLE #temptb3(sampletime DATETIME, samplevalue NUMERIC(28,2))
DECLARE @rnSQL NVARCHAR(MAX)
SET @rnSQL =
'INSERT INTO #temptb3(sampletime,samplevalue)
select
sampletime,
samplevalue
from ' + @INTableName + ' WITH(NOLOCK)
where table_id = ' + @INTableId
PRINT @rnSQL
EXECUTE sp_executesql @rnSql
CREATE CLUSTERED INDEX clx_tmptb3 ON #temptb3(sampletime)
-------------------------------------------------------------------------
--SET @Sql = N'INSERT INTO #ResltsTab3(table_id, averageValue, tz_offset, updatedTime) SELECT table_id, avgvalue, tz_offset, enddate ' +
--N' FROM ( ' +
--N' SELECT a.tz_offset, a.table_id, b.enddate, ( ' +
--N' Select ' + @INAggregate + '(' + @filedname + ' ) avgvalue ' +
--N' FROM ' + @INTableName + ' WITH(NOLOCK) ' +
--N' WHERE table_id = a.table_id AND sampletime >= b.startdate and sampletime <= b.enddate ' +
--N' ) avgvalue ' +
--N' FROM #temptbl b, #temptb2 a ' +
--N' ) TAB1 ' +
--N' order by table_id, enddate ';
SET @Sql = N'INSERT INTO #ResltsTab1(table_id, averageValue, tz_offset, updatedTime) SELECT table_id, avgvalue, tz_offset, enddate ' +
N' FROM ( ' +
N' SELECT a.tz_offset, a.table_id, b.enddate, ( ' +
N' Select ' + @INAggregate + '(' + @filedname + ' ) avgvalue ' +
N' FROM #temptb3 ' +
N' WHERE sampletime >= b.startdate and sampletime <= b.enddate ' +
N' ) avgvalue ' +
N' FROM #temptbl b, #temptb2 a ' +
N' ) TAB1 ' +
N' order by table_id, enddate ';
--print '---------------------------------'
--PRINT @Sql
EXECUTE sp_executesql @Sql
--SELECT * FROM #ResltsTab3;
IF @Interpolate = 1
BEGIN
--PRINT 'INTERPOLATING DATA'
EXEC [prdInterpolateData] @INinterval = @INinterval,@INAggregate = @INAggregate,@filedname =@filedname,@QTable = @INTableName, @TableType = 3
END
END; --
END
SELECT table_id, averageValue samplevalue, tz_offset,updatedTime sampleTime FROM #ResltsTab1
ORDER BY table_type, table_id, sampleTime;
DROP TABLE #temptbl
DROP TABLE #temptb2
DROP TABLE #ResltsTab1
END;
Original Message:
Sent: Dec 29, 2025 05:35 AM
From: Sam Green
Subject: PRD Slow Performance
Hi Garin,
Attached is the amended SP which resolved the speed issues for us. It has been commented and supplied to Broadcom for review.
Please note, this is for release 23.4.4
Edit, my files doesn't appear to have uploaded...
Original Message:
Sent: Oct 04, 2025 12:41 AM
From: Garin Walsh
Subject: PRD Slow Performance
Absolutely have the same issue. Been told it was fixed several times since PRD was released but not successfully or completely. There's been a comment made that maybe in CU6 it'll be corrected for good.
One stopgap that works sometimes is to ensure that the first thing you do when creating a new report is to make the sample period one hour or greater before changing the length of the report. This helps but it's no good if you don't want everything heavily smoothed when displayed.
Would love to see what your DBA did to fix the sp - generally speaking for us the PRD feature is unusable and anything would be better than what's there now.
Original Message:
Sent: Oct 03, 2025 05:46 AM
From: Sam Green
Subject: PRD Slow Performance
Hi Community,
We've had a problem with the PRD for a long time where retrieving Disk usage takes a long time (5-7 minutes for each metric), other cdm metrics take a long time too.
This was reported to support, after much investigation it was determined that the stored procedure (dbo.prdReportData_Raw) getting the data from the DB was loading ALL the data in the table, and then selecting the "last hour" or whatever the selection is in PRD.
Supports solution was to hold less data and to lower the retention settings in the data_engine. Our retention settings are Raw data for 60 days and historic for 90 days.
I've used these settings in several other UIM environments and never had any problems.
Our DBA modified the SP to be more efficient and this did resolve the problem, but the SP needs modifying after every upgrade.
Engineering have this on their backlog to resolve under Feature ID F142973.
The problem has become apparent as our DB Server is hitting 100% cpu for 2-3 hours each morning when 3 scheduled reports are trying to run. This then causes our live dashboards to load slow, or never load at all.
Has anyone else experienced this? Any advice is appreciated.
Cheers,
Sam
-------------------------------------------