We have a search function the business use. It does an SQL search like this (only with more joins)
I.e. it searches if either field contains the search term, case insensitive
Our Ops guys are complaining because it is run hundreds of times a day and uses a notable amount of CPU. IN worse cases it can take over 70 seconds to run, which can make the users think it has failed and rerun it.
Anyone have suggestions on how to speed it up? All the Index Advisor will say is to create EVI indexes on the two columns, which seems like a bad idea as they are largely distinct values, which is not what EVI indexes are for
Code:
select * from Clients where upper(CompanyName) like '%SEARCHTERM%' or upper(TradingName) like '%SEARCHTERM%'
Our Ops guys are complaining because it is run hundreds of times a day and uses a notable amount of CPU. IN worse cases it can take over 70 seconds to run, which can make the users think it has failed and rerun it.
Anyone have suggestions on how to speed it up? All the Index Advisor will say is to create EVI indexes on the two columns, which seems like a bad idea as they are largely distinct values, which is not what EVI indexes are for
Comment