Clever Geek Handbook

.NET. How can I efficiently search the database for multiple fields in a single text field?

Linq search example for sql I am using to search multiple fields in a DB table through one textbox:

var TheOutput = (from t in TheDC.SomeTable

                 where TheIDs.Contains(t.ID) && (
                 t.column1.Contains(TheSearchTerm) ||
                 t.column2.Contains(TheSearchTerm) ||
                 t.column3.Contains(TheSearchTerm) )           
                 select t.ID).ToList();
}

But this is very slow as the fields are text fields (first name, last name, email address ...). What strategy can you use to quickly search a single text box?

+3


source to share


2 answers


Can you try something like this?



var TheOutput = TheIDs.Select(id => TheDC.SomeTable.Find(id)).Where(t => 
    t.column1.Contains(TheSearchTerm) || 
    t.column2.Contains(TheSearchTerm) || 
    t.column3.Contains(TheSearchTerm)
)
+1


source


Assuming the string contains what is causing the slowdown, then the full text index is worth trying.

You need to run something like:

CREATE UNIQUE INDEX unique_index_on_id ON TheDc.SomeTable(ID);
CREATE FULLTEXT CATALOG ft AS DEFAULT;
CREATE FULLTEXT INDEX ON TheDc.SomeTable(column1, column2, column3) 
   KEY INDEX unique_index_on_id 
   WITH STOPLIST = SYSTEM;


See MSDN docs for more details for more information

It is said that it is always worth using the SQL Profiler (or if you are using the EF awesome EF Profiler from Hibernating Rhinos ) to confirm that the SQL generated meets your expectations

+1


source






More articles:


All Articles