Можно вставить с параметром таблицы, а также получить значения идентичности?

Я пытаюсь вставить записи, используя высокопроизводительный метод таблицы параметров ( http://www.altdevblogaday.com/2012/05/16/sql-server-high-performance-inserts/), и мне интересно, если это можно получить обратно значения идентичности для каждой записи, которую я вставляю.

На данный момент ответом будет "нет" - я вставляю данные, затем извлекаю значения идентификаторов, и они не совпадают. В частности, они не соответствуют примерно 75% времени, и они не совпадают непредсказуемым образом. Вот некоторый код, который повторяет эту проблему:

// Create a datatable with 100k rows
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("item_id", typeof(int)));
dt.Columns.Add(new DataColumn("comment", typeof(string)));
for (int i = 0; i < 100000; i++) {
    dt.Rows.Add(new object[] { 0, i.ToString() });
}

// Insert these records and retrieve back the identity
using (SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=testdb;Integrated Security=True")) {
    conn.Open();
    using (SqlCommand cmd = new SqlCommand("proc_bulk_insert_test", conn)) {
        cmd.CommandType = CommandType.StoredProcedure;

        // Adding a "structured" parameter allows you to insert tons of data with low overhead
        SqlParameter param = new SqlParameter("@mytable", SqlDbType.Structured);
        param.Value = dt;
        cmd.Parameters.Add(param);
        SqlDataReader dr = cmd.ExecuteReader();

        // Set all the records' identity values
        int i = 0;
        while (dr.Read()) {
            dt.Rows[i].ItemArray = new object[] { dr.GetInt32(0), dt.Rows[i].ItemArray[1] };
            i++;
        }
        dr.Close();
    }

    // Do all the records' ID numbers match what I received back from the database?
    using (SqlCommand cmd = new SqlCommand("SELECT * FROM bulk_insert_test WHERE item_id >= @base_identity ORDER BY item_id ASC", conn)) {
        cmd.Parameters.AddWithValue("@base_identity", (int)dt.Rows[0].ItemArray[0]);
        SqlDataReader dr = cmd.ExecuteReader();
        DataTable dtresult = new DataTable();
        dtresult.Load(dr);
    }
}

База данных определяется с помощью этого сценария сервера SQL:

CREATE TABLE bulk_insert_test (
    item_id int IDENTITY (1, 1) NOT NULL PRIMARY KEY,
    comment varchar(20)
)
GO

CREATE TYPE bulk_insert_table_type AS TABLE ( item_id int, comment varchar(20) )
GO

CREATE PROCEDURE proc_bulk_insert_test
    @mytable bulk_insert_table_type READONLY
AS

DECLARE @TableOfIdentities TABLE (IdentValue INT)

INSERT INTO bulk_insert_test (comment)
OUTPUT Inserted.item_id INTO @TableOfIdentities(IdentValue)
SELECT comment FROM @mytable

SELECT * FROM @TableOfIdentities

Вот проблема: значения, возвращаемые из proc_bulk_insert_test не в том порядке, в котором были вставлены исходные записи. Поэтому я не могу программно назначить каждой записи item_id значение, которое я получил от OUTPUT заявление.

Кажется, единственное правильное решение SELECT назад весь список записей, которые я только что вставил, но, честно говоря, я бы предпочел любое решение, которое уменьшило бы объем данных, передаваемых по сетевой карте моего SQL Server. У кого-нибудь есть лучшие решения для больших вставок, в то же время получая значения идентификаторов?

РЕДАКТИРОВАТЬ: Позвольте мне попытаться прояснить вопрос немного больше. Проблема в том, что я хотел бы, чтобы моя программа на C# узнала, какие значения идентичности SQL Server присвоены только что вставленным данным. Порядок не важен; но я хотел бы иметь возможность взять произвольный набор записей в C#, вставить их, используя метод параметров быстрой таблицы, а затем назначить их автоматически сгенерированные идентификационные номера в C#, не запрашивая всю таблицу обратно в память.

Учитывая, что это искусственный набор тестов, я попытался сжать его до как можно меньшего размера для читаемого кода. Позвольте мне описать, какие методы я использовал для решения этой проблемы:

  1. В моем исходном коде в приложении, из которого получен этот пример, я вставлял около 15 миллионов строк, используя 15 миллионов отдельных операторов вставки, получая обратно значение идентификатора после каждой вставки. Это работало, но было медленно.
  2. Я пересмотрел код, используя параметры таблицы высокой производительности для вставки. Затем я бы избавился от всех объектов в C# и прочитал из базы данных все объекты. Однако исходные записи содержали десятки столбцов с множеством значений varchar и десятичных чисел, поэтому этот метод требовал значительного сетевого трафика, хотя он был быстрым и работал.
  3. Теперь я начал исследование, чтобы выяснить, возможно ли использовать вставку параметра таблицы, и в то же время просить SQL Server просто сообщить значения идентификаторов. Я старался scope_identity() а также OUTPUT но до сих пор не были успешными.

По сути, эта проблема была бы решена, если бы SQL Server всегда вставлял записи именно в том порядке, в котором я их предоставил. Можно ли сделать так, чтобы записи SQL-сервера вставлялись именно в том порядке, в котором они указаны в параметре вставки табличного значения?

РЕДАКТИРОВАТЬ 2: Этот подход кажется очень похожим на то, что Cade Roux цитирует ниже:

http://www.sqlteam.com/article/using-the-output-clause-to-capture-identity-values-on-multi-row-inserts

Однако в статье автор использует магическое уникальное значение "ProductNumber", чтобы соединить вставленную информацию из "выходного" значения с исходным параметром табличного значения. Я пытаюсь понять, как это сделать, если у моей таблицы нет волшебного уникального значения.

Источник

2 ответа

Решение

Ваш TVP - неупорядоченный набор, как обычный стол. Он имеет порядок, только когда вы указываете как таковой. Мало того, что у вас нет никакого способа указать фактический порядок здесь, вы также просто делаете SELECT * в конце без ORDER BY. Какой порядок вы ожидаете здесь? Вы фактически сказали SQL Server, что вам все равно. Тем не менее, я реализовал ваш код и без проблем вернул строки в правильном порядке. Я немного изменил процедуру, чтобы вы могли определить, какое значение идентификатора принадлежит какому комментарию:

DECLARE @TableOfIdentities TABLE (IdentValue INT, comment varchar(20))

INSERT INTO bulk_insert_test (comment)
OUTPUT Inserted.item_id, Inserted.comment 
INTO @TableOfIdentities(IdentValue, comment)
SELECT comment FROM @mytable

SELECT * FROM @TableOfIdentities

Затем я назвал его с помощью этого кода (для этого нам не нужен весь C#):

DECLARE @t bulk_insert_table_type;
INSERT @t VALUES(5,'foo'),(2,'bar'),(3,'zzz');
SELECT * FROM @t;

EXEC dbo.proc_bulk_insert_test @t;

Результаты:

1   foo
2   bar
3   zzz

Если вы хотите убедиться, что выходные данные имеют порядок присвоения идентификаторов (что не обязательно соответствует "порядку", который имеет ваш неупорядоченный TVP), вы можете добавить ORDER BY item_id до последнего выберите в вашей процедуре.

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

  • добавьте столбец к вашему TVP и вставьте порядок в этот столбец, затем используйте курсор для перебора строк в этом порядке и вставляйте по одной за раз. ИМХО, все же более эффективно, чем вызов всей процедуры для каждой строки.

  • добавьте столбец к вашему TVP, который указывает порядок, и используйте ORDER BY на вставке. Это не гарантируется, но относительно надежно, особенно если вы устраняете проблемы параллелизма с помощью MAXDOP 1.

В любом случае, вы, кажется, уделяете много внимания ORDER. Что на самом деле означает ваш заказ? Если вы хотите придать смысл определенному порядку, вам не следует делать это с помощью столбца IDENTITY.

Источник

Вы указываете нет ORDER BY на этом: SELECT * FROM @TableOfIdentities так что нет гарантии заказа. Если вы хотите, чтобы они были в том же порядке, в котором они были отправлены, выполните INNER JOIN в том, что к данным, которые были вставлены с ORDER BY соответствует порядку, в котором были отправлены строки.

Источник
Другие вопросы по тегам