我尝试使用c#从sql server数据库获取大量数据。我从数据库中得到了大约30万行的数据（我相信这离最坏的情况不远了），其中可能包含数亿条数据。
我认为问题不在于数据库的大小，因为 command.ExecuteReader(); 不需要什么也不需要一秒钟。
我试过这个代码：
public List<ResultPulser> GetReportResult(SqlConnection opCon, SqlCommand command,
int minReport,int maxReport,int machineNumber)
{
List<ResultPulser> results = new List<ResultPulser>();
using (DataContext dc = new DataContext(opCon))
{
try
{
command.CommandText = "select * from ResultPulser " +
"where CAST(SUBSTRING([ReportNumber], 0, 8) as int) = @machineNumber and " +
"CAST(SUBSTRING([ReportNumber],8,LEN([ReportNumber])) as int) BETWEEN @minReport AND @maxReport";
command.Parameters.Clear();
command.Parameters.AddWithValue("@minReport", minReport);
command.Parameters.AddWithValue("@maxReport", maxReport);
command.Parameters.AddWithValue("@machineNumber", machineNumber);
Stopwatch SW1 = Stopwatch.StartNew();
SqlDataReader reader = command.ExecuteReader();
SW1.Stop();
DataTable table = new DataTable();
Stopwatch SW2 = Stopwatch.StartNew();
table.Load(reader);
SW2.Stop();
Stopwatch SW3 = Stopwatch.StartNew();
ResultPulser[] report = new ResultPulser[table.Rows.Count];
for (int i = 0; i < table.Rows.Count; i++)
{
DataRow dr = table.Rows[i];
report[i] = new ResultPulser(Convert.ToInt64(dr[0]), dr[1].ToString().Trim(),
dr[2].ToString().Trim(), Convert.ToDateTime(dr[3]), Convert.ToDouble(dr[4]),
Convert.ToDouble(dr[5]), Convert.ToDouble(dr[6]), Convert.ToDouble(dr[7]),
Convert.ToDouble(dr[8]), Convert.ToDouble(dr[9]), Convert.ToInt64(dr[10]),
Convert.ToInt64(dr[11]), Convert.ToInt64(dr[12]), Convert.ToBoolean(dr[13]),
Convert.ToInt32(dr[14]));
}
SW3.Stop();
reader.Close();
return report.ToList();
}
catch (Exception ex)
{
LocalPulserDBManagerInstance.WriteLog(ex.StackTrace, ex.Message);
throw ex;
}
}
}但是下一行 table.Load(reader); 大约需要20秒才能完成。
我也试过这样：
public List<ResultPulser> GetReportResult(SqlConnection opCon, SqlCommand command,
int minReport,int maxReport,int machineNumber)
{
List<ResultPulser> results = new List<ResultPulser>();
using (DataContext dc = new DataContext(opCon))
{
try
{
command.CommandText = "select * from ResultPulser " +
"where CAST(SUBSTRING([ReportNumber], 0, 8) as int) = @machineNumber and " +
"CAST(SUBSTRING([ReportNumber],8,LEN([ReportNumber])) as int) BETWEEN @minReport AND @maxReport";
command.Parameters.Clear();
command.Parameters.AddWithValue("@minReport", minReport);
command.Parameters.AddWithValue("@maxReport", maxReport);
command.Parameters.AddWithValue("@machineNumber", machineNumber);
Stopwatch SW1 = Stopwatch.StartNew();
SqlDataReader reader = command.ExecuteReader();
SW1.Stop();
DataTable table = new DataTable();
Stopwatch SW2 = Stopwatch.StartNew();
while (reader.Read())
{
results.Add(new ResultPulser(reader.GetInt64(0), reader.GetString(1).Trim(), reader.GetString(2).Trim(),
reader.GetDateTime(3), reader.GetDouble(4), reader.GetDouble(5), reader.GetDouble(6),
reader.GetDouble(7), reader.GetDouble(8), reader.GetDouble(9), reader.GetInt64(10),
reader.GetInt64(11), reader.GetInt64(12), reader.GetBoolean(13), reader.GetInt32(14)));
}
SW2.Stop();
reader.Close();
return results;
}
catch (Exception ex)
{
LocalPulserDBManagerInstance.WriteLog(ex.StackTrace, ex.Message);
throw ex;
}
}
}在这种情况下，这个代码部分大约需要17-16秒。。。
while (reader.Read())
{
results.Add(new ResultPulser(reader.GetInt64(0), reader.GetString(1).Trim(), reader.GetString(2).Trim(),
reader.GetDateTime(3), reader.GetDouble(4), reader.GetDouble(5), reader.GetDouble(6),
reader.GetDouble(7), reader.GetDouble(8), reader.GetDouble(9), reader.GetInt64(10),
reader.GetInt64(11), reader.GetInt64(12), reader.GetBoolean(13), reader.GetInt32(14)));
}我怎样才能优化我的代码，使之比这更快呢？
1条答案
按热度按时间q35jwt9p1#
这就是你的问题：
通过使用这些
CAST(SUBSTRING(...))在这个（大概很大）表上强制执行完全扫描的表达式。您需要对其进行更改/优化，使其在where中包含空列名。我从 predicate 中假设reportnumber列的值的形式为mmr+where
MMMMMMMM是机器编号和R+是报表编号的一个或多个数字。我不确定reportnumber列的数据类型是什么。它可以是一个
bigint或者某种绳子。从查询来看，它似乎是一个字符串，但您也可以使用隐式转换。无论如何，有很大的潜力来优化这一点，并把它的运行时间测量在一毫秒。
我只能给你一个起点，因为没有足够的信息。
第一部分很简单：
变成
这就变成了一个前缀搜索，您可以更改c#部分，将参数作为前缀搜索传递给如下对象：
然后将其传递给查询，如下所示：
然后在sql端，您可以创建一个索引来支持此查询的快速搜索数据（但仅限于经常使用的情况—这是一个主观问题，取决于您的工作负载和需求）。记住，指数有其成本，收益必须大于成本。
假设列reportnumber是某种字符串数据类型，例如varchar或nvarchar，它就可以工作。如果它是数字，它可能工作，也可能不工作。需要修改策略。但是我没时间了，我没有足够的数据和表格信息。
编辑：如果您在一个查询中提取30万行，它可能没有帮助。sql server需要30万次查找才能为您的
SELECT *. 您可以考虑在reportnumber列上对该表进行集群（创建集群索引…）。最后警告：不要使用
SELECT *（选择星号）在生产代码中。始终明确指定所需的列。希望这对你有帮助。