为什么SQLCLR proc会比同一个代码客户端运行慢

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.

练习中的第一步只是扫描表格 - 这是下面的代码所做的。问题是这段代码在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();
        }
    }

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)

然而,相同的代码(当然,不同的连接字符串)在大约44秒内在控制台应用程序中运行(这比我在客户端期望的更接近)

What am I missing on the SP side, that would cause it to run so slow.

我在SP方面缺少什么,这会导致它运行得如此之慢。

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.

请注意:我完全理解如果我想要行数,我应该使用count(*)聚合---这不是本练习的目的。

2 个解决方案

#1

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.

您编写的代码类型非常容易受到SQL注入的影响。您可以使用RecordsAffected属性来查找阅读器中的行数,而不是像您一样处理阅读器。

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:

在做了一些研究之后,您看到的差异是上下文连接和常规连接之间的设计差异。彼得·德贝塔(Peter Debetta)在此博文中写道:

"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."

“编写上下文连接使得它一次只获取一行,因此对于2000万个奇数行中的每一行,代码分别要求每一行。但是,使用非上下文连接,它要求8K值一次排成行。“

http://sqlblog.com/blogs/peter_debetta/archive/2006/07/21/context-connection-is-slow.aspx

#2

Well it would seem the answer is in the connection string after all.

好吧,似乎答案就在连接字符串中。

context connection=true

versus

server=(local); database=foo; integrated security=true

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)

对于一些奇怪的原因,使用“外部”连接,SP的运行速度几乎与控制台应用程序一样快(仍然没有那么快意思! - 55秒)

Of course now the assembly has to be deployed as External rather than Safe --- and that introduces more frustration.

当然,现在必须将组件部署为外部而不是安全 - 这会带来更多的挫败感。

#1