Skip to content

Instantly share code, notes, and snippets.

@JerryNixon
Created January 25, 2023 19:35
Show Gist options
  • Select an option

  • Save JerryNixon/aaa8996f4e98e714eb48b5ae9c1ee0d7 to your computer and use it in GitHub Desktop.

Select an option

Save JerryNixon/aaa8996f4e98e714eb48b5ae9c1ee0d7 to your computer and use it in GitHub Desktop.
Inserting as fast as I can.
using System.Data;
using System.Data.SqlClient;
internal class Program
{
static int threads = 0;
static int records = 0;
static int batch = 10_000;
static DateTime started = DateTime.Now;
private static void Main(string[] args)
{
var db = "MyDemoDatabase";
ExecuteSql($"""
IF NOT EXISTS(SELECT 1 FROM sys.databases WHERE NAME = '{db}')
CREATE DATABASE {db};
USE {db};
DROP TABLE IF EXISTS Reading;
CREATE TABLE[dbo].[Reading]
(
[Id][int] PRIMARY KEY NONCLUSTERED IDENTITY(1,1) NOT NULL
, [Sensor] [int] NOT NULL
, [Date] [datetimeoffset] (7) NOT NULL
, [Value] [decimal](18, 0) NOT NULL
);
""", "master");
_ = new Timer(x =>
{
Console.SetCursorPosition(0, 0);
var seconds = (DateTime.Now - started).TotalSeconds;
var rate = records / seconds;
var million = 1_000_000 / rate;
Console.WriteLine($"{seconds:#,#} seconds so far");
Console.WriteLine($"{threads:#,#} active threads");
Console.WriteLine($"{batch:#,#} batch size");
Console.WriteLine($"{records:#,#} records inserted");
Console.WriteLine($"{rate:#,#} inserted per second");
Console.WriteLine($"1 million records in {million:#,#} seconds.");
}, default, TimeSpan.Zero, TimeSpan.FromSeconds(1));
Parallel.For(0, 100, i =>
{
Interlocked.Increment(ref threads);
StartWriting(i, db);
Interlocked.Decrement(ref threads);
});
Console.WriteLine("Done");
Console.ReadLine();
}
static void StartWriting(int sensor, string db)
{
using SqlConnection connection = ConnectSql(db);
while (connection.State == System.Data.ConnectionState.Open)
{
using var bulkCopy = new SqlBulkCopy(connection);
var dataTable = GenerateData(batch);
bulkCopy.DestinationTableName = dataTable.TableName;
bulkCopy.BulkCopyTimeout = 0;
bulkCopy.WriteToServer(dataTable);
Interlocked.Add(ref records, dataTable.Rows.Count);
Thread.Sleep(10);
}
DataTable GenerateData(int rows)
{
var dataTable = new DataTable("Reading");
dataTable.Columns.Add(new DataColumn("Id", typeof(int)));
dataTable.Columns.Add(new DataColumn("Sensor", typeof(int)));
dataTable.Columns.Add(new DataColumn("Date", typeof(DateTime)));
dataTable.Columns.Add(new DataColumn("Value", typeof(int)));
for (int i = 0; i < rows; i++)
{
var row = dataTable.NewRow();
row["Sensor"] = sensor;
row["Date"] = DateTime.Now;
row["Value"] = Random.Shared.Next();
dataTable.Rows.Add(row);
}
return dataTable;
}
}
static int ExecuteSql(string sql, string db)
{
using SqlConnection connection = ConnectSql(db);
using SqlCommand command = connection.CreateCommand();
command.CommandText = sql;
return command.ExecuteNonQuery();
}
private static SqlConnection ConnectSql(string db)
{
var connectionString = $"Server=(localdb)\\MSSQLLocalDB;Integrated Security=true;database={db}";
var connection = new SqlConnection(connectionString);
connection.Open();
return connection;
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment