PureBasic Forums - English

Hi,
I am processing all blob data in huge PostgreSQL table of 200K records.
so I select * from thisTable and process record up to last in table.
I receive "out of memory for query result" in DatabaseError()
I have no idea how to fix this problem and many thanx for replys.

Only a suggestion based on what works for me - others may have better ideas -

I'm using SQLite rather than PostreSQL but I'd imagine any system could get a memory problem if pulling a large number of BLOB rows.

Firstly - I split out the large BLOB data into a separate DB file and ATTACH it to the "main" DB which has other types of data tables. I don't know the technical reasons, but did some tests many years ago and found this gave better overall query performance for all tables.

In the BLOB DB file there are 2 tables - the BLOB data in a table of it's own with just a numeric key (the rowid) and the BLOB column itself. A 2nd table is maintained with exactly the same rowid key. This table also has any text or number data columns that relate to the BLOBs, but the rows are quite small. All processing is done on this smaller table and then when needed a single record query using the rowid handles the individual BLOB, one at a time.

ThanQ for reply,
for now I just select selectively from 1st element to half and then remaining.
But would be great if better solution.

This question is impossible to answer without more details on what the tables look like,
what data we're talking about (size) and how the database is configured.

Anyway:
- You do SELECT *...do you really need all rows? SELECT * is almost always a waste of resources.
- If you get a memory error on the server you may be able to fix this by throwing more at it.
Maybe your server isn't configured appropriately and you may just need to give it more memory to work with.

This could be a 32-bit\64-bit problem. 32-bit has a memory limitation.

Sveinung

Because of library is 32bit I cannot move to 64bit so I am oliged to stay in 32bit and need 32bit solution.
All records should be select and processed.

If you must use 32-bit, you will have problems with objects >1Gb. What lib is the one that is 32-bit?

Sveinung

Not only library but os running application also 32bit and device attached to it only has 32bit drivers.
Manufacturer isn't going to make 64 bit. Anyway it has to be 32bit.
The bottleneck is manufacturer drivers.
PureBasic is 5.44LTS 32 bit with DroopyHmod

2nd problem I have is that I couldn't find 64 bit droopy for Purebasic 5.44 LTS

What type of data is the blob(like a pictrue or something)? Can you export it to a file, using lo_export?

Sveinung

its binary file of 15kb
Again, on 64bit (not considering driver usage) everything OK.
On 32 bit processing with portioning again OK.
But may be there is a better way to handle all data in database faster than portioning.

I each time open connection, select portion, process, close connection, repeat until last record.

This may be the problem, however it is always best (from experience) to create an index for as many of your queries as possible!

The 'explain analyze' process if your friend!

Hi RichAlgeni,
ThanQ for reply.
The problem is solved by optimizing variables.
Somewhere inside code long needed and somewhere integer.
That was the problem causing buffer overflaw and memory being unusable.

LiK137 wrote:Hi RichAlgeni,
ThanQ for reply.
The problem is solved by optimizing variables.
Somewhere inside code long needed and somewhere integer.
That was the problem causing buffer overflaw and memory being unusable.

Very good! Always learn!