Created
April 6, 2012 05:49
-
-
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
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; | |
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