Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / Other Technologies / Full-Text Search / February 2008

Tip: Looking for answers? Try searching our database.

I need to optimize this query

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Nightcrawler - 07 Feb 2008 18:49 GMT
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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.