Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Full Text search pefromance for long string Expand / Collapse
Author
Message
Posted Monday, May 5, 2014 6:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 1:02 AM
Points: 166, Visits: 1,301
Hi,

I have two queries related to full text search.In that first one is giving result in 10 sec and second one is giving result in less than 1 sec.


SELECT	CandidateID,
ResumeID
FROM CandidateResume
WHERE CONTAINS(ResumeContent, 'designer and HTML and CSS and UX and ("wirefram*" or Omnigraffle or Axure or Balsamiq) and ("UI develop*" or "UX developer" or "GUI developer" or "graphic designer" or "UX architect*" or "UI designer" or "UX designer" or "Interface designer" or "ui framework" or "UI debug*" or "UI using" or "UI bug" or "UI Engineer" or "UI control" or "UI component*" or "UI tech*") and not "present employer" and not "Sponsorship"')

SELECT CandidateID,
ResumeID
FROM CandidateResume
WHERE CONTAINS(ResumeContent, 'ASP and SQL')

DDL statement for table :
CREATE TABLE [dbo].[CandidateResume](
[ResumeID] [int] IDENTITY(1,1) NOT NULL,
[ResumeLabel] [varchar](100) NULL,
[ResumeContent] [varchar](max) NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[CandidateID] [int] NOT NULL,
CONSTRAINT [PK_CandidateResume] PRIMARY KEY CLUSTERED
(
[ResumeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


CandidateResume table has around 6 lakh records.Full text index is created on ResumeContent field.

why first query is performing bad?what i can do for searching long strings with good performance?
I have tried with containstable but performance is same.

I have also attached query plan for both queries.

SQL version is Microsoft SQL Server 2008 R2 (SP2) Enterprise Edition (64-bit)

Any suggestion is appreciated

Thanks,
Megha


  Post Attachments 
queryPlan.sqlplan (1 view, 31.91 KB)
Post #1567482
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse