1

I am a Developer seeking help in terms of DB technology for a specific requirement. I have done some research but can not conclude with list.

Requirement: There will be a web application which will allow user to upload Excel document in specified format(expecting 1 Million of rows) and it will then Save somewhere in DB and run some calculation against our data base values. After that will present user with some numbers/chart based on calculations.

  • My first question is MySQL DB good enough for this requirement over any other DB like Postgres?

  • Second question is how much time will it take to upload 1 million rows of data from Excel, considering there will be 6 columns?

  • Third, which technologies should I consider to make this application fast and efficient like InnoDB, etc.

Once again I have done my research but I could not reach into conclusions, and any DB expert feedback/help will be highly appreciated.

Improve this question
7
  • 1
    Your requirements are quite vague. For instance, how many concurrent users will be uploading data to the server? You say each .xls will have 1,000,000 rows and 6 columns, but what data is in each column? A number or a very long string? How fast is the connection from the Internet to the server? Is the server behind a firewall? What is "good enough"?
    – Hannah Vernon ♦
    Commented May 7, 2014 at 19:15
  • 1
    Does the data need to be kept after the calculations are done? If not, do you really even need to use a DBMS?
    – Colin 't Hart
    Commented May 7, 2014 at 19:25
  • @MaxVernon There will be ID of 10 digit in one column and other will be mostly string and it will not be very long string. Not sure about connection from internet from server but server has good hardware components.
    – webmajic
    Commented May 7, 2014 at 20:57
  • @Colin'tHart Data need to kept after the calculations are done.
    – webmajic
    Commented May 7, 2014 at 20:58
  • @MaxVernon - There will be no concurrent users and only admin will be uploading data at a time.
    – webmajic
    Commented May 7, 2014 at 20:58

3 Answers 3

Reset to default
2

I can speak for Postgres. PostgreSQL is really fast to copy to if you are a super user. Postgresql has a copy to command. Merely save the file as a csv (which can also be opened in excel) and use:

COPY table FROM 'fpath' CSV HEADER DELIMITER ';'

You can copy anything with COPY (SELECT STATEMENT) TO 'fpath' as well.

If you must use excel formats like .xlsx, why not use something like Pentaho and develop an application that can connect to any database. There is a java api for this one. Other ETL tools may do the trick too.

In these instances, upload speed is under 10 minutes. My last upload over a non-commercial network of 900000 rows lasted 20 minutes in Pentaho. A straight copy to in PostgreSQL is actually much faster.

As for speed, querying is your major problem. If you are just copying and querying, Postgres is fine. However, if you are running intensive queries, counts; aggregation, use an ETL tool like Pentaho that you can run from Java and connect with a connection pool like BoneCP for Postgres or another connection pooling software for other databases.

Most databases support connection pooling but have connection limits so look at your configuration settings.

Improve this answer
2
  • -Thank you for your comment. I am not very familiar with Postgres, but do you see any downside of using mySQL is this scenario ?
    – webmajic
    Commented May 8, 2014 at 17:52
  • You can use a copy to like command with tech-recipes.com/rx/2345/import_csv_file_directly_into_mysql. If you do not need to edit data later to make sure they conform to your standards outside of a trigger (since 5.0.2), using PLPSQL, maybe MySQL is best. MySQL is light and fast until your loads are large, as with 1 million records. See: odesk.com/blog/2009/06/postgresql-vs-mysql. We process 100,000+ records regularly and use PostgreSQL. It is really fast at large loads and maintains integrity well.
    – Andrew Scott Evans
    Commented May 8, 2014 at 18:04
0

  1. MySQL can handle millions of records just fine and is one of the most prevalent databases available, so you will find plenty of support and tools on the web for it. There is little performance difference between MySQL and other databases like PostgreSQL (another fine Database).

  2. I regularly use http://excel2mysql.net to automate the import of 1 million+ records (I am the author). 1 million records with 6 columns on a reasonably fast machine and server connection should take less than 5 minutes to import. I have a video that demonstrates it loading 10,000 records in less than 2 seconds at http://youtu.be/e5JU6Fn_JYc

  3. Innodb is 1 of the 2 database engines available within MySQL. Generally it is best to use Innodb because it provides row locking, which helps prevent concurrent update errors and such. MyISAM performance isn't much faster than Innodb in most situations and I wouldn't use it unless I had a reason.

I would simply use excel2mysql to import the excel data into your MySQL database and then run your program on the server to do your calculations. The server program can be automatically triggered by many different techniques. An http php script for example. Or the program could simply monitor the MySQL table for updates and then take action.

Improve this answer
-1

I have used this script called File Big Dump, works really well for CSV/Excel files to be uploaded.

http://www.ozerov.de/bigdump/

Improve this answer
1
  • I have no connection with bigdump, I have just used it a few times.
    – MoJo
    Commented Nov 11, 2014 at 19:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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