Slowness issue when processing Inventory Audit Report in SAP B1

[hemabhushan_g]

I am having a problem with our report processing (Inventory Audit Report), it takes more than 5 hrs to generate the report.

SAP B1 9.3 (9.30.150) PL: 05

SQL details:

Microsoft SQL Server Management Studio-12.0.6164.21

Microsoft .NET Framework-4.0.30319.42000

Thanks in advance for any suggestions

[azizelmir]

Declare @DATEFROM as datetime
Declare @DATETILL as datetime
Declare @WHSE as nvarchar(20)

select @DATEFROM = T0.Docdate from OINM T0 where T0.Docdate = '[%01]' 
select @DATETILL = T0.Docdate from OINM T0 where T0.Docdate = '[%02]' 

set @DATEFROM = '[%01]' 
set @DATETILL = '[%02]' 

SELECT t1.*
FROM
(
    SELECT '___Mvt' AS [Trans Type],
		 
           InvntAct as 'G/L Account',
           (Select Name from [@TRANSTYPE] where Code=OINM.TransType) + ' ' +  BASE_REF as 'Document',
           CreateDate as 'System Date',
           DocDate as 'Posting Date',
           Comments,
           ItemCode,
           Dscription,
           (select Location from OLCT where code =cast(OINM.Location as int)) 'Loaction' ,
           Warehouse,
           PrjCode,
           InQty,
           OutQty,
           (InQty-OutQty) as 'Bal Qty',
           CalcPrice,
           TransValue
           
                      
    FROM OINM
    WHERE 
           Transvalue<>0  
          AND (DocDate >= @DATEFROM  AND DocDate <= @DATETILL)
 
    UNION ALL
    
    SELECT 'Opening Balance' AS [Trans Type],
		  '',
		  '',
           NULL,
           NULL,
           '',
           '',
           NULL,
           '',
           '',
           '',
           SUM(InQty) AS 'INq Opening',
           SUM(OutQty) AS 'OUTq Opening',
           SUM(InQty)- SUM(OutQty) 'Bal Qty',
           Null,
           SUM(TransValue) AS 'TransValue'
        
    FROM OINM
    WHERE  Transvalue<>0  
          AND DocDate < @DATEFROM
) AS t1

ORDER BY t1.[Trans Type] DESC;

[hemabhushan_g]

Thanks for your query, we modified our existing query and now its working fine as per our requirment.