SQL Server存储过程的最优查询方法？

时间：2022-11-16 03:54:38

I have 5 meta tables that have the same format but depend on 5 other tables. Each Meta table looks like this:

我有5个元表具有相同的格式,但依赖于其他5个表。每个Meta表看起来像这样:

CREATE TABLE [dbo].[SiteMetas] 
(
    [SiteMetaId] [bigint] IDENTITY(1,1) NOT NULL,
    [SiteId] [bigint] NOT NULL,
    FOREIGN KEY([SiteId]) REFERENCES [dbo].[Sites] ([SiteId]),
    [MetaGroup] [nvarchar] (64) NOT NULL,
    [MetaName] [nvarchar] (128) NOT NULL,
    [MetaType] [char] NOT NULL DEFAULT 0, -- t, i, r, d, s, b
    [MetaBool] [bit] DEFAULT NULL, -- t
    [MetaInteger] [bigint] DEFAULT NULL, -- i
    [MetaReal] [real] DEFAULT NULL, -- r
    [MetaDateTime] [datetime] DEFAULT NULL, -- d
    [MetaString] [nvarchar] (MAX) DEFAULT NULL, -- s
    [MetaBinary] [varbinary] (MAX) DEFAULT NULL, -- b
    [MetaCreated] [datetime] NOT NULL DEFAULT (GETUTCDATE()),
    [MetaExpires] [datetime] DEFAULT NULL,
    [MetaUpdated] [datetime] DEFAULT NULL,
    PRIMARY KEY CLUSTERED ([SiteMetaId] ASC) WITH (IGNORE_DUP_KEY = ON),
    UNIQUE NONCLUSTERED ([SiteId] ASC, [MetaGroup] ASC, [MetaName] ASC) WITH (IGNORE_DUP_KEY = ON)
);

This is for Site but there's 4 more. Like Users, ...

这是针对网站,但还有4个。像用户一样,......

And I want to read the Binary meta value from Site. So wrote this stored procedure:

我想从Site中读取二进制元值。所以编写了这个存储过程:

CREATE PROCEDURE [dbo].[GetSiteMetaBinary]
  @SiteId AS bigint,
  @Group AS nvarchar(64),
  @Name AS nvarchar(128)
AS
BEGIN
    SELECT TOP 1 [MetaBinary] 
    FROM [dbo].[SiteMetas]
    WHERE [SiteId] = @SiteId
      AND [MetaGroup] = @Group 
      AND [MetaName] = @Name 
      AND [MetaType] = 'b';
END;

This stored procedure has duplicates for User too... and the rest of the tables. That just replaces Site with User in its body.

此存储过程也为User也有重复...以及其余表。这只是将Site替换为User。

But thinking that I have too many of these I wrote this one:

但是我认为我有太多这些,我写了这个:

CREATE PROCEDURE [dbo].[GetMeta]
  @Set AS nvarchar(64),
  @Id AS bigint,
  @Group AS nvarchar(64),
  @Name AS nvarchar(128),
  @Type AS nvarchar(16)
AS
BEGIN
    DECLARE @Flag nchar(1);
    DECLARE @Sql nvarchar(MAX);

    SET @Flag = CASE @Type
        WHEN 'Bool' THEN 't'
        WHEN 'Integer' THEN 'i'
        WHEN 'Real' THEN 'r'
        WHEN 'DateTime' THEN 'd'
        WHEN 'String' THEN 's'
        WHEN 'Binary' THEN 'b'
        ELSE NULL
    END;

    SET @Sql = N'SELECT TOP 1 [Meta' + @Type + N'] FROM [dbo].[' + @Set + N'Metas]' +
        N'WHERE [' + @Set + N'Id] = @Id AND [MetaGroup] = @Group AND [MetaName] = @Name AND [MetaType] = @Flag;';

    -- SELECT @Sql; -- DEBUG

    EXEC sp_executesql @Sql,
        N' @Id AS bigint, @Group AS nvarchar(64), @Name AS nvarchar(128), @Flag AS nchar(1)',
        @Id, @Group, @Name, @Flag
        ;
END;

which is a general use stored procedure to read any data typed stored in a column based on input arguments. I use it like this [dbo].[GetMeta] 'Site', 1, 'group', 'name', 'Binary' the difference being that the actual query is dynamically generated so it's not known before hand by SQL Server like the first specialized variant.

这是一个通用的存储过程,用于根据输入参数读取存储在列中的任何类型的数据。我像这样使用它[dbo]。[GetMeta]'Site',1,'group','name','Binary'不同之处在于实际查询是动态生成的,因此SQL Server之前不知道它是什么第一个专门变种。

Which of the two choices is better from a performance point of view and friendlier to SQL Server's internals? A dedicated one for each table and column data type of a general one that internally builds a query based on fed arguments.

从性能的角度来看,哪两种选择更好,而对SQL Server的内部结构更友好?用于每个表和列数据类型的专用数据类型,通常基于反馈参数在内部构建查询。

I can use either. I like the last as it does not pollute my stored procedure space. :) The first one is more clear and SQL Server might be able to optimize it better. Not sure...

我也可以使用。我喜欢上一个,因为它不会污染我的存储过程空间。 :)第一个更清楚,SQL Server可能能够更好地优化它。不确定...

PS: I'm quite new to SQL Server

PS:我对SQL Server很陌生

1 个解决方案

#1

1  

Static procedures are usually faster because the SQL engine can cache the compiled SP's execution plan.

静态过程通常更快,因为SQL引擎可以缓存已编译的SP的执行计划。

However, unless this SP will be called a lot or is time-critical, it probably isn't worth worrying about it because the time savings of only having to maintain one SP will make up for the very small amount of time difference spent waiting for the SP to finish.

但是,除非这个SP被大量调用或者是时间关键的,否则它可能不值得担心,因为只需维持一个SP所节省的时间就可以弥补等待的极少时间差。 SP完成。

#1

1  

Static procedures are usually faster because the SQL engine can cache the compiled SP's execution plan.

静态过程通常更快,因为SQL引擎可以缓存已编译的SP的执行计划。

However, unless this SP will be called a lot or is time-critical, it probably isn't worth worrying about it because the time savings of only having to maintain one SP will make up for the very small amount of time difference spent waiting for the SP to finish.

但是,除非这个SP被大量调用或者是时间关键的,否则它可能不值得担心,因为只需维持一个SP所节省的时间就可以弥补等待的极少时间差。 SP完成。

标签：

相关文章