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

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

query-optimization stored-procedures t-sql sql-server sql

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 年前

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