代码之家  ›  专栏  ›  技术社区  ›  joaocarlosib

用于动态查询情况的存储过程常识模式

  •  1
  • joaocarlosib  · 技术社区  · 1 年前

    我正在设计一个可以容纳数十万行的表,例如:

    CREATE TABLE [dbo].[Messages](
        [MessageID] [uniqueidentifier] NOT NULL,
        [Number] [int] NOT NULL,
        [CreationDate] [datetime] NOT NULL,
        [Status] [tinyint] NOT NULL,
        [Message] [nvarchar](1022) NOT NULL,
        [Topic] [smallint] NOT NULL,
        [DestinataryID] [int] NULL
    )
    

    几乎所有这些都可以在应用程序运行时由用户进行过滤,destinataryID是最频繁的。我确实创建了索引,只是不是所有地方都创建了索引。所以,我现在的工作是设计一个SP来优化它。我做了这样的事:

    CREATE PROCEDURE GetFilteredData
    @Topic INT = NULL,
    @DestinataryID INT = NULL,
    @CreationDateStart DATETIME = NULL,
    @CreationDateEnd DATETIME = NULL,
    @Status BIT = NULL
    AS
    BEGIN
    DECLARE @SQL NVARCHAR(MAX);
    
    SET @SQL = 'SELECT ID, Number, CreationDate, Status, Message, Topic, DestinataryID FROM YourTable WHERE 1=1';
    
    IF @DestinataryID IS NOT NULL
        SET @SQL += ' AND DestinataryID = @DestinataryID';
    
    IF @Topic IS NOT NULL
        SET @SQL += ' AND Topic = @Topic';
    
    -- more ifs
    
    EXEC sp_executesql @SQL, N'@Topic INT, @DestinataryID INT', @Topic, @DestinataryID;
    END
    

    我主要担心的是,当过滤多个可能的上下文时,它可能会开始使用糟糕的执行计划,因为这对一些请求可能是好的,对其他请求可能是坏的。

    我的东西是做这些动态操作的常规方式吗?我应该为每种情况设计一个单独的SP吗?也许使用提示会有所帮助?提前感谢

    1 回复  |  直到 1 年前
        1
  •  2
  •   Charlieface    1 年前

    我的东西是做这些动态操作的常规方式吗?

    是的,这是推荐的方式。这种类型的查询称为 Kitchen Sink Query , 而你的解决方案实际上是非常好的。

    具有大量的单片查询的问题 AND OR 逻辑是,它完全打乱了计划,服务器无法同时提供这么多不同的选项(它只有一个针对所有可能选项的计划)。

    您所做的动态解决方案实际上有很多 较好的 ,因为它为每个过滤器组合生成一个新的计划。(注意“过滤器组合” “值的组合”,这就是为什么您需要使用参数化它 sp_executesql ). 该计划是根据正在执行的查询的确切类型量身定制的,因此将使用正确的索引。

    考虑在任何客户端语言中使用ORM来构建这些动态过滤器,因为它们在这方面比T-SQL要好得多。

    它可能会开始使用糟糕的执行计划,因为当过滤多个可能的上下文时,它可能对某些请求有利,对其他请求不利。

    你对糟糕计划的担忧并非完全没有根据,但你需要监控你的服务器,看看会发生什么。在得到实际信息之前,不要急于做任何事情。如果你有适当的索引,主要是平等(而不是不平等或 LIKE )谓词,那么您不应该有太多问题。

    我应该为每种情况设计一个单独的SP吗?

    如果你看到某个组合效果特别差,你想定制它,那么是的,这样做是有意义的。否则就不用麻烦了。没有必要对每种可能的组合都进行单独的程序。

    通常,过滤索引或索引视图是比自定义查询更好的选择。

    也许使用提示会有所帮助?

    这将取决于计划的问题是什么,但通常应该避免暗示,以利于更好的索引和一般重构。

    重构的一个例子是:数据类型与列不匹配。纠正这一点会有所改善

    CREATE PROCEDURE GetFilteredData
      @Topic smallint = NULL,
      @DestinataryID INT = NULL,
      @CreationDateStart DATETIME = NULL,
      @CreationDateEnd DATETIME = NULL,
      @Status tinyint = NULL
    AS