Insert 100k rows in database from website

Question

Hi all,

I have a website where the user can upload an excel spreadsheet to load data in a table. There can be a few 100k rows in the excel spreadsheet. When he uploads the file the website needs to insert an equal amount of rows in a database table.

What strategy should i take to do this? I was thinking of displaying a "Please wait page" until the operation is completed but i want him to be able to continue browsing the website. Also, since the database at that time will be kind of busy - wouldn't that stop people from working on the website?

My data access layer is in NHibernate.

Thanks, Y

Answer 1

Displaying a please wait page would be pretty unfriendly as your user could be wating quite a while and would block threads on your web server.

I would upload the file, store it and create an entry in a queue (you'll need anouther table for this) to indicate that there is a file waiting to be processed. You can then have another process (which could even run on it's own server) which picks up tasks from this queue table and processes the xls file in it's own time.

Answer 2

I would create an upload queue that would submit this request to. Then the user could just check in on the queue every once in a while. You could store the progress of the batch operation in the queue as the rows are processed.

Also, database servers are robust, powerful, multi-tasking systems. Unless you have observed a problem with the website while the inserts are happening don't assume it will stop people from working on the website.

However, as far as insert or concurrent read/write performance goes there are mechanisms to deal with this. You could use the "INSERT LOW PRIORITY" syntax in MySQL or have your application throttle the inserts by sleeping a millisecond between each insert. Also, how you craft your insert statements, wether you use bound parameters or not, and wether you use multi-valued inserts can affect the insert performance and how it affects clients to a large degree.

Answer 3

On Submit you could pass the DB Operation to a asynchronous RequestHandler and set a Session Value when its done.

While the asynch process is in progress you can check the Session Value on each request and if it is set (operation = completed) display a message, eg in a modal or whatever message mechanism you have.