Fastest way to do a weighted tag search in SQL Server
- by Hasan Khan
My table is as follows
ObjectID bigint
Tag nvarchar(50)
Weight float
Type tinyint
I want to get search for all objects that has tags 'big' or 'large'
I want the objectid in order of sum of weights (so objects having both the tags will be on top)
select objectid, row_number() over (order by sum(weight) desc) as rowid
from tags
where tag in ('big', 'large')
and type=0
group by objectid
the reason for row_number() is that i want paging over results.
The query in its current form is very slow, takes a minute to execute over 16 million tags.
What should I do to make it faster?
I have a non clustered index (objectid, tag, type)
Any suggestions?
