Help optimizing a full-text search

  • Hi,

    I've got the following query that uses a full-text index to search for the TOP 5 products (from the RawProducts table) matching the query, in a given Shop (populated by the @ShopId variable).

    At the moment I'm calling this procedure over and over again for every ShopId (there are 27 Shops) - which is a bit slow.

    My question is - could anyone let me know how to modify the query to accept, say, a comma-separated list of ShopIds in the @ShopId variable, and to return the TOP 5 matches from each shop?

    Here's the query so far:

    DECLARE @ShopId uniqueidentifier

    SET @ShopId = '49506541-4ce2-40ac-812a-7ab262e6f0b0'

    SELECT TOP 5

    ftt.RANK,

    rp.*

    FROM

    RawProducts rp

    INNER JOIN

    CONTAINSTABLE

    (

    RawProducts,

    RawProductName,

    'ISABOUT("*radox*","*shower*")'

    )

    AS ftt

    ON

    ftt.=rp.RawProductId

    WHERE

    rp.ShopId = @ShopId

    ORDER BY

    ftt.RANK DESC

    Really appreciate your help! 🙂

    thanks!

  • Have a look at the DENSE_RANK function I used. All the rest is just a quick & dirty code sample. Please note that I haven't tested this but it should do the job...

    DECLARE @ShopIdCSV NVARCHAR(MAX)

    SET @ShopIdCSV = '49506541-4ce2-40ac-812a-7ab262e6f0b0,49506541-4ce2-40ac-812a-7ab262e6f0b1,49506541-4ce2-40ac-812a-7ab262e6f0b2'

    --This is a very quick & dirty way to

    --convert CSV list to table.

    --I usually use a function that does that

    --using XML, but that's for another post...

    --It works here because it's a GUID. I never use that in real production code...

    DECLARE @ShopList TABLE (ShopID uniqueidentifier)

    SET @ShopIdCSV = ',' + @ShopIdCSV + ','

    INSERT @ShopList (ShopID)

    SELECT DISTINCT RawProducts.ShopID

    FROM

    RawProducts

    WHERE

    @ShopIdCSV LIKE '%,' + CAST(RawProducts.ShopID as VARCHAR(36)) = ',%'

    ;WITH Q AS

    (

    SELECT

    DENSE_RANK() OVER (PARTITION BY RawProducts.ShopID ORDER BY ftt.RANK) AS RankInShop,

    ftt.RANK,

    rp.*

    FROM

    RawProducts rp

    INNER JOIN

    CONTAINSTABLE

    (

    RawProducts,

    RawProductName,

    'ISABOUT("*radox*","*shower*")'

    )

    AS ftt

    ON

    ftt.=rp.RawProductId

    INNER JOIN @ShopList SL ON

    RawProducts.ShopID = SL.ShopID

    )

    SELECT

    * FROM

    Q

    WHERE

    RankInShop <=5

    ORDER BY

    ShopID, RankInShop

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply