I have a database table in which each row is one movie (with fields like ‘title’, ‘director’ and ‘writer’), and a related table where each row is one screenshot from a movie (with the fields ‘movie_id’ and ‘jpeg_data’). Each movie has 2-8 screenshots.
I want to output data in the order
- Movie 1
- Screenshot 1
- Screenshot 2
- Movie 2
- Screenshot 1
- Screenshot 2
- Screenshot 3
- Screenshot 4
Now the simplest solution I’ve found is to do (a rough pseudocode):
for movie in database.select id,title,director from movies
print movie.title, movie.director
for screenshots in database.select screenshots where movie_id = id
print screenshots.screenshot
This works — but it’s a nested loop where the inner loop is making a database call. This would make ~2,800 database calls with my current dataset, which seems terribly slow and inefficient.
There must be a better solution, but I can’t really think of one. I’d like to select all movies, and select all screenshots, and then find some way of iterating through the two arrays while keeping them in sync with each other, if that’s the right word.
What is the correct/optimal thing to do in this case?
5
After some comments and reading the question again I came to a core realization – you have an XY problem:
and a related table where each row is one screenshot from a movie (with the fields ‘movie_id’ and ‘jpeg_data’)
I do see a performance hit, yeah; even with only 12 images on my locally-hosted webpage I’m seeing a 5-second loadtime as it fetches the images.
The web browser is intended to go and fetch all the data as fast as it can. When rendering the page, it would often (not always) prefer to do lots of small fetches of static data (like images) rather than one big fetch. The one big fetch – it has to wait a bit until it can start rendering.
This is likely the core problem. Not the iterating over the loops, but rather that you have to iterate over the loop to get the jpeg data.
Don’t store the image in the database. Store the image as a static file that the web server can access as a static file. Store the file name or path to the file in the database.
Trying to pull large (not consistently sized) binary things out of a database isn’t what its designed for or good at – thats what filesystems are good at.
Related:
- Should heavy binary files not be stored in database?
- Store images in the database, or in files with a database link?
Setting that aside, you’ve got two result sets. You can:
-
Use the database (if it supports it) to store the concatenated information (file name) into one column.
select M.id, M.moviename, group_concat(S.filename) from movie M join screenshots S on (S.movieid = M.id) group by M.id -
Just fetch all the data as one joined structure (why get two result sets in the first place?)
select M.id, M.moviename, S.filename from movie M join screenshots S on (S.movieid = M.id) -
Fetch all the data and put it into a nice structure in your program and iterate over that.
public class Movie { int id; String name; List<String> files; } -
Iterate over the result set and when you get to the next movie, backup.
for movie in moviesCursor print movie.title, movie.director for screenshots in screenShotCursor if screenshot.movieId != movie.id screenShotCursor.previous break print "<img src="screenshots.fileName">"
Some of these may be limited by your choice of language and database.