2

I'm writing an application in C++ using libpq as the interface to postgresql. I'm currently putting together a small library of helper functions to help deal with the conversion from C++ to C for an application to be used on either a PC or an ARM device (2GHz 2GB ram).

With regards to pulling data from the database, I'm concerned as to whether I should pull all the results from the database at once, or use a cursor to read one or a few at a time.

The returned result set could probably be in the hundreds and I'm assuming:

  • if I pull all results, it'll be a memory hit for the application
  • if I pull one tuple at a time via a cursor, it could be a heavy burden on the database server (currently localhost for development)

I don't understand how I could obtain some suitable metrics to determine which would be best, so my questions are:

  1. Is there a best practice when it comes to retrieving results from the database in the real world (all at once / a row at a time / a few rows at a time)
  2. How would I determine some form of metrics to test efficiency? I'm guessing I'd have to take both application memory usage and database load into account along with response time.
  3. If it is best to pull a few tuples at a time, how do I determine the optimal amount to pull at once?
1

From our experience, cursors cause more trouble than they're worth. SQL is about result sets, and that's what you should be dealing with. The ideal is to decide on some result set size that optimises network traffic and application memory usage, and see if you can deliver within that limit. Scrolling list views have been doing this since forever.

  • For queries on an index you can WHERE and LIMIT.
  • For many (simpler) queries you can use OFFSET and LIMIT.
  • Test the optimiser -- this may work for complex queries too, but you don't want to execute the query twice.
  • Depending on your API, it may be possible to create a stored procedure and a temporary table.

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.