Just anather input
The query fetches around 100 rows as all the domains have sub domains, and in the current senario its taking much time.

Do you have an index on SourceDomainName ? And it is curious that you are using AND and not OR for the differing LIKE statements ... begs the question if you couldn't just put them in the pattern which they should appear in one like.

LIKE '%abc%def%ghi%jkl%'

Or something to that effect.

If you ar espeaking about the pattern

Select distinct SourceDomainName from ITM_Alerts where
SourceDomainName like '%ABC%DEF%HIJ%KLO%'
this is not possible as the like parameters are diffrent values and not part of a single string.

Then HOW can you use an AND??

select distinct SourceDomainName
from ITM_Alerts
where SourceDomainName like '%ABC%'
   and SourceDomainName like '%DEF%'
   and SourceDomainName like '%HIJ%'
   and SourceDomainName like '%KLO%'

Should probably be:

select distinct SourceDomainName
from ITM_Alerts
where SourceDomainName like '%ABC%'
      or SourceDomainName like '%DEF%'
      or SourceDomainName like '%HIJ%'
      or SourceDomainName like '%KLO%'

You may not need this unless you have duplicates in the data. Just noting in case you were doing this in case a domain matches multiple.

The query tries to find all sub domains for the given LIKE parameters i have to used AND because i want all the sub domains for the parent domains.

select distinct SourceDomainName
from ITM_Alerts
where SourceDomainName like '%CSC%'
      or SourceDomainName like '%BHP%'
      or SourceDomainName like '%IMM%'
      or SourceDomainName like '%RIO%'
this will also return the same result , but can we optimize the time

Okay what about my index question, is there one?

No there is no Index, can i implement one, kindly tell me how to do the same

solution was fine but didnt had the expected result