[Python] Optimizing Python Batch Process for Large Excel Files (5M+ Rows) to Reduce Processing...

I am working on a Python batch processing task that involves reading large Excel files (up to 5 million rows) and performing various operations on each row. Despite optimizing the database queries and leveraging multiprocessing, the total processing time is still around 3 hours for a 5-million-row file.

Current Setup: Data Handling:

Using pandas to read and preprocess Excel files. Files are divided into chunks for parallel processing using Python's multiprocessing module. Database Operations:

Database has tables with over 1 million rows. Queries are optimized and execute in ~20 minutes in total. Indexed tables and efficient joins are used. Processing:

Each row in the Excel file undergoes validation and transformations. Some rows involve additional logic or lookups in the database. CPU-bound operations dominate due to row-level processing logic. Environment:

Python 3.10. Multiprocessing with 8 cores. Database: MySQL with optimizations applied. Problem: The remaining ~2.5 hours are consumed by row-level processing in Python. For smaller files (e.g., 100k rows), it takes about 30 minutes, which is still too slow.

Continue reading...