Memory usage for requested rows from database
-
When you are running in Java:
ResultSet rs = stmt.executeQuery("SELECT Column FROM Table WHERE Condition");
Does the database create in memory a list with all the rows/values that have the given condition, and it is keeping that list until the
rsis close? Or it will only keep the first value, and move to the next value when rs.next() is called? I'm asking this because you can give it the command toFETCH NEXT X ROWS ONLY, and in case you have a database with millions of entries, that would save time and resources when you only want X rows, and not all of them that match the given condition. Or theFETCHis only a hard limit to know when to stop with sending the rows/values one by one? -
When you are running in Java:
ResultSet rs = stmt.executeQuery("SELECT Column FROM Table WHERE Condition");
Does the database create in memory a list with all the rows/values that have the given condition, and it is keeping that list until the
rsis close? Or it will only keep the first value, and move to the next value when rs.next() is called? I'm asking this because you can give it the command toFETCH NEXT X ROWS ONLY, and in case you have a database with millions of entries, that would save time and resources when you only want X rows, and not all of them that match the given condition. Or theFETCHis only a hard limit to know when to stop with sending the rows/values one by one?Valentinor wrote:
SELECT Column FROM Table WHERE Condition
First keep in mind that is SQL. It is sent as is to the database.
Valentinor wrote:
Does the database create in memory a list with all the rows/values that have the given condition
That is not how the jdbc is defined to work. But a bad driver could definitely do that. There is (or was) a jdbc/odbc 'text' driver and presumably it could have loaded everything into memory. But that was basically a toy anyways. The next() technically loads it. But drivers I have actually looked at usually use some sort of batch request that might load 10, 100, etc, and then wait for the next to pull from the local set. I think I even recall somewhere that can be adjusted. The initial set as somewhat low value.
Valentinor wrote:
FETCH NEXT X ROWS ONLY
How you do that depends on the database (see above). But every query you write should always be constrained (the where clause) and most should have a limit on the number of rows. The result should be paged. Only time that would not be true when you know for a fact that the target table has an extraordinarily low growth factor. Only ones I know for sure like that are application enumeration tables. Table examples where you should ALWAYS include the paging mechanism are things like customers, users, orders, events, etc.
Valentinor wrote:
database with millions of entries, that would save time and resources when you only want X rows,
That is a complex statement and there is no simple answer. You should NEVER, for example, allow for a design where a UI could expect a user to view 'millions' of rows. As a developer if you see a requirement like that you should insist that the requirements be changed to include reasonable constraint restrictions. Some examples 1. Require a date range, account number, customer store, etc. 2. Do a count(*) with the constraints in place and throw an error if the result is larger than X (say 10,000) which would then require the user (in the UI) to add more constraints to narrow the result. And such queries should ALWAYS require a page and row size. So for example 'page 5', 'size 20'. The backend then restricts (hard code) the max size to something like 1000. Additionally the users (whoever they are) might claim that the
-
Valentinor wrote:
SELECT Column FROM Table WHERE Condition
First keep in mind that is SQL. It is sent as is to the database.
Valentinor wrote:
Does the database create in memory a list with all the rows/values that have the given condition
That is not how the jdbc is defined to work. But a bad driver could definitely do that. There is (or was) a jdbc/odbc 'text' driver and presumably it could have loaded everything into memory. But that was basically a toy anyways. The next() technically loads it. But drivers I have actually looked at usually use some sort of batch request that might load 10, 100, etc, and then wait for the next to pull from the local set. I think I even recall somewhere that can be adjusted. The initial set as somewhat low value.
Valentinor wrote:
FETCH NEXT X ROWS ONLY
How you do that depends on the database (see above). But every query you write should always be constrained (the where clause) and most should have a limit on the number of rows. The result should be paged. Only time that would not be true when you know for a fact that the target table has an extraordinarily low growth factor. Only ones I know for sure like that are application enumeration tables. Table examples where you should ALWAYS include the paging mechanism are things like customers, users, orders, events, etc.
Valentinor wrote:
database with millions of entries, that would save time and resources when you only want X rows,
That is a complex statement and there is no simple answer. You should NEVER, for example, allow for a design where a UI could expect a user to view 'millions' of rows. As a developer if you see a requirement like that you should insist that the requirements be changed to include reasonable constraint restrictions. Some examples 1. Require a date range, account number, customer store, etc. 2. Do a count(*) with the constraints in place and throw an error if the result is larger than X (say 10,000) which would then require the user (in the UI) to add more constraints to narrow the result. And such queries should ALWAYS require a page and row size. So for example 'page 5', 'size 20'. The backend then restricts (hard code) the max size to something like 1000. Additionally the users (whoever they are) might claim that the
jschell wrote:
That is not how the jdbc is defined to work.
OK, good. I'm the only developer for now, so I'm working on everything, and DB/SQL isn't something I'm good at, but I don't want to make a bad DB when it comes to performance.
jschell wrote:
But a bad driver could definitely do that.
I'm using Oracle JDBC driver 11, so I would say that is out of the question.
jschell wrote:
The result should be paged.
Is this a good example of paging?[^] Unfortunately, that isn't something I know of.
jschell wrote:
You should NEVER, for example, allow for a design where a UI could expect a user to view 'millions' of rows.
Oh, no way that. By that I meant something else, but you answered it here:
jschell wrote:
That is not how the jdbc is defined to work. But a bad driver could definitely do that.
Thank you for all the info!
-
jschell wrote:
That is not how the jdbc is defined to work.
OK, good. I'm the only developer for now, so I'm working on everything, and DB/SQL isn't something I'm good at, but I don't want to make a bad DB when it comes to performance.
jschell wrote:
But a bad driver could definitely do that.
I'm using Oracle JDBC driver 11, so I would say that is out of the question.
jschell wrote:
The result should be paged.
Is this a good example of paging?[^] Unfortunately, that isn't something I know of.
jschell wrote:
You should NEVER, for example, allow for a design where a UI could expect a user to view 'millions' of rows.
Oh, no way that. By that I meant something else, but you answered it here:
jschell wrote:
That is not how the jdbc is defined to work. But a bad driver could definitely do that.
Thank you for all the info!
-
When you are running in Java:
ResultSet rs = stmt.executeQuery("SELECT Column FROM Table WHERE Condition");
Does the database create in memory a list with all the rows/values that have the given condition, and it is keeping that list until the
rsis close? Or it will only keep the first value, and move to the next value when rs.next() is called? I'm asking this because you can give it the command toFETCH NEXT X ROWS ONLY, and in case you have a database with millions of entries, that would save time and resources when you only want X rows, and not all of them that match the given condition. Or theFETCHis only a hard limit to know when to stop with sending the rows/values one by one?There's a phrase in TSQL parlance: "Row by Agonizing Row" (In order to give proper credit to the conspiracy that does the stabbing here I'd have to misquote someone so I'll just type in stuff which an eleventh grade high school teacher could type if she were quoting herself). Look up INDEX and think "I'll use INDEX, in 'automatic assign of an index' and just DROP the original index and reassign it after I do all the tabulation"
-
When you are running in Java:
ResultSet rs = stmt.executeQuery("SELECT Column FROM Table WHERE Condition");
Does the database create in memory a list with all the rows/values that have the given condition, and it is keeping that list until the
rsis close? Or it will only keep the first value, and move to the next value when rs.next() is called? I'm asking this because you can give it the command toFETCH NEXT X ROWS ONLY, and in case you have a database with millions of entries, that would save time and resources when you only want X rows, and not all of them that match the given condition. Or theFETCHis only a hard limit to know when to stop with sending the rows/values one by one?