http://syska.dk/ (...)Koden som er brugt er følgende ... gør jeg noget forkert her? Følgende update som du kan se i overstående ss, tager 1 min 35 sek, hvilket er ca. 19 gange langere end når jeg gør det med min MERGE ... :-s ...
Hvordan skal det lige laves for at kunne være så hurtig ? Jeg er helt tabt ...
StopWatch sw = new StopWatch();
Regex reg = new Regex("(\\d+),(-?\\d+),(-?\\d+),(-?\\d+),(-?\\d+),'(.*)',(\\d+),'(.+)',(\\d+),'(.*)',(\\d+)", RegexOptions.Compiled);
Match m;
string s;
List<WorldRow> list = new List<WorldRow>();
WorldRow wr;
int lines = 0;
sw.Start();
using (StreamReader sr = new StreamReader(@"C:\temp\DK\s4.travian.dk\2009-01-17.sql", Encoding.UTF8))
{
while (!sr.EndOfStream)
{
lines++;
s = sr.ReadLine();
m = reg.Match(s);
if (reg.IsMatch(s))
{
wr = new WorldRow();
wr.CoorID = int.Parse(m.Groups[1].Value);
wr.X = short.Parse(m.Groups[2].Value);
wr.Y = short.Parse(m.Groups[3].Value);
wr.TID = byte.Parse(m.Groups[4].Value);
wr.VID = int.Parse(m.Groups[5].Value);
wr.VillageName = m.Groups[6].Value;
wr.UID = int.Parse(m.Groups[7].Value);
wr.PlayerName = m.Groups[8].Value;
wr.AID = (int.Parse(m.Groups[9].Value) == 0) ? null : (int?)int.Parse(m.Groups[9].Value);
wr.AllianceName = m.Groups[10].Value;
wr.Population = short.Parse(m.Groups[11].Value);
list.Add(wr);
}
}
}
Console.WriteLine("Lines: {0}, Reg Hits: {1}", lines, list.Count);
sw.Stop("Parse input");
sw.Start();
SqlConnection myConnection = new SqlConnection(connString);
myConnection.Open();
SqlCommand myCommand = myConnection.CreateCommand();
SqlTransaction myTrans = null;
myCommand.CommandText = "UPDATE Villages SET Population = @Population WHERE SID = @SID AND VID = @VID";
SqlParameter pop = myCommand.Parameters.Add("@Population", System.Data.SqlDbType.SmallInt);
SqlParameter sid = myCommand.Parameters.Add("@SID", System.Data.SqlDbType.SmallInt);
SqlParameter vid = myCommand.Parameters.Add("@VID", System.Data.SqlDbType.Int);
sid.Value = 7;
try
{
for (int i = 0; i <= (list.Count / 1000); i++)
{
// Start a local transaction
myTrans = myConnection.BeginTransaction();
// Must assign both transaction object and connection
// to Command object for a pending local transaction
// myCommand.Connection = myConnection;
myCommand.Transaction = myTrans;
foreach (WorldRow row in list.Skip(1000 * i).Take(1000))
{
pop.Value = row.Population;
vid.Value = row.VID;
myCommand.ExecuteNonQuery();
}
Console.WriteLine("First commit");
myTrans.Commit();
}
}
catch (Exception e)
{
try
{
myTrans.Rollback();
}
catch (SqlException ex)
{
if (myTrans.Connection != null)
{
Console.WriteLine("An exception of type " + ex.GetType() +
" was encountered while attempting to roll back the transaction.");
}
}
Console.WriteLine("An exception of type " + e.GetType() +
" was encountered while inserting the data.");
Console.WriteLine("Neither record was written to database.");
}
finally
{
myConnection.Close();
}
sw.Stop("UPDATE");
