SQL Server Performance

full text query

Discussion in 'T-SQL Performance Tuning for Developers' started by jebsilver, Jul 29, 2004.

  1. jebsilver New Member

    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")')
    )
    )

Share This Page