On Aug 15, 2008, at 8:06 AM, [EMAIL PROTECTED] wrote:
> Hello
>
> Why does SQLite consume 2.5MB memory every time when running a
> statement on a attached database with LIKE operator?
>
> Example 1:
> SELECT fs_rec FROM fs_main WHERE fs_itemtype=? AND fs_textid
> LIKE ?; // consumes <50kB RAM
>
> Example 2:
> SELECT fs_rec FROM _job01.fs_main WHERE fs_itemtype=? AND fs_textid
> LIKE ?; // consumes 2.5MB RAM
(Note to mailing list readers: Daniel sent me a sample database by
private email....)
The database is about 3MB in size and the example 2 query is probably
doing something close to a full table scan. This causes most of the
database to be loaded into cache. That will use about 2.5MB of RAM.
The cache will flush itself automatically when you close the database
connection or when the cache becomes stale.
Depending on what you are storing in fs_textid and what your LIKE
pattern is, you might get much better performance (and lower memory
usage) if you use GLOB instead of LIKE and if you explicitly code the
pattern rather than using the wildcard "?", and if you create a new
index:
CREATE INDEX newidx1 ON fs_main(fs_itemtype, fs_textid);
See http://www.sqlite.org/optoverview.html#like_opt
D. Richard Hipp
[EMAIL PROTECTED]
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
