Created
January 25, 2023 19:35
-
-
Save JerryNixon/aaa8996f4e98e714eb48b5ae9c1ee0d7 to your computer and use it in GitHub Desktop.
Inserting as fast as I can.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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