Help optimising a full-text search

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help optimising a full-text search

Author

Topic

dgb2009
Starting Member

3 Posts

Posted - 2009-09-19 : 05:26:56

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.[KEY]=rp.RawProductId
WHERE
rp.ShopId = @ShopId
ORDER BY
ftt.RANK DESC

Really appreciate your help! :-)

thanks!

Andreas
Starting Member

11 Posts

Posted - 2009-09-20 : 09:41:32

For the issue of sending all ShopId's into the procedure at once I suggest using an XML-parameter and then creating a table variable out of it:

declare @Shops xml
declare @tShop table(ShopId uniqueidentifier)
set @Shops=
	'<shops>
		<shop>49506541-4ce2-40ac-812a-7ab262e6f0b0</shop>
		<shop>49506541-4ce2-40ac-812a-7ab262e6f0b1</shop>
		<shop>49506541-4ce2-40ac-812a-7ab262e6f0b2</shop>
	</shops>'
insert into @tShop(ShopId)
select
	T.c.value('.', 'uniqueidentifier') as ShopId
from
	@Shops.nodes('/shops/shop') T(c)

You can of course use a comma separated string if you feel more comfortable with that, I doubt you will notice any performance hit with only 27 shop-id's. The important thing is that you end up with a table containing the shop-id's.

Then you can join on the shop-table and combine it with the window function dense_rank() to be able to fetch the products you want, something like this:

select
	t.[Rank],
	RP.*
from
	RawProducts RP
	inner join
	(
		SELECT
			iSh.ShopId,
			iRP.RawProductId,
			ftt.[Rank],
			dense_rank() over(partition by iSh.ShopId order by ftt.[Rank] desc) DenseRank
		FROM
			@tShop iSh
			inner join RawProducts iRP
				on iRP.ShopId = iSh.ShopId
			INNER JOIN CONTAINSTABLE
			(
				RawProducts,
				RawProductName,
				'ISABOUT("*radox*","*shower*")'
			) AS ftt
				ON ftt.[KEY] = iRP.RawProductId
	) t
		on t.RawProductId = iRP.RawProductId
		and t.ShopId = iRP.ShopId
where t.DenseRank <= 5
order by t.ShopId, t.[Rank] desc

Please note that I have not been able to test the code, so you will probably face some errors at first, but hopefully you will get the idea.

dgb2009
Starting Member

3 Posts

Posted - 2009-09-20 : 13:18:28

thanks - will try that out... :-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources