Return to Answer

If the data can be stored as upper case in the table then this will save some additional CPU time as no need to call UPPER(Data) which will remove the highlighted operation (reduces it down to ~65 ms for me).

The binary collate clause made a reduction from 500ms to 100ms even with the additional overhead of calling UPPER but if the data can be stored as upper case in the table then this will save some additional CPU time as no need to call UPPER(Data) which will remove the highlighted operation (reduces it down to ~65 ms for me).

A potentially faster method that works with your existing structure would be

• Defines @Query as a non max datatype as doesn't sound like you need max and that can be slower.
• Only splits @Query once rather than redoing the splitting for each row in MyTable
• Removes the (non inlined) scalar UDF
• Simplifies the calculation of RANK to remove some execution plan operators
• Uses a binary collation in the hope that this should speed up the CHARINDEX search (NB: If you are able to ensure that all the Data will be stored in a canonical upper case form then do so and remove that UPPER(Data) call to avoid spending CPU time doing that at run time)

With this dummy data I found the revised method executes in around 100ms on my machine (original method was around 3 seconds)

INSERT INTO MyTable VALUES ('A CAR'), 
                           ('A BIKE'), 
                           ('CAR WASH'), 
                           ('CAR BIKE RACK'), 
                           ('A HOUSE');

INSERT INTO MyTable
SELECT TOP 75000 REPLACE(NEWID(), '-', ' ' )
FROM sys.all_columns c1, sys.all_columns c2

For the following example data

INSERT INTO MyTable VALUES ('A CAR'), 
                           ('A BIKE'), 
                           ('CAR WASH'), 
                           ('CAR BIKE RACK'), 
                           ('A HOUSE');

INSERT INTO MyTable
SELECT TOP 75000 REPLACE(NEWID(), '-', ' ' )
FROM sys.all_columns c1, sys.all_columns c2

In WPR your current method looks like this

There is a sort for the final ranking, and other inner sorts for the count distinct as well as CPU time spent in string split (potentially made worse by dealing with LOB datatypes)

A faster method that works with your existing structure would be

• Defines @Query as a non max datatype as doesn't sound like you need max and that can be slower.
• Only splits @Query once rather than redoing the splitting for each row in MyTable
• Removes the overhead of the (non inlined) scalar UDF
• Simplifies the calculation of RANK to remove some execution plan operators - including the inner sort.
• Uses a binary collation in the expectation that this should speed up the CHARINDEX search (NB: If you are able to ensure that all the Data will be stored in a canonical upper case form then do so and remove that UPPER(Data) call to avoid spending CPU time doing that at run time)

With this dummy data I found the revised method executes in around 100ms on my machine (original method was around 3 seconds).

If the data can be stored as upper case in the table then this will save some additional CPU time as no need to call UPPER(Data) which will remove the highlighted operation (reduces it down to ~65 ms for me).

With this dummy data I found the revised method executes in around 100ms on my machine (original method was around 3 seconds)

INSERT INTO MyTable VALUES ('A CAR'), 
                           ('A BIKE'), 
                           ('CAR WASH'), 
                           ('CAR BIKE RACK'), 
                           ('A HOUSE');

INSERT INTO MyTable
SELECT TOP 75000 REPLACE(NEWID(), '-', ' ' )
FROM sys.all_columns c1, sys.all_columns c2