Our e-mails crossed on the way to the list... sorry for the confusion.
Thank you for your advices - i'll follow up them to make the mentioned wildcard
search working.
Basically, I'm looking for a way to have a fast wildcard search on a dataset
that is distributed over multiple
databases whose are attached to a main database. I hope I can do it with GLOB.
At the moment, the question is still unanswered, why LIKE consumes multiple
times 2.5MB when applied to attached
dabases.
Daniel
----Ursprüngliche Nachricht----
Von: [EMAIL PROTECTED]
Datum: 15.08.2008 16:30
An: "General Discussion of SQLite Database"<sqlite-users@sqlite.org>
Betreff: Re: [sqlite] LIKE operator and ATTACH databases memory usage
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
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
