sql-server - 基于动态比较查找匹配实体

Question

我不确定这个主题是否真的有意义，但我不知道如何措辞。设置如下:我有一个 Item，它有许多 ItemLogic，每个都有一个 Field。例如，每个 Item 有 25 个 ItemLogic 实体。该逻辑确定 Item 是否与表单中的给定输入匹配。例如，字段 X 的值大于 A，字段 Y 的值等于 B，对于 25 个字段中的每一个字段，依此类推。

在当前版本的应用中，所有相关实体都会被查询并循环，返回第一个匹配的 Item，其中所有 ItemLogic 都为 true。它有点贵，但代码简单，而且从来没有那么多项目可供查看。到目前为止。

现在应用程序需要过滤 3000 个项目才能找到匹配项。上一个查询至少有两个连接，在我们的 SQL 实例上花费了大约 45 秒。这太长了。

存储过程似乎很自然，但有一个问题:每组项目的数据都是动态的，它以字符串值的形式出现，并且通常需要转换为不同的类型(最常见的是 DateTime 或 int)来执行实际的比较，并且一些逻辑被忽略而不是比较。这对于存储过程来说是很大的额外开销，至少我是这么认为的。

或者，我可以对数据进行分块，但这对于试图匹配集合中最后一个项目的可怜人来说并没有节省多少。

可以采取哪些方法来加快比赛速度？

架构和一些示例数据:

CREATE TABLE [dbo].[Items](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](255) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE TABLE [dbo].[ItemLogic](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ItemId] [int] NOT NULL,
    [FieldId] [int] NOT NULL,
    [Value] [nvarchar](max) NULL,
    [Comparison] [int] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE TABLE [dbo].[Fields](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Value] [nvarchar](max) NOT NULL,
    [Type] [int] NOT NULL,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

INSERT INTO [dbo].[Fields] (Value, Type) VALUES ('abc', 0), ('def', 0), ('123', 1)

INSERT INTO [dbo].[Items] (Name) VALUES ('Item 1'), ('Item 2'), ('Item 3')

INSERT INTO [dbo].[ItemLogic] (ItemId, FieldId, Value, Comparison) VALUES (1, 1, 'xyz', 1), (1, 2, 'qrs', 1), (1, 3, '200', 0), (2, 1, 'abc', 1), (2, 2, 'xyz', 1), (2, 3, '123', 2), (3, 1, 'abc', 1), (3, 2, 'def', 1), (3, 2, '100', 0)

对于 Comparison 字段，它是一个枚举匹配:0 = 大于、1 = 等于、2 = 忽略。对于 Type 字段，它是一个枚举匹配:0 = string，1 = int。

上述匹配的预期结果应该是返回Item 3。

Answer 1

它永远不会很快。然而，这是我能想象到的最简单、最紧凑的解决方案:

SELECT * 
FROM 
    Items
WHERE 
    Id NOT IN (
        SELECT IL.ItemId
        FROM
            Fields F
            INNER JOIN ItemLogic IL
                ON F.Id = IL.FieldId
        WHERE
            NOT (
                IL.Comparison = 2 -- Ignore
                OR
                F.Type = 0 AND ( -- string types
                    IL.Comparison = 0 AND F.Value > IL.Value
                    OR 
                    IL.Comparison = 1 AND F.Value = IL.Value
                )
                OR
                F.Type = 1 AND ( -- integer types
                    IL.Comparison = 0 AND TRY_CAST(F.Value AS int) > TRY_CAST(IL.Value AS int)
                    OR 
                    IL.Comparison = 1 AND TRY_CAST(F.Value AS int) = TRY_CAST(IL.Value AS int)
                )
            )
        )