Indsætte store mængder data i database, effektivt

Ah hvad.

At indsætte 500000 rækker bør tage 1 sekund + 1 minut afhaengigt af din DB HW.

Små optimeringer:
- hold connection aaben (selvom .NET bruger connection pooling)
- bundt flere INSERT i en enkelt transaktion
- kør multithreaded (ihvertfald hvis client program og DB tilsammen har 4 eller flere kerner)

Eller kig paa SQLServer bulkcopy som burde vaere det hurtigste.

Jeg leger lidt med at køre flere inserts i samme transaktion. Er der nogen måde at lave exekvere flere sqlcommands på samme connection på samme tid?

Alternativet ser ud til at være at lave en StringBuilder og så skrive et antal inserts på den og så lave en enkelt insert for hver 50 inserts, det giver mig nogle problemer med parametre, da jeg skal have sat datoen ind, og konvertering af dato er noget bras hvis den skal køre på flere forskellige collations...

Nu ved jeg ikke lige hvordan din database connection ser ud, men noget i den her stil burde kunne gøre det:

using System.Threading;
...
...
...

ADOConnection conn = new ADOConnection("[Din_Connection_String]");

public void BuildDates()
{
  DateTime basedate = new DateTime(2008, 1, 1, 0, 1, 0);
  int max = 365 * 24 * 60;
  List<DateTime> dates = new List<DateTime();
  for (int i = 0; i <= max; i++)
  {
      dates.Add(basedate.Add(new TimeSpan(0, 0, i));
      if (i % 50 == 0 || i == max)
      {
        Thread t1 = new Thread(new ParameterizedThreadStart(InsertRows));
        t1.Start(dates);
        Thread.Sleep(1000);
        dates.Clear();
      }
  }
}

public static void InsertRows(object obj)
{
  if (obj is List<DateTime>)
      InsertRows((List<DateTime>)obj);
}

public static void InsertRows(List<DateTime> input)
{
  string basesql = "INSERT INTO [Din_Tabel] ([Tabel_Felt]) VALUES(";
  string sql = "";
  foreach (DateTime d in input)
  {
      sql += basesql + d + ");";
  }
  conn.Execute(sql);
}

Det hele er skrevet uden om en editor, så kan ikke være helt sikker på om det er valid kode det hele, men princippet er der.

Ideen med ovenstående er at gå ud fra en basis dato og så tilføje 1 minut af gangen. Når der er samlet 50 datoer, køres en SQL insert i en seperat tråd og resten af programmet står stille i det sekund det max tager at tilføje de 50 datoer.

arne_v har sikkert en bedre løsning eller rettelser til min, men den skulle virke :)

MiReiGi

Tager det ikke længere tid at smide det hele i en liste for derefter at løbe listen igennem? Jeg er heller ikke så vild med ideen om at den skal vente i et sekund hver gang, er ideen med at lave den multithreaded ikke at det ikke burde være nødvendigt? Og hvad med SqlParametre... kan man lave noget smart der i stedet for at lave string concatenation?

En SqlCommand ad gangen. Man kan godt have flere SQL saetninger i en enkelt SqlCommand,
men jeg tror bare ikke at det giver noget i forhold til flere SqlCommand i en enkelt
transaktion.

Alternativt skal jeg bare tage mig sammen og så kun udfylde data fra start til nu første gang jeg kører, hejse et flag og herefter kun fylde data i fra nyeste dato til i dag... mine bekymringer er blot data inkonsistens, hvis jeg "taber" datoer på gulvet

Jeg lavede engang en lille test med:
  string concat / params / SP
  non reuse command / reuse command
  mange tx / en tx

Resulatet var:

String no reuse command object multiple transactions 6000 ms
String reuse command object multiple transactions 5859 ms
String no reuse command object one transaction 1671 ms
String reuse command object one transaction 1640 ms
Parameters no reuse command object multiple transactions 5968 ms
Parameters reuse command object multiple transactions 5843 ms
Parameters no reuse command object one transaction 1671 ms
Parameters reuse command object one transaction 1609 ms
Stored Procedure no reuse command object multiple transactions 5515 ms
Stored Procedure reuse command object multiple transactions 5421 ms
Stored Procedure no reuse command object one transaction 1359 ms
Stored Procedure reuse command object one transaction 1265 ms
String no reuse command object multiple transactions 5890 ms
String reuse command object multiple transactions 5859 ms
String no reuse command object one transaction 1656 ms
String reuse command object one transaction 1640 ms
Parameters no reuse command object multiple transactions 6125 ms
Parameters reuse command object multiple transactions 5828 ms
Parameters no reuse command object one transaction 1671 ms
Parameters reuse command object one transaction 1593 ms
Stored Procedure no reuse command object multiple transactions 5500 ms
Stored Procedure reuse command object multiple transactions 5421 ms
Stored Procedure no reuse command object one transaction 1343 ms
Stored Procedure reuse command object one transaction 1265 ms
String no reuse command object multiple transactions 5859 ms
String reuse command object multiple transactions 5843 ms
String no reuse command object one transaction 1687 ms
String reuse command object one transaction 1640 ms
Parameters no reuse command object multiple transactions 5906 ms
Parameters reuse command object multiple transactions 5828 ms
Parameters no reuse command object one transaction 1703 ms
Parameters reuse command object one transaction 1609 ms
Stored Procedure no reuse command object multiple transactions 5531 ms
Stored Procedure reuse command object multiple transactions 5390 ms
Stored Procedure no reuse command object one transaction 1343 ms
Stored Procedure reuse command object one transaction 1265 ms

Men som sagt boer bulk copy vaere endnu hurtigere.

@arne_v
Jepsen, det gør jeg nu, men jeg ville gerne gøre det med en SqlCommand og så bruge en SqlPArameter til at indkapsle min datatype (datoen), det kan jeg vel ikke gøre med flere inserts, uden at skulle lave en insert pr. sqlcommand....

Update:
internal void FillInDates()
        {
            DateTime startTime = GetMinimumDateFromHands();
            startTime = startTime.AddDays(-1);
            DateTime endTime = DateTime.Now.AddMonths(1);
            DateTime currentTime = new DateTime(startTime.Year, startTime.Month, startTime.Day, startTime.Hour, startTime.Minute, 0);
            try
            {
                connection.Open();
                StringBuilder sqlBuilder = new StringBuilder();
                int count = 0;
                TimeSpan span = endTime - startTime;
                int max = (int)span.TotalMinutes;
                while (currentTime < endTime)
                {
                    sqlBuilder.AppendFormat("insert into PIPeriod(thedate) values ('{0}/{1}/{2} {3}:{4}:00')\n", currentTime.Year, currentTime.Month, currentTime.Day, currentTime.Hour, currentTime.Minute);
                    if (count % 60 == 0 || count == max)
                    {
                        SqlCommand cmd = connection.CreateCommand();
                        cmd.CommandText = sqlBuilder.ToString();
                        cmd.ExecuteNonQuery();
                        count = 0;
                        sqlBuilder.Remove(0, sqlBuilder.Length);
                    }
                    currentTime = currentTime.AddMinutes(1);
                    count++;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Databasehandler error: " + ex.Message);
            }
            finally
            {
                connection.Close();
            }

Kører igennem på 2m:26s

"Jeg er heller ikke så vild med ideen om at den skal vente i et sekund hver gang, er ideen med at lave den multithreaded ikke at det ikke burde være nødvendigt?"

Det er ikke nødvendigt, men du kan bare sætte "ventetiden" ned til fx 50ms. Det er anbefalet at have en lille ventetid på så den nye tråd når at tage listen "dates" med sig inden den kaldende tråd tømmer denne.

Jeg har aldrig proevet med flere SQL saetninger til en enkelt SqlCommand sammen med
parameters. Fordi jeg mener ikke at det giver noget. Hvis det skulle virke saa skulle
du nok bruget noget a la:

"INSERT INTO t(f) VALUES(@t1);INSERT INTO t(f) VALUES(@t2);INSERT INTO t(f) VALUES(@t3);"

men jeg ved ikke om det virker. Strebge kan naturligvis opbygges dynamisk i et loop.

Jepsen, tak for det - begge to, smid lige nogle svar.

Det var så lidt.

Håber du finder ud af noget. Du må gerne poste den komplette løsning herinde når du har den, så andre kan finde den :)

MiReigi

svar