In one of my stored procs, I have a query that performs a keyword search (keywords are always strings) on many columns, and returns the resultset.
The query is as folllows: SELECT a.cnd_id, a.cnd_update_date FROM candidatemaster a inner join candidateresume b on a.cnd_id = b.cnd_id WHERE ((a.core_skill1 like ''%' + @VALUE + '%'' OR a.core_skill2 like ''%' + @VALUE + '%'' OR a.core_skill3 like ''%' + @VALUE + '%'' OR a.CND_CAREER_OBJ like ''%' + @VALUE + '%'' OR b.res_resume like ''%' + @VALUE + '%'') AND (a.delete_flag = 0 AND a.blocksearch=0) @Value is one of the input params, and is a string. All the columns being searched are varchar, except for one, that is res_resume, which is a text column, and can be upto 16,000 characters in length. The search on the text column alone doubles the length of time taken for the query to run. If I remove the join on the candidateresume table and remove the search on the res_reume column, it executes in half the time. Any tips on improving the performance of this query ? I cannot avoid doing the search on this res_resume column. Vishal ______________________________________________________________________ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
