Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Sorry, you didn't convert i.ToString(); so I didn't check your loop counter - just assumed it was a string defined elsewhere. –  Aaron Bertrand Feb 15 '12 at 17:44
2  
In general, if you are concerned with speed of modifications, try saving them in batches, using TVPs or SqlBulkCopy. –  AlexKuznetsov Feb 16 '12 at 15:42

3 Answers 3

up vote 8 down vote accepted

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

share|improve this answer
    
Well, you're definitely on to something here. I just ran your code and it blasted through in a few seconds. Makes sense - opening and closing the connection is going to take time. I'll need to do some more testing, because I think you just proved that my test was wrong. –  Nick Vaccaro Feb 15 '12 at 18:05
    
Changing to use a single transaction will obviously speed things up because before a transaction commits SQL Server needs to wait for confirmation that the transaction has been written to disc. I thought that's what the OP was testing. Maybe not then. @Norla - What are you trying to measure exactly? Though as your drive spec gives an expected random write seek time of <9.5ms and you are averaging a transacion every 8ms maybe the performance is to be expected. –  Martin Smith Feb 15 '12 at 22:43

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?

share|improve this answer
    
Updated question. –  Nick Vaccaro Feb 15 '12 at 17:43
1  
Yeah, really sorry I didn't read every single word. I tend to focus on the code and the problem statement. Please forgive frustrating questions that you may have already answered - you could easily ignore them instead (in fact I removed the question from the answer before you replied). –  Aaron Bertrand Feb 15 '12 at 17:45

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.