为什么 SQLCLR 过程会比相同的代码客户端运行得慢 [英] why would a SQLCLR proc run slower than the same code client side

问题描述

我正在编写一个存储过程，完成后将用于逐列扫描临时表中的虚假数据.

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.

练习的第一步只是扫描表格——这就是下面的代码所做的.问题是此代码在 5:45 秒内运行 --- 但是与控制台应用程序运行相同的代码(当然更改连接字符串)在大约 44 秒内运行.

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();
        }
    }

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

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)

我在 SP 方面缺少什么，这会导致它运行如此缓慢.

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

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

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.

推荐答案

您正在编写的代码类型极易受到 SQL 注入的影响.您可以使用 RecordsAffected 属性来查找读取器中的行数，而不是像您一样处理读取器.

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.

在进行一些研究之后，您看到的区别是上下文连接和常规连接之间的设计差异.Peter Debetta 就此发表了博客并写道:

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:

"上下文连接被编写为一次只获取一行，因此对于 2000 万个奇数行中的每一行，代码单独请求每一行.然而，使用非上下文连接，它一次请求 8K 行."

"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

这篇关于为什么 SQLCLR 过程会比相同的代码客户端运行得慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！