How to optimize SQL query its getting to much time to execute

Question

1.00/5 (2 votes)

See more:

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:

SQL

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
		--,JournalNo
		,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--,LTRIM(RTRIM( z.JournalDate))
,agnt.AgentName
order by (sum (z.[Local])+sum(z.[Foreign])) desc

Posted 29-Apr-20 19:58pm

Noman Suleman

Updated 30-Apr-20 3:56am

RickZeeland

v2

Add a Solution

Comments

Richard MacCutchan 30-Apr-20 5:38am

Remove all those trim and replace commands, you are creating a huge overhead in your query.

Noman Suleman 30-Apr-20 5:47am

if i removed replace command than i will get duplicate records, after replace command its return unique record

Richard MacCutchan 30-Apr-20 6:04am

Well it is going to be slow doing it that way.

Noman Suleman 30-Apr-20 6:09am

its not when i remove outer apply then this query will return within 1 sec, after outer apply its goes to high consumption, its due to where clause parameter @Fno and @Tjno , and this outer clause return thousand record that is why its getting slow, i want to know is there any way to replace with other apprach?

Sinisa Hajnal 30-Apr-20 5:46am

Just blindly...do replacements as high up as you can so they are triggered on lower number of rows
Do memory or temp tables, fill them independently and then join them
explicitly write out joins instead of local, foreign
consider having a column in the table that already has 'searchable' version of the name so you can do all those replaces when inserting or updating instead in WHERE clause (bad practice)

Noman Suleman 30-Apr-20 6:19am

ok thanks for suggestion. let me try

3 solutions

Solution 2

Maybe these articles will be of help to you:
basic-tips-on-tuning-sql-server-queries-f834f09bafaf[^]
query-optimization-techniques-in-sql-server-tips-and-tricks/[^]

Posted 29-Apr-20 21:15pm

RickZeeland

Updated 29-Apr-20 21:24pm

v2

Comments

Maciej Los 30-Apr-20 8:14am

5!

Solution 3

I am not user of SQL, but I got a couple ideas:
- would replace

SQL

(case when CompanyCountry like '%pakistan%' then 'Local' when CompanyCountry != '%pakistan%' then 'Foreign'  else '' end) as CompanyCountry

with

SQL

(case when CompanyCountry like '%pakistan%' then 'Local' else 'Foreign' end) as CompanyCountry

or create a custom column with the answer to avoid recalculate on every query run.

- I would create a custom column containing the result of:

SQL

RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(upper(AgentName), '&', ''), '(', ''), ')', ''), ',', ''), '.', ''), '-', ''))

Posted 30-Apr-20 3:13am

Patrice T

Comments

Maciej Los 30-Apr-20 9:29am

Good catch!
Wrong CASE WHEN ... END statement might be the reason of duplicated records.

Hats off to non SQL user!

Patrice T 30-Apr-20 10:10am

Thank you.

Noman Suleman 30-Apr-20 11:13am

thanks and appreciated, let me change this well pointed out

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2020-04-30T03:58:00

Solution 4

What about the shortest version of your very long and complicated query:

SQL

SELECT AgentName, [Local], [Foreign], [Local] + [Foreign] Total
FROM
(
    SELECT upper(p.AgentName) AgentName,
        SUM(CASE WHEN CompanyCountry like '%pakistan%' then 1 ELSE 0 END) [Local], 
        SUM(CASE WHEN CompanyCountry like '%pakistan%' then 0 ELSE 1 END) [Foreign]
    FROM [TheLegalFiles-Dev].dbo.SearchProductGoodsClass p
    WHERE p.DataEntryStatusid=1 and p.JournalNo> = @fJno and p.JournalNo<=@toJno
    GROUP BY upper(p.AgentName)
) T
ORDER BY [Local] + [Foreign] DESC

Posted 30-Apr-20 3:58am

Maciej Los

Comments

Noman Suleman 30-Apr-20 11:23am

this shows a company name multiple times i need sum of all those

AgentName Local Foreign Total
UNITED TRADEMARK & 40 90 130
UNITED TRADE MARK 0 1 1
UNITED TRADEMARK PATENT 0 1 1

Maciej Los 30-Apr-20 11:43am

Yes, because these companies have different names.

If you would like to get a "common-company-name", you have to define helper table, which will return that "common-name". For example, a Coca-Cola company has agents over the world. In Poland, we have 2 companies: "Coca Cola HBC Polska Sp. z o.o." and "Coca Cola Services Polska Sp. z o.o.". Original company name is "The Coca Cola Company". So, if you would like to return company names which is related to that world-company, you need to create a'ka dictionary:

AgentName | CommonName
United Trademark | United Trademark
United Trademark & | United Trademark
United Trademark Patent | United Trademark

Then you'll be able to use that common name, by joing helper table.

Noman Suleman 30-Apr-20 11:57am

that is perfect would you provide me example link of a'ka dictionary how can i create this. kindly guide me more. thanks in advance

Maciej Los 30-Apr-20 13:01pm

That's quite easy part...
1. CREATE TABLE[^]

Create table CompanyCommonNames
(
    AgentName  NVARCHAR(255),
    CommonName NVARCHAR(255)
)

2. INSERT INTO[^]

INSERT INTO CompanyCommonNames(AgentName, CommonName)
SELECT DISTINCT upper(y.AgentName), upper(REPLACE(REPLACE(...)...))
FROM YourTableNameHere y
ORDER BY upper(y.AgentName)

Note that REPLACE(REPLACE(...)...) is used to get company common name. You have to use your logic here!

3. use Join[^]

SELECT ccn.CommonName, T.[Local], T.[Foreign], T.[Local] + T.[Foreign] Total
FROM
(
    --inner query from solution #4
) T INNER JOIN CompanyCommonNames ccn ON T.AgetName =  ccn.AgentName
ORDER BY T.[Local] + T.[Foreign] DESC

That's all!

Noman Suleman 2-May-20 4:08am

thanks a lot sir, i got it all , very nice and simple solution appreciated!

Maciej Los 2-May-20 13:54pm

You're very welcome.

Noman Suleman 5-May-20 2:51am

Thanks!