Alternative Full-Text Search for memory optimized tables

Question

Currently, I use a LIKE operator with %% to search match text in memory optimized tables but performance is poor. so,I try to find the alternative way like Full-Text Search but Fulltext indexes are not supported for memory-optimized tables. (https://learn.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/transact-sql-constructs-not-supported-by-in-memory-oltp?view=sql-server-ver15#natively-compiled-stored-procedures-and-user-defined-functions)

Is there any alternative way to implement search text in memory-optimized tables

Answer 1

Hi ballmill-1605,
Please check index analysis when you are experiencing slow query execution times. If you are investigating a single query, you can use the Analyze Query in Database Engine Tuning Advisor option in SQL Query Analyzer; if you have a SQL Profiler trace of a large workload, you can use the Database Engine Tuning Advisor. And if the query that you are analyzing includes any hints, remove them, and then reevaluate the performance.
After you confirm that the correct indexes exist, and that no hints are restricting the optimizer's ability to generate an efficient plan, you can examine the query execution plan.
Please refer to Indexes on Memory-Optimized Tables and Troubleshoot slow-running queries which might be helpful.
Best Regards,
Amelia