I am working on a SQL Server database which will provide with data some grid. The grid will enable filtering, sorting and paging but also there is a strict requirement that users can enter free text to a text input above the grid for example 'Engine 1001 Requi' and that the result will contain only rows which in some columns contain all the pieces of the text. So one column may contain Engine, other column may contain 1001 and some other will contain Requi.

I created a technical column (let's call it myTechnicalColumn) in the table (let's call it myTable) which will be updated each time someone inserts or updates a row and it will contain all the values of all the columns combined and separated with space.

Now to use it with entity framework I decided to use a table valued function which accepts one parameter @searchQuery and it will handle it like this:

CREATE FUNCTION myFunctionName(@searchText NVARCHAR(MAX))
RETURNS @Result TABLE
( ... here come columns )
    AS
    BEGIN
        DECLARE @searchToken TokenType
        INSERT INTO @searchToken(token) SELECT value FROM STRING_SPLIT(@searchText,' ')
    
        DECLARE @searchTextLength INT
        SET @searchTextLength = (SELECT COUNT(*) FROM @searchToken)

        INSERT INTO @Result
        SELECT
        ... here come columns
        FROM myTable
        WHERE (SELECT COUNT(*) FROM @searchToken WHERE CHARINDEX(token, myTechnicalColumn) > 0) = @searchTextLength
        RETURN;
    END

Of course the solution works fine but it's kinda slow. Any hints how to improve its efficiency?