What exactly is a database result set?

Factual Questions
1

I understand that a result set represents the results of a query, but what is it about a result set that forces you close the query (I am specifically thinking of JDO here, but I am sure there are analogs in other implementations)? It is my understanding that a result set is not merely some snapshot of the queried data, but rather a resource that must be released. What exactly is it? If the answer varies by database, then what is it generally?

Thanks,
Rob

2

As I understand it, though I only work with SQL, you’re posting the results to memory as an object then your code can interact with the object. If you don’t destroy the object when you’re done using it, objects “build up” in memory until memory clears them out, which isn’t the optimal way to treat your server. If you create the results object, get what you need, then destroy the object you’ve made but a blip on the memory and it’s now free to do other stuff.

3

There is more to it than that. In languages like Java, the JVM will take care of allocated memory in a reasonably efficient way as long as you let the variable go out of scope. In JDO, you have to call query.closeAll() when you are done the the results. I am not sure what the equivalent is in other systems like Hibernate, but I was told that the result set is not just some snapshot of the database.

Thanks for your help,
Rob

4

In some cases (as you note, different db’s do it differently), the result set is stored in a special temp area on the database server itself, and what you have in your client app is actually a live pointer to that space. It gets complicated, but there are reasons for this. Check out Oracle cursors for an example of what I’m talking about.

But the short, simplistic answer is, it’s often still a live connection to a chunk of resources that has been set aside by the data server just for you. Close the resultset and you’re telling the data server that it can toss it.

5

So then a result set is a snapshot of the queried data that is cached on the rdbms host and only a pointer to this set is returned to the client?

Thanks for your help,
Rob

6

A little bit more info, in a very generic sense because (as many have pointed out) each DBMS does things differently:

  • in general you don’t want to send really large data sets over the wire. If your query returned 2 Billion rows, most DBMSs/languages are smart enough to feed them to you a bit at a time.

  • one thing that hasn’t been mentioned is DB security and locking levels. If you get a block of data, and in the meantime someone else changes the data your looking at, what should the DB do? Conversely, if you have data, sit on it for a few minutes, then start making changes, what do other people who are accessing the DB see? There’s a lot of different answers to these questions, and modern databases (thankfully) hide a lot of the details from you, but if you’re coding Big Systems you still have to think about this to optimize queries and updates well. This is part of the reason why DB connections need to be explicitly closed - depending on what the you’re doing, the DB is not just handing you a bunch of data and saying “see ya!”

  • and thirdly, more and more lately, the answer is “they don’t” Modern languages have nice modern garbage collectors that make it so you don’t have to close nothin’ if you don’t want to.