SQL Query tuning

looks like this part of the SQL might be taking most part of the time... If you can remove the substring, replace functions and case statement from the where clause by getting the polished data columns that you need in a temp table or a CTE and using those (CTE/Temp table) columns in your where clause might help you increase the performance

something like we suggested in this post here

https://www.experts-exchange.com/questions/28285583/T-SQL-How-to-speed-up-a-JOIN-using-a-CAST-REPLACE-expression.html

FROM [BCAMDB].dbo.SAP_ZVBRP SAP_ZVBRPINV

      WHERE SAP_ZVBRPINV.SO LIKE LTRIM(CAST(INV.[SO] as varchar(50)))+'%') as decimal (18,2)) AS VARCHAR(50)) AS [Invoice $ to date],

      CASE  WHEN COUNT(INV.SO)>0 THEN

      ''

      ELSE

      INV.[Billing Document] END AS [Invoice Number],

      CASE  WHEN COUNT(INV.SO)>0 THEN

      ''

      ELSE

      CAST (INV.[Billing Date] AS date) END AS [Invoice Date],

 

      /********************DATA 7***********************/

      CUST.[SO Net Value] - INV.[Line Net Value] AS [Invoice $ Delta],

      CONVERT(VARCHAR(2000),PRA.[comments]) As [Comments]

FROM 

		REACT.dbo.ps_requests_asr PRA WITH(NOLOCK)   

      LEFT JOIN [BCAMDB].dbo.CPOA CPON WITH(NOLOCK) ON REPLACE(REPLACE(CPON.Inquiries, '[', ''),']', '') = PRA.inq_num   

      LEFT JOIN [BCAMDB].dbo.BI_CUSTMON CUST WITH(NOLOCK) ON CPON.[PO Number]  = SUBSTRING(CUST.[Customer PO], 1, LEN(CPON.[PO Number]))

      LEFT JOIN [BCAMDB].dbo.BI_INVOICING INV WITH(NOLOCK) ON CUST.SO=INV.SO 

      LEFT JOIN [BCAMDB].dbo.SAP_ZVBAK BAK WITH(NOLOCK) ON CUST.SO=BAK.SO 

      LEFT JOIN [BCAMDB].dbo.SAP_ZVBRP BRP WITH(NOLOCK) ON INV.SO=BRP.SO

and ISNULL(PRA.inq_num,'') <> '' 

Select allOpen in new window

I have tried with temp table but i didn't get any improvement in execution time.

How to reduce "stream aggregate (aggregate) " cost?

I don't see any query plan, so I can't follow what you're saying.

But, for any query, I can tell you that such a massive GROUP BY will destroy your performance.

Please find the attached query execution plan
execution-plan.sqlplan

There are 2 correlated subqueries in the select clause.
Both use "TOP 1", removing the TOP 1 would help immediately.

Note as it stands these correlated subqueries are causing substantial "rebinds" and the top 1 is causing very expensive sorts.

Replacing both these inefficient subquery types with more efficient join (perhaps as subqueries) is a top prioriity.

-------------
These joins involving functions are inefficient. Is all that processing absolutely necessary?
      LEFT JOIN [BCAMDB].dbo.CPOA CPON WITH (NOLOCK)
            ON REPLACE(REPLACE(CPON.Inquiries, '[', ''), ']', '') = PRA.inq_num -- *1

      LEFT JOIN [BCAMDB].dbo.BI_CUSTMON CUST WITH (NOLOCK)
            ON CPON.[PO Number] = SUBSTRING(CUST.[Customer PO], 1, LEN(CPON.[PO Number])) --*2
           
Ideally there is no need for such manipulation in joining tables.

If you cannot get rid of those manipulations can you minimize them?      
*1
REACT.dbo.ps_requests_asr PRA appears to be the smaller of the 2 tables
could you add '[' & ']' to  PRA.inq_num istead?

      LEFT JOIN [BCAMDB].dbo.CPOA CPON WITH (NOLOCK)
            ON RCPON.Inquiries = '[' + PRA.inq_num + ']'
           
*2
      LEFT JOIN [BCAMDB].dbo.BI_CUSTMON CUST WITH (NOLOCK)
            ON  CUST.[Customer PO] LIKE CPON.[PO Number] + '%'
-----------
this line:
                  AND ISNULL(PRA.inq_num, '') <> ''
I think should be:
                  WHERE PRA.inq_num IS NOT NULL

right now it is being handled as part of a  left join, and that join appears redundant (next)
-------------

You join a table but then do not use it in select, group or order clauses
      LEFT JOIN [BCAMDB].dbo.SAP_ZVBRP BRP WITH (NOLOCK)
            ON INV.SO = BRP.SO

-----------
basically:

You have to find a better way to arrive at those summed values.

Only you will know enough about the data to know if you can make those joins more efficient - but I would be trying to ensure I had better ways to join the tables.

--