sql-server - 如何提高 ADO 查找速度?
问题描述
我通过 Visual Studio 2008 + ADO(不是 ADO.net)编写了一个 C++ 应用程序。它将一一完成以下任务:
- 在 SQL Server 数据库中创建表,如下:
CREATE TABLE MyTable
(
[S] bigint,
[L] bigint,
[T] tinyint,
[I1] int,
[I2] smallint,
[P] bigint,
[PP] bigint,
[NP] bigint,
[D] bit,
[U] bit
);
通过插入 5,030,242 条记录
BULK INSERT
在表上创建索引:
CREATE Index [MyIndex] ON MyTable ([P]);
- 启动一个将查找 65,000,000 次的函数。每次查找使用以下查询:
SELECT [S], [L]
FROM MyTable
WHERE [P] = ?
每次查询要么不返回任何内容,要么返回一行。如果用 [S] 和 [L] 获取一行,我会将 [S] 转换为文件指针,然后从 [L] 指定的偏移量读取数据。
第 4 步需要很多时间。所以我尝试分析它并找出查找查询花费的时间最多。每次查找大约需要 0.01458 秒。
我尝试通过执行以下任务来提高性能:
使用参数化 ADO 查询。见步骤 4
仅选择所需的列。最初我在第 4 步中使用“Select *”,现在我
Select [S], [L]
改为使用。这将性能提高了约 1.5%。尝试了 [P] 的聚集索引和非聚集索引。看来使用非聚集索引会好一些。
还有其他空间可以提高查找性能吗?
注:[P]
在表中是唯一的。
非常感谢。
解决方案
您需要批处理工作并执行一个返回多行的查询,而不是多个查询,每个查询只返回一行(并导致单独的数据库往返)。
在 SQL Server 中执行此操作的方法是重写查询以使用表值参数 (TVP),并一次性将所有搜索条件(如?
您的问题中所示)一起传递。
首先,我们需要声明 TVP 将使用的类型:
CREATE TYPE MyTableSearch AS TABLE (
P bigint NOT NULL
);
然后新的查询将非常简单:
SELECT
S,
L
FROM
@input I
JOIN MyTable
ON I.P = MyTable.P;
主要的复杂性在于客户端,如何将 TVP 绑定到查询。不幸的是,我对 ADO 并不熟悉——就其价值而言,这就是它在 ADO.NET 和 C# 下的实现方式:
static IEnumerable<(long S, long L)> Find(
SqlConnection conn,
SqlTransaction tran,
IEnumerable<long> input
) {
const string sql = @"
SELECT
S,
L
FROM
@input I
JOIN MyTable
ON I.P = MyTable.P
";
using (var cmd = new SqlCommand(sql, conn, tran)) {
var record = new SqlDataRecord(new SqlMetaData("P", SqlDbType.BigInt));
var param = new SqlParameter("input", SqlDbType.Structured) {
Direction = ParameterDirection.Input,
TypeName = "MyTableSearch",
Value = input.Select(
p => {
record.SetValue(0, p);
return record;
}
)
};
cmd.Parameters.Add(param);
using (var reader = cmd.ExecuteReader())
while (reader.Read())
yield return (reader.GetInt64(0), reader.GetInt64(1));
}
}
请注意,我们SqlDataRecord
对所有输入行重复使用相同的内容,从而最大限度地减少分配。这是记录在案的行为,它之所以有效,是因为 ADO.NET 流式传输 TVP。
注:[P]在表中是唯一的。
然后你应该使 P 上的索引也唯一 - 为了正确性并避免在唯一性上浪费空间。
推荐阅读
- python - 如何在循环中给出的最后一个值中添加一个确切的数量?
- html - 使用 Python 和 Selenium,您将如何创建处理下拉框的方法
- node.js - 棱镜:类型“字符串”不可分配给类型“从不”
- ios - 单击导航栏外部垂直对齐的栏按钮时出现问题
- facebook - 即使 chrome 的网络限制设置为离线,Facebook Messenger 似乎也能正常工作——这是如何工作的?
- python - Python:如何使用此代码片段将文本附加到下一行?
- c# - 如何在静默安装中不显示自定义错误消息?
- javascript - Discord.js:返回用户当前的 voice.channelId 已过时
- c++ - 通过控制台循环运行程序以获取不同的参数
- codenameone - IOS上的对话框处置行为,重新进入第二次以上时锁定