Display large result set

Question

Hi,

This is my problem: I need to store a lot of log messages and thought it would be smart to keep it in a SQLite3 database to be able to search and filter it easily.

I will display the log messages in a standard list widget (using wxWidgets). The list will have several columns and can be sorted and filtered by the user.

Now, I'm not sure what is the best way to handle this. I'm thinking about some possible solutions:

1. Read all messages to memory. When there is a new or changed log message (at a random position in the list) the whole list will have to be refreshed. Same thing when the user wants to filter the list or sort on a different column.
2. Read all ID's into an array and retrieve the full log message on demand (when the user scrolls the list so that they are made visible).
3. Use the SQL-interface to fetch the results on demand, using SQL to select the exact sub-result that is required.

But really, I'm just not used to working with this kind of problem, so any tips are appreciated!

Answer 1

How about using pagination?

SELECT *
FROM logs
WHERE ...
ORDER BY ...
LIMIT offset, count

Where offset and count are values you can choose. You can use this to get any number of log entries. Then add a next button to allow the user to view the next page of entries. Combined with the ability to filter and a sort, the log search cannot be any easier.

Answer 2

Started writing this as a comment to Nadia's answer, but I started rambling and it got too long :)

Keep in mind that as the number of log entries gets very large, the scroll bar becomes useless. The tiniest movement in the scroll ends up moving through the list drastically. That makes it impractical for a user to use to search the list.

Think about why users will actually be looking through these entries and design around that. Very large lists aren't just hard to work with in code, they're also hard for the users to work with. I would probably give them some filter criteria and paged results.