Improving the performance of a db import process

Question

I have a program in Microsoft Access that processes text and also inserts data in MySQL database. This operation takes 30 mins or less to finished.

I translated it into VB.NET and it takes 2 hours to finish.

The program goes like this:

1. A text file contains individual swipe from a corresponding person, it contains their id, time and date of swipe in the machine, and an indicator if it is a time-in or a time-out.
2. I process this text, segregate the information and insert the time-in and time-out per row.
3. I also check if there are double occurrences in the database.
4. After checking, I simply merge the time-in and time-out of the corresponding person into one row only.

This process takes 2 hours to finished in VB.NET considering I have a table to compare which contains 600,000+ rows.

Now, I read in the internet that python is best in text processing, i already have a test but i doubt in database operation.

What do you think is the best programming language for this kind of problem?

How can I speed up the process? My first idea was using python instead of VB.NET, but since people here telling me here on PSE that this most probably won't help I am searching for different solutions.

Here is a link to the test i've done.

Answer 1

I find it very unlikely that changing the programming language will bring you any further. So apply the topmost rule of performance optimization:

Find the bottleneck by measuring the running time for the individual steps!.

It may be either the text processing, the DB operations or both. You did not show us any code, but in situations like this the DB operations would be the first suspect to me.

For example, you may consider to comment the DB operations out, leave only the text processing active and then measure and compare the running times.

Answer 2

I agree with Doc Brown that you should first try to find the bottleneck in your application, otherwise we are all only hypothesing and there's no real use to it. Anything could be suspect.

The first thing you could is to measure the duration of:

• the entire process, consisting in reading a line in the file, checking for duplicates, inserting a row
• the entire process again, but when you found a duplicate and as such, when you are not inserting a line

Also, please take measures of individual operations. How long does it take to read from the file on average ? How long does it take to check for duplicates ? How long does it take to insert a record ? Maybe the duration keeps growing along the size of the table ?

The only way to answer these questions is get the numbers.

Finally, I think there are several possible optimizations depending on the location of the bottleneck. For example, you could:

• read more than 1 line at once, say 10 for example, and dispatch the processing of these lines to 10 separate threads.
• more generally, separate the process of reading the file from writing to the database by using the publish/subscribe pattern.
• optimize indexes in your database table.
• instead of checking every time if there is no duplicate, maybe store the entire table in a map in memory before you start processing the file. This could take a few seconds (and some memory size) but you avoid one IO round trip per line.

To conclude, I think you are right about not changing your language, the langue is not the problem.