I have a simple music catalog:
CREATE TABLE [dbo].[Catalog](
[CatalogId] [int] IDENTITY(1,1) NOT NULL,
[Artist] [nvarchar](256) NULL,
[Title] [nvarchar](256) NULL,
[Remix] [nvarchar](256) NULL,
[Album] [nvarchar](256) NULL,
[Label] [nvarchar](256) NULL,
[DateAdded] [datetime] NULL CONSTRAINT [DF_Catalog_DateAdded]
DEFAULT (getdate()),
CONSTRAINT [PK_Catalog] PRIMARY KEY CLUSTERED
(
[CatalogId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
The Full Text index is set on Artist and Title. I wrote the following
Query:
CREATE PROCEDURE [dbo].[CatalogSelectBySearch]
(
@Artist nvarchar(256),
@Title nvarchar(256)
)
AS
SET NOCOUNT ON;
DECLARE @SearchTerms nvarchar(512);
SET @SearchTerms = '("' + @Artist + '") OR
("' + @Title + '")'
SELECT C.CatalogId, C.Artist, C.Title, C.Remix,
C.Album, C.Label,
C.DateAdded
FROM dbo.Catalog AS C
INNER JOIN FREETEXTTABLE(dbo.Catalog, (Artist, Title),
@SearchTerms)
AS KEY_TBL
ON C.CatalogId = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC;
I have a feeling that this query can be done better but I am fairly
new to Full-text and don't know where to start. I tried using
CONTAINSTABLE but when I did that the search was very restrictive. For
instance, if the artist is "Shawn Christoper" in the database and
someone typed "Sean Christoper" to search, no results would come up
using the query structure above. FREETEXTTABLE returns is but the
query is very loose and returns alot of irrelevant results.
Please let me know how I can optimize this query.
Thanks
Hilary Cotter - 08 Feb 2008 14:30 GMT
Does this offer any better performance:
create unique index CatalogID on Catalog(CatalogID,DateAdded)
include(Artist,Title,Remix,Album,Label)
create unique index CatalogID1 on Catalog(CatalogID)
create fulltext index on catalog(Artist,title) key index CatalogID1
> I have a simple music catalog:
>
[quoted text clipped - 51 lines]
>
> Thanks
Nightcrawler - 08 Feb 2008 22:52 GMT
Hilary,
Please explain what you are suggesting. I am very curious (and eager
to learn)...
Thanks
Hilary Cotter - 11 Feb 2008 15:08 GMT
Clustered index seeks are in general more expensive than non-clustered
ones.
Putting the unique index on the clustered key should make things
faster for you. You will need to try this out to verify that it will
work for you!
> Hilary,
>
> Please explain what you are suggesting. I am very curious (and eager
> to learn)...
>
> Thanks