Clash Royale CLAN TAG#URR8PPP

How can i improve performance of below query

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.

4 Answers
4

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.

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

m.Depth is null

WHERE

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

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.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.