I have a function that searches and ranks to be used on inventory and part numbers. We have tried Full Text Search and Azure Cognitive Search and its been a long journey. I need to match and rank on arbitrary words defined as "Space delimited". On tables of > 75k records, the query takes 3-5sec.
The following demonstrates what I have working but would like to optimize:
IF OBJECT_ID(N'dbo.MyTable', N'U') IS NOT NULL DROP TABLE MyTable; CREATE TABLE MyTable ( Id int NOT NULL, Data varchar(255), PRIMARY KEY (Id) ); INSERT INTO MyTable VALUES (1, 'A CAR'), (2, 'A BIKE'), (3, 'CAR WASH'), (4, 'CAR BIKE RACK'), (5, 'A HOUSE'); IF OBJECT_ID('dbo.fn__SearcRankMyTable') IS NOT NULL DROP FUNCTION fn__SearcRankMyTable GO
CREATE FUNCTION [dbo].[fn__SearcRankMyTable] ( @Id INT, @Query VARCHAR(MAX) ) RETURNS INT AS BEGIN DECLARE @Result INT; WITH T AS ( SELECT B.[value] FROM MyTable CROSS APPLY STRING_SPLIT([Data], ' ') as A CROSS JOIN STRING_SPLIT(@Query, ' ') AS B WHERE Id = @id AND A.value LIKE b.[value] + '%' ) SELECT @Result = COUNT(Distinct(value)) FROM T RETURN @Result END GO
Intended use query:
SELECT S.*, Rank FROM MyTable S CROSS APPLY ( SELECT [dbo].[fn__SearcRankMyTable](S.iD, 'CAR BIKE RACK')) ca (Rank) WHERE RANK > 0 ORDER BY Rank DESC;
Search behavior defined:
- Assume row already has computed column(data) of distinct words:
- Foreach query word that matches the beginning of a word in the data row, increment the rank. The highest possible rank will be the number of words in the query.
- Search query can be at most 4 words, can be passed broken into words server-side as passed if helps.
Next things to try:
- I have read that Scalar Valued Functions are slow(er) / Single threaded. Try to convert to Table Valued function.
- Inside the
CROSS APPLY STRING_SPLIT([Data], ' ') as Aand the latter
Distinctwould be nice to have a way to bail out. This will be my next approach.
Are there other considerations / technologies / approaches I should consider?
Optimization with RETURN SELECT using WHERE EXISTS (60% slower unfortunately)
CREATE FUNCTION [dbo].[fn__SearcRankMyTable] ( @Id INT, @Query VARCHAR(MAX) ) RETURNS INT AS BEGIN RETURN (SELECT COUNT(*) FROM STRING_SPLIT(@Query, ' ') as A WHERE EXISTS (SELECT value FROM STRING_SPLIT((SELECT DATA FROM MyTable WHERE Id = @Id), ' ') WHERE [value] LIKE a.[value] + '%' )); END GO