[sqlite] Shadow Pager
Has anyone implemented the shadow pager for sqlite??? Any info regarding this will be very helpful.. Thanks Sankarshana M __ Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers! http://promotions.yahoo.com/new_mail
[sqlite] SQLite "single writer"
A program that I wrote eventually continues to return SQLITE_BUSY when it tries to insert a new row into a table, even though I'm pretty sure that at each attempted time of insert (at least when it continually returns SQLITE_BUSY), there are no other SQL commands running. I inserted Sleep(500) after each SQLITE_BUSY return and tried the sqlite3_busy_timeout. I'm using the latest CVS verion of SQLite. There is a thread that inserts rows into tables. There is a thread that selects rows from tables and updates rows in those tables. There is a thread that deletes rows from tables. Each of the the commands (insert, select, update, and delete) always uses its own sqlite3* So, I started looking at the documentation and found that according to: http://www.sqlite.org/cvstrac/wiki?p=SqliteVersusDerby "SQLite allows ... a single writer." I have some questions about that statement. I would like to know if the following cases are supported by SQLite. Single SQLite Database File: Multiple processes accessing the same database file: 1) Each process may attempt to modify the file using SQLite. 2) Each process may attempt to modify the same table. 3) Each process may attempt to modify tables that the other processes may not modify Multiple threads accessing the same database file: 4) Each thread may attempt to modify the file using SQLite. 5) Each thread may attempt to modify the same table 6) Each thread may attempt to modify tables that the other threads may not modify Thank you for your time. Mike - Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers!
Re: [sqlite] Efficient select for multiple ids
Ken Cooper wrote: SELECT id, . FROM additionalchunks WHERE id=? OR id=? OR . or SELECT id, . FROM additionalchunks WHERE id in (? ? ? ? ? .) Which of these is the more efficient... The second, assuming you have an index on additionalchunks.id. Without an index, they are about the same. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] Improving Performance of INSERT INTO?
Roger Dant wrote: I didn't realize sqlite3 had transaction support! I must have missed it in the documentation -- off to re-read it. Sqlite2 has transaction support and I don't think it's been removed for sqlite3.
Re: [sqlite] Improving Performance of INSERT INTO?
Will Leshner <[EMAIL PROTECTED]> wrote: >Try doing all the INSERTs in a single transaction. I didn't realize sqlite3 had transaction support! I must have missed it in the documentation -- off to re-read it. Thanks. - Do you Yahoo!? vote.yahoo.com - Register online to vote today!
Re: [sqlite] Improving Performance of INSERT INTO?
On Wed, Sep 15, 2004 at 02:14:58PM -0700, Roger Dant wrote:
>
> Here's the slow code:
>
> sqlite3* db;
> CString sql;
> sqlite3_open("c:\\test.db", );
> sqlite3_exec(db, "PRAGMA SYNCHRONOUS", NULL, NULL, NULL);
> sqlite3_exec(db, "CREATE TABLE X (I LONG, J LONG)", NULL, NULL, NULL);
> for (int i = 0; i < 1000; i++) {
>for (int j = 0; j < 500; j++) {
> sql.Format("INSERT INTO X VALUES (%d,%d)", i, j);
> sqlite3_exec(db, sql, NULL, NULL, NULL);
>}
> }
> sqlite3_close(db);
Adding a BEGIN at the start and COMMIT at the end makes this test
complete in 14.5 seconds for me.
Cheers,
Matt
Re: [sqlite] Improving Performance of INSERT INTO?
Roger Dant wrote: Since INSERT INTO is the only way I know of to populate a table, I'm assuming that I'll have to find a way to optimize these calls. Do you have any tips for optimizing mass INSERTs in Sqlite? Try doing all the INSERTs in a single transaction.
[sqlite] Improving Performance of INSERT INTO?
Hi,
I'm working on a program that builds a table with 2 columns and up to 500,000 rows.
I'm using INSERT INTO to add each row, and the process takes 2 - 3 hours to finish.
Since INSERT INTO is the only way I know of to populate a table, I'm assuming that
I'll have to find a way to optimize these calls. Do you have any tips for optimizing
mass INSERTs in Sqlite?
I'm on a Windows XP system w/ 192 MB of RAM, and I built the library from the source.
I was able to benchmark enough to know the slowdown was the INSERTs themselves.
Here's the slow code:
sqlite3* db;
CString sql;
sqlite3_open("c:\\test.db", );
sqlite3_exec(db, "PRAGMA SYNCHRONOUS", NULL, NULL, NULL);
sqlite3_exec(db, "CREATE TABLE X (I LONG, J LONG)", NULL, NULL, NULL);
for (int i = 0; i < 1000; i++) {
for (int j = 0; j < 500; j++) {
sql.Format("INSERT INTO X VALUES (%d,%d)", i, j);
sqlite3_exec(db, sql, NULL, NULL, NULL);
}
}
sqlite3_close(db);
-
Do you Yahoo!?
vote.yahoo.com - Register online to vote today!
RE: [sqlite] Efficient select for multiple ids
The '.'s below should be ellipses. -Original Message- From: Ken Cooper [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 15, 2004 1:56 PM To: [EMAIL PROTECTED] Subject: [sqlite] Efficient select for multiple ids I am implementing a virtualized listview on the result of a query by first retrieving all unique ids for each row in the query result, then on demand looking up additional information based on the ids in small chunks, say 20 at a time. I was thinking the best way to approach this second query was to create and bind queries something like the following against the 20 ids I have at the time: SELECT id, . FROM additionalchunks WHERE id=? OR id=? OR . or SELECT id, . FROM additionalchunks WHERE id in (? ? ? ? ? .) Which of these is the more efficient way to approach the problem, or does someone have a better suggestion? I remember seeing something referring to this issue posted awhile back, but I'm having trouble locating it (googling on 'where' and 'or' and 'in' is rather painful.). Ken
[sqlite] Efficient select for multiple ids
I am implementing a virtualized listview on the result of a query by first retrieving all unique ids for each row in the query result, then on demand looking up additional information based on the ids in small chunks, say 20 at a time. I was thinking the best way to approach this second query was to create and bind queries something like the following against the 20 ids I have at the time: SELECT id, . FROM additionalchunks WHERE id=? OR id=? OR . or SELECT id, . FROM additionalchunks WHERE id in (? ? ? ? ? .) Which of these is the more efficient way to approach the problem, or does someone have a better suggestion? I remember seeing something referring to this issue posted awhile back, but I'm having trouble locating it (googling on 'where' and 'or' and 'in' is rather painful.). Ken
[sqlite] invalid btree cursor read locks honored?
I'm having problems with code that starts a transaction, creates a
table, commits, begins a new transaction, creates a temporary table,
inserts data into the temporary table, and the inserts into the table
the results of a select from the temporary table. The insert
statement results in a SQLITE_ERROR on sqlite3_step. This is because
there are two open btree cursors, one invalid read cursor, one write
cursor. I don't totally understand the significance of the isValid
flag of a btree cursor, but if I ignore cursors with isValid == 0 in
checkReadLocks(), the attached test case succeeds.
Current HEAD CVS has 15 failures both with and without the attached
patch, but this is in a subtle area of the database, so I'm not
certain this is the right fix.
Cheers,
Matt
#include
#include
#include
#include
void exec(sqlite3 *pDb, const char *sql) {
sqlite3_stmt *pStmt;
const char *zLeftover;
int rc;
rc = sqlite3_prepare(pDb, sql, -1, , );
if (rc != SQLITE_OK) {
printf("error occurred while preparing statement: %s\n",
sqlite3_errmsg(pDb));
abort();
}
assert(rc == SQLITE_OK);
assert(*zLeftover == '\0');
rc = sqlite3_step(pStmt);
if (rc != SQLITE_DONE) {
printf("error: sqlite3_step returned %d, expected %d.\n",
rc, SQLITE_DONE);
abort();
}
rc = sqlite3_finalize(pStmt);
assert(rc == SQLITE_OK);
}
int main(void) {
sqlite3 *pDb;
int rc;
rc = sqlite3_open(":memory:", );
assert(rc == SQLITE_OK);
exec(pDb, "BEGIN");
exec(pDb, "CREATE TABLE Dependencies(depId integer primary key,"
"class integer, name str, flag str);");
exec(pDb, "COMMIT");
exec(pDb, "BEGIN");
exec(pDb, "CREATE TEMPORARY TABLE DepCheck(troveId INT, depNum INT, "
"flagCount INT, isProvides BOOL, class INTEGER, name STRING, "
"flag STRING)");
exec(pDb, "INSERT INTO DepCheck "
"VALUES(-1, 0, 1, 0, 2, 'libc.so.6', 'GLIBC_2.0')");
exec(pDb, "INSERT INTO Dependencies \
SELECT DISTINCT \
NULL, \
DepCheck.class, \
DepCheck.name, \
DepCheck.flag \
FROM DepCheck LEFT OUTER JOIN Dependencies ON \
DepCheck.class == Dependencies.class AND \
DepCheck.name == Dependencies.name AND \
DepCheck.flag == Dependencies.flag \
WHERE \
Dependencies.depId is NULL");
exec(pDb, "ROLLBACK");
printf("success\n");
return 0;
}
Index: src/btree.c
===
RCS file: /sqlite/sqlite/src/btree.c,v
retrieving revision 1.189
diff -u -r1.189 btree.c
--- src/btree.c 8 Sep 2004 20:13:05 - 1.189
+++ src/btree.c 15 Sep 2004 18:54:31 -
@@ -3498,7 +3498,7 @@
static int checkReadLocks(Btree *pBt, Pgno pgnoRoot, BtCursor *pExclude){
BtCursor *p;
for(p=pBt->pCursor; p; p=p->pNext){
-if( p->pgnoRoot!=pgnoRoot || p==pExclude ) continue;
+if( p->pgnoRoot!=pgnoRoot || p==pExclude || p->isValid == 0) continue;
if( p->wrFlag==0 ) return SQLITE_LOCKED;
if( p->pPage->pgno!=p->pgnoRoot ){
moveToRoot(p);
Re: [sqlite] like-op
Low, indeed! It amazes me how ordinary citizens are always ready to jump in with inane remarks like that. As it turns out, LIKE is a lot smarter than I thought, e.g. this works: WHERE FLAGS LIKE '% % 1 %' As long as there are at least 3 blanks in each FLAGS value. I suspect that it would also find records where the 4th word was '1', but that will not be possible in my situation. -R. David Morel wrote: Le mer 15/09/2004 à 00:43, Peter a écrit : Rod Dav4is wrote: 'm reminded of the guy who wanted to buy a loaf of bread and was told how to build a bakery. ;O) Don't hesitate to contact customer services to arrange for your 30day no quibble refund. :-Þ THAT whas low. Actually, you could search the list archive, somebody already wrote such an extension, with astonishing results in terms of speed. I don't remember, I think it was 6 or 8 months ago. -- Regards, Rod Dav4is / P.O. Box 118 / Hyde Park, NY 12538 / USA Genealogy, et Cetera: http://freepages.rootsweb.com/~dav4is/ 391 ancestral & collateral families, mostly 17th - 19th century New England & European roots, total population: 89,400+ Annex: http://www.gencircles.com/users/dav4is/ email: [EMAIL PROTECTED]
[sqlite] Temproary File Size
Hi, I am be porting Sqlite3.0 for an embedded application. I need to initiate my file system with the space required for storing the database file and temporary files. The maximum size of the database file that could be used is 2MB (fixed and checks are done that this size does not exceed). I need details about the size requirement for TEMP files that are created by Sqlite. How could I estimate the temporary file size? Please provide me some information on how I could estimate the Temporary Database file size. If the temp file size will be huge (in excess of 500K), if I execute the command "PRAGMA temp_store = 0", the usage of temporary file be eliminated? (where #define TEMP_STORE 2 is used in the code to use memory for storing temp database). Please provide me details. Thank you, With regards, Sankara Narayanan B
[sqlite] sqlite on Cygwin ignoring Unix style paths
I have noticed this for sqlite 2.8.15 and 3.0.6 (both compiled under
Cygwin): sqlite doesn't recognise Unix style paths for opening databases
(but "-init unix_path" works). Cygwin itself supports both paths styles.
Symptoms:
[EMAIL PROTECTED] pwd
/home/tkampe
[EMAIL PROTECTED] sqlite3 test.db
Loading resources from /home/tkampe/.config/sqlite/.sqliterc
SQLite version 3.0.6
Enter ".help" for instructions
sqlite> create table test_table(test_col);
sqlite> .exit
[EMAIL PROTECTED] sqlite3 /home/tkampe/test.db
Unable to open database "/home/tkampe/test.db": unable to open database
file
[EMAIL PROTECTED] sqlite3 'C:\cygwin\home\tkampe\test.db'
Loading resources from /home/tkampe/.config/sqlite/.sqliterc
SQLite version 3.0.6
Enter ".help" for instructions
sqlite>
The only difference between 2.8.15 and 3.06 is that 2.8.15 doesn't error
on "sqlite /home/tkampe/test.db" - but on the first connection attempt
to the database ("sqlite> .databases" for instance).
Thorsten
Re: [sqlite] like-op
Le mer 15/09/2004 à 00:43, Peter a écrit : > Rod Dav4is wrote: > > > I'm reminded of the guy who wanted to buy a loaf of bread and was told > > how to build a bakery. ;O) > > Don't hesitate to contact customer services to arrange for your 30day no > quibble refund. :-Þ THAT whas low. Actually, you could search the list archive, somebody already wrote such an extension, with astonishing results in terms of speed. I don't remember, I think it was 6 or 8 months ago. -- *** [EMAIL PROTECTED] OpenPGP public key: http://www.amakuru.net/dmorel.asc signature.asc Description: Ceci est une partie de message =?ISO-8859-1?Q?num=E9riquement?= =?ISO-8859-1?Q?_sign=E9e=2E?=
