Django/Sqlite Improve Database performance

Question

We are developing an online school diary application using django. The prototype is ready and the project will go live next year with about 500 students. Initially we used sqlite and hoped that for the initial implementation this would perform well enough. The data tables are such that to obtain details of a school day (periods, classes, teachers, classrooms, many tables are used and the database access takes 67ms on a reasonably fast PC. Most of the data is static once the year starts with perhaps minor changes to classrooms. I thought of extracting the timetable for each student for each term day so no table joins would be needed. I put this data into a text file for one student, the file is 100K in size. The time taken to read this data and process it for a days timetable is about 8ms. If I pre-load the data on login and store it in sessions it takes 7ms at login and 2ms for each query. With 500 students what would be the impact on the web server using this approach and what other options are there (putting the student text files into a sort of memory cache rather than session for example?) There will not be a great deal of data entry, students adding notes, teachers likewise, so it will mostly be checking the timetable status and looking to see what events exist for that day or week.

Answer 1

What is your expected response time, and what is your expected number of requests per minute? One twentieth of a second for the database access (which is likely to be slow part) for a request doesn't sound like a problem to me. SQLite should perform fine in a read-mostly situation like this. So I'm not convinced you even have a performance problem.

If you want faster response you could consider:

1. First, ensuring that you have the best response time by checking your indexes and profiling individual retrievals to look for performance bottlenecks.
2. Pre-computing the static parts of the system and storing the HTML. You can put the HTML right back into the database or store it as disk files.
3. Using the database as a backing store only (to preserve state of the system when the server is down) and reading the entire thing into in-memory structures at system start-up. This eliminates disk access for the data, although it limits you to one physical server.