E9.2 Report takes more time to run in PD

[Ashwin Ramkumar]

Hi
I have a Custom report which takes 4 mins to run in DV and for the same data selection its taking 16 mins to run in PD. The Data refresh was done on DV recently , So both the PD and DV have the same number of data. The sequencing and everything is same in both PD and DV. The Report's Business view has a join on F15L109/F0901/F0006 tables.
R5515L109A - AREF Header Base Account Definition - All Fields

F15L109.AID Left Outer(*=) = F0901.AID

F15L109.MCU Simple(==) = F0006.MCU

Can someone tell what might be the reason for these much time differences ?

 

[Larry_Jones]

Look at the job execution detail in work with submitted jobs. Drill down to see where it’s spending the most time.

 

[DaveWagoner]

My first thought is that your DBA should take a look to see if the query being executed can be optimized in PD. Making sure the correct indexes and such were built. On AS400 I'd ask for a SQL package refresh.

My experiences are based on superstition and wand waving more than anything.

 

[EggyPaw]

Dave is right , we had the same issue in our company and it was related to indexes

 

[MFreitag]

Depending on how you do your data refreshes and what the underlying DBMS might be my first guess would also be indexes or query execution plans. If you could give us more insight on that, we might be able to help you a lot better

 

[brother_of_karamazov]

My first thought is that your DBA should take a look to see if the query being executed can be optimized in PD. Making sure the correct indexes and such were built. On AS400 I'd ask for a SQL package refresh.

My experiences are based on superstition and wand waving more than anything.

Run these in both environments.

Do this first (only one time):

USE MASTER
GO
IF exists (select * from sys.objects where name = N'fn_PhysicalIndexStatistics_Wrapper')
    DROP FUNCTION fn_PhysicalIndexStatistics_Wrapper;
GO
CREATE FUNCTION fn_PhysicalIndexStatistics_Wrapper
(@DatabaseID INT,
@ObjectID INT,
@IndexID INT,
@PartitionNumber INT,
@Mode VARCHAR)
 
RETURNS @IndexStats TABLE
([Object ID] INT,
[Index ID] INT,
[Index Description] VARCHAR(30),
[Avg. Fragmentation %] FLOAT,
[Fragment Count] BIGINT,
[Page Count] BIGINT,
[Alloc. Unit Type] nvarchar(60))
 
BEGIN    
INSERT INTO @IndexStats
([Object ID], [Index ID], [Index Description],  [Avg. Fragmentation %], [Fragment Count], [Page Count], [Alloc. Unit Type])

SELECT
object_id, index_id, index_type_desc, round(avg_fragmentation_in_percent, 0), fragment_count, page_count, alloc_unit_type_desc
FROM sys.dm_db_index_physical_stats
(@DatabaseID,
@ObjectID,
@IndexID,
@PartitionNumber,
@Mode)

RETURN
END

Then this:

use databasename


SELECT cast(DB_NAME() + '.' + sys.schemas.name + '.' + sys.objects.name as char(40)) as 'Object Name',
cast(sys.indexes.name as char(40)) as 'Index Name',
master.dbo.fn_PhysicalIndexStatistics_Wrapper.[Index Description],
master.dbo.fn_PhysicalIndexStatistics_Wrapper.[Avg. Fragmentation %],
master.dbo.fn_PhysicalIndexStatistics_Wrapper.[Fragment Count],
master.dbo.fn_PhysicalIndexStatistics_Wrapper.[Page Count]
FROM sys.objects
JOIN sys.schemas on sys.objects.schema_id = sys.schemas.schema_id
CROSS APPLY master.dbo.fn_PhysicalIndexStatistics_Wrapper(DB_ID(), object_id, NULL, NULL, NULL)
JOIN sys.indexes
on master.dbo.fn_PhysicalIndexStatistics_Wrapper.[Object Id]=sys.indexes.object_id
and master.dbo.fn_PhysicalIndexStatistics_Wrapper.[Index Id]=sys.indexes.index_id
WHERE [Index Description] ! = 'HEAP'
--and [Page Count] > 100
--order by [Object Name], [Index Name]
--or
order by [Avg. Fragmentation %] desc