[yunqa.de] SQLite3 - EOutOfMemory when concatenating strings

I recently converted over an application that has thousands of users to SQLite3 
(With Delphi 5)

For the most part, things are working well. However, I kept getting some users 
reporting an EOutOfMemory - enough to be a real concern.

It nearly drove me nuts trying to track down this obscure problem, but I was 
finally able to track down the problem area. I was then able to duplicate the 
problem with similar code (but much smaller) in a stand alone test program.

Basically, the problem occurs when building a string in memory which is just 
the concatenation of a variable from several records of separate SQL Queries. 
However, it only happens when you apply the Pragma "PRAGMA cache_size = 20000"

Pragma "PRAGMA cache_size = 100000"  and Pragma "PRAGMA cache_size = 150000" 
will also throw the error. I didn't check all values, but I can confirm that I 
don't get the error when I remove this pragma all together.

I had originally put in this statment to help speed up the database, and my 
understanding is that this would add about 30mb (20000 x 1.5k) memory overhead 
to the application. I thought this to be quite small considering the amount of 
memory in computers these days. However, it appears this Pragma has a very 
strange effect on string concatenation in Delphi. Removing this Pragma (which I 
believe then uses the default value of 2000) no longer causes a problem.

The following is the small snipped of code that will produce the out of memory 
exception. Some extra notes that might help:

1. The sample code consistently throws the out of memory error after about 3000 
iterations
2. The "CacheMemo" Table being queried is nothing special, with the field 
(column_str16(1)) being text entries ranging from about 100 to 5000 bytes (5k)
3. The line of code that causes the "out of memory error" is: work := work + 
stmt.column_str16(1);
4. I have debugged the code and just before the crash have determined the size 
of the field "work" is only a  mere 3mb in size. Delphi string variables should 
be allowed to grow to 2gb
5. Removing the "PRAGMA cache_size = 20000" allows this code to run without 
crashing. In fact, I have run the code on a larger database with over 50,000 
entries and it runs OK. The moment I add ""PRAGMA cache_size = 20000", it 
crashes after about 3000 iterations again.

Why should this Pragma effect the way strings are concatenated and throw this 
error?

Is this a known bug/problem?

Thanks
Clyde


Sample code to produce the out of memory error:

var
x,i : integer ;
work : string;
stmt : tdisqlite3statement;
begin
x := 0;
work := '';
dbs.databasename := 'c:\temp\test.db3";
dbs.open;
dbs.execute('PRAGMA cache_size = 20000;');
while x < 10000  do
begin
  inc(x);
  stmt := dbs.prepare16('select * from cachememo where rowid = ' + inttostr(x));
  i := stmt.step;
  if i = 100 then
  begin
      work := work + stmt.column_str16(1);
   end;
   stmt.Free;
 end;



_______________________________________________
Delphi Inspiration mailing list
yunqa@xxxxxxxxxxxxx
http://www.freelists.org/list/yunqa

Other related posts: