0

I have sql server table Bookchapter with about ~ 140.000 rows, FullContent field is nvarchar (max) with long content inside.

 SELECT     COUNT(Id) AS Expr1
    FROM         dbo.BookChapter
    WHERE     (FullContent LIKE '%xxx%')

without Where clause, it runs fine but with Where Clause involved with Fullcontent, sql server always cause timeout expired

I have no idea how to get it to work.

Preston Guillot
  • 6,493
  • 3
  • 30
  • 40
nam vo
  • 3,271
  • 12
  • 49
  • 76
  • try changing your LIKE method to a CONTAINS method – Jonesopolis Jun 13 '13 at 17:37
  • 3
    oy...`LIKE` is an expensive operation. A fulltext index will help... – Chris Pfohl Jun 13 '13 at 17:38
  • `FullContent` is alot of data and slow since need to scan the all field , is there any option of refine the `like` to something faster ? what can you share about what you are looking for ? – Mzf Jun 13 '13 at 17:38
  • 2
    You are searching for a substring within 140,000 *chapters* worth of text? You need to look into [full text indexing](http://msdn.microsoft.com/en-us/library/cc879306.aspx). – mbeckish Jun 13 '13 at 17:40
  • thanks, i'll try fulltext indexing. – nam vo Jun 13 '13 at 17:45
  • Fultext indexing also doesn't work when using leading wildcards see: http://social.msdn.microsoft.com/Forums/en-US/sqlsearch/thread/02ebc411-0fcf-40ee-9963-3f64ed4409bb or http://stackoverflow.com/questions/2664387/sql-server-full-text-search-leading-wildcard – Preston Guillot Jun 13 '13 at 17:53

0 Answers0