为什么 SQLCLR 过程比相同代码的客户端运行得慢
我正在编写一个存储过程，完成后将用于逐列扫描暂存表中的虚假数据。
练习的第一步只是扫描表格——这就是下面的代码的作用。 问题是该代码运行时间为 5 分 45 秒——然而，作为控制台应用程序运行的相同代码（当然更改连接字符串）运行时间约为 44 秒。
using (SqlConnection sqlConnection = new SqlConnection("context connection=true"))
{
sqlConnection.Open();
string sqlText = string.Format("select * from {0}", source_table.Value);
int count = 0;
using (SqlCommand sqlCommand = new SqlCommand(sqlText, sqlConnection))
{
SqlDataReader reader = sqlCommand.ExecuteReader();
while (reader.Read())
count++;
SqlDataRecord record = new SqlDataRecord(new SqlMetaData("rowcount", SqlDbType.Int));
SqlContext.Pipe.SendResultsStart(record);
record.SetInt32(0, count);
SqlContext.Pipe.SendResultsRow(record);
SqlContext.Pipe.SendResultsEnd();
}
}
然而，相同的代码（当然不同的连接字符串）在控制台应用程序中运行大约 44 秒（这更接近我在客户端的预期）
我在 SP 端缺少什么，这会导致它运行如此慢的。
请注意：我完全理解，如果我想要行数，我应该使用 count(*) 聚合——这不是本练习的目的。
I am writing a stored procedure that when completed will be used to scan staging tables for bogus data on a column by column basis.
Step one in the exercise was just to scan the table --- which is what the code below does. The issue is that this code runs in 5:45 seconds --- however the same code run as a console app (changing the connectionstring of course) runs in about 44 seconds.
using (SqlConnection sqlConnection = new SqlConnection("context connection=true"))
{
sqlConnection.Open();
string sqlText = string.Format("select * from {0}", source_table.Value);
int count = 0;
using (SqlCommand sqlCommand = new SqlCommand(sqlText, sqlConnection))
{
SqlDataReader reader = sqlCommand.ExecuteReader();
while (reader.Read())
count++;
SqlDataRecord record = new SqlDataRecord(new SqlMetaData("rowcount", SqlDbType.Int));
SqlContext.Pipe.SendResultsStart(record);
record.SetInt32(0, count);
SqlContext.Pipe.SendResultsRow(record);
SqlContext.Pipe.SendResultsEnd();
}
}
However the same code (different connection string of course) runs in a console app in about 44 seconds (which is closer to what I was expecting on the client side)
What am I missing on the SP side, that would cause it to run so slow.
Please note: I fully understand that if I wanted a count of rows, I should use the count(*) aggregation --- that's not the purpose of this exercise.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您正在编写的代码类型非常容易受到 SQL 注入的影响。 您可以使用 RecordsAffected 属性来查找读取器中的行数，而不是像您一样处理读取器。
编辑：
经过一些研究后，您看到的差异是上下文连接和常规连接之间的设计差异。 Peter Debetta 在博客中谈到了这一点，并写道：
“上下文连接的编写方式是一次仅获取一行，因此对于 2000 万个奇数行中的每一行，代码分别请求每一行。使用非上下文但是，连接一次会请求 8K 行。”
http://sqlblog.com/博客/peter_debetta/archive/2006/07/21/context-connection-is-slow.aspx
The type of code you are writing is highly susceptible to SQL Injection. Rather than processing the reader like you are, you could just use the RecordsAffected Property to find the number of rows in the reader.
EDIT:
After doing some research, the difference you are seeing is a by design difference between the context connection and a regular connection. Peter Debetta blogged about this and writes:
"The context connection is written such that it only fetches a row at a time, so for each of the 20 million some odd rows, the code was asking for each row individually. Using a non-context connection, however, it requests 8K worth of rows at a time."
http://sqlblog.com/blogs/peter_debetta/archive/2006/07/21/context-connection-is-slow.aspx
看来答案毕竟在连接字符串中。
由于
某些奇怪的原因，使用“外部”连接，SP 的运行速度几乎与控制台应用程序一样快（请注意，仍然没有那么快！ - 55 秒）
当然，现在程序集必须部署为外部而不是安全 - ——这会带来更多的挫败感。
Well it would seem the answer is in the connection string after all.
versus
For some bizzare reason, using the "external" connection the SP runs almost as fast as a console app (still not as fast mind you! -- 55 seconds)
Of course now the assembly has to be deployed as External rather than Safe --- and that introduces more frustration.