SQL Server Performance Forum – Threads Archive

full text query

Is there a better way to write the following query? I need to do a full text search that brings back records when all of the entered terms are matched in any of the columns in the joined tables. If keyword 1 is matched in "description" and keyword 2 is matched in "state", it should return this record. My method takes a few seconds to run. SELECT DISTINCTt1.auctionid
FROMauctions t1 WITH (NOLOCK)
INNER JOIN listings t2 WITH (NOLOCK) ON t1.listingid = t2.listingid
AND t1.ispaid = 1
AND t1.auctionisactive = 1
AND t1.auctionstart <= GETDATE()
INNER JOIN auctiontypes t9 WITH (NOLOCK) ON t1.auctiontypeid = t9.auctiontypeid
LEFT OUTER JOIN timeshareaffiliations t3 WITH (NOLOCK) ON t2.timeshareaffiliationid = t3.timeshareaffiliationid
LEFT OUTER JOIN states t4 WITH (NOLOCK) ON t2.StateID = t4.StateAbbrev
LEFT OUTER JOIN regions t5 WITH (NOLOCK) ON t2.regionid = t5.regionid
LEFT OUTER JOIN propertytypes t6 WITH (NOLOCK) ON t2.propertytypeid = t6.propertytypeid
LEFT OUTER JOIN listingtypes t7 WITH (NOLOCK) ON t2.listingtypeid = t7.listingtypeid
LEFT OUTER JOIN listingdescriptions t8 WITH (NOLOCK) ON t2.listingid = t8.listingid
LEFT OUTER JOIN timesharepointsaffiliations t10 WITH (NOLOCK) ON t2.timesharepointsaffiliationid = t10.timesharepointsaffiliationid
WHERE
(
(CONTAINS (t1.title, ‘FORMSOF(INFLECTIONAL, "keyword1")’) OR
CONTAINS (t2.propertyname, ‘FORMSOF(INFLECTIONAL, "keyword1")’) OR
CONTAINS (t2.city, ‘FORMSOF(INFLECTIONAL, "keyword1")’) OR
CONTAINS (t2.cruisedestination, ‘FORMSOF(INFLECTIONAL, "keyword1")’) OR
CONTAINS (t3.timeshareaffiliation, ‘FORMSOF(INFLECTIONAL, "keyword1")’) OR
CONTAINS (t3.timeshareaffiliationabbrev, ‘FORMSOF(INFLECTIONAL, "keyword1")’) OR
CONTAINS (t4.state, ‘FORMSOF(INFLECTIONAL, "keyword1")’) OR
CONTAINS (t4.stateabbrev, ‘FORMSOF(INFLECTIONAL, "keyword1")’) OR
CONTAINS (t5.region, ‘FORMSOF(INFLECTIONAL, "keyword1")’) OR
CONTAINS (t6.propertytype, ‘FORMSOF(INFLECTIONAL, "keyword1")’) OR
CONTAINS (t7.listingtype, ‘FORMSOF(INFLECTIONAL, "keyword1")’) OR
CONTAINS (t8.description, ‘FORMSOF(INFLECTIONAL, "keyword1")’) OR
CONTAINS (t9.auctiontype, ‘FORMSOF(INFLECTIONAL, "keyword1")’) OR
CONTAINS (t10.timesharepointsaffiliation, ‘FORMSOF(INFLECTIONAL, "keyword1")’) OR
CONTAINS (t10.timesharepointsaffiliationabbrev, ‘FORMSOF(INFLECTIONAL, "keyword1")’)
)
AND
(CONTAINS (t1.title, ‘FORMSOF(INFLECTIONAL, "keyword2")’) OR
CONTAINS (t2.propertyname, ‘FORMSOF(INFLECTIONAL, "keyword2")’) OR
CONTAINS (t2.city, ‘FORMSOF(INFLECTIONAL, "keyword2")’) OR
CONTAINS (t2.cruisedestination, ‘FORMSOF(INFLECTIONAL, "keyword2")’) OR
CONTAINS (t3.timeshareaffiliation, ‘FORMSOF(INFLECTIONAL, "keyword2")’) OR
CONTAINS (t3.timeshareaffiliationabbrev, ‘FORMSOF(INFLECTIONAL, "keyword2")’) OR
CONTAINS (t4.state, ‘FORMSOF(INFLECTIONAL, "keyword2")’) OR
CONTAINS (t4.stateabbrev, ‘FORMSOF(INFLECTIONAL, "keyword2")’) OR
CONTAINS (t5.region, ‘FORMSOF(INFLECTIONAL, "keyword2")’) OR
CONTAINS (t6.propertytype, ‘FORMSOF(INFLECTIONAL, "keyword2")’) OR
CONTAINS (t7.listingtype, ‘FORMSOF(INFLECTIONAL, "keyword2")’) OR
CONTAINS (t8.description, ‘FORMSOF(INFLECTIONAL, "keyword1")’) OR
CONTAINS (t9.auctiontype, ‘FORMSOF(INFLECTIONAL, "keyword2")’) OR
CONTAINS (t10.timesharepointsaffiliation, ‘FORMSOF(INFLECTIONAL, "keyword2")’) OR
CONTAINS (t10.timesharepointsaffiliationabbrev, ‘FORMSOF(INFLECTIONAL, "keyword2")’)
)
)

---

]]>