Таким образом, я видел это сообщение здесь, и считайте его, и кажется, что массовое копирование могло бы быть способом пойти.
То, что лучший способ состоит в том, чтобы увеличить объем базы данных, вставляет от c#?
Я все еще имею некоторые вопросы и хочу знать, как на самом деле работают вещи.
Таким образом, я нашел 2 учебных руководства.
http://www.codeproject.com/KB/cs/MultipleInsertsIn1dbTrip.aspx#_Toc196622241
http://www.codeproject.com/KB/linq/BulkOperations_LinqToSQL.aspx
Первый путь использует 2 функции ado.net 2.0. BulkInsert и BulkCopy. второй использует linq для sql и OpenXML.
Этот вид обращений ко мне, поскольку я уже использую linq для sql и предпочитаю его по ado.net. Однако, поскольку один человек указал в сообщениях что он просто обхождение проблемы за счет производительности (ничто неправильно с этим, по-моему)
Сначала я буду говорить об этих 2 путях в первом учебном руководстве
Я использую VS2010 Express (для тестирования учебных руководств, я использовал VS2008 и не уверенный, какая версия .NET я просто загрузил там файлы примера и выполнил их), .net 4.0, MVC 2.0, SQL-сервер 2005
BulkInsert
Я использую эту таблицу для всех примеров.
CREATE TABLE [dbo].[TBL_TEST_TEST]
(
ID INT IDENTITY(1,1) PRIMARY KEY,
[NAME] [varchar](50)
)
Код SP
USE [Test]
GO
/****** Object: StoredProcedure [dbo].[sp_BatchInsert] Script Date: 05/19/2010 15:12:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_BatchInsert] (@Name VARCHAR(50) )
AS
BEGIN
INSERT INTO TBL_TEST_TEST VALUES (@Name);
END
Код C#
///
/// Another ado.net 2.0 way that uses a stored procedure to do a bulk insert.
/// Seems slower then "BatchBulkCopy" way and it crashes when you try to insert 500,000 records in one go.
/// http://www.codeproject.com/KB/cs/MultipleInsertsIn1dbTrip.aspx#_Toc196622241
///
private static void BatchInsert()
{
// Get the DataTable with Rows State as RowState.Added
DataTable dtInsertRows = GetDataTable();
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand("sp_BatchInsert", connection);
command.CommandType = CommandType.StoredProcedure;
command.UpdatedRowSource = UpdateRowSource.None;
// Set the Parameter with appropriate Source Column Name
command.Parameters.Add("@Name", SqlDbType.VarChar, 50, dtInsertRows.Columns[0].ColumnName);
SqlDataAdapter adpt = new SqlDataAdapter();
adpt.InsertCommand = command;
// Specify the number of records to be Inserted/Updated in one go. Default is 1.
adpt.UpdateBatchSize = 1000;
connection.Open();
int recordsInserted = adpt.Update(dtInsertRows);
connection.Close();
}
Таким образом, первой вещью является пакетный размер. Почему Вы установили бы пакетный размер на что-нибудь кроме количества записей, которые Вы отправляете? Как я отправляю 500 000 записей, таким образом, я сделал Пакетный размер 500 000.
Затем, почему это отказывает, когда я делаю это? Если я установил его на 1 000 для пакетного размера, это работает просто великолепно.
System.Data.SqlClient.SqlException was unhandled
Message="A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)"
Source=".Net SqlClient Data Provider"
ErrorCode=-2146232060
Class=20
LineNumber=0
Number=233
Server=""
State=0
StackTrace:
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
at TestIQueryable.Program.BatchInsert() in C:\Users\a\Downloads\TestIQueryable\TestIQueryable\TestIQueryable\Program.cs:line 124
at TestIQueryable.Program.Main(String[] args) in C:\Users\a\Downloads\TestIQueryable\TestIQueryable\TestIQueryable\Program.cs:line 16
InnerException:
Время, которое потребовалось для вставки 500 000 записей с размером пакета вставки 1 000, заняло "2 минуты и 54 секунды"
Конечно, это не официальное время, я сидел там с часами остановки (я уверен, что существуют лучшие пути, но было слишком лениво для взгляда что они где),
Таким образом, я нахожу такое медленное по сравнению со всеми своими другими (ожидайте, что linq к sql вставляют один), и я не действительно уверен почему.
Затем я посмотрел на массовое копирование
///
/// An ado.net 2.0 way to mass insert records. This seems to be the fastest.
/// http://www.codeproject.com/KB/cs/MultipleInsertsIn1dbTrip.aspx#_Toc196622241
///
private static void BatchBulkCopy()
{
// Get the DataTable
DataTable dtInsertRows = GetDataTable();
using (SqlBulkCopy sbc = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.KeepIdentity))
{
sbc.DestinationTableName = "TBL_TEST_TEST";
// Number of records to be processed in one go
sbc.BatchSize = 500000;
// Map the Source Column from DataTabel to the Destination Columns in SQL Server 2005 Person Table
// sbc.ColumnMappings.Add("ID", "ID");
sbc.ColumnMappings.Add("NAME", "NAME");
// Number of records after which client has to be notified about its status
sbc.NotifyAfter = dtInsertRows.Rows.Count;
// Event that gets fired when NotifyAfter number of records are processed.
sbc.SqlRowsCopied += new SqlRowsCopiedEventHandler(sbc_SqlRowsCopied);
// Finally write to server
sbc.WriteToServer(dtInsertRows);
sbc.Close();
}
}
Этот, казалось, пошел действительно быстро и даже не нуждался в SP (можно ли использовать SP с массовым копированием? Если Вы, банка была бы, это лучше?)
BatchCopy не имел никакой проблемы с 500 000 пакетных размеров. Таким образом, снова, почему делают это меньшим затем количество записей, которые Вы хотите отправить?
Я нашел, что с BatchCopy и 500 000 пакетных размеров потребовалось только 5 секунд для завершения. Я затем попробовал пакетным размером 1 000, и только потребовалось 8 секунд.
Настолько быстрее затем bulkinsert один выше.
Теперь я попробовал другое учебное руководство.
USE [Test]
GO
/****** Object: StoredProcedure [dbo].[spTEST_InsertXMLTEST_TEST] Script Date: 05/19/2010 15:39:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spTEST_InsertXMLTEST_TEST](@UpdatedProdData nText)
AS
DECLARE @hDoc int
exec sp_xml_preparedocument @hDoc OUTPUT,@UpdatedProdData
INSERT INTO TBL_TEST_TEST(NAME)
SELECT XMLProdTable.NAME
FROM OPENXML(@hDoc, 'ArrayOfTBL_TEST_TEST/TBL_TEST_TEST', 2)
WITH (
ID Int,
NAME varchar(100)
) XMLProdTable
EXEC sp_xml_removedocument @hDoc
Код C#.
///
/// This is using linq to sql to make the table objects.
/// It is then serailzed to to an xml document and sent to a stored proedure
/// that then does a bulk insert(I think with OpenXML)
/// http://www.codeproject.com/KB/linq/BulkOperations_LinqToSQL.aspx
///
private static void LinqInsertXMLBatch()
{
using (TestDataContext db = new TestDataContext())
{
TBL_TEST_TEST[] testRecords = new TBL_TEST_TEST[500000];
for (int count = 0; count < 500000; count++)
{
TBL_TEST_TEST testRecord = new TBL_TEST_TEST();
testRecord.NAME = "Name : " + count;
testRecords[count] = testRecord;
}
StringBuilder sBuilder = new StringBuilder();
System.IO.StringWriter sWriter = new System.IO.StringWriter(sBuilder);
XmlSerializer serializer = new XmlSerializer(typeof(TBL_TEST_TEST[]));
serializer.Serialize(sWriter, testRecords);
db.insertTestData(sBuilder.ToString());
}
}
Таким образом, мне нравится это, потому что я добираюсь для использования объектов даже при том, что это довольно избыточно. Я не добираюсь, как SP работает. Как я не получаю все это. Я не знаю, имеет ли OPENXML некоторую пакетную вставку под капотом, но я даже не знаю, как взять этот пример SP и изменить его для установки моим таблицам с тех пор как, я сказал, что не знаю то, что продолжается.
Я также не знаю то, что произошло бы, если объект у Вас есть больше таблиц в нем. Как говорят, что у меня есть таблица ProductName, что имеет отношения к Таблице product или чему-то как этот.
В linq к sql Вы могли получить объект названия продукта и внести изменения в Таблицу product в том же самом объекте. Таким образом, я не уверен, как принять это во внимание. Я не уверен, должен ли я был сделать отдельные вставки или что.
Время было довольно хорошо для 500 000 записей, потребовалось 52 секунды
Последний путь, конечно, просто использовал linq, чтобы сделать все это, и это было довольно плохо.
///
/// This is using linq to sql to to insert lots of records.
/// This way is slow as it uses no mass insert.
/// Only tried to insert 50,000 records as I did not want to sit around till it did 500,000 records.
/// http://www.codeproject.com/KB/linq/BulkOperations_LinqToSQL.aspx
///
private static void LinqInsertAll()
{
using (TestDataContext db = new TestDataContext())
{
db.CommandTimeout = 600;
for (int count = 0; count < 50000; count++)
{
TBL_TEST_TEST testRecord = new TBL_TEST_TEST();
testRecord.NAME = "Name : " + count;
db.TBL_TEST_TESTs.InsertOnSubmit(testRecord);
}
db.SubmitChanges();
}
}
Я сделал только 50 000 записей, и это приняло минуту, чтобы сделать.
Таким образом, я действительно сузил сделанный к linq к sql объемной вставке путь или массовое копирование. Я просто не уверен, как сделать это, когда у Вас есть отношения для так или иначе. Я не уверен, как они оба встают при выполнении обновлений вместо вставок, поскольку я не двигался для попытки его все же.
Я не думаю, что должен буду когда-либо вставлять/обновлять больше чем 50 000 записей в одном типе, но в то же время я знаю, что должен буду сделать проверку на записях прежде, чем вставить так, чтобы замедлился, это и такой делает linq к sql более хорошим как Ваши полученные объекты особенно, если Ваши первые данные парсинга из XML-файла перед вставкой в базу данных.
Полный код C#
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Serialization;
using System.Data;
using System.Data.SqlClient;
namespace TestIQueryable
{
class Program
{
private static string connectionString = "";
static void Main(string[] args)
{
BatchInsert();
Console.WriteLine("done");
}
///
/// This is using linq to sql to to insert lots of records.
/// This way is slow as it uses no mass insert.
/// Only tried to insert 50,000 records as I did not want to sit around till it did 500,000 records.
/// http://www.codeproject.com/KB/linq/BulkOperations_LinqToSQL.aspx
///
private static void LinqInsertAll()
{
using (TestDataContext db = new TestDataContext())
{
db.CommandTimeout = 600;
for (int count = 0; count < 50000; count++)
{
TBL_TEST_TEST testRecord = new TBL_TEST_TEST();
testRecord.NAME = "Name : " + count;
db.TBL_TEST_TESTs.InsertOnSubmit(testRecord);
}
db.SubmitChanges();
}
}
///
/// This is using linq to sql to make the table objects.
/// It is then serailzed to to an xml document and sent to a stored proedure
/// that then does a bulk insert(I think with OpenXML)
/// http://www.codeproject.com/KB/linq/BulkOperations_LinqToSQL.aspx
///
private static void LinqInsertXMLBatch()
{
using (TestDataContext db = new TestDataContext())
{
TBL_TEST_TEST[] testRecords = new TBL_TEST_TEST[500000];
for (int count = 0; count < 500000; count++)
{
TBL_TEST_TEST testRecord = new TBL_TEST_TEST();
testRecord.NAME = "Name : " + count;
testRecords[count] = testRecord;
}
StringBuilder sBuilder = new StringBuilder();
System.IO.StringWriter sWriter = new System.IO.StringWriter(sBuilder);
XmlSerializer serializer = new XmlSerializer(typeof(TBL_TEST_TEST[]));
serializer.Serialize(sWriter, testRecords);
db.insertTestData(sBuilder.ToString());
}
}
///
/// An ado.net 2.0 way to mass insert records. This seems to be the fastest.
/// http://www.codeproject.com/KB/cs/MultipleInsertsIn1dbTrip.aspx#_Toc196622241
///
private static void BatchBulkCopy()
{
// Get the DataTable
DataTable dtInsertRows = GetDataTable();
using (SqlBulkCopy sbc = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.KeepIdentity))
{
sbc.DestinationTableName = "TBL_TEST_TEST";
// Number of records to be processed in one go
sbc.BatchSize = 500000;
// Map the Source Column from DataTabel to the Destination Columns in SQL Server 2005 Person Table
// sbc.ColumnMappings.Add("ID", "ID");
sbc.ColumnMappings.Add("NAME", "NAME");
// Number of records after which client has to be notified about its status
sbc.NotifyAfter = dtInsertRows.Rows.Count;
// Event that gets fired when NotifyAfter number of records are processed.
sbc.SqlRowsCopied += new SqlRowsCopiedEventHandler(sbc_SqlRowsCopied);
// Finally write to server
sbc.WriteToServer(dtInsertRows);
sbc.Close();
}
}
///
/// Another ado.net 2.0 way that uses a stored procedure to do a bulk insert.
/// Seems slower then "BatchBulkCopy" way and it crashes when you try to insert 500,000 records in one go.
/// http://www.codeproject.com/KB/cs/MultipleInsertsIn1dbTrip.aspx#_Toc196622241
///
private static void BatchInsert()
{
// Get the DataTable with Rows State as RowState.Added
DataTable dtInsertRows = GetDataTable();
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand("sp_BatchInsert", connection);
command.CommandType = CommandType.StoredProcedure;
command.UpdatedRowSource = UpdateRowSource.None;
// Set the Parameter with appropriate Source Column Name
command.Parameters.Add("@Name", SqlDbType.VarChar, 50, dtInsertRows.Columns[0].ColumnName);
SqlDataAdapter adpt = new SqlDataAdapter();
adpt.InsertCommand = command;
// Specify the number of records to be Inserted/Updated in one go. Default is 1.
adpt.UpdateBatchSize = 500000;
connection.Open();
int recordsInserted = adpt.Update(dtInsertRows);
connection.Close();
}
private static DataTable GetDataTable()
{
// You First need a DataTable and have all the insert values in it
DataTable dtInsertRows = new DataTable();
dtInsertRows.Columns.Add("NAME");
for (int i = 0; i < 500000; i++)
{
DataRow drInsertRow = dtInsertRows.NewRow();
string name = "Name : " + i;
drInsertRow["NAME"] = name;
dtInsertRows.Rows.Add(drInsertRow);
}
return dtInsertRows;
}
static void sbc_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
{
Console.WriteLine("Number of records affected : " + e.RowsCopied.ToString());
}
}
}
Размер партии используется для уменьшения влияния сетевой задержки. Он не должен быть больше нескольких тысяч. Несколько выписок собираются вместе и отправляются как единое целое, поэтому вы получаете одно сетевое путешествие раз в N выписок, а не раз на выписку.
Это разовое массовое копирование или обычное дело?
Если это разовое копирование или, например, только раз в день, используйте BCP, это намного быстрее, так как в нем используются специальные API, которые быстрее, чем ado.net.