> 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);
Thank you for the optimization hints – I changed the application that it uses
all three suggestions and many queries
are now executed faster. Although, not if the search string begins with a
wildcard char-acter as described in http:
//www.sqlite.org/optoverview.html#like_opt under point 2. I assume this causes
again most of the database to be loaded
into cache as this was the case with the LIKE opera-tor.
> Are you sure that the memory is not freed? Calling free() does not
> normally return memory to the operating system so just because the
> process memory usage went up does *not* mean that the memory is still
> in use. It might just mean that the memory is being held by the
> malloc()/free() for possible reuse later. What does the
> sqlite3_memory_used() interface tell you? What about
> sqlite3_memory_highwater()?
I wrote two small example programs to illustrate the memory usage difference
between working on the main database and
working with multiple ATTACHed database. The example with just one main
database does not consume more than ~300KB
which seems to be pretty close to the specified 'cache_size'. The second
example with three attached database consumes
around 500KB per query and it looks like 'cache_size' setting is not relevant.
All memory seems to be freed correctly.
--- Example A ---
--- One database only, cache size limited to 250 pages ---
long rc = SQLITE_OK;
sqlite3_int64 mem01;
sqlite3_int64 mem02;
// Open database connection
sqlite3* m_pDB;
sqlite3_open("job01.db", &m_pDB); // application memory usage: 1.5 MB
// PRAGMA
sqlite3_stmt* pStmtPragma01(NULL);
rc = sqlite3_prepare_v2(m_pDB, "PRAGMA cache_size=250", -1, &pStmtPragma01,
NULL);
rc = sqlite3_step(pStmtPragma01);
rc = sqlite3_finalize(pStmtPragma01);
// Run queries
sqlite3_stmt* pStmtLike01(NULL);
rc = sqlite3_prepare_v2(m_pDB, "SELECT COUNT(fs_recid) FROM main.fs_main
WHERE fs_itemtype=10 AND fs_textid GLOB
'*1';", -1, &pStmtLike01, NULL);
mem01 = sqlite3_memory_used(); // 17937 ...application memory
usage: 1.8MB
rc = sqlite3_step(pStmtLike01);
mem01 = sqlite3_memory_used(); // 305725 ...application memory
usage: 2.0MB
rc = sqlite3_finalize(pStmtLike01);
mem01 = sqlite3_memory_used(); // 302855 ...application memory
usage: 2.0MB
mem02 = sqlite3_memory_highwater(1); // 305757
// Close database connection
sqlite3_close(m_pDB); // application memory usage: 1.5 MB <- all memory
is freed correctly
--- Example B ---
--- :memory: database and three attached databases, cache size limited to 250
pages ---
long rc = SQLITE_OK;
sqlite3_int64 mem01;
sqlite3_int64 mem02;
// Open database connection
sqlite3* m_pDB;
sqlite3_open(":memory:", &m_pDB); // application memory usage: 1.5 MB
// PRAGMA
sqlite3_stmt* pStmtPragma01(NULL);
rc = sqlite3_prepare_v2(m_pDB, "PRAGMA cache_size=250", -1, &pStmtPragma01,
NULL);
rc = sqlite3_step(pStmtPragma01);
rc = sqlite3_finalize(pStmtPragma01);
// ATTACH job databases
sqlite3_stmt* pStmtAttach01(NULL);
rc = sqlite3_prepare_v2(m_pDB, "ATTACH 'job01.db' AS _job01;", -1,
&pStmtAttach01, NULL);
rc = sqlite3_step(pStmtAttach01);
rc = sqlite3_finalize(pStmtAttach01);
sqlite3_stmt* pStmtAttach02(NULL);
rc = sqlite3_prepare_v2(m_pDB, "ATTACH 'job02.db' AS _job02;", -1,
&pStmtAttach02, NULL);
rc = sqlite3_step(pStmtAttach02);
rc = sqlite3_finalize(pStmtAttach02);
sqlite3_stmt* pStmtAttach03(NULL);
rc = sqlite3_prepare_v2(m_pDB, "ATTACH 'job03.db' AS _job03;", -1,
&pStmtAttach03, NULL);
rc = sqlite3_step(pStmtAttach03);
rc = sqlite3_finalize(pStmtAttach03);
// Run queries
sqlite3_stmt* pStmtLike01(NULL);
rc = sqlite3_prepare_v2(m_pDB, "SELECT COUNT(fs_recid) FROM _job01.fs_main
WHERE fs_itemtype=10 AND fs_textid
GLOB '*1';", -1, &pStmtLike01, NULL);
mem01 = sqlite3_memory_used(); // 42568 ...application memory
usage: 1.8MB
rc = sqlite3_step(pStmtLike01);
mem01 = sqlite3_memory_used(); // 526932 ...application memory
usage: 2.3MB
rc = sqlite3_finalize(pStmtLike01);
mem01 = sqlite3_memory_used(); // 524058 ...application memory
usage: 2.3MB
mem02 = sqlite3_memory_highwater(1); // 526964
sqlite3_stmt* pStmtLike02(NULL);
rc = sqlite3_prepare_v2(m_pDB, "SELECT COUNT(fs_recid) FROM _job02.fs_main
WHERE fs_itemtype=10 AND fs_textid
GLOB '*1';", -1, &pStmtLike02, NULL);
mem01 = sqlite3_memory_used(); // 526656 ...application memory
usage: 2.3MB
rc = sqlite3_step(pStmtLike02);
mem01 = sqlite3_memory_used(); // 965624 ...application memory
usage: 2.8MB
rc = sqlite3_finalize(pStmtLike02);
mem01 = sqlite3_memory_used(); // 965624 ...application memory
usage: 2.8MB
mem02 = sqlite3_memory_highwater(1); // 965656
sqlite3_stmt* pStmtLike03(NULL);
rc = sqlite3_prepare_v2(m_pDB, "SELECT COUNT(fs_recid) FROM _job03.fs_main
WHERE fs_itemtype=10 AND fs_textid
GLOB '*1';", -1, &pStmtLike03, NULL);
mem01 = sqlite3_memory_used(); // 965348 ...application memory
usage: 2.8MB
rc = sqlite3_step(pStmtLike03);
mem01 = sqlite3_memory_used(); // 1285588 ...application memory
usage: 3.1MB
rc = sqlite3_finalize(pStmtLike03);
mem01 = sqlite3_memory_used(); // 1282714 ...application memory
usage: 3.1MB
mem02 = sqlite3_memory_highwater(1); // 1285620
// Close database connection
sqlite3_close(m_pDB); // application memory usage: 1.5 MB <- all memory
is freed correctly
Questions:
- > Is there one cache per database connection or one cache per ATTACH'ed
database?
- Is it true, that 'cache_size' does not limit the total memory used by SQLite
correctly when ATTACH is used?
- How can I limit the total amount of memory that is used by SQLite event if I
work with ATTACH?
Regards
Daniel
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
