[yunqa.de] SQLite3 - EOutOfMemory when concatenating strings
- From: "Clyde England" <clyde@xxxxxxxx>
- To: yunqa@xxxxxxxxxxxxx
- Date: Thu, 05 Feb 2009 22:06:56 +0900
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
//www.freelists.org/list/yunqa
Other related posts:
