May 5, 2014 at 6:15 am
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.
SELECTCandidateID,
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"')
SELECTCandidateID,
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
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply