下表

数据访问代码如何影响数据库性能

Bob Beauchemin

内容

查询计划重用
存储的过程和参数化的查询
参数长度
SQL Server 统计信息
右计划的作业

通过优化查询，和数据库应用程序性能优化通常情况下，该数据库管理员联系，该应用程序开发人员或两个的省是否的被一个一致的辩论。 数据库管理员通常具有更多的工具，开发人员比访问权限。 DBA 可以查看性能监视器计数器，并运行 SQL 事件探查器的动态管理视图确定放置数据库，创建索引以便更好地执行查询的位置。 查询和访问数据库的存储的过程，通常将写入应用程序开发人员。 并开发人员可以使用一个测试系统中的大部分相同的工具及基于应用程序的设计和使用情况的知识，开发人员可以提供有用索引建议。 但经常被忽视的一个点是应用程序开发人员编写数据库 API 访问数据库的代码。 访问数据库，如 ADO.NET、 OLE DB 或 ODBC，代码可以对产生影响数据库性能。 这一点尤其是当尝试编写一个通用的数据访问框架，或选择一个现有的框架时。 在本文中，我们将深入探讨一些典型的方法编写数据访问代码并查看它们可以对性能有的影响。

查询计划重用

让我们从通过所查询的生命周期开始。 通过查询处理器提交时查询，查询处理器分析文本的语法的正确性。 在存储过程中的查询是语法检查过程中创建过程语句。 执行查询或存储的过程之前，处理器检查计划缓存查询计划与相匹配。 查询计划重用如果匹配的文本 ； 没有匹配项发生的查询文本创建查询计划。 执行查询后，计划返回到缓存中重复使用。 查询计划的创建是昂贵的操作，查询计划重用几乎总是一个好主意。 查询的被比较计划在缓存中的文本的文本必须匹配使用区分大小写的字符串比较。

因此，查询

   SELECT a.au_id, ta.title_id FROM authors a
   JOIN titleauthor ta ON a.au_id = ta.au_id
   WHERE au_fname = 'Innes';

将不匹配

   SELECT a.au_id, ta.title_id FROM authors a
   JOIN titleauthor ta ON a.au_id = ta.au_id
   WHERE au_fname = 'Johnson';

请注意它也将不匹配此文本

   SELECT a.au_id, ta.title_id 
   FROM authors a JOIN titleauthor ta ON a.au_id = ta.au_id
   WHERE au_fname = 'Innes';

这是因为换行符字符中的语句中的不同位置。 要查询计划重用相关帮助 SQL Server 查询处理器可以执行该过程称为 autoparameterization。 Autoparameterization 将一个语句

SELECT * FROM authors WHERE au_fname = 'Innes' 

参数化的语句并在参数声明：

(@0 varchar(8000))SELECT * FROM authors WHERE au_fname = @0

这些语句可以观察到在计划缓存中使用 sys.dm_exec_query_stats 或 sys.dm_exec_cache_plans，一个 CROSS 应用 sys.dm_exec_sql_text(handle) 使文本与其他信息相关联。 Autoparameterization 帮助查询计划重用，但不是完美。

渚嬪的方式  语句

    SELECT * FROM titles WHERE price > 9.99

若要为参数化

    (@0 decimal(3,2))SELECT * FROM titles WHERE price > @0

    SELECT * FROM titles WHERE price > 19.99

若要使用一个不同的数据类型是参数化

    (@0 decimal(4,2))SELECT * FROM titles WHERE price > @0

    SELECT * FROM titles WHERE price > $19.99

若要为参数化

    (@0 money)SELECT * FROM titles WHERE price > @0

有多个相似的查询，可以使用同一计划查询计划被称为计划缓存污染。 它不会填满与冗余计划，计划缓存不仅时间 （和 CPU 和 I/O） 会消耗它也会导致创建冗余的计划。 请注意，在 autoparameterization，查询处理器必须"猜测"基于参数值的参数类型。 Autoparameterization 帮助，但不是会完全消除计划缓存污染。 鍙 ﹀ 的方式  参数化查询的文本进行规范化，以便计划重复使用，即使原始文本使用不同的格式。 Autoparameterization 只用于基于查询的复杂性的查询的一个子集。 尽管所有 autoparameterization 规则的完整讨论超出了本文的范围，意识到 SQL Server 使用两个规则集之一： SIMPLE 和 FORCED 参数化。 不同之处的一个示例是简单的参数化功能将不 autoparameterize 多表查询，但将强制参数化。

存储的过程和参数化的查询

一个更好的选择是使用参数化的查询或存储的过程。 不执行这些帮助查询计划重用，但如果您定义您的参数正确，数据类型猜测永远不会完成。 使用存储的过程是最佳选择，因为存储的过程定义中完全指定该参数数据类型。 记住的存储过程或者不完美。 一个困难是数据库对象的名称解析不在完成创建过程的时间 ； 一个表或列的名称不存在的原因的执行时间错误。 此外，尽管存储的过程的参数构成应用程序代码和过程代码之间"合同"，存储的过程还可以返回 resultsets。 没有元数据的定义，因此没有合同上存在的 resultsets 数以及结果集列的数字和数据类型。

可以在数据库 API 代码中至少两种方式调用存储的过程。 以下是使用 ADO.NET 的一个示例：

SqlCommand cmd = new SqlCommand("EXECUTE myproc 100", conn);
int i = cmd.ExecuteNonQuery(); 

SqlCommand cmd = new SqlCommand("myproc", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@a", SqlDbType.Int); cmd.Parameters.Value = 100;
int i = cmd.ExecuteNonQuery(); 

作为命令字符串 （CommandType.Text） 执行存储的过程，而不使用 ADO.NET 的 ParameterCollection 使用开销较低的远程过程调用 (RPC) 中使用 CommandType.StoredProcedure 结果时一个 SQL Server 语言语句。 这种差异都可以在 SQL 事件探查器中观察到。 您将在参数的传递也是重要的查询计划创建时由于，但将得到返回的更高版本。

参数化的查询使用参数在 API 中使用几个重要例外方式存储的过程完成。 ADO.NET 的 SqlParameter 类包含属性不仅参数名称和值，还适用参数数据类型、 长度、 精度，和小数位数。 若要避免通过在参数化查询中指定正确的值的所有相关参数的计划缓存污染重要的。 否则，因为不存在参数合同因为使用存储过程，ADO.NET 必须猜测在这些属性。 它类似于方法的 autoparameterization 猜测，但实现不同几个方面。 以下图表 图 1 和 图 2 ，显示当前实现中 SQL Server 2008 的 autoparameterization 和 ADO.NET 3.5 SP1 SqlParameterCollection AddWithValue 方法。

使用参数化的查询时, 是使用 Parameters.AddWithValue 一个坏的主意。 在这种情况下 ADO.NET 必须猜测，数据类型，还有一个特殊的危险材料使用字符串和 AddWithValue 时。 第一次的所有，.NET 字符串类是一个的 Unicode 字符串而中 T-SQL 的字符串常量可以是指定为 Unicode 或非-Unicode。 ADO.NET 将传递一个 Unicode 字符串参数 (NVARCHAR 在 SQL 中） 中。 如果正在使用谓词中的列是非-Unicode，在查询计划本身，这可以有负面影响。 渚嬪的方式  假设有一个具有 VARCHAR 聚集为主键列的表：

CREATE TABLE sample (
  thekey varchar(7) primary key,
  name varchar(20) -- other columns omitted
)

在我的 ADO.NET 代码中我需要通过主键执行查找操作：

cmd.CommandText = "SELECT * FROM sample when thekey = @keyvalue;"

并指定使用此参数：

cmd.Parameters.AddWithValue("@keyvalue", "ABCDEFG");

ADO.NET 将一个参数数据类型 NVARCHAR(7) 的决定。 因为从 NVARCHAR 转换为 VARCHAR 会检索行的查询执行步骤中，参数值不能用作一个搜索参数。 而不是执行一个聚集索引查找的一个行，查询将执行一个聚集索引扫描整个表。 现在，假设这一个具有 5，000，000 行的表。 由于您已提交参数化的查询，还有可以执行 SQL Server autoparameterization，执行任何操作，数据库管理员可以更改在服务器上来修复此问题。 使用该 FORCESEEK 失败在所有生成计划作为最后的手段的查询提示。 当参数类型被指定为 SqlDbType.VarChar 而进行猜测，数据类型 ADO.NET，这样的查询的响应除去从多个秒 （最） 以毫秒为单位）。

参数长度

始终指定该参数的长度将获得基于字符串的数据类型到另一个好习惯。 此值应使用参数，SQL 谓词中的字段的长度或最大字符串长度 4，(000 NVARCHAR，8，000 的 VARCHAR 为），而不是字符串本身的长度。 SQL Server autoparameterization 始终假定该字符串最大长度但 SqlParameterCollection.AddWithValue 参数长度等于字符串的长度。 因此，使用下面的调用将产生不同的参数数据类型和因此是不同的计划：

// produces an nvarchar(5) parameter
cmd.Parameters.AddWithValue(
"SELECT * FROM authors WHERE au_fname = @name", "@name", "Innes"); 
// produces an nvarchar(7) parameter
cmd.Parameters.AddWithValue(
"SELECT * FROM authors WHERE au_fname = @name", "@name", "Johnson"); 

通过使用 ParameterCollection.AddWithValue 时不指定长度，可以有多个不同的查询计划缓存中，具有不同的字符串长度。 现在的计划缓存污染的大方法。 尽管我提到结合此行为的 ADO.NET，请注意其他数据库 API 共享字符串参数的计划缓存污染的问题。 这两个 LINQ to SQL 和 ADO.NET 实体框架的当前版本表现出此行为的变体。 香草 ADO.NET，您可以指定一个字符串参数的长度的 ； 框架，使用 API 调用转换通过 LINQ to SQL 或实体框架本身，因此您不能执行任何有关其字符串参数计划缓存污染。 这两个 LINQ to SQL 和实体框架将解决在即将进行的.NET 4release 此问题。 因此如果您使用自己的参数化的查询，不要忘记指定正确的 SqlDbType，字符串的参数的长度和精度和小数的参数的小数位数。 此处性能完全在的程序员的领域，大多数 DBA 不会检查 ADO.NET 代码，如果担心性能。 如果您使用存储的过程，显式参数合同将确保您始终使用正确的参数类型和长度。

虽然您始终应使用参数化的 SQL 语句内，并尽可能输出的存储的过程，少数情况下时，有不能使用参数化。 在您的 SQL 语句中您不能对列的名称或表的名称参数化。 这可以包括 DDL （数据定义语言语句），以及 DML （数据操作语言语句）。 因此虽然参数化有助于性能并是了最佳的安全措施防止 SQL 注入攻击 （而不是参数可以允许恶意用户添加 SQL 注入代码，则使用字符串串联），并不总是可以参数化的所有内容。

您将您的参数的值设置也是重要的。 如果您已经注意到当您使用参数化的查询时生成的 ADO.NET SQL 使用 SQL 事件探查器，您会注意到它不像下面这样：

(@0 VARCHAR(40))SELECT * FROM authors WHERE au_fname = @0

而您将看到：

sp_executesql N'SELECT * FROM authors WHERE au_fname = @name',
           N'@name VARCHAR(40)', 'Innes'

该过程 sp_executesql 是执行一个动态生成的 SQL 字符串可以包含参数的系统存储过程。 为什么 ADO.NET 使用它来执行参数化的查询的一个原因是这会导致系统开销较低的 RPC 调用的使用。 为什么使用 sp_executesql 另一个重要的原因是，启用 SQL Server 查询处理器行为称为"参数嗅探。 这导致最佳的性能，因为查询处理计划生成时知道参数值，并使其统计信息的最佳使用。

SQL Server 统计信息

SQL Server 使用来帮助生成作业的最佳查询计划的统计信息。 统计信息的两个主要类型为 （为指定列存在多少个唯一值） 的密度统计信息和基数统计 （值分发的直方图）。 有关这些统计信息，引用该白皮书" 使用查询优化器在 Microsoft SQL Server 2005 中的统计信息"通过 Eric N。 Hanson 和 Lubor Kollar。 了解如何 SQL Server 使用统计信息的关键了解 SQL Server 在批处理或存储的过程的批处理开头或存储的过程中创建的所有查询的查询计划。 如果查询处理器在计划的生成时间知道参数的值，然后可以使用基数和密度统计信息。 如果在计划的创建时间未知值，然后可以使用仅密度统计信息。 例如，如果 ADO.NET 程序员使用类似于下面的参数，有没有办法知道您正在寻找从加利福尼亚的作者和使用上状态列中的基数统计信息，查询处理器：

cmd.CommandText = "declare @a char(2); set @a = 'CA'; select * from   authors where state = @a ";
SqlDataReader rdr = cmd.ExecuteReader();

（之前声明语句在这种情况下），第一个语句之前创建该计划时，参数值没有被分配还。 这就是为什么参数化的查询被转换成 sp_executesql。 在这种情况下在存储 sp_executesql 过程的项上创建计划，并查询处理器可以在计划的生成时间嗅探该参数的值。 sp_executesql 调用中指定参数值。 相同的概念适用于您编写存储的过程。 假设您有一个检索加利福尼亚的作者，如果传递的值为的 NULL 中，否则为，用户必须指定的状态他希望，如下所示的查询：

CREATE PROCEDURE get_authors_by_state (@state CHAR(2))
AS
BEGIN
IF @state IS NULL THEN @state = 'CA';
SELECT * FROM authors WHERE state = @state;
END

现在，在最常见的情况下 （如果不指定任何参数和状态为空），查询已经优化值 NULL，而不是值"CA"。 如果 CA 是列的公共值，然后您将被可能获得错误的计划。 因此，在 ADO.NET 中使用参数化的查询时请记住这意味着使用该 SqlParameterCollection，并不指定 SQL 语句本身中的的参数声明和分配。 如果您正在编写存储的过程，请确保您记住的在代码中设置参数本身工作对参数探测。 请注意您将不会看到不同的查询计划，在上面的示例使用 pubs 数据库中的作者表 ； 它太小。 在较大的表中这样做可以影响使用的物理的联接类型并间接影响查询计划的其他部分。 有关的参数探测如何影响查询计划的示例请参阅白皮书" 批编译、 重新编译和计划缓存 SQL Server 2005 中的问题"通过 Arun Marathe 苏云 Scott。

探测参数是一个好方法。 在基数统计信息计算包含一个大约等于每个直方图步骤中的行数的任何参数值的基数作为一个整体的代表。 但由于没有有限的数量的基数存储桶 (最大 200 存储桶） 并且主要重复的值包含的某些列，这并非总是这种情况。 假设有一个表中的客户。 因为您的业务基于在加利福尼亚，您的客户的大多数来自加利福尼亚。 假设 200，000 加利福尼亚客户和俄勒冈 10 个客户。 要查找的客户在加利福尼亚为而不是在俄勒冈客户时，加入您的客户表与五个其他表的查询计划可能会很大的不同。 如果第一个查询在俄勒冈客户，加利福尼亚客户的缓存和重用计划将也认为相对于大量的加利福尼亚客户的 10 个加利福尼亚客户。 在这种情况下使用基数统计信息不在的帮助，但一个 hindrance。 超出了此问题，最简单的 （但最脆弱的） 的方法是使用条件代码 — 一个用于多个客户，一个用于状态在应用程序或要调用两个不同存储的过程是单独存储过程中指出与几个客户。 如果两个不同的存储过程中出现的查询 SQL Server 将不共享的查询计划，甚至完全在同一个查询。 确定构成"与很多的客户的状态"脆弱的部分，意识到您分发的客户可以更改时间。 SQL Server 还提供了可以帮助解决某些查询提示。 如果您决定让每个人都使用加利福尼亚客户的计划确定 （因为您只有少量的行是否仍要处理其他状态），然后可以使用查询提示 OPTION (parameter_name 优化 FOR = 值）。 确保在缓存中的计划将始终为许多客户状态的计划。 作为一种替代方法，您可以使用 SQL Server 2008 的新 OPTION (优化 FOR 未知） 提示，这样 SQL Server 忽略基数统计信息，并提出可能大或小的状态不优化的中间计划。 此外如果您有一个查询，使用多的参数，但只用于一个值，它们一次 （假设有人可以搜索从一到十个不同的条件相同的查询中的参数定义的系统） 然后，您的最佳匹配可能故意生成不同的查询计划每次。 这是一个 OPTION 重新编译查询提示与指定的。

右计划的作业

若要汇总使用参数对计划缓存污染和 SQL 注入攻击的临界条件。 请始终使用参数化的查询或参数化存储的过程。 始终指定右数据类型、 长度、 精度，和小数位数将确保您在不做数据类型强制执行时。 使查询计划创建时的值可确保优化器可以有访问它需要的所有统计信息。 并如果探测参数 （太多缓存） 问题，不返回到每个查询破坏缓存的计划。 而是，使用查询提示或存储的过程，以确保您获得正确的计划作业。 请记住在数据访问和存储的过程代码您，应用程序程序员，写进行大的性能差异。

将您的问题和提出发送到 Bob 的 mmdbdev@microsoft.com.

Bob Beauchemin 是一种数据库为中心的应用程序 practitioner 和架构师、 课程的作者和教师、 编写器和 SQLskills 在开发人员技能伙伴。 他的编写书籍和文章 SQL Server、 数据访问和集成的技术和数据库的安全性。 您可以与他在 bobb@sqlskills.com.