Problem 1: Loading the movie dataset took 8.5 minutes
- Problem
- The loader imports six CSV files (genres, stars, movies, ratings and two link tables) into MySQL. The first version did, for every row, a SELECT to check for duplicates, another SELECT to check foreign keys, and then an INSERT. A full load took 507.8 s, and stars.csv alone took 384.5 s. Every row cost several database round trips, and CPU usage reached about 85%.
- What I did
- Optimization 1: before loading, read the keys already in the database into in-memory lookup tables (hash sets and maps) once, then check duplicates and foreign keys against memory instead of querying per row. Total time dropped to 21.5 s.
- Optimization 2: for the large, insert-heavy files, group INSERTs into JDBC batches so many rows share one round trip. Total time dropped to 4.74 s.
- Why this approach
- The bottleneck was the number of round trips, not the queries themselves. This task had no memory limit, so trading memory for fewer queries was the direct fix; batching then removed most of the remaining per-row round trips.
Total time to import the same dataset, by loader version (seconds)
- Baseline507.8 s
Per-row SELECT for duplicates + SELECT for foreign keys + INSERT
- Optimization 121.5 s
Existing keys preloaded into memory; checks run against memory
- Optimization 24.74 s
Optimization 1 plus batched JDBC INSERTs
Per-file timings (ms)
| File | Baseline | Optimization 1 | Optimization 2 |
|---|---|---|---|
| genres.csv | 284 | 155 | 118 |
| stars.csv | 384,525 | 4,264 | 722 |
| movies.csv | 77,163 | 2,553 | 486 |
| ratings.csv | 1,087 | 398 | 100 |
| genres_in_movies.csv | 6,357 | 2,178 | 336 |
| stars_in_movies.csv | 38,171 | 10,499 | 1,323 |
| Total | 507,772 | 21,498 | 4,736 |
Totals are the loader's own end-to-end timer, which includes setup work outside the six per-file timers, so they are slightly larger than the sum of the rows.
