Masseneinfügung von beste Weg, um über Sie? + Helfen Sie mir zu verstehen vollkommen, was ich bisher gefunden
So sah ich diese hier posten und Lesen, und es scheint, wie bulk copy könnte der Weg zu gehen.
Was ist der beste Weg, um bulk-Datenbank-inserts, die aus c#?
Ich habe noch einige Fragen und möchten wissen, wie die Dinge wirklich funktionieren.
So fand ich 2 tutorials.
http://www.codeproject.com/KB/cs/MultipleInsertsIn1dbTrip.aspx#_Toc196622241
http://www.codeproject.com/KB/linq/BulkOperations_LinqToSQL.aspx
Ersten Weise verwendet, 2 ado.net 2.0-features. BulkInsert und BulkCopy. der zweite verwendet linq to sql und OpenXML.
Diese Art von Appellen an mich, wie ich mit linq to sql schon und ziehe es über ado.net. Aber als einer person darauf hingewiesen, in den Pfosten, was er nur gehen, um das Problem auf Kosten der Leistung gehen( nichts falsch mit, dass in meiner Meinung nach)
Zuerst werde ich sprechen über die 2 Möglichkeiten in der ersten tutorial -
Bin ich mit VS2010 Express(für die Prüfung des tutorials die ich verwendet, VS2008 und nicht sicher, was .net-version, die ich gerade geladen es Beispieldateien und lief).net 4.0, MVC 2.0, SQl Server 2005
- Ist ado.net 2.0 die aktuelle version?
- Basiert auf der Technologie, die ich verwende, gibt es einige updates zu dem, was ich werde zeigen, dass zu verbessern wäre es irgendwie?
- Gibt es eine Sache, dass diese tutorial-Links, die ich wissen sollte?
BulkInsert
Ich bin mit diesem Tisch für alle Beispiele.
CREATE TABLE [dbo].[TBL_TEST_TEST]
(
ID INT IDENTITY(1,1) PRIMARY KEY,
[NAME] [varchar](50)
)
SP-Code
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# - Code
///<summary>
///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
///</summary>
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();
}
Also als erstes ist die batch-Größe. Warum würden Sie eine batch-Größe für alles, aber die Anzahl der Datensätze, die Sie versenden? Wie sende ich die 500.000 Datensätze, so ich habe eine Batch-Größe von 500.000.
Weiter, warum es abstürzt, wenn ich dies tun? Wenn ich es auf 1000 für batch-Größe funktioniert es Prima.
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:
Zeit, die zum einfügen von 500.000 Datensätze mit insert batch-Größe von 1000 nahm "2 Minuten und 54 Sekunden"
Dies ist natürlich keine offizielle Zeit, ich saß da mit einer stop Uhr( ich bin sicher, es gibt bessere Möglichkeiten, war aber zu faul, um zu suchen, was Sie wo)
Also ich finde, dass ein bisschen langsam im Vergleich zu allen meinen anderen(erwarten, dass die linq to sql-insert-one) und ich bin nicht wirklich sicher, warum.
Weiter sah ich bulkcopy
///<summary>
///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
///</summary>
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();
}
}
Dieser schien schnell geht und noch nicht einmal einen SP( kann man SP mit bulk-kopieren? Wenn du kannst wäre es besser?)
BatchCopy hatte kein problem mit einer batch-Größe von 500.000.Also nochmal, warum machen Sie es kleiner, dann die Anzahl der Datensätze, die Sie wollen, zu senden?
Fand ich, dass mit BatchCopy und 500.000 batch-Größe dauerte es nur 5 Sekunden zu vervollständigen. Ich habe dann versucht mit einer batch-Größe von 1.000 und es dauerte nur 8 Sekunden.
So viel schneller als die bulkinsert oben.
Nun habe ich versucht die andere tutorial.
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# - code.
///<summary>
///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
///</summary>
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());
}
}
So mag ich das, weil ich bekommen, um Objekte zu nutzen, obwohl es ist ein bisschen redundant. Ich weiss nicht, wie die SP funktioniert. Wie ich nicht bekommen, die ganze Sache. Ich weiß nicht, ob OPENXML hat einige batch-insert unter der Haube, aber ich weiß gar nicht, wie zum Beispiel SP und ändern Sie es zu passen, meine Tabellen, da, wie ich sagte, ich weiß nicht, was Los ist.
Ich weiß auch nicht, was passieren würde, wenn das Objekt, das Sie haben mehrere Tabellen. Wie sagen, dass ich eine ProductName Tabelle, welche eine Beziehung hat zu einer Produkt-Tabelle oder sowas.
In linq to sql können Sie die Produkt-name Objekt-und änderungen an die Produkt-Tabelle in das gleiche Objekt. Also ich bin nicht sicher, wie das zu berücksichtigen. Ich bin nicht sicher, ob ich es tun müsste, wäre, separate Einsätze oder was.
Die Zeit war ziemlich gut für 500.000 Datensätze dauerte es 52 Sekunden
Die Letzte Möglichkeit war natürlich nur mit linq zu tun es alle und es war ziemlich schlecht.
///<summary>
///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
///</summary>
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();
}
}
Ich habe nur die 50.000 Datensätze und das dauerte über eine minute zu tun.
Damit ich auch wirklich verengt es getan, um die linq to sql bulk insert-Weg oder bulk copy. Ich bin mir nur nicht sicher, wie Sie es tun, wenn Sie die Beziehung so oder so. Ich bin mir nicht sicher, wie Sie beide aufstehen, wenn updates statt-Einsätze habe ich nicht bekommen, um zu versuchen es noch.
Ich glaube nicht, dass ich jemals brauchen werden, um insert - /update-mehr als 50.000 Datensätze auf eine Art, aber zur gleichen Zeit ich weiß, ich werde zu tun haben, die Validierung auf Einträge vor dem einfügen, damit wird es langsam nach unten und diese Art von macht linq to sql schöner als Ihr bekam Objekte vor allem, wenn Sie Ihre erste Analyse von Daten aus einer xml-Datei vor dem einfügen in die Datenbank.
Komplette C# - code
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");
}
///<summary>
///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
///</summary>
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();
}
}
///<summary>
///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
///</summary>
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());
}
}
///<summary>
///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
///</summary>
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();
}
}
///<summary>
///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
///</summary>
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());
}
}
}
InformationsquelleAutor chobo2 | 2010-05-19
Du musst angemeldet sein, um einen Kommentar abzugeben.
Die batch-Größe ist es zur Verringerung der Auswirkungen der Netzwerk-Latenz. Es braucht nicht mehr als ein paar tausend. Mehrere Aussagen werden gesammelt und gesendet, als Einheit, so erhalten Sie die Treffer eines Netzwerk-Fahrt mal alle N Aussagen, anstatt einmal pro Anweisung.
Sie wollen versuchen zu vermeiden, die Netzwerk-Latenz, d.h. die Verringerung der Auswirkungen der Wartezeit so viel wie möglich. Dosierung 1000-10000 ist im Allgemeinen gut, aber es hängt davon ab, wie viel Wartezeit Sie haben in Ihrem Netzwerk an und wie viel Speicher Sie sich leisten können, verwenden Sie für die Dosierung Anweisungen. E. g. wenn Sie N Aussagen, und eine Latenz von Lms, anschließend die Gesamt-Latenz ist NL ms. Wenn Sie batch-Größe von B, dann ist die Latenz N - L/B, so reduzieren Sie die Latenz, indem Sie die Größe des Stapels. Die Arbeit dieser Variablen zu erreichen, eine Gesamt-Latenz akzeptabel ist, in Ihrer situation - z.B. ein kleiner Prozentsatz Ausführungszeit von Abfragen.
Meinst du die batch-Größe hat keinen Einfluss auf die Transaktion melden und verpflichtet?
ja, genau. Es ist nur steuert, wie oft die Daten über das Netzwerk gesendet.
InformationsquelleAutor mdma
Ist das ein one-off-bulk-Kopie oder eine reguläre Sache?
Wenn es eine einmalige, oder nur einmal am Tag zum Beispiel, verwenden Sie BCP, es ist viel schneller, als dass es nutzt eine spezielle API ' s, die schneller sind als ado.net.
Ich verwendet BCP gestern zu übermitteln, fast eine million Datensätze aus eine sehr große Tabelle mit über 400 Felder. Es ist also genau das, was Sie brauchen für Ihre Aufgabe, wenn Ihr ein one-off von 50k Zeilen.
Wie lange haben Sie nehmen? Nun, ich bin auch auf der Suche auf das ganze Bild. Wie nehme ich ein xml-Dokument und mittels Serialisierung. Wenn es gut ist dann könnte ich nur hinzufügen, dass es ein anderes Objekt-array und call senden Sie es mit linq to sql Weg.
Dauerte etwa 5 Minuten zu extrahieren 900k Zeilen. So ja, BCP ist wirklich gut für den gelegentlichen extrahiert. Wie für Ihre regelmäßige 50record update, BCP ist wahrscheinlich übertrieben.
Und legen Sie die 900k Zeilen in eine andere Datenbank, vielleicht ein bisschen mehr, sagen wir 10 Minuten? Nicht lange genug für mich zu kümmern, Sie zu Messen.
InformationsquelleAutor Chris
Oh, ich bin froh, dass wir nicht die einzigen, die von diesem leiden InsertOnSubmit() Problem.
Kürzlich der Umzug unseres Unternehmens von Daten-Zentren, und dann unsere SQL Server Maschinen wurden 6.000 Meilen entfernt, anstatt in das gleiche Land.
Und plötzlich, Einsparung einen Stapel von 1800 Datensätze wurde unter 3,5 Minuten, eher als 3-4 Sekunden. Unsere Benutzer waren nicht glücklich !!
War die Lösung zu ersetzen, die InsertOnSubmit Anrufe mit einer Bulk Insert-Bibliothek.
Haben, Lesen Sie die "Einfügen von Datensätzen mithilfe einer Bulk Insert" - Sektion auf dieser Seite. Es zeigt die (sehr wenigen) änderungen, die Sie tatsächlich brauchen, um Ihren code, um zu bekommen, um dieses Latenz problem.
Brauchen Sie nur zum hinzufügen von drei Zeilen code, und verwenden Sie ein paar von C# - Klassen, die auf dieser Seite bereitgestellten.
http://mikesknowledgebase.com/pages/LINQ/InsertAndDeletes.htm
InformationsquelleAutor Mike Gledhill