0

Using SQL Server 2012, I've got a table that currently has several hundred-thousand rows, and will grow.

In this table, I've got a nvarchar(30) field that contains Medical Record Number (MRN) values. These values can be just about any alphanumeric value, but are not words.

For Example,

DR-345687
34568523
*45612345;T

My application allows the end user to enter a value, say '456' in the search field. The application would need to return all three of the example records.

Currently, I'm using Entity Framework 5.0, and asking for a field.Contains('456') type of search.

This always takes 3-5 seconds to return since it appears to do a table search.

My question is: Would creating a Full Text Index on this column help performance? I haven't tried it yet because the only copy of the database that I have with lots of data in it is currently in QA trials.

Looking at the documentation for the Full Text Indexes it appears that it is optimized around separate words in the field value, so I am hesitant to take the performance hit to create the index without knowing how it is likely to affect my query performance.

scott-pascoe
  • 1,463
  • 1
  • 13
  • 31

1 Answers1

1

EF won't use the T-SQL keywords needed to access the SQL Server full text index (http://msdn.microsoft.com/en-us/library/ms142571.aspx#queries) so your solution won't fly without more work.

I think you would have to create a SProc to get the data using the FTI and then have EF call this. I have a similar issue and would be interested to know your results.

Andy

Andy Newland
  • 11
  • 1
  • Being interested in a solution is not the answer. So please, if you have a question do not hesitate to ask one here http://stackoverflow.com/questions/ask – Radim Köhler Jun 27 '13 at 12:08