sql server - T-SQL - How can I make a SELECT query with multiple LIKE clauses quicker? -


i've got search function news articles looks (contains more 5 search items):

select top 5 * newsarticles (headline '% sustainable %'or headline '% sustainability %' or headline '% conservation %' or headline '% environmental % or headline '% environmentally %')  or (body '% sustainable %'or body '% sustainability %' or body '% conservation %' or body '% environmental % or body '% environmentally %') order publishdate desc  

this query designed pull out top 5 news stories relating sustainability , sits on main sustainability homepage. however, takes while run , page slow load. i'm looking ways speed up. having many clauses seems cumbersome i've tried join this:

create table #searchitem (search varchar(255))  insert #searchitem values ('sustainable'), ('sustainability'), ('conservation'), ('environmental'), ('environmentally')  select top 5 * newsarticles n join #searchitem s on n.headline collate database_default '% ' + s.search + ' %' or  n.body collate database_default '% ' + s.search + ' %' order n.publishdate desc 

this seems work performance, seems bring duplicate articles 1 of search words appears in both body , headline (which case). i've tried using word using 'select distinct top 5 *' gives me error saying 'the ntext data type cannot selected distinct because not comparable'. there away of stopping bringing duplicates without doing 2 separate searches , using union?

since multiple hits on multiple words, can use selected id's filter actual selection of articles:

select top 5 *  newsarticles  id in (select id     newsarticles n     join #searchitem s     on n.headline collate database_default '% ' + s.search + ' %' or         n.body collate database_default '% ' + s.search + ' %' ) order publishdate desc 

it should still reasonably fast (compared original query) , duplicate-free.

(as in rawheiser's response, there assumption id field exists :))


Comments

Popular posts from this blog

c# - Send Image in Json : 400 Bad request -

jquery - Fancybox - apply a function to several elements -

An easy way to program an Android keyboard layout app -