Optimizing SQL 2008 full text query (CONTAINSTABLE)

Question

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
    JOIN CONTAINSTABLE(RawProducts, 
                       RawProductName, 
                      'ISABOUT("*radox*","*shower*")') AS ftt ON ftt.key = rp.RawProductId 
   WHERE rp.ShopId = @ShopId
ORDER BY ftt.RANK DESC

Answer 1

you can create a temporary table before calling the proc, populate it wih shopIds. you have to modify the query by doing a join with that temp table

Answer 2

You definitely can do what you suggest:

• pass comma separated list of identifiers to a stored procedure
• convert the comma-separated list into a temp table (or table variable)
• change your query to JOIN on this filter table

See SQL User Defined Function to Parse a Delimited String for the UDF on parsing a comma-separated list.

---

But since you use SQL Server 2008, then I suggest you use XML data type:

• compose little XML and pass it to this stored procedure: <Filter><Row ID="1"/><Row ID="2"/></Filter>
• change a parameter of your stored procedure to @FilterXML NVARCHAR(MAX)
• cast input parameter to XML and then insert it into table variable (shown below)
• join your query on this table variable as before

Sample:

CREATE PROCEDURE getMyData(@FilterXML NVARCHAR(MAX))
AS BEGIN
    DECLARE @x XML
    SELECT @x = CONVERT(XML, @FilterXML)
    DECLARE @Filter TABLE (ShopID INT)

    -- insert into temporary table
    INSERT INTO @Filter (ShopID)
    -- @important: XML iS CaSe-SenSiTiv
    SELECT      x.value('@ID', 'INTEGER')
    FROM        @x.nodes('/Filters/Row') AS R(x)
    ...

You could even avoid this table variable @Filter and join directly on the results of the XML table-view, but it is somewhat less readable.