How to tune SELECT

Question

How to tune SELECT

vsamantha35

SSChampion

Points: 11551
More actions
June 22, 2019 at 9:01 am

#3656431

Hi Experts,
Can you provide some guidance on tuning below query. On prod env, it is taking more than 25 mins and eventually developer had to kill the query. Unfortunately, not able share the plan but looking for pointers which can help tune this particular query.What all things to watch out to make this query run faster?
SQL version : sql 2016 EE
select distinct comm_val,country_cd
from comm x,Testbl
where Testbl.rowid_object = x.PARTY_ID
and party_typ_cd = 'Contact'
and country_cd is not null
and comm_typ_cd = 'PHONE' and comm_usg in ('FINANCE','HR')
and exists (select 1 from validate_tbl
where replace(replace(rtrim(in_number),'Â',''),'ï¿½','') = replace(replace(rtrim(x.comm_val),'Â',''),'ï¿½','')
and in_iso_country_code = country_cd )
and len(comm_val) > 8
Thanks,
Sam

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99130 More actions · Answer 1

Let's put some formatting into that code first, so we can read it:

SELECT DISTINCT comm_val,country_cd
FROM comm x,Testbl
WHERE Testbl.rowid_object = x.PARTY_ID
  AND party_typ_cd = 'Contact'
  AND country_cd IS NOT NULL
  AND comm_typ_cd = 'PHONE'
  and comm_usg IN ('FINANCE','HR')
  AND EXISTS (SELECT 1
              FROM validate_tbl
              WHERE REPLACE(REPLACE(RTRIM(in_number),'Â',''),'ï¿½','') = REPLACE(REPLACE(RTRIM(x.comm_val),'Â',''),'ï¿½','')
                AND in_iso_country_code = country_cd )
  AND LEN(comm_val) > 8

First things, you're using ANSI-89 JOIN syntax here; that's been out dated for 27 years now, as it was replaced by the ANSI-92 syntax. Aaron Bertrand wrote an article on why you should really switch to the more recent syntax here.

Next, you really need to quantify your columns (and alias your tables). I have no idea which columns reference which tables in the above, which makes this a lot harder to help you on. I can tell, however, that the REPLACE process is going to be very expensive and is going to be a likely candidate as a performance killer. Considering you're removing the same characters from both strings do you need to REPLACE?

Finally, to really do a performance check we really need the DDL of your tables (including their indexes) and the query plan for your query. Can you reply with your DDL and attach the .sqlplan file please?

Thanks.

This reply was modified 5 years, 5 months ago by Thom A.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

John Esraelo-498130 SSCertifiable Points: 5914 More actions · Answer 2

Agree with Thom about the "from" statement, and also the replace and LEN calculation may be costly.

Important factors are: size of the tables, appropriate indexes, take advantage of common table expressions (CTE), you don't need the "select top 1" when you are using "exist" operator.

Recommendation: I normally, when dealing with multiple tables in a query, decide which table(s) should be part of a sub-query / CTE / or temp tables. In another work, if you can break 1 query into several queries, and bind the results at the end (leave formatting at the end or any other calculation) you may get a better outcome.

Again, lot of pieces of information are not available to make a better analysis for you. Share more information if you should find assisting us assisting you.

Cheers,
John Esraelo

vsamantha35 SSChampion Points: 11551 More actions · Answer 3

Thanks Thom for valuable inputs. I will try to see If I can get the ddl scripts and actual execution plan.