How to import data to analyse it the fastest way

I just have a question which way gives me more performance and would be easier to get done. We have a DB with over 120000 datarows which is stored in a database. These data is currently exported as CSV file to an ftp location.

Now from this csv file there should be a webform created to filter the datasets. What would you recommend regarding performance and work todo. Should I parse the csv file and get the information out to the webpage or should I reimport the csv file to a DB (MySQL) and use SQL queries to filter the data (Note: The original DB and export is on a different server than the webpage/webform.)

A direct connection to the DB on the original server is not possible. I prefer reuploading it to a DB, because it makes the development easier, I just simply need to create the SQL query against the filter criteria entered in the webform and run it.

Any ideas?

Thanks... WorldS开发者_如何学JAVAignia

The database is undoubtedly the best answer. Since you are looking to use a web form to analyze the results and perform complex queries, the other alternative may prove VERY expensive in terms of server processing time, and quite more difficult to implement. After all, on the one hand you have SQL that handles all filtering details for you, and on the other you will have to implement something yourself.

I would advise, performance - wise, that you create indices for all fields that you know you will be using as criteria, and to display results partially, say 50 per page to minimize load times.

These data is currently exported as CSV file to an ftp location.

There are so many things wrong in that one sentence.

Should I parse the csv file and get the information out to the webpage

Definitely not.

While it is technically possible, and will probably be faster given the number of rows if you use the right tools this is a high risk approach which gives a lot less clarity of code. And while it may meet your immediate requirement is it rather inflexible.

Since the only sensible option is to transfer to another database, perhaps you should think about how you can do this

1. without using FTP
2. without using CSV

What happens to the data after it has been filtered?

I think the DB with indexes may be a better solution in case you need to filter the data. Actually this is the idea of DB to optimize your work with data. But you could profile you work and measure the performance. Then you just choose..

hmm good question.

i would think the analysis with a DB is faster. You can set Indizes and optimize the analysis. But it could take some time to load the CSV into the Database.

To analyse the CSV without a Db it could take some time. You have to create a concrete algorithm and this may be a lot of work :)

So I think u have to proof it both and take the best performance... evaluate them ;-)