CODEWITHSUNDEEP
c#sql-serverentity-frameworkfull-text-searchlarge-query
Andrzej Wieśliwski
Andrzej Wieśliwski

Reputation: 41

SQL Server full text search - large query

I'm writing an application in C# using Full Text Search queries in SQL Server 2014. The user needs to be able to search the database using a very large query containing up to 100 000 parameters. For instance:

FTS Query:

SELECT * FROM Client WHERE contains(Name, '"John" OR "Sarah"')

LIKE query:

SELECT * FROM Client WHERE Name in ('John', 'Sarah')

Except instead of 2 names, we have 100 000 names. Obviously, this exceeds the maximum query length supported by SQL. The database contains a bit over 1 million records. In some cases I will need to find client names starting with a specified string, which is why FTS is preferable over "in" or "like" clauses. Is there some clever way of bypassing that other than batching the requests into many chunks? Are there any better alternative to using an SQL database?

I've also tried implementing the search using Lucene.NET, but generating an index for over 1 million records takes many hours, which is not acceptable, as the data will be synchronized daily.

Upvotes: 4

Views: 941

Answers (1)

Ries Vriend
Ries Vriend

Reputation: 987

When using plain SQL, I suggest to use sp_executesql. This allows you to pass in up to about 2100 parameters, one for each name. SQL Server will be able to re-use the query plan for that query - no recompile needed - so it's fast.

By creating batches of 2100 names you need 48 batches; the latency for that should be reasonably low. Plus you can start them in parallel if you use multiple connections.

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-2017

Consider snapshot isolation or 'WITH NOLOCK' to prevent delays during data-reload.

https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server

https://www.mssqltips.com/sqlservertip/2470/understanding-the-sql-server-nolock-hint/

Finally make sure you set the proper indexes and use SQL Profiler to inspect the actual query plans to verify that is the case.

Upvotes: 1

Related Questions