Full Text search pefromance for long string

• Megha P

  SSCommitted

  Points: 1976

  More actions

  May 5, 2014 at 6:15 am

  #307450

  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