首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >用于参数化查询的Sql server索引

问用于参数化查询的Sql server索引
EN

Stack Overflow用户
提问于 2013-02-07 12:39:16
回答 2查看 813关注 0票数 3

我想为sql查询创建最佳索引。用户可以选择UI上的选项,从UI中生成查询。下面是表和查询:

代码语言:javascript
复制
CREATE TABLE [dbo].[MyTable](
    [Id] [nvarchar](32) NOT NULL,
    [SomeKey] [int] NOT NULL,
    [Col1] [nvarchar](max) NULL,
    [Col2] [nvarchar](max) NULL,
    [NumCol1] [int] NOT NULL,
    [NumCol2] [int] NOT NULL,
    [BitCol1] [bit] NOT NULL,
    [BitCol2] [bit] NOT NULL,
    [Created] [datetime] NOT NULL
CONSTRAINT [PK_dbo.MyTable] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

查询如下(SP):

代码语言:javascript
复制
ALTER PROCEDURE [dbo].[MyTable] 
@SomeKey INT,@Col1 BIT,@Col2 BIT,@BitCol1 BIT,@BitCol2 BIT,
@NumCol1 INT,@NumCol2 INT,@offset INT,@take INT
AS
DECLARE @sql NVARCHAR(MAX), @paramlist  nvarchar(4000) 
SET @sql = 'SELECT [Id],[SomeKey],[Col1],[Col2],[NumCol1],[NumCol2],[BitCol1],[BitCol2], FROM MyTable WHERE SomeKey = @SomeKey'
IF @NumCol1 IS NOT NULL SELECT @sql = @sql + ' AND NumCol1 = @NumCol1'
IF @NumCol2 IS NOT NULL SELECT @sql = @sql + ' AND (NumCol2 > @NumCol2)'
IF @Col1 IS NOT NULL SELECT @sql = @sql + ' AND (Col1 = '''' OR Col1 IS NULL)'
IF @Col2 IS NOT NULL SELECT @sql = @sql + ' AND (Col2 = '''' OR Col2 IS NULL)'
IF @BitCol1 IS NOT NULL SELECT @sql = @sql + ' AND BitCol1 = 1'
IF @BitCol2 IS NOT NULL SELECT @sql = @sql + ' AND BitCol2 = 1'
SELECT @sql = @sql + ' ORDER BY Created OFFSET @offset ROWS FETCH NEXT @take ROWS ONLY'
SELECT @paramlist = '@SomeKey INT, @Col1 INT, ....'
EXEC sp_executesql @sql, @paramlist, @SomeKey, @Col1, @Col2, ...

希望你明白重点。我正在db中构建查询字符串,并执行它。如果我发送param Col1 = true,这意味着我需要db中的所有空Col1。在app中,我确保我准备好了所有的参数。休息是不言自明的,我希望(但请随时提出任何问题的意见)!

如何为此表/查询建立索引,以避免索引扫描或类似的“慢速”搜索?而且,我只是一个网页开发人员,所以这个(高级)索引对我来说是新的东西!Db是Server 2012。

更新:使用场景

桌子将有100到10米-20米的最大行数。行将不会被更新。当应用程序运行时,插入将在500次(sqlbulkcopy c#类)中完成,每5-10分钟或更长时间完成一次(它并不总是在运行)。每周一次--两行将被删除(基于SomeKey col)。

应用程序不会有很多用户(可能很少同时使用,最多有10个用户),所以不要期望多个查询/秒(每5-10秒1次查询?),但我希望它是快速的(以毫秒为单位,而不是秒)。

EN

回答 2

Stack Overflow用户

发布于 2013-02-07 13:37:09

指数是一种权衡。必须对表中的数据更改进行更新,因此要小心使用包含大量插入和更新的表。

在中,您可以看到查询的执行播放。在“查询”菜单中有一些选项。您将能够看到在执行查询过程中所花费的最多时间。

其他选项是从菜单工具>数据库引擎优化顾问访问优化顾问。它将建议创建索引。

不管你在搜索中有什么变量,比如哪里是一个很好的被索引的候选。

索引并不会杀死你。您可以使用它来找到最佳解决方案,并且可以在生产过程中进行更改,而不会出现重大问题。不幸的是,如果您需要索引,不仅要根据表的设计,还要根据这个表中的数据量、数据多样性和您在表上执行的操作的性质。

更新:

什么对你有帮助:

MSDN查询性能

MSDN查询调优

关于您的执行计划,我建议您阅读有关理解嵌套循环连接的部分内容。

索引扫描通常是从给定列中检索大量数据的结果。

票数 1
EN

Stack Overflow用户

发布于 2013-02-07 13:53:53

最好的解决方案实际上取决于我们正在处理的资源--表包含多少行、可用内存/空间有多少、使用动态参数的频率以及表上的典型使用模式(读多于写)。

我的赌注是在SomeKey上添加总是被引用的索引,然后尝试添加Created,因为排序也总是使用。您还可以通过让bit列过滤静态(它们总是以一种或另一种方式(您知道所有可能的值),然后将它们添加到SomeKey和Created之间的索引中来改进逻辑。

最后,您可以尝试添加INT列作为键的包含列,以查看添加的索引大小是否与较少的磁盘读取相抵消。

这是为初学者准备的,因为一切都取决于您典型的工作负载。做相应的测试。

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/14751346

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档