При выполнении хранимой процедуры, в чем преимущество использования CommandType.StoredProcedure в сравнении с использованием CommandType.Text?

Ответ 1

В соответствии с тестами в этот пост в блоге SQL Server выполнит параметризацию для вас, обернув свой оператор в sp_executesql, когда вы используете CommandType.Text. Но когда вы используете CommandType.StoredProcedure, вы будете параметризовать его и тем самым сохранить базу данных. Последний метод выполняется быстрее.

Edit:

Настройка

Я сам сделал несколько тестов, и вот результаты.

Создайте эту процедуру:

create procedure dbo.Test
(
   @Text1 varchar(10) = 'Default1'
  ,@Text2 varchar(10) = 'Default2'
)
as
begin
   select @Text1 as Text1, @Text2 as Text2
end

Добавьте трассировку к ней с помощью SQL Server Profiler.

И затем вызовите его, используя следующий код:

using System;
using System.Data;
using System.Data.SqlClient;

namespace ConsoleApplication2
{
    class Program
    {
        static void Main()
        {
            CallProcedure( CommandType.Text );
            CallProcedure( CommandType.StoredProcedure );
        }

        private static void CallProcedure(CommandType commandType)
        {
            using ( SqlConnection connection = new SqlConnection("Data Source=localhost;Initial Catalog=Test;Integrated Security=SSPI;") )
            {
                connection.Open();
                using ( SqlCommand textCommand = new SqlCommand("dbo.Test", connection) )
                {
                    textCommand.CommandType = commandType;
                    textCommand.Parameters.AddWithValue("@Text1", "Text1");
                    textCommand.Parameters.AddWithValue("@Text2", "Text2");
                    using ( IDataReader reader = textCommand.ExecuteReader() )
                    {
                        while ( reader.Read() )
                        {
                            Console.WriteLine(reader["Text1"] + " " + reader["Text2"]);
                        }
                    }
                }
            }
        }
    }
}

Результаты

В обоих случаях вызовы выполняются с использованием RPC.

Вот что показывает трассировка с помощью CommandType.Text:

exec sp_executesql N'dbo.Test',N'@Text1 nvarchar(5),@Text2 nvarchar(5)',@Text1=N'Text1',@Text2=N'Text2'

И вот результат: CommandType.StoredProcedure:

exec dbo.Test @Text1=N'Text1',@Text2=N'Text2'

Как вы можете видеть, текстовый вызов завернут в вызов sp_executesql, чтобы он был правильно параметризован. Это, конечно, создаст небольшие накладные расходы, и, таким образом, мое предыдущее утверждение о том, что использование CommandType.StoredProcedure быстрее, остается.

Еще одна заслуживающая внимания вещь, которая также является видом прерывателя сделки, заключается в том, что когда я создал процедуру без значений по умолчанию, я получил следующую ошибку:

Msg 201, уровень 16, состояние 4, проверка процедуры, строка 0 Процедура или функция 'Test' ожидает параметр '@Text1', который не был предоставлен.

Причиной этого является то, как создается вызов sp_executesql, так как вы можете видеть, что параметры объявлены и инициализированы, но они не используются. Для вызова работы он должен выглядеть следующим образом:

exec sp_executesql N'dbo.Test @Text1, @Text2',N'@Text1 nvarchar(5),@Text2 nvarchar(5)',@Text1=N'Text1',@Text2=N'Text2'

Значение, когда вы используете CommandType.Text, вам нужно добавить параметры в CommandText, если вы не хотите, чтобы значения по умолчанию использовались.

Итак, чтобы ответить на ваш вопрос

• Использование CommandType.StoredProcedure выполняется быстрее.
• Если вы используете CommandType.Text, вам придется добавить имена параметров к вызову процедуры, если вы не хотите использовать значения по умолчанию.

Ответ 2

Вы должны установить это, чтобы ADO.NET помог вам. Когда вы используете CommandType.StoredProcedure, вам нужно просто поставить CommandText равным имени хранимой процедуры.

Например, это:

YourSqlCommand.CommandType = CommandType.StoredProcedure;
YourSqlCommand.CommandText = "dbo.YourStoredProc";

Является эквивалентным:

YourSqlCommand.CommandText = "exec dbo.YourStoredProc";

Ответ 3

На самом деле существует огромная разница. Если вы укажете тип команды StoredProcedure, то любой параметр, который вы добавите в свой SqlCommand, будет параметром, добавленным вызовом процедуры. Если вы оставите его как Text, тогда параметры будут добавлены в пакет, а не в процедуру. Чтобы проиллюстрировать эту точку, создайте фиктивную процедуру:

create procedure usp_test 
    @p1 char(10)  = 'foo',
    @p2 int = 42
as
    select @p1, @p2;    
go

Затем скомпилируйте это крошечное приложение С#:

   static void Main(string[] args)
    {
        ExecWithType(CommandType.Text);
        ExecWithType(CommandType.StoredProcedure);
    }

    static void ExecWithType(CommandType type)
    {
        using (SqlConnection conn = new SqlConnection(Settings.Default.connString))
        {
            conn.Open();
            using (SqlCommand cmd1 = new SqlCommand("usp_test", conn))
            {
                cmd1.CommandType = type;
                cmd1.Parameters.AddWithValue("@p1", "bar");
                cmd1.Parameters.AddWithValue("@p2", 24);
                using (SqlDataReader rdr = cmd1.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        Console.WriteLine("Type: {0} Result: @p1: {1} @p2: {2}", type, rdr[0], rdr[1]);
                    }
                }
            }
        }
    }

Результат:

Type: Text Result: @p1: foo        @p2: 42
Type: StoredProcedure Result: @p1: bar        @p2: 24

Ouch! Для параметра CommandType.Text, хотя параметры были переданы в пакет, они не были переданы процедуре. Источник многих часов отладки...

Ответ 4

CommandType не относится к SQL Server. Это свойство IDbCommand интерфейса, который инструктирует базового провайдера обрабатывать CommandText определенным образом. Хотя SQL Server может рассматривать имена с одним словом в качестве процедур, вы не должны ожидать, что это будет работать у других поставщиков.

В общем, вы должны предпочесть использовать классы, созданные поставщиком, такие как DbCommand, вместо определенных классов, таких как SqlCommand. Таким образом, вы можете настроить таргетинг на разные базы данных, просто изменив строку поставщика в вашем файле конфигурации.