What is the most efficient way to insert thousands of records after a click event?_问答_开发者

Hai guys,

I ve developed a web application using asp.net 开发者_JS百科and sql server 2005 for an attendance management system.. As you would know attendance activities will be carried out daily.. Inserting record one by one is a bad idea i know,my questions are

Is Sqlbulkcopy the only option for me when using sql server as i want to insert 100 records on a click event (ie) inserting attendance for a class which contains 100 students? I want to insert attendance of classes one by one?

Unless you have a particularly huge number of attendance records you're adding each day, the best way to do it is with insert statements (I don't know why exactly you've got it into your head that this is a bad idea, our databases frequently handle tens of millions of rows being added throughout the day).

If your attendance records are more than that, you're on a winner, getting that many people to attend whatever functions or courses you're running :-)

Bulk copies and imports are generally meant for transferring sizable quantities of data and I mean sizeable as in the entire contents of a database to a disaster recovery site (and other things like that). I've never seen it used in the wild as a way to get small-size data into a database.

Update 1:

I'm guessing based on the comments that you're actually entering the attendance records one by one into your web app and 1,500 is taking too long.

If that's the case, it's not the database slowing you down, nor the web app. It's how fast you can type.

The solution to that problem (if indeed it is the problem) is to provide a bulk import functionality into your web application (or database directly if you wish but you're better off in my opinion having the application do all the work).

This is of course assuming that the data you're entering can be accessed electronically. If all you're getting is pieces of paper with attendance details, you're probably out of luck (OCR solutions notwithstanding), although if you could get muliple people doing it concurrently, you may have some chance of getting it done in a timely manner. Hiring 1,500 people do do one each should knock it over in about five minutes :-)

You can add functionality to your web application to accept the file containing attendance details and process each entry, inserting a row into your database for each. This will be much faster than manually entering the information.

Update 2:

Based on your latest information that it's taking to long to process the data after starting it from the web application, I'm not sure how much data you have but 100 records should basically take no time at all.

Where the bottleneck is I can't say, but you should be investigating that.

I know in the past we've had long-running operations from a web UI where we didn't want to hold up the user. There are numerous solutions for that, two of which we implemented:

take the operation off-line (i.e., run it in the background on the server), giving the user an ID to check on the status from another page.
same thing but notify user with email once it's finished.

This allowed them to continue their work asynchronously.

Ah, with your update I believe the problem is that you need to add a bunch of records after some click, but it takes too long.

I suggest one thing that won't help you immediately:

Reconsider your design slightly, as this doesn't seem particularly great (from a DB point of view). But that's just a general guess, I could be wrong

The more helpful suggestion is:

Do this offline (via a windows service, or similar)

If it's taking too long, you want to do it asynchronously, and then later inform the user that the operation is completed. Probably they don't even need to be around, you just don't let them do whatever functions that the data is needed, before it's completed. Hope that idea makes sense.

The fastest general way is to use ExecuteNonQuery.

internal static void FastInsertMany(DbConnection cnn)
{
    using (DbTransaction dbTrans = cnn.BeginTransaction())
    {
        using (DbCommand cmd = cnn.CreateCommand())
        {
            cmd.CommandText = "INSERT INTO TestCase(MyValue) VALUES(?)";
            DbParameter Field1 = cmd.CreateParameter();
            cmd.Parameters.Add(Field1);
            for (int n = 0; n < 100000; n++)
            {
                Field1.Value = n + 100000;
                cmd.ExecuteNonQuery();
            }
        }
        dbTrans.Commit();
    }
}

Even on a slow computer this should take far less than a second for 1500 inserts.

[reference]