SQL Server - Transaction Slowness

Question

Using: SQL Server 2008 R2

I'm seeing some behavior right now in SQL Server, and I can't decide if the server is underperforming or not.

I've set up a test to insert an integer into a table, as defined below, 75,000 times.

dbo.Test1
(
    id INT IDENTITY(1,1)
    , [text] VARCHAR(2000)
    , [timestamp] DATETIME NOT NULL DEFAULT(GETDATE())
)

Both the application (C# code below) inserting the data and the SQL Server instance are on the same physical box.

The results: the process is taking almost 10 minutes to loop and insert the data. Not really sure how this can be possible...

for (int i = 0; i < 75000; i++)
{
     using (SqlConnection connection = new SqlConnection(@"<server>"))
     {
           connection.Open();

           using (SqlCommand command = new SqlCommand("INSERT INTO dbo.Test1 ([text]) SELECT '" + i + "'", connection))
           {
                 command.ExecuteNonQuery();
           }

           connection.Close();
     }
}

EDIT:

Update to question as per @AaronBertrand's response.

How big is i? As I specified, it's an integer in a loop. I don't care about text that may or may not go in the text column.

What kind of drives? It's a 7200 SATA (http://www.seagate.com/ww/v/index.jsp?vgnextoid=2ee06c02c732f110VgnVCM100000f5ee0a0aRCRD)

Is the database tiny? Yes, this is a possible issue.

Is the database remote? Please read the question more closely. Everything is on the same physical box, right in front of me. Same issues with two boxes across a network though, but I'm not trying to debug network issues right now. Just SQL Server insert issues.

Good money's on the autogrow issue right now.

EDIT 2:

Update again. Thanks in advance for taking the time to read/respond.

Is the machine a VM? No VM, thank god.

Application connection method Researching that. Since everything's on the same box, I'm not sure how it connects.

Triggers None

Resource governor Not set up.

Profiler The profiler doesn't show any delays whatsoever, which is bugging the hell out of me.

Blocking This is the only thing happening, so there shouldn't be any blocking or waits, perhaps excluding log waits. On my box, everything's on the same physical disk. However, (big however) on other boxes with the same problem this is NOT the case, so I will not pursue this as a problem at this time.

Answer 1

My hunch is that it has something to do with your loop opening and closing the connection 75,000 times. Maybe try something like this and see what happens:

 using (SqlConnection connection = new SqlConnection(@"<server>"))
 {
       connection.Open();

       using (SqlCommand command = new SqlCommand("INSERT INTO dbo.Test1 ([text]) VALUES (@i)", connection))
       {
             command.Parameters.Add(new SqlParameter("@i", SqlDbType.Int));
             for (int i = 0; i < 75000; i++) {
                 command.Parameters["@i"].Value = i;
                 command.ExecuteNonQuery();
             }
       }

       connection.Close();
 }

EDIT: One other speed-up I found is wrapping all the executions in a single transaction (presumably this eliminates the overhead of constantly locking and releasing locks). Do SqlTransaction trans = connection.BeginTransaction() before instantiating your SqlCommand, and trans.Commit() after it's done. Here are the run times on my development machine:

1:08.14 - Original version
0:33.05 - Changed to open the connection only once, and reuse the SqlConnection
0:11.93 - Changed to use a single transaction

Answer 2

How big is i, roughly? What kind of drives are behind this, your laptop's 5400rpm drives? Is the database tiny and incurring a lot of autogrow events? I suspect there is some kind of configuration problem. I ran the following code in 52 seconds on my Win7 VM against 7200rpm drives.

CREATE TABLE dbo.Test1
(
    id INT IDENTITY(1,1)
    , [text] VARCHAR(2000)
    , [timestamp] DATETIME NOT NULL DEFAULT(GETDATE())
);
GO

INSERT dbo.Test1([text]) SELECT 'Not sure how long this text is supposed to be.';
GO 75000

Other questions: Is the machine a VM? Is the application connecting via TCP/IP, shared memory, named pipes? What other activity is going on the box? Are there triggers on the table, resource governor implemented, etc.? Have you checked sys.dm_exec_requests or profiler to see how long these individual inserts are actually taking, what else C# is sending to the server, and what blocking or other waiting might be occurring?

Answer 3

Thoughts, in addition to the "opening/closing connections per iteration" issue

Each insert will send a packet with (xx rows affected) which will be sent (evebn if you don't handle it) unless you use SET NOCOUNT ON. See http://stackoverflow.com/q/1483732/27535 on SO for more.