0

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?

  • What type of database is it? Can you join all the screenshots when you pull the movies? Or if you don't want all the rows, a group_concat? When you pull the screenshots out, are they in the same order as the movies? Are you storing the results in a structure? or iterating over the result set? – user40980 Dec 3 '14 at 23:14
  • It's a SQLite 3 database, which returns a simple array of arrays (each element is an array representing a row, each element within that array is a field on the row). I'm unfamiliar with group_concat, I'll research that now. The screenshots are in the same order as the movies, but there are varying amounts of them (2-8 per movie). – GreenTriangle Dec 3 '14 at 23:22
  • With SQLite, do you actually see a performance issue? You're not going over the net to some limited pool of connections. Have you looked at using a profiler to see how long the multiple queries take (prepare the statement) vs. how long fetching all the data to a local data structure? – user40980 Dec 3 '14 at 23:25
  • 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. But there may be other reasons for that... I'm running it as a web app in the Sinatra framework purely to display the images from the DB, so maybe I need to find a non-webapp way to create views. Should learn to make a GUI but in Ruby that seems tough and that's getting into a whole other issue -- plus, this keeping-result-sets-in-sync seems like a common problem, where it's good to know the correct solution. – GreenTriangle Dec 3 '14 at 23:50
  • "this keeping-result-sets-in-sync seems like a common problem" – Actually, if that is a problem, it seems more like a flaw in the data model somewhere. It really shouldn't be a problem. The database should be in charge of the keeping-in-sync, at least if the database has some notion of "relation" (which a SQL DB does) or "link" (e.g. in a graph DB) or "collection" (e.g. lists in Redis). – Jörg W Mittag Dec 4 '14 at 0:38
4

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:


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.