![pt_headerlogo[1]](http://blog.felipecorrea.com/wp-content/uploads/2011/08/pt_headerlogo1.gif "pt_headerlogo[1]")
Long time no see! :D
Aos poucos vou voltando.
Nas últimas semanas pesquisei quais os melhores métodos para inserir registros no SQL via programação ADO.NET e C#.
Fiz alguns testes (inserindo 20000 linhas) e aqui vai os resultados e o código. — O Parallel não está assim tão bem, acho que não é a melhor forma de se fazer, mas funciona heheh
Se curtirem e/ou tiverem outras idéias/sugestões, comemtem.
ADO.NET PERFORMANCE TEST
using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data.SqlClient;using System.Windows.Forms;
namespace SQLStatementTest{ class Program { static void Main(string[] args) { SQLParallelTests sql = new SQLParallelTests(); sql.DoTests(); } }
public class SQLParallelTests { private const string CONNECTION_STRING = @"SERVER=.\SQLEXPRESS;integrated security=true;initial catalog=TESTDATABASE; Asynchronous Processing=true;MultipleActiveResultSets=True"; const string INSERT = "INSERT INTO SQLTEST (COL1, COL2, COL3, COL4, COL5) VALUES ('{0}','{1}','{2}','{3}','{4}')"; struct RowStruct { public string Column1; public string Column2; public string Column3; public string Column4; public DateTime Column5; } private List<RowStruct> PopulateRowsData(int n) { List<RowStruct> datas = new List<RowStruct>(); for (int aux = 0; aux < n; aux++) { RowStruct data = new RowStruct(); data.Column1 = string.Format("Column1=({0})", Guid.NewGuid().ToString()); data.Column2 = string.Format("Column1=({0})", Guid.NewGuid().ToString()); data.Column3 = string.Format("Column1=({0})", Guid.NewGuid().ToString()); data.Column4 = string.Format("Column1=({0})", Guid.NewGuid().ToString()); data.Column5 = DateTime.Now; datas.Add(data); } return datas; }
public void DoTests() { int nRows = 20000; List<RowStruct> rowsData = PopulateRowsData(nRows);
Dictionary<string, TimeSpan> counter = new Dictionary<string, TimeSpan>();
using (System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope()) { counter.Add("Normal SQL SCOPE", NormalSQL(rowsData)); scope.Complete(); } using (System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope()) { counter.Add("Normal Parameter SQL SCOPE", NormalParameterSQL(rowsData)); scope.Complete(); } using (System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope()) { counter.Add("Parallel SQL SCOPE", ParallelSQL(rowsData)); scope.Complete(); }
using (System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope()) { counter.Add("Stored Procedure SQL SCOPE", ProcedureParameterSQL(rowsData)); scope.Complete(); }
counter.Add("Normal SQL", NormalSQL(rowsData)); counter.Add("Normal Parameter SQL", NormalParameterSQL(rowsData)); counter.Add("Parallel SQL", ParallelSQL(rowsData)); counter.Add("Stored Procedure SQL", ProcedureParameterSQL(rowsData));
var results = from result in counter orderby result.Value select result;
foreach (var result in results) { Console.WriteLine(string.Format("[{0}] ({1})\t{2}", result.Value, nRows / result.Value.TotalSeconds, result.Key)); } Console.WriteLine(); Console.WriteLine("PRESS ANY KEY TO EXIT."); Console.ReadLine();
}
private TimeSpan ParallelSQL(List<RowStruct> rowsData) { System.Diagnostics.Stopwatch watch = new System.Diagnostics.Stopwatch(); watch.Start();
System.Threading.Tasks.Parallel.ForEach(rowsData, r => { using (SqlConnection connection = new SqlConnection(CONNECTION_STRING)) { connection.Open(); SqlCommand command = new SqlCommand(); command.Connection = connection; command.CommandText = string.Format(INSERT, r.Column1, r.Column2, r.Column3, r.Column4, r.Column5); int n = command.ExecuteNonQuery(); } }); watch.Stop(); return watch.Elapsed; }
private TimeSpan NormalSQL(List<RowStruct> rowsData) { System.Diagnostics.Stopwatch watch = new System.Diagnostics.Stopwatch(); watch.Start(); using (SqlConnection connection = new SqlConnection(CONNECTION_STRING)) { connection.Open(); foreach (var r in rowsData) { SqlCommand command = new SqlCommand(); command.Connection = connection; command.CommandText = string.Format(INSERT, r.Column1, r.Column2, r.Column3, r.Column4, r.Column5); int n = command.ExecuteNonQuery(); } } watch.Stop(); return watch.Elapsed; }
private TimeSpan NormalParameterSQL(List<RowStruct> rowsData) { System.Diagnostics.Stopwatch watch = new System.Diagnostics.Stopwatch(); watch.Start(); using (SqlConnection connection = new SqlConnection(CONNECTION_STRING)) { connection.Open();
SqlCommand command = new SqlCommand(); command.Connection = connection; command.CommandText = string.Format(INSERT.Replace(@"'",""), "@COL1", "@COL2", "@COL3", "@COL4", "@COL5"); command.Parameters.Add("COL1", System.Data.SqlDbType.VarChar, 250); command.Parameters.Add("COL2", System.Data.SqlDbType.VarChar, 250); command.Parameters.Add("COL3", System.Data.SqlDbType.VarChar, 250); command.Parameters.Add("COL4", System.Data.SqlDbType.VarChar, 250); command.Parameters.Add("COL5", System.Data.SqlDbType.VarChar, 250); //command.Prepare(); foreach (var r in rowsData) { command.Parameters["COL1"].Value = r.Column1; command.Parameters["COL2"].Value = r.Column2; command.Parameters["COL3"].Value = r.Column3; command.Parameters["COL4"].Value = r.Column4; command.Parameters["COL5"].Value = r.Column5; int n = command.ExecuteNonQuery(); } } watch.Stop(); return watch.Elapsed; }
private TimeSpan ProcedureParameterSQL(List<RowStruct> rowsData) { System.Diagnostics.Stopwatch watch = new System.Diagnostics.Stopwatch(); watch.Start();
using (SqlConnection connection = new SqlConnection(CONNECTION_STRING)) { connection.Open();
using (SqlCommand command = connection.CreateCommand()) { command.CommandText = "INSERT_BY_PARAMENTER"; command.CommandType = System.Data.CommandType.StoredProcedure; command.Parameters.Add("COL1", System.Data.SqlDbType.VarChar, 250); command.Parameters.Add("COL2", System.Data.SqlDbType.VarChar, 250); command.Parameters.Add("COL3", System.Data.SqlDbType.VarChar, 250); command.Parameters.Add("COL4", System.Data.SqlDbType.VarChar, 250); command.Parameters.Add("COL5", System.Data.SqlDbType.VarChar, 250); //command.Prepare(); foreach (var r in rowsData) { command.Parameters["COL1"].Value = r.Column1; command.Parameters["COL2"].Value = r.Column2; command.Parameters["COL3"].Value = r.Column3; command.Parameters["COL4"].Value = r.Column4; command.Parameters["COL5"].Value = r.Column5;
int n = command.ExecuteNonQuery(); } }
}
watch.Stop(); return watch.Elapsed; } }
}
O código acima pode parecer zuado, pois estou testando um outro plugin, pra pegar as info direto do GitHub.
