2

Just trying to chip away at some bottlenecks in our process (that existed long before I got here).

There's a part of the process where we try to categorize input based on a set of full text queries (if a text blob matches X, then its category is Y). The queries (about 60 of them) are stored in one table, and the set of current inputs is in another, with a full-text-indexed column on it.

CONTAINS() doesn't let you use another column from a JOIN (at least from what I've been able to find) - the search string has to be a literal or a variable, so our old implementation goes over the query table with a cursor and runs CONTAINS() queries for each one on the inputs table.

I was wondering if there were any efficiencies I could try to eke out here. Like is there any way to save the pre-parsed queries and use those in the full text search, for instance?

CREATE FUNCTION [dbo].[GetInputCategories] (@DateProcessed DATETIME)
RETURNS @CategoryTypes TABLE
(
    CategoryID INT,
    CategoryGroupID INT,
    InputID varchar(25)
)
AS
BEGIN

IF EXISTS(SELECT * FROM dbo.NewInputs WHERE DateProcessed = @DateProcessed)
BEGIN
----------------------------------------------------
--          Get CategoryIDs                 ----
----------------------------------------------------                  
DECLARE @ContainsClause VARCHAR(1000);
DECLARE @CategoryID INT;

DECLARE get_fnid CURSOR LOCAL FAST_FORWARD FOR
    SELECT osCategoryid, ContainsClause FROM dbo.FuzzyCategoryMapping
    
OPEN get_fnid;
WHILE (0=0)
BEGIN   
    FETCH NEXT FROM get_fnid
        INTO @CategoryID, @ContainsClause;

    IF (@@FETCH_STATUS <> 0) 
            BREAK;  -- out of cursor 

    INSERT INTO @CategoryTypes (CategoryID, InputID)
        SELECT  @CategoryID, InputID FROM dbo.NewInputs
        WHERE CONTAINS(InputText, @ContainsClause) AND DateProcessed = @DateProcessed
END
    
CLOSE       get_fnid;
DEALLOCATE  get_fnid;

----------------------------------------------------
--          Get CategoryGroupIDs                ----
----------------------------------------------------
 UPDATE @CategoryTypes 
    SET CategoryGroupID = cg.CategoryGroupID
    FROM dbo.CategoryGroups cg
        INNER JOIN  @CategoryTypes tt ON tt.CategoryID = cg.CategoryID

INSERT INTO @CategoryTypes (CategoryID, InputID)
    SELECT 'Unknown', Input
        FROM dbo.NewInputs WHERE InputID NOT IN (SELECT InputID FROM @CategoryTypes) AND DateProcessed = @DateProcessed
END
                     
RETURN
END
3
  • Not using a cursor would probably be a pretty good start. Seeing the actual query would be helpful for us to make suggestions.
    – J.D.
    Aug 31, 2022 at 17:25
  • 1
    Thanks for taking the time to respond. I'll try to get the query in an edit. Aug 31, 2022 at 17:32
  • How many rows are there in the cursor? Aug 31, 2022 at 22:01

1 Answer 1

0

As you mention, you cannot use CONTAINS with a joined column, only with a variable or literal.

I think the best solution might be to create the function dynamically, with the CONTAINS parameters hard-coded. We can do this with a trigger on dbo.FuzzyCategoryMapping. This would assume that this table is rarely updated, I would not use such a solution on a frequently updated table as you would get major Sch-M locking issues.

The template for the function would be the following:

CREATE OR ALTER FUNCTION dbo.GetInputCategories (@DateProcessed DATETIME)
RETURNS TABLE
AS RETURN

SELECT
  v.CategoryID,
  cg.CategoryGroupID,
  ni.InputID
FROM dbo.NewInputs ni
OUTER APPLY (
    SELECT osCategoryid1
    WHERE CONTAINS(ni.InputText, ContainsClause1)
    UNION ALL
    SELECT osCategoryid2
    WHERE CONTAINS(ni.InputText, ContainsClause2)
    UNION ALL
    ...
) v(CategoryID)
LEFT JOIN dbo.CategoryGroups cg ON cg.CategoryID = v.CategoryID
WHERE ni.DateProcessed = @DateProcessed;

Note the fully set-based nature of this. It is a single inline Table Valued Function.

And then your trigger would be this

CREATE OR ALTER TRIGGER tr_UpdateCategoriesFunction
ON dbo.FuzzyCategoryMapping
AFTER INSERT, UPDATE, DELETE
AS

SET NOCOUNT ON;

IF NOT EXISTS (SELECT 1 FROM inserted) AND NOT EXISTS (SELECT 1 FROM deleted)
    RETURN;

IF NOT EXISTS (SELECT 1 FROM dbo.FuzzyCategoryMapping)
    RETURN;

DECLARE @union nvarchar(max);
SELECT @union = STRING_AGG('
    SELECT ' + CAST(fcm.osCategoryid AS nvarchar(30)) + ' 
    WHERE CONTAINS(ni.InputText, ''' + REPLACE(CAST(fcm.ContainsClause AS nvarchar(max)), '''', '''''') + ''')', '
UNION ALL'
)
FROM dbo.FuzzyCategoryMapping fcm;

DECLARE @sql nvarchar(max) = '
CREATE OR ALTER FUNCTION dbo.GetInputCategories (@DateProcessed DATETIME)
RETURNS TABLE
AS RETURN

SELECT
  v.CategoryID,
  cg.CategoryGroupID,
  ni.InputID
FROM dbo.NewInputs ni
OUTER APPLY (' + @union + ') v(CategoryID)
LEFT JOIN dbo.CategoryGroups cg ON cg.CategoryID = v.CategoryID
WHERE ni.DateProcessed = @DateProcessed;
';

EXEC(@sql);

As a side note: should your parameter be datetime or date?

4
  • Thanks for the innovative idea! FuzzyCategoryMatching hasn't changed in years. It's got 62 rows, currently. I'll bang up the function and run some speed comparisons but it looks very helpful! The whole batching process is a little wonky. The calling sproc marks all the rows to be categorized with a datetime and then calls this function to do the categorizing. Sep 1, 2022 at 14:20
  • Note that the final result may contain multiple categories per InputId which is what your original function did also Sep 1, 2022 at 14:24
  • How well did this solution perform? Sep 25, 2022 at 15:49
  • Sorry I've been so slow to respond; I was on vacation for a couple of weeks in there and catching up the rest. Surprisingly, I didn't really find any improvement in performance with the unrolling. I guess the full text search function overhead just dwarfed the rest. But thanks for the innovative idea. Oct 3, 2022 at 19:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.