Skip to content

Instantly share code, notes, and snippets.

@lukemcgregor
Created April 6, 2012 05:49
Show Gist options
  • Save lukemcgregor/2317397 to your computer and use it in GitHub Desktop.
Save lukemcgregor/2317397 to your computer and use it in GitHub Desktop.
SQL is slower straight after creating the DB, This occurs because of AutoGrow
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace ConsoleApplication8
{
class Program
{
static void Main(string[] args)
{
int iterations = 10000;
var cnn = new SqlConnection("Data Source=localhost\\sqlexpress;Integrated Security=True;");
cnn.Open();
var createCmd = new SqlCommand();
createCmd.Connection = cnn;
createCmd.CommandText = "if exists(select * from sys.databases where name = 'test-db') drop database [test-db]";
createCmd.ExecuteNonQuery();
createCmd.CommandText = "create database [test-db]";
createCmd.ExecuteNonQuery();
createCmd.CommandText = "create table [test-db].[dbo].TestEntities(Id int primary key, TestDate datetime, TestInt int, TestString nvarchar(512))";
createCmd.ExecuteNonQuery();
var insertCmd = new SqlCommand();
insertCmd.Connection = cnn;
insertCmd.CommandText = "insert [test-db].[dbo].TestEntities(Id , TestDate , TestInt, TestString) values(@Id, '2012-1-1',423423,'543254gddvfcsa')";
var idParam = insertCmd.Parameters.Add("@Id", System.Data.SqlDbType.Int);
Stopwatch timer = new Stopwatch();
timer.Restart();
using (var t = cnn.BeginTransaction())
{
insertCmd.Transaction = t;
for (int i = 1; i <= iterations; i++)
{
idParam.Value = i;
insertCmd.ExecuteNonQuery();
}
t.Commit();
}
timer.Stop();
Console.WriteLine(timer.ElapsedMilliseconds);
var truncateCmd = new SqlCommand();
truncateCmd.Connection = cnn;
truncateCmd.CommandText = "truncate table [test-db].[dbo].TestEntities";
truncateCmd.ExecuteNonQuery();
timer.Restart();
using (var t = cnn.BeginTransaction())
{
insertCmd.Transaction = t;
for (int i = 1; i <= iterations; i++)
{
idParam.Value = i;
insertCmd.ExecuteNonQuery();
}
t.Commit();
}
timer.Stop();
Console.WriteLine(timer.ElapsedMilliseconds);
cnn.Close();
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment