How to close an SQL database and regain its memory

[SarahWard]

Does anyone know how I can close a database and regain the memory resources that it was using?

I am not trying to close the connection. I need to completely close the database and its table and regain the resources it was using.

(I am aware that there is no way or forcing a device resources refresh, and that the operating system will do this in its own time.)

Sarah

 

[Erel]

Did you encounter an 'out of memory' exception?

 

[SarahWard]

The database uses much more internal memory than other features of the software. On some peoples' devices it does cause an out-of-memory error. I am trying to find a way to release those memory resources used by the sqlite database/table control,once it is no longer in use.

 

[agraham]

The database itself should not use much memory as it is disk based. You must be returning a lot of data from a query into a table to have to consider memory issues. Can you not refine the queries to return only the data immediately required?

 

[SarahWard]

The database itself should not use much memory as it is disk based. You must be returning a lot of data from a query into a table to have to consider memory issues. Can you not refine the queries to return only the data immediately required?

Yes. I get all the data into the table. That is 4100 records with maybe 20 fields per record. Then, when a paticular record is chosen from the table, I read the table's various fields to get the full data from that record.

Are you suggesting that I only load (say) the name/title of each record. Then, after the user has chosen a record from this field-limited list, I read that record's full data from the sqlite database? Is that what you mean by limiting the sql query and table size?

 

[mjcoon]

... That is 4100 records with maybe 20 fields per record. Then, when a paticular record is chosen from the table, I read the table's various fields to get the full data from that record.

Are you suggesting that I only load (say) the name/title of each record. Then, after the user has chosen a record from this field-limited list, I read that record's full data from the sqlite database?

All this reminds me that I don't think I ever got access to Sarah's tree database code... and that I didn't get far trying to convert one of my own table-control based programs to use SQL instead!

But it does sound as if you are using a visible table that contains much more data than is actually displayed. In which case reducing the table to include only the displayed fields, as Andrew suggests, should help.

An extreme case would be to abandon the handy and "intuitive" (meaning familiar) scrolling mechanism of a table control and limit the table to only a screen's worth of data of the name/title field(s). Then scrolling through more of the data would have to be done using a separate (set of) scroll control and re-populating the visible table from the SQL accordingly.

Erel is often telling us that SQL is much more efficient than a table control, so perhaps a "simulated scrolling" mechanism, as it might be described, could be quite generally useful; maybe someone has already written one?

Mike.

 

[agraham]

Are you suggesting that I only load (say) the name/title of each record. Then, after the user has chosen a record from this field-limited list, I read that record's full data from the sqlite database? Is that what you mean by limiting the sql query and table size?

Yes, that is how databases are meant to be used.

 

[glook]

Maybe it is possible to only select the 'rowid'? Then use it to retrieve each row as needed. This is very efficient as every SQLite table already has a rowid - even if you don't declare an Integer Primary Key. See SQLite Autoincrement

You can use very large databases this way.

 

[SarahWard]

trees

All this reminds me that I don't think I ever got access to Sarah's tree database code... .

Oh, I am so sorry, Mike. Here it is...

PocketTrees.rar - download now for free. File sharing. Software file sharing. Free file hosting. File upload. FileFactory.com

Trees has now been incorporated into my Aviasoft Nature Explorer 1 (Flora & Fauna) freeware program. Lots of new trees and photographs are part of that program. It it interests you I will post the source for it.

Sarah

 

[mjcoon]

Oh, I am so sorry, Mike. Here it is...

PocketTrees.rar - download now for free. File sharing. Software file sharing. Free file hosting. File upload. FileFactory.com

Trees has now been incorporated into my Aviasoft Nature Explorer 1 (Flora & Fauna) freeware program. Lots of new trees and photographs are part of that program. It it interests you I will post the source for it.

Thanks Sarah. Unfortunately that file fails to open with either WinZip or 7-Zip. It is 29,639 bytes as downloaded.

Maybe I'll just try out your new freeware program...

Mike.

 

[agraham]

It is 29,639 bytes as downloaded.

Sounds like you tried to right click and save on that link. Don't, it won't work. Go to it as a normal link to visit the download site.

 

[mjcoon]

Sounds like you tried to right click and save on that link. Don't, it won't work. Go to it as a normal link to visit the download site.

Thanks Andrew, that's not the 1st time I've been caught out by an indirect page with the same URL as the file I was expecting (and it doesn't get any more amusing)...

Mike.

 

[mjcoon]

An extreme case would be to abandon the handy and "intuitive" (meaning familiar) scrolling mechanism of a table control and limit the table to only a screen's worth of data of the name/title field(s). Then scrolling through more of the data would have to be done using a separate (set of) scroll control and re-populating the visible table from the SQL accordingly.

Erel is often telling us that SQL is much more efficient than a table control, so perhaps a "simulated scrolling" mechanism, as it might be described, could be quite generally useful; maybe someone has already written one?

After a bit of playing around with the SQL demo program I think that my suggestion is a non-starter except for a few very limited cases.

For instance, Geoffrey Looker mentions using the RowId column to start a transfer to Table control part way down, and the Maximum argument of ExecuteTable() could constrain the Table to a small number of rows. But this works only if the rows are wanted in RowId order, which will not be common.

I thought an alternative would be to use ExecuteReader instead, but since it only gives forwards access this would be one-way scrolling. Not to mention that the Table population process would have be explicitly programmed since a Reader does not have a ExecuteTable() method...

Mike.

 

[SarahWard]

Thanks Sarah. Unfortunately that file fails to open with either WinZip or 7-Zip. It is 29,639 bytes as downloaded.

Maybe I'll just try out your new freeware program...

Mike.

The file on my laptop opens ok so it might be a file upload - download problem. It was 1.53mb in size originally. I can post it again if you wish.

Nature Explorer 1 (v4.5) has more trees and additional photographs of trees. It also includes birds, fungi, wild flowers, butterflies & moths, too. If you find it useful I can supply the source code.

You can download NX1 or NX2 from my freeware site

Aviasoft Nature Freeware

Sarah

 

[mjcoon]

You can download NX1 or NX2 from my freeware site

Aviasoft Nature Freeware

That site is, since last night, giving:

Bandwidth Limit Exceeded

The server is temporarily unable to service your request due to the site owner reaching his/her bandwidth limit. Please try again later.

Mike.

 

[mjcoon]

After a bit of playing around with the SQL demo program I think that my suggestion is a non-starter except for a few very limited cases.

For instance, Geoffrey Looker mentions using the RowId column to start a transfer to Table control part way down, and the Maximum argument of ExecuteTable() could constrain the Table to a small number of rows. But this works only if the rows are wanted in RowId order, which will not be common.

I thought an alternative would be to use ExecuteReader instead, but since it only gives forwards access this would be one-way scrolling. Not to mention that the Table population process would have be explicitly programmed since a Reader does not have a ExecuteTable() method...

In http://www.b4x.com/forum/questions-help-needed/7897-search-pagination-error.html#post44666, Jothis has posted a sample program that uses the LIMIT and OFFSET SQL clauses to do exactly the sort of scrolling that I was suggesting (and that I thought that was "a non-starter"!). As I said, the Table population process would have be explicitly programmed, so he has done that too. I shall have a go at that myself, starting from the SQL demo. Unlike Jothis, I would limit the size of the table to however many rows will fit on the screen, without using scrolling within the table.

Mike.

P.S. I should not have said "the Table population process would have be explicitly programmed" because once the scrolling selection of the wanted rows has been made using LIMIT and OFFSET, the result can be loaded into a (short) table with ExecuteTable() just like an un-LIMITed SELECT.