Hmm... Have you tried using full text search indexing instead?
You have a litany of mistakes... You didn't normalize your database. You have OR clauses in your query - thus your query is non-SARGable. You're using a wildcard on the right side of the expression - thus any indexes on those columns are not used. > -----Original Message----- > From: Vishal Narayan [mailto:[EMAIL PROTECTED]] > Sent: Monday, July 01, 2002 9:52 PM > To: CF-Talk > Subject: Query on text column - how to improve performance ? > > > 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
