How can i improve performance of below query

Question

select 
    m.* 
from 
    MEDICALHIERARCHY_LEAF_LEVEL_V_5_0_LC_1 m 
inner join  
    (select synid 
     from SYNID_PROFILE_MAP_5 
     where upper(map_type) = upper('primary') 
       and upper(map_to_profile) = upper('Diagnostic Tests & Exams'))
map on m.name = map.synid 
where 
    m.Depth is null

Above query is taking 5-20 seconds depending on data. What can further improved in it for better tuning.

You are the only one who knows the data... so you are the only one able to improve it. We don't know volumetry, indexes, execution plans... How do you want us to help on this without this information!? — James, Aug 08 '18 at 06:44
@jamie Drq I wanted to know what alternative can i use to improve it(e.g what can be alternative for upper as upper itself is a function which slows it down) — SAJAL MATHUR, Aug 08 '18 at 06:51
if you do really want to improve that query, the information that I asked you is crucial, trust me — James, Aug 08 '18 at 06:54
What **RDBMS** are you using? Please add a relevant tag to your question — marc_s, Aug 08 '18 at 07:21

score 2 · Answer 1 · answered Aug 08 '18 at 06:46

You can start by fixing basics like:

UPPER(map_to_profile)=UPPER('Diagnostic Tests & Exams'))

Unless you use a case sensitive colaltion, the upper does exactly one thing: make sure indices are not used and the query may slow down. Do not use a case sensitive collation when you do not want a case sensitive collation.

And then remove the useless upper.

score 1 · Answer 2 · answered Aug 08 '18 at 06:59

Try this one

Select m.* from MEDICALHIERARCHY_LEAF_LEVEL_V_5_0_LC_1 m inner join  
(select synid from SYNID_PROFILE_MAP_5 where  UPPER(map_type) = 
'PRIMARY' and UPPER(map_to_profile)='DIAGNOSTIC TESTS & EXAMS')
map on m.name=map.synid where m.Depth is null

Avoid * in m.* instead of this use column name

score 0 · Answer 3 · answered Aug 08 '18 at 06:52

Try removing subquery:

SELECT m.* 
FROM MEDICALHIERARCHY_LEAF_LEVEL_V_5_0_LC_1 m INNER JOIN  
SYNID_PROFILE_MAP_5 map ON (CASE WHEN m.Depth is null THEN m.name end)=(CASE WHEN UPPER(map_type) = 
UPPER('primary') and UPPER(map_to_profile)=UPPER('Diagnostic Tests & Exams') THEN map.synid end)

You may want to put m.Depth is null in WHERE clause depending on your join condition

score 0 · Answer 4 · answered Aug 08 '18 at 07:17

Try this code (although I'm in dark without knowing the real data):

SELECT m.*
  FROM MEDICALHIERARCHY_LEAF_LEVEL_V_5_0_LC_1 m
 WHERE m.DEPTH IS NULL
   AND m.name = (SELECT synid
                   FROM SYNID_PROFILE_MAP_5
                  WHERE UPPER (map_type) = 'PRIMARY'
                    AND UPPER (map_to_profile) = 'DIAGNOSTIC TESTS & EXAMS')

I removed unnecessary UPPER functions and removed your JOIN to WHERE clause (as it doesn't contribute to your select), while setting m.DEPTH IS NULL as first filter.

How can i improve performance of below query

4 Answers4