I have a query when i execute it on development and production server this it takes CPU consumption increase to 100%.
it is due to outer apply i also tried with sub query and with inner join but occurs same issue
What I have tried:
declare @fJno varchar (50)=820,
@noOfRows int =10,
@toJno varchar (50)=828
select top (@noOfRows) z.AgentName Nick,
upper(agnt.AgentName)AgentName
,sum (z.[Local])[Local]
,sum(z.[Foreign])[Foreign]
,(sum (z.[Local])+sum(z.[Foreign]))Total
from (
select * from( SELECT
Substring(LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(upper(AgentName), '&', ''), '(', ''), ')', ''), ',', ''), '.', ''), '-', ''))), 1,Charindex(' ', LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(upper(AgentName), '&', ''), '(', ''), ')', ''), ',', ''), '.', ''), '-', '')))+' ')-1) [AgentName]
,( [Local])[Local]
,( [Foreign])[Foreign]
, [Local]+[Foreign] as Total
FROM
(
SELECT
LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(upper(p.AgentName), '&', ''), '(', ''), ')', ''), ',', ''), '.', ''), '-', '')))AgentName
,(case when CompanyCountry like '%pakistan%' then 'Local' when CompanyCountry != '%pakistan%' then 'Foreign' else '' end) as CompanyCountry
,FORMAT( JournalDate,'MMMyy')JournalDate
FROM
[TheLegalFiles-Dev].dbo.SearchProductGoodsClass p
where
LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(upper(p.AgentName), '&', ''), '(', ''), ')', ''), ',', ''), '.', ''), '-', ''))) like ''+Substring(LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(upper(p.AgentName), '&', ''), '(', ''), ')', ''), ',', ''), '.', ''), '-', ''))), 1,Charindex(' ', LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(upper(p.AgentName), '&', ''), '(', ''), ')', ''), ',', ''), '.', ''), '-', '')))+' ')-1)+'%'
and
JournalNo >= @fJno and JournalNo<=@toJno
) t
PIVOT(
count(CompanyCountry) FOR CompanyCountry IN ([Local], [Foreign])
) AS pivot_table )pvt
)z
outer apply (
select top 1 agentname from [TheLegalFiles-Dev].dbo.SearchProductGoodsClass where DataEntryStatusid=1 and JournalNo> = @fJno and JournalNo<=@toJno and agentname is not null and Substring(LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(upper(AgentName), '&', ''), '(', ''), ')', ''), ',', ''), '.', ''), '-', ''))), 1,Charindex(' ', LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(upper(AgentName), '&', ''), '(', ''), ')', ''), ',', ''), '.', ''), '-', '')))+' ')-1) like ''+ z.AgentName +''
) agnt
group by z.AgentName
,agnt.AgentName
order by (sum (z.[Local])+sum(z.[Foreign])) desc