0

I have a table with more than 600 000 records and I'm using varchar(max). While using full text search, performance is very slow and it uses 100% of the CPU.

So I'm planning to change data type varchar(max) to text.

Which data type is good to full text search? How to increase performance?

Table size:

enter image description here

James Z
  • 12,209
  • 10
  • 24
  • 44
Prabhu Kanna
  • 32
  • 7
  • 1
    Have you search that at StackOverflow before? [What is good variable type for full text search in SQL Server - Mohammad Ha](http://stackoverflow.com/questions/32167445/what-is-good-variable-type-for-full-text-search-in-sql-server) – Ivan Corcoles Aug 29 '16 at 13:17
  • 5
    Text is deprecated, do not under any circumstances use that. – HLGEM Aug 29 '16 at 13:36
  • In your comments on another answer you said you have varchar(300) and varchar(max) but the performance is slow. How about you share the table definition including indexes and the query you are using? Otherwise we are left trying to guess. – Sean Lange Aug 29 '16 at 15:42

1 Answers1

0

It depends on what type of data you are storing and the size of the data. If the data is frequently retrieved then storing it with "varchar" type is acceptable.

But, If the data is too large. It is obvious that to use the datatype "text".

You should use :

varchar - If the data is of string type(which won't be too large).

(e.g) UserName, Email, Country etc..,

text - If the data is too large and consists complex types(int + string) of data.

(e.g) Messages, Logs ..,

you can also use "mediumtext" and "largetext" as and when they are required.

when you are storing some "json" objects as string you can use medium text. And if you are going to store some large arrays of "json" consider using "largetext".

You should consider the above cases, Before choosing the datatype which will affect your performance.

Conclusion:

Large amount of small data - var
Small amount of large data - text

I also advise you to visit this link

Community
  • 1
  • 1
Mohanavel T
  • 371
  • 4
  • 17
  • 4
    Please do NOT suggest that anyone use the text datatype. It has been deprecated for more than a decade now and it is incredibly awful to work with. You should instead use varchar(max) – Sean Lange Aug 29 '16 at 13:38
  • But, If you are performing full text search using text would be the optimal since we also store indices for them. kindly visit this link: http://www.brandonsavage.net/designing-databases-picking-the-right-data-types/ – Mohanavel T Aug 29 '16 at 13:47
  • NO that is not correct. In fact, how about this directly from the article you posted. "Another favorite (and bad) development practice is to use TEXT columns to store large blocks of text" – Sean Lange Aug 29 '16 at 14:18
  • You should read about and understand more about how FULLTEXT searching works. https://msdn.microsoft.com/en-us/library/ms142571.aspx It can be used on many character based datatypes. And you should also read about the TEXT datatype. https://msdn.microsoft.com/en-us/library/ms187993.aspx It is a nightmare to work with, just don't do it. – Sean Lange Aug 29 '16 at 14:21