Jak zmienić powolne, sparametryzowane wstawki w szybką kopię zbiorczą (nawet z pamięci)
Miałem coś takiego w moim kodzie (.Net 2.0, MS SQL)
SqlConnection connection = new SqlConnection(@"Data Source=localhost;Initial
Catalog=DataBase;Integrated Security=True");
connection.Open();
SqlCommand cmdInsert = connection.CreateCommand();
SqlTransaction sqlTran = connection.BeginTransaction();
cmdInsert.Transaction = sqlTran;
cmdInsert.CommandText =
@"INSERT INTO MyDestinationTable" +
"(Year, Month, Day, Hour, ...) " +
"VALUES " +
"(@Year, @Month, @Day, @Hour, ...) ";
cmdInsert.Parameters.Add("@Year", SqlDbType.SmallInt);
cmdInsert.Parameters.Add("@Month", SqlDbType.TinyInt);
cmdInsert.Parameters.Add("@Day", SqlDbType.TinyInt);
// more fields here
cmdInsert.Prepare();
Stream stream = new FileStream(fileName, FileMode.Open, FileAccess.Read);
StreamReader reader = new StreamReader(stream);
char[] delimeter = new char[] {' '};
String[] records;
while (!reader.EndOfStream)
{
records = reader.ReadLine().Split(delimeter, StringSplitOptions.None);
cmdInsert.Parameters["@Year"].Value = Int32.Parse(records[0].Substring(0, 4));
cmdInsert.Parameters["@Month"].Value = Int32.Parse(records[0].Substring(5, 2));
cmdInsert.Parameters["@Day"].Value = Int32.Parse(records[0].Substring(8, 2));
// more here complicated stuff here
cmdInsert.ExecuteNonQuery()
}
sqlTran.Commit();
connection.Close();
ZcmdInsert.ExecuteNonQuery () skomentował ten kod wykonuje się w mniej niż 2 sekundy. Wykonanie SQL zajmuje 1m 20 sek. Istnieje około 0,5 miliona rekordów. Tabela jest opróżniana wcześniej. Zadanie przepływu danych SSIS o podobnej funkcjonalności zajmuje około 20 sekund.
Wkładka zbiorczabyło nie jest opcją (patrz poniżej). Podczas tego importu zrobiłem kilka ciekawych rzeczy.Moja maszyna testowa to Core 2 Duo z 2 GB pamięci RAM.Podczas szukania w Menedżerze zadań procesor nie był w pełni gotowy. IO wydawało się również nie być w pełni wykorzystywane.Schemat jest prosty jak diabli: jedna tabela z AutoInt jako podstawowym indeksem i mniej niż 10 intów, małe inty i znaki (10).Po kilku odpowiedziach tutaj znalazłem, że możliwe jest wykonaniekopiowanie zbiorcze z pamięci! Odmówiłem użycia kopii zbiorczej, ponieważ myślałem, że trzeba to zrobić z pliku ...
Teraz używam tego i trwa to 20 sekund (jak zadanie SSIS)
DataTable dataTable = new DataTable();
dataTable.Columns.Add(new DataColumn("ixMyIndex", System.Type.GetType("System.Int32")));
dataTable.Columns.Add(new DataColumn("Year", System.Type.GetType("System.Int32")));
dataTable.Columns.Add(new DataColumn("Month", System.Type.GetType("System.Int32")));
dataTable.Columns.Add(new DataColumn("Day", System.Type.GetType("System.Int32")));
// ... and more to go
DataRow dataRow;
object[] objectRow = new object[dataTable.Columns.Count];
Stream stream = new FileStream(fileName, FileMode.Open, FileAccess.Read);
StreamReader reader = new StreamReader(stream);
char[] delimeter = new char[] { ' ' };
String[] records;
int recordCount = 0;
while (!reader.EndOfStream)
{
records = reader.ReadLine().Split(delimeter, StringSplitOptions.None);
dataRow = dataTable.NewRow();
objectRow[0] = null;
objectRow[1] = Int32.Parse(records[0].Substring(0, 4));
objectRow[2] = Int32.Parse(records[0].Substring(5, 2));
objectRow[3] = Int32.Parse(records[0].Substring(8, 2));
// my fancy stuf goes here
dataRow.ItemArray = objectRow;
dataTable.Rows.Add(dataRow);
recordCount++;
}
SqlBulkCopy bulkTask = new SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock, null);
bulkTask.DestinationTableName = "MyDestinationTable";
bulkTask.BatchSize = dataTable.Rows.Count;
bulkTask.WriteToServer(dataTable);
bulkTask.Close();