[sqlite] winLock() in SQLITE 3.5.1...
While working on a VFS for use in 3.5.1, I was looking at the winLock() and have a question. Is it possible for the lock on a winFile object to progress from SHARED_LOCK to EXCLUSIVE_LOCK without first acquiring a RESERVED_LOCK? Assuming that it is, it seems that the comments at the start of the function are at odds with the implementation a little bit. The comments indicate that a PENDING_LOCK will be an intermediate state between a SHARED_LOCK and EXCLUSIVE_LOCK. This would make sense based on the other SQLITE docs I've read. However, as I read the code it would seem that a SHARED_LOCK is not acquired if proceeding from a SHARED_LOCK to EXCLUSIVE_LOCK. The PENDING_LOCK is only acquired if the current lock type is NO_LOCK or the current lock type is RESERVED_LOCK and an EXCLUSIVE_LOCK is desired. The reason I'm asking is that later in the function where the EXCLUSIVE_LOCK is acquired, the SHARED_LOCK is released before trying to acquire the EXCLUSIVE_LOCK. Without the protection of the PENDING_LOCK, it seems there is a race condition here where a winFile object may think it has tried (and possibly failed) to promote a SHARED_LOCK to an EXCLUSIVE_LOCK without realizing that at least once, and possibly twice, it was in a state where it held no lock at all. I apologize in advance if I've misread the code. Thanks in advance... - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLITE3 Prepare / Step
The prepare creates a virtual machine which can be rused. A useful way to implement Sqlite is to use prepare to compile all the SQL in the initialization phase of the program and then to execute the virutal machines using step. By compiling a SQL in advance you can ensure that the program will not fail in mid execution with an SQl error. Uma Krishnan wrote: In SQLite3 one uses prepare/step to execute query. The question that I have is, when your stepping yields no more rows, and one has to re-execute the query, does one have to call the prepare statement again. If that's the case, what's the advantage of pre-compiling. If not, how does Sqlite3 knows it has to reissue the query. In standard DB/JDBC parlance, one prepares (one time, unless recompiled), executes, loops for next (/step) until all rows fetched, then closes. Subsequently one can skip prepare and proceed to execute. Thanks in advance Uma Uma Krishnan <[EMAIL PROTECTED]> wrote: Yes. Makes sense (not to cache query results for embedded apps). So what is cached. Just dirty pages? or are raw tables cached when queried? Thanks Uma Scott Hess wrote: On 10/17/07, Trevor Talbot wrote: On 10/17/07, Uma Krishnan wrote: One other question, when a query is issued, does SQLite cache the results, so that future queries can be processed off the cache (I think not) Like the "query cache" in some other databases? No. SQLite does have a cache of database pages, but they mimic what's on disk, not the results of a particular query. A query cache would not be very useful for an embedded database. If you're caching results, you might as well do it in the application's native form -- it's the same process after all. To add another nail, the reason a query cache is often useful in database servers is because you can usually share the cache across all the front-ends. Since SQLite effectively lives inside the front-end, this sharing goes away. Worse, any caching SQLite does is adding to the memory footprint of the containing app (or, put another way, stealing memory the app could use in other ways). -scott - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] HELP WITH SQLITE INTERNALS - VDBE and Virtual tables
PostgreSQL has the capability of storing pre-compiled SQL so that it can be reused and have data bound to the compiled statement. I have not looked at the mechanics, but it would be of interest and educational for you to see the PostgreSQL approach. Sqlite does cache the results of a query. The persistence of that cache varies with the version of Sqlite. It has an optional shared cache mode which can lift performance in appropriate applications. The evolution of cache persistence and sharing in successive versions of Sqlite should give you an insight into the problems of implementing such features. Uma Krishnan wrote: Thanks John and Joe for your responses. As far as I know, Postgres does not have a virtual engine. I could be wrong. One other question, when a query is issued, does SQLite cache the results, so that future queries can be processed off the cache (I think not) Thanks Uma John Stanton <[EMAIL PROTECTED]> wrote: >Moreover, is it typical to have an implementation like VDBE in other databases as well? This is a common approach and has been used for a very long time. For example we used it in products produced during the 1980s because producing a virtual machine and a compiler for its application-specific instruction set was a far better solution than masses of procedural logic. At that time it was a time honored technique and not at all innovative. Look at how PostgreSQL compiles and stores SQL statements for background information on the concept. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Behavior of SELECT queries during transaction
Think about it. A transaction does not affect SELECts, it only defers the processing of the journal until there is a COMMIT. According to your version of Sqlite you may get some benefit from SELECTs within a transaction by retaining the cache. Ken wrote: Thomas, if i understand you correctly: 1. Place the writing of multiple rows of data inside a transaction. 2. Querry performance will not be affected by the transaction. So, If you have multiple rows and are doing something like the following: 1. Begin Transaction 2. Read data from somewhere. 3. Insert to sqlite 4. Querry sqlite 5. Update sqlite. 6. Repeat 2-5, till no more data. 7. Commit. Now depending upon how much data you may wish to add a step to periodically commit and start a new Txn. 5a. if (rows loaded Mod some number ) commit txn ... Begin Txn. Thomas DILIGENT <[EMAIL PROTECTED]> wrote: Igor Tandetnik wrote: Thomas DILIGENT wrote: What about the performance issue ? What about it? You need to provide some context. Your questions, standing alone, don't make much sense, no offence intended. What problem are you trying to solve, and what specifically are you concerned about? The program I am working on performs the following steps: - transform events from a trace into persistent (C++ and java) objects that are stored to and load from a sqlite database. - query objects for analysis. Only one client accesses this database in the same time, i.e. we do not have any synchronisation issue. As queries are performed on object's load and store, the sequence of queries depends on the usage of these objects, i.e. the incoming events. In this context, I try to improve speed of database accesses : Instead of using autocommit, I would like to start and end transactions which would contain a fixed number (as an example) of transactions. First question : Will transactions increase speed ? In the first step, objects are stored and may be queried to retrieve some information. As a result, since objects load and store are performed regarding incoming events, queries are performed in an undeterministic way. Thus I have to define a strategy that hooks object load and store in order to perform begin and commit at the judicious time. Second question : When to perform begin and commit ? In this context, is it realistic and judicious to perform begin at the beginning of the first step and commit at the end of the first step ? Hope this is clear enough ... Thomas Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] In-memory database: manually modifying ROWID value increases memory.
Babu, Lokesh wrote:
sorry I forgot to mention the sqlite version that I'm using, its SQLite
3.3.8.
Below is the sample code that I tried,
static char *database_name = ":memory:";
static sqlite3* db_handle;
#define PRINT_TIME \
{ \
unsigned long millisec = clock(); \
printf("milliseconds = %ld\n", millisec); \
}
static const char *TestSqlStats[] = {
"CREATE TABLE testTbl (t_id INTEGER, t_name TEXT);",
"INSERT INTO testTbl (ROWID,t_id,t_name) VALUES (:A,:B,:C);",
};
int main(int argc, char *argv[])
{
char *zErrMsg = NULL;
int status, i = 0;
/* Open database */
status = sqlite3_open(database_name, _handle);
/* Create Table */
status = sqlite3_exec(db_handle, "BEGIN;", NULL, NULL, NULL);
status = sqlite3_exec(db_handle, TestSqlStats[0], NULL, NULL, NULL);
PRINT_TIME
{
static sqlite3_stmt *MyInsertStmt = NULL;
status = sqlite3_prepare(db_handle, TestSqlStats[1], -1,
, (void*)0);
for (i = 1; i > 0; i--)
{
status = sqlite3_bind_int(MyInsertStmt, 1, i);
status = sqlite3_bind_int(MyInsertStmt, 2, i);
status = sqlite3_bind_text(MyInsertStmt, 3, "It's a bad
statement", -1, SQLITE_TRANSIENT);
status = sqlite3_step(MyInsertStmt);
status = sqlite3_reset(MyInsertStmt);
}
status = sqlite3_finalize(MyInsertStmt);
}
PRINT_TIME
}
These are the test results on VC++6.0 on Pentium 4 and 3GHz Processor with
1GB RAM.
with descending order
milliseconds = 0
milliseconds = 672
Total Memory Consumed = 626040
1 It's a bad statement
2 It's a bad statement
3 It's a bad statement
4 It's a bad statement
5 It's a bad statement
Press any key to continue
with ascending order
milliseconds = 0
milliseconds = 234
Total Memory Consumed = 328960
0 It's a bad statement
1 It's a bad statement
2 It's a bad statement
3 It's a bad statement
4 It's a bad statement
Press any key to continue
From the above results, the time & space complexity has been almost doubled
when ROWID is modified with another unique value or if inserted in
descending order.
Is there any way in which I can overcome this? or is it corrected in higher
version, before trying higher version if anybody already tried please reply.
Scott -- thanks for the info, I tried enabling the macro
SQLITE_OMIT_QUICKBALANCE, it didn't effect anything.
There are some TODO's comment which may not be implemented. Please correct
me if I'm wrong.
Thanks,
-Lokee
On 10/17/07, Vitali Lovich <[EMAIL PROTECTED]> wrote:
Well, rough calculations indicate that the situation that sqlite
preallocates about an additional 3 rows every time a new ROWID is
assigned (on the fairly common use case that ROWID is always
monotonically increasing). Assuming that this corner case is not
accounted for, it could be that when assigning randomly or in decreasing
order, the additional rows are kept around but unassigned, resulting in
unused space.
Without actually looking into the internals or profiling the code, this
is just a guess and I would recommend that someone better familiar with
the internals comment on this topic.
Scott Hess wrote:
Could it be that you're seeing the btree optimization around in-order
insertion? From btree.c:
#ifndef SQLITE_OMIT_QUICKBALANCE
/*
** A special case: If a new entry has just been inserted into a
** table (that is, a btree with integer keys and all data at the
leaves)
** and the new entry is the right-most entry in the tree (it has the
** largest key) then use the special balance_quick() routine for
** balancing. balance_quick() is much faster and results in a tighter
** packing of data in the common case.
*/
-scott
On 10/16/07, Babu, Lokesh <[EMAIL PROTECTED]> wrote:
Dear all,
I'll reframe the question again, If ROWID(hidden column/b-tree
key/internal
to all table) is changed manually, means I'll insert some unique values
in
random order / in descending order (say from 1 to 1), the memory
occupied increases more. why? I observed entire table is getting sorted
based on ROWID.
My results were, for 1 records if it is normally inserted (ie., not
modifying ROWID) it takes 500KB. The same thing if I modify the ROWID
it
consumes 1.5MB. why?
Is that all the columns are indexed or only ROWID is indexed?
Memory measurement is done by sqlite3GenericMalloc using
SQLITE_ENABLE_MEMORY_MANAGEMENT macro enabled. Calculating each
allocation
size gives total memory allocation.
Thanks.
On 10/17/07, Vitali Lovich <[EMAIL PROTECTED]> wrote:
My question is how you're measuring the memory useage? Are you
accounting for the space overhead of the various bookkeeping sqlite
needs (i.e. master table)? The way you're creating you're table
implies
you're not using autoincrement for the integer field - are you
accounting for the extra internal row id column?
Joe Wilson wrote:
It could be malloc fragmentation.
Which sqlite version, operating system, and
[sqlite] PATCH: improve query speed involving floating point and int64 constants
This is a working proof of concept patch against sqlite 3.5.1 that
can improve query speed by as much as 50% when floating point or
64 bit integer constants are used. It does so by eliminating repeated
parsing of numbers from strings in OP_Real and OP_Int64 by storing
the parsed binary numbers in the otherwise unused P1 and P2 opcode
arguments. The string form of the number is still retained in P3.
Although "make test" passes with no errors, the patch does not attempt
to check for out of memory conditions. Comments or bug reports are
welcome.
Example:
CREATE TABLE n1(a integer primary key);
INSERT INTO "n1" VALUES(1);
INSERT INTO "n1" VALUES(2);
INSERT INTO "n1" VALUES(3);
INSERT INTO "n1" VALUES(4);
INSERT INTO "n1" VALUES(5);
INSERT INTO "n1" VALUES(6);
INSERT INTO "n1" VALUES(7);
INSERT INTO "n1" VALUES(8);
INSERT INTO "n1" VALUES(9);
INSERT INTO "n1" VALUES(10);
CREATE VIEW vu as select v3.a a from n1 v1,n1 v2,n1 v3,n1 v4,n1 v5,n1 v6,n1 v7;
select sum(a-1234567890.12345678+123456789012345) from vu where a>1;
Without the patch, the SQL above completes in 15.1 seconds on my machine.
With the patch it takes just 10.4 seconds.
I hereby place this code in the public domain.
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com Index: src/expr.c
===
RCS file: /sqlite/sqlite/src/expr.c,v
retrieving revision 1.313
diff -u -3 -p -r1.313 expr.c
--- src/expr.c 18 Sep 2007 15:55:07 - 1.313
+++ src/expr.c 17 Oct 2007 23:01:01 -
@@ -1680,6 +1680,32 @@ void sqlite3CodeSubselect(Parse *pParse,
}
#endif /* SQLITE_OMIT_SUBQUERY */
+static void codeReal(Vdbe *v, const char *z, int n){
+ double r;
+ int p1, p2, rc;
+ char *buf = sqlite3_malloc(n+1);
+ memcpy(buf, z, n);
+ buf[n] = 0;
+ rc = sqlite3AtoF(buf, ); // XXX check rc
+ sqlite3_free(buf);
+ memcpy((char*), (char*), 4);
+ memcpy((char*), 4+(char*), 4);
+ sqlite3VdbeOp3(v, OP_Real, p1, p2, z, n);
+}
+
+static void codeInt64(Vdbe *v, const char *z, int n){
+ i64 r;
+ int p1, p2, rc;
+ char *buf = sqlite3_malloc(n+1);
+ memcpy(buf, z, n);
+ buf[n] = 0;
+ rc = sqlite3Atoi64(buf, ); // XXX check rc
+ sqlite3_free(buf);
+ memcpy((char*), (char*), 4);
+ memcpy((char*), 4+(char*), 4);
+ sqlite3VdbeOp3(v, OP_Int64, p1, p2, z, n);
+}
+
/*
** Generate an instruction that will put the integer describe by
** text z[0..n-1] on the stack.
@@ -1691,9 +1717,9 @@ static void codeInteger(Vdbe *v, const c
if( sqlite3GetInt32(z, ) ){
sqlite3VdbeAddOp(v, OP_Integer, i, 0);
}else if( sqlite3FitsIn64Bits(z) ){
- sqlite3VdbeOp3(v, OP_Int64, 0, 0, z, n);
+ codeInt64(v, z, n);
}else{
- sqlite3VdbeOp3(v, OP_Real, 0, 0, z, n);
+ codeReal(v, z, n);
}
}
}
@@ -1772,14 +1798,18 @@ void sqlite3ExprCode(Parse *pParse, Expr
codeInteger(v, (char*)pExpr->token.z, pExpr->token.n);
break;
}
-case TK_FLOAT:
case TK_STRING: {
- assert( TK_FLOAT==OP_Real );
assert( TK_STRING==OP_String8 );
sqlite3DequoteExpr(pParse->db, pExpr);
sqlite3VdbeOp3(v, op, 0, 0, (char*)pExpr->token.z, pExpr->token.n);
break;
}
+case TK_FLOAT: {
+ assert( TK_FLOAT==OP_Real );
+ sqlite3DequoteExpr(pParse->db, pExpr);
+ codeReal(v, (char*)pExpr->token.z, pExpr->token.n);
+ break;
+}
case TK_NULL: {
sqlite3VdbeAddOp(v, OP_Null, 0, 0);
break;
@@ -1879,9 +1909,12 @@ void sqlite3ExprCode(Parse *pParse, Expr
assert( pLeft );
if( pLeft->op==TK_FLOAT || pLeft->op==TK_INTEGER ){
Token *p = >token;
-char *z = sqlite3MPrintf(pParse->db, "-%.*s", p->n, p->z);
+char *z = sqlite3_malloc(p->n+2);
+z[0] = '-';
+memcpy(z+1, p->z, p->n);
+z[p->n+1] = 0;
if( pLeft->op==TK_FLOAT ){
- sqlite3VdbeOp3(v, OP_Real, 0, 0, z, p->n+1);
+ codeReal(v, z, p->n+1);
}else{
codeInteger(v, z, p->n+1);
}
Index: src/vdbe.c
===
RCS file: /sqlite/sqlite/src/vdbe.c,v
retrieving revision 1.651
diff -u -3 -p -r1.651 vdbe.c
--- src/vdbe.c 5 Oct 2007 16:23:55 - 1.651
+++ src/vdbe.c 17 Oct 2007 23:01:02 -
@@ -713,9 +713,10 @@ case OP_Int64: {
assert( pOp->p3!=0 );
pTos->flags = MEM_Str|MEM_Static|MEM_Term;
pTos->z = pOp->p3;
- pTos->n = strlen(pTos->z);
+ pTos->n = strlen(pTos->z); /* should probably put strlen in pOp */
pTos->enc = SQLITE_UTF8;
- pTos->u.i = sqlite3VdbeIntValue(pTos);
+ memcpy( (char*)>u.i, (char*)>p1, 4);
+ memcpy(4+(char*)>u.i, (char*)>p2, 4);
pTos->flags |= MEM_Int;
break;
}
@@ -728,9 +729,10 @@ case OP_Real: {/* same as TK
pTos++;
pTos->flags = MEM_Str|MEM_Static|MEM_Term;
pTos->z = pOp->p3;
- pTos->n = strlen(pTos->z);
+ pTos->n =
Re: [sqlite] SQLITE3 Prepare / Step
Hi, Uma Krishnan wrote: In SQLite3 one uses prepare/step to execute query. The question that I have is, when your stepping yields no more rows, and one has to re-execute the query, does one have to call the prepare statement again. If that's the case, what's the advantage of pre-compiling. If not, how does Sqlite3 knows it has to reissue the query. You would use sqlite3_reset() or its equivalent in your wrapper's API. Regards, Eugene Wee - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] SQLITE3 Prepare / Step
In SQLite3 one uses prepare/step to execute query. The question that I have is, when your stepping yields no more rows, and one has to re-execute the query, does one have to call the prepare statement again. If that's the case, what's the advantage of pre-compiling. If not, how does Sqlite3 knows it has to reissue the query. In standard DB/JDBC parlance, one prepares (one time, unless recompiled), executes, loops for next (/step) until all rows fetched, then closes. Subsequently one can skip prepare and proceed to execute. Thanks in advance Uma Uma Krishnan <[EMAIL PROTECTED]> wrote: Yes. Makes sense (not to cache query results for embedded apps). So what is cached. Just dirty pages? or are raw tables cached when queried? Thanks Uma Scott Hess wrote: On 10/17/07, Trevor Talbot wrote: > On 10/17/07, Uma Krishnan wrote: > > One other question, when a query is issued, does SQLite cache the results, > > so that future queries can be processed off the cache (I think not) > > Like the "query cache" in some other databases? No. > > SQLite does have a cache of database pages, but they mimic what's on > disk, not the results of a particular query. > > A query cache would not be very useful for an embedded database. If > you're caching results, you might as well do it in the application's > native form -- it's the same process after all. To add another nail, the reason a query cache is often useful in database servers is because you can usually share the cache across all the front-ends. Since SQLite effectively lives inside the front-end, this sharing goes away. Worse, any caching SQLite does is adding to the memory footprint of the containing app (or, put another way, stealing memory the app could use in other ways). -scott - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Compact statement for table to table copy
I am not changing the ordering, but i do not want to copy the primary key field since it is auto increment (I neglected too mention that before, sorry). On 10/16/07, Vitali Lovich <[EMAIL PROTECTED]> wrote: > Well, you don't have to list the columns if you're not changing the > ordering. > > INSERT INTO table1 SELECT * FROM table2; > > Rich Rattanni wrote: > > I have two tables in a database with exactly the same schema > > (idNum PRIMARY KEY, data1, data2) > > > > I want to copy all the records in table 2 to table 1, currently I am > > using the following statement: > > INSERT INTO table1 (data1, data2) SELECT data1, data2 FROM table2. > > > > Now this is just a simplified illustration, in my case I am copying > > about 10 columns over. I was wondering if there was a compact way to > > write the SQL statement, that copied the data over from one table to > > the other ignoring the primary key fields. I suspect there is not, > > but I figured it wouldnt hurt to ask. > > > > -- > > Thanks, > > Richard Rattanni > > > > - > > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > > > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] HELP WITH SQLITE INTERNALS - VDBE and Virtual tables
Yes. Makes sense (not to cache query results for embedded apps). So what is cached. Just dirty pages? or are raw tables cached when queried? Thanks Uma Scott Hess <[EMAIL PROTECTED]> wrote: On 10/17/07, Trevor Talbot wrote: > On 10/17/07, Uma Krishnan wrote: > > One other question, when a query is issued, does SQLite cache the results, > > so that future queries can be processed off the cache (I think not) > > Like the "query cache" in some other databases? No. > > SQLite does have a cache of database pages, but they mimic what's on > disk, not the results of a particular query. > > A query cache would not be very useful for an embedded database. If > you're caching results, you might as well do it in the application's > native form -- it's the same process after all. To add another nail, the reason a query cache is often useful in database servers is because you can usually share the cache across all the front-ends. Since SQLite effectively lives inside the front-end, this sharing goes away. Worse, any caching SQLite does is adding to the memory footprint of the containing app (or, put another way, stealing memory the app could use in other ways). -scott - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Behavior of SELECT queries during transaction
Thomas DILIGENT <[EMAIL PROTECTED]> wrote: Hi Ken, Ken wrote: > Thomas, if i understand you correctly: > > 1. Place the writing of multiple rows of data inside a transaction. > 2. Querry performance will not be affected by the transaction. > > So, If you have multiple rows and are doing something like the following: > > 1. Begin Transaction > 2. Read data from somewhere. > 3. Insert to sqlite > 4. Querry sqlite > 5. Update sqlite. > 6. Repeat 2-5, till no more data. > 7. Commit. > > Now depending upon how much data you may wish to add a step to periodically > commit and start a new Txn. > 5a. if (rows loaded Mod some number ) commit txn ... Begin Txn. > That's it. This is what I want to do. From this point, I have the following questions : 1) Will this increase speed compared to a basic solution where I would use autocommit mode ? (in other words, is it worthy implementing such a solution ?) 2) If yes, how much and how to chose the number of rows between begin and commit ? Thomas, As drh discussed. Try 1000 entries first, then 2000, 4000, etc... Eventually you'll hit a point of diminishing return. It really depends upon your data and page size of the database. Ken
Re: [sqlite] HELP WITH SQLITE INTERNALS - VDBE and Virtual tables
On 10/17/07, Trevor Talbot <[EMAIL PROTECTED]> wrote: > On 10/17/07, Uma Krishnan <[EMAIL PROTECTED]> wrote: > > One other question, when a query is issued, does SQLite cache the results, > > so that future queries can be processed off the cache (I think not) > > Like the "query cache" in some other databases? No. > > SQLite does have a cache of database pages, but they mimic what's on > disk, not the results of a particular query. > > A query cache would not be very useful for an embedded database. If > you're caching results, you might as well do it in the application's > native form -- it's the same process after all. To add another nail, the reason a query cache is often useful in database servers is because you can usually share the cache across all the front-ends. Since SQLite effectively lives inside the front-end, this sharing goes away. Worse, any caching SQLite does is adding to the memory footprint of the containing app (or, put another way, stealing memory the app could use in other ways). -scott - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Behavior of SELECT queries during transaction
Thomas DILIGENT <[EMAIL PROTECTED]> wrote: > > That's it. This is what I want to do. > From this point, I have the following questions : > 1) Will this increase speed compared to a basic solution where I would > use autocommit mode ? (in other words, is it worthy implementing such a > solution ?) > 2) If yes, how much and how to chose the number of rows between begin > and commit ? > The speed of COMMIT is limited by the speed of your disk drive. Generally speaking, the disk platter has to do at least two complete revolutions in order complete a COMMIT. This limits you to about 60 commits per second on a 7200 RPM disk drive. On the other hand, SQLite can process in excess of 6 INSERT statements per second on a modern workstation. So roughly speaking, INSERT is about 1000 times faster than COMMIT. So if you do BEGIN, 1000 INSERTs, and then COMMIT, that takes roughly the same amount of time as doing two INSERT statements in autocommit mode. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] HELP WITH SQLITE INTERNALS - VDBE and Virtual tables
On 10/17/07, Uma Krishnan <[EMAIL PROTECTED]> wrote: > As far as I know, Postgres does not have a virtual engine. I could be wrong. It's not a virtual machine style, where it has a specific instruction set; instead it's more like a graph of operations. Execution means walking a graph instead of interpreting an instruction stream. It's still an abstract virtual engine, just implemented differently. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] HELP WITH SQLITE INTERNALS - VDBE and Virtual tables
On 10/17/07, Uma Krishnan <[EMAIL PROTECTED]> wrote: > One other question, when a query is issued, does SQLite cache the results, so > that future queries can be processed off the cache (I think not) Like the "query cache" in some other databases? No. SQLite does have a cache of database pages, but they mimic what's on disk, not the results of a particular query. A query cache would not be very useful for an embedded database. If you're caching results, you might as well do it in the application's native form -- it's the same process after all. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] HELP WITH SQLITE INTERNALS - VDBE and Virtual tables
Regarding: "... when a query is issued, does SQLite cache the results, so that future queries can be processed off the cache (I think not) " ... = P.S. And I should certainly have mentioned the sqlite items below: http://sqlite.org/pragma.html PRAGMA cache_size= Number-of-pages; PRAGMA default_cache_size = Number-of-pages; PRAGMA page_size = bytes; - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Behavior of SELECT queries during transaction
Hi Ken, Ken wrote: Thomas, if i understand you correctly: 1. Place the writing of multiple rows of data inside a transaction. 2. Querry performance will not be affected by the transaction. So, If you have multiple rows and are doing something like the following: 1. Begin Transaction 2. Read data from somewhere. 3. Insert to sqlite 4. Querry sqlite 5. Update sqlite. 6. Repeat 2-5, till no more data. 7. Commit. Now depending upon how much data you may wish to add a step to periodically commit and start a new Txn. 5a. if (rows loaded Mod some number ) commit txn ... Begin Txn. That's it. This is what I want to do. From this point, I have the following questions : 1) Will this increase speed compared to a basic solution where I would use autocommit mode ? (in other words, is it worthy implementing such a solution ?) 2) If yes, how much and how to chose the number of rows between begin and commit ? Thomas DILIGENT <[EMAIL PROTECTED]> wrote: Igor Tandetnik wrote: Thomas DILIGENT wrote: What about the performance issue ? What about it? You need to provide some context. Your questions, standing alone, don't make much sense, no offence intended. What problem are you trying to solve, and what specifically are you concerned about? The program I am working on performs the following steps: - transform events from a trace into persistent (C++ and java) objects that are stored to and load from a sqlite database. - query objects for analysis. Only one client accesses this database in the same time, i.e. we do not have any synchronisation issue. As queries are performed on object's load and store, the sequence of queries depends on the usage of these objects, i.e. the incoming events. In this context, I try to improve speed of database accesses : Instead of using autocommit, I would like to start and end transactions which would contain a fixed number (as an example) of transactions. First question : Will transactions increase speed ? In the first step, objects are stored and may be queried to retrieve some information. As a result, since objects load and store are performed regarding incoming events, queries are performed in an undeterministic way. Thus I have to define a strategy that hooks object load and store in order to perform begin and commit at the judicious time. Second question : When to perform begin and commit ? In this context, is it realistic and judicious to perform begin at the beginning of the first step and commit at the end of the first step ? Hope this is clear enough ... Thomas Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] sqlite3_update_hook
Hi, with sqlite3_update_hook I can get the rowid of the row AFTER it has been INSERTed, DELETEd or UPDATEd. Is there a way to get the rowid of a row BEFORE it is DELETEd or UPDATEd ? If not, can someone suggest a good approach to this problem? Thanks a lot, --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] HELP WITH SQLITE INTERNALS - VDBE and Virtual tables
Regarding: "... when a query is issued, does SQLite cache the results, so that future queries can be processed off the cache (I think not) " Hi Uma, In effect, powerful caching effects *do* occur because of the disk cache provided by modern operating systems. Since the hard disk operations are typically orders of magnitude longer than the sql engine's cpu work for a query, the fact that the disk sectors required by a recent query tend to hang around a bit makes for very efficient use of RAM memory -- probably much better for the system as a whole than if sqlite tried to reserve all this ram for itself. For some applications, it even makes sense to perform a command-line copy of the entire sqlite database to a NUL device, since this will pre-load the operating system cache. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] HELP WITH SQLITE INTERNALS - VDBE and Virtual tables
Thanks John and Joe for your responses. As far as I know, Postgres does not have a virtual engine. I could be wrong. One other question, when a query is issued, does SQLite cache the results, so that future queries can be processed off the cache (I think not) Thanks Uma John Stanton <[EMAIL PROTECTED]> wrote: >Moreover, is it typical to have an implementation like VDBE in other databases as well? This is a common approach and has been used for a very long time. For example we used it in products produced during the 1980s because producing a virtual machine and a compiler for its application-specific instruction set was a far better solution than masses of procedural logic. At that time it was a time honored technique and not at all innovative. Look at how PostgreSQL compiles and stores SQL statements for background information on the concept. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Behavior of SELECT queries during transaction
Thomas, if i understand you correctly: 1. Place the writing of multiple rows of data inside a transaction. 2. Querry performance will not be affected by the transaction. So, If you have multiple rows and are doing something like the following: 1. Begin Transaction 2. Read data from somewhere. 3. Insert to sqlite 4. Querry sqlite 5. Update sqlite. 6. Repeat 2-5, till no more data. 7. Commit. Now depending upon how much data you may wish to add a step to periodically commit and start a new Txn. 5a. if (rows loaded Mod some number ) commit txn ... Begin Txn. Thomas DILIGENT <[EMAIL PROTECTED]> wrote: Igor Tandetnik wrote: > Thomas DILIGENT wrote: >> What about the performance issue ? > > What about it? > > You need to provide some context. Your questions, standing alone, > don't make much sense, no offence intended. What problem are you > trying to solve, and what specifically are you concerned about? The program I am working on performs the following steps: - transform events from a trace into persistent (C++ and java) objects that are stored to and load from a sqlite database. - query objects for analysis. Only one client accesses this database in the same time, i.e. we do not have any synchronisation issue. As queries are performed on object's load and store, the sequence of queries depends on the usage of these objects, i.e. the incoming events. In this context, I try to improve speed of database accesses : Instead of using autocommit, I would like to start and end transactions which would contain a fixed number (as an example) of transactions. First question : Will transactions increase speed ? In the first step, objects are stored and may be queried to retrieve some information. As a result, since objects load and store are performed regarding incoming events, queries are performed in an undeterministic way. Thus I have to define a strategy that hooks object load and store in order to perform begin and commit at the judicious time. Second question : When to perform begin and commit ? In this context, is it realistic and judicious to perform begin at the beginning of the first step and commit at the end of the first step ? Hope this is clear enough ... Thomas > > Igor Tandetnik > > - > > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Lemon: Help on conflic resolution?
Richard, this helped me greatly! I also derived from your example that I can use multiple characters without conflicts like this: --- doc ::= inline_list. // List of allowed characters. Add more as you like. c ::= CHAR. c ::= SPACE. // The c character repeat. chars ::= c. chars ::= chars CHAR. // Any sequence of just c and 'c' (c surrounded by apostrophes). inline ::= c. inline ::= APOS chars APOS. // The inline repeat. inline_list ::= inline. inline_list ::= inline_list inline. - Many thanks! Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Lemon: Help on conflic resolution?
Richard, this helped me greatly! I also derived from your example that I can use multiple characters without conflicts like this: --- doc ::= inline_list. // List of allowed characters. Add more as you like. c ::= CHAR. c ::= SPACE. // The c character repeat. chars ::= c. chars ::= chars CHAR. // Any sequence of just c and 'c' (c surrounded by apostrophes). inline ::= c. inline ::= APOS chars APOS. // The inline repeat. inline_list ::= inline. inline_list ::= inline_list inline. - Many thanks! Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] In-memory database: manually modifying ROWID value increases memory.
sorry I forgot to mention the sqlite version that I'm using, its SQLite
3.3.8.
Below is the sample code that I tried,
static char *database_name = ":memory:";
static sqlite3* db_handle;
#define PRINT_TIME \
{ \
unsigned long millisec = clock(); \
printf("milliseconds = %ld\n", millisec); \
}
static const char *TestSqlStats[] = {
"CREATE TABLE testTbl (t_id INTEGER, t_name TEXT);",
"INSERT INTO testTbl (ROWID,t_id,t_name) VALUES (:A,:B,:C);",
};
int main(int argc, char *argv[])
{
char *zErrMsg = NULL;
int status, i = 0;
/* Open database */
status = sqlite3_open(database_name, _handle);
/* Create Table */
status = sqlite3_exec(db_handle, "BEGIN;", NULL, NULL, NULL);
status = sqlite3_exec(db_handle, TestSqlStats[0], NULL, NULL, NULL);
PRINT_TIME
{
static sqlite3_stmt *MyInsertStmt = NULL;
status = sqlite3_prepare(db_handle, TestSqlStats[1], -1,
, (void*)0);
for (i = 1; i > 0; i--)
{
status = sqlite3_bind_int(MyInsertStmt, 1, i);
status = sqlite3_bind_int(MyInsertStmt, 2, i);
status = sqlite3_bind_text(MyInsertStmt, 3, "It's a bad
statement", -1, SQLITE_TRANSIENT);
status = sqlite3_step(MyInsertStmt);
status = sqlite3_reset(MyInsertStmt);
}
status = sqlite3_finalize(MyInsertStmt);
}
PRINT_TIME
}
These are the test results on VC++6.0 on Pentium 4 and 3GHz Processor with
1GB RAM.
with descending order
milliseconds = 0
milliseconds = 672
Total Memory Consumed = 626040
1 It's a bad statement
2 It's a bad statement
3 It's a bad statement
4 It's a bad statement
5 It's a bad statement
Press any key to continue
with ascending order
milliseconds = 0
milliseconds = 234
Total Memory Consumed = 328960
0 It's a bad statement
1 It's a bad statement
2 It's a bad statement
3 It's a bad statement
4 It's a bad statement
Press any key to continue
>From the above results, the time & space complexity has been almost doubled
when ROWID is modified with another unique value or if inserted in
descending order.
Is there any way in which I can overcome this? or is it corrected in higher
version, before trying higher version if anybody already tried please reply.
Scott -- thanks for the info, I tried enabling the macro
SQLITE_OMIT_QUICKBALANCE, it didn't effect anything.
There are some TODO's comment which may not be implemented. Please correct
me if I'm wrong.
Thanks,
-Lokee
On 10/17/07, Vitali Lovich <[EMAIL PROTECTED]> wrote:
>
> Well, rough calculations indicate that the situation that sqlite
> preallocates about an additional 3 rows every time a new ROWID is
> assigned (on the fairly common use case that ROWID is always
> monotonically increasing). Assuming that this corner case is not
> accounted for, it could be that when assigning randomly or in decreasing
> order, the additional rows are kept around but unassigned, resulting in
> unused space.
>
> Without actually looking into the internals or profiling the code, this
> is just a guess and I would recommend that someone better familiar with
> the internals comment on this topic.
>
> Scott Hess wrote:
> > Could it be that you're seeing the btree optimization around in-order
> > insertion? From btree.c:
> >
> > #ifndef SQLITE_OMIT_QUICKBALANCE
> > /*
> > ** A special case: If a new entry has just been inserted into a
> > ** table (that is, a btree with integer keys and all data at the
> leaves)
> > ** and the new entry is the right-most entry in the tree (it has the
> > ** largest key) then use the special balance_quick() routine for
> > ** balancing. balance_quick() is much faster and results in a tighter
> > ** packing of data in the common case.
> > */
> >
> > -scott
> >
> >
> > On 10/16/07, Babu, Lokesh <[EMAIL PROTECTED]> wrote:
> >
> >> Dear all,
> >>
> >> I'll reframe the question again, If ROWID(hidden column/b-tree
> key/internal
> >> to all table) is changed manually, means I'll insert some unique values
> in
> >> random order / in descending order (say from 1 to 1), the memory
> >> occupied increases more. why? I observed entire table is getting sorted
> >> based on ROWID.
> >>
> >> My results were, for 1 records if it is normally inserted (ie., not
> >> modifying ROWID) it takes 500KB. The same thing if I modify the ROWID
> it
> >> consumes 1.5MB. why?
> >>
> >> Is that all the columns are indexed or only ROWID is indexed?
> >>
> >> Memory measurement is done by sqlite3GenericMalloc using
> >> SQLITE_ENABLE_MEMORY_MANAGEMENT macro enabled. Calculating each
> allocation
> >> size gives total memory allocation.
> >>
> >> Thanks.
> >>
> >>
> >> On 10/17/07, Vitali Lovich <[EMAIL PROTECTED]> wrote:
> >>
> >>> My question is how you're measuring the memory useage? Are you
> >>> accounting for the space overhead of the various bookkeeping sqlite
> >>> needs (i.e. master table)? The way you're creating you're table
> implies
> >>> you're not using
Re: [sqlite] Re: Behavior of SELECT queries during transaction
Igor Tandetnik wrote: Thomas DILIGENT <[EMAIL PROTECTED]> wrote: What about the performance issue ? What about it? You need to provide some context. Your questions, standing alone, don't make much sense, no offence intended. What problem are you trying to solve, and what specifically are you concerned about? The program I am working on performs the following steps: - transform events from a trace into persistent (C++ and java) objects that are stored to and load from a sqlite database. - query objects for analysis. Only one client accesses this database in the same time, i.e. we do not have any synchronisation issue. As queries are performed on object's load and store, the sequence of queries depends on the usage of these objects, i.e. the incoming events. In this context, I try to improve speed of database accesses : Instead of using autocommit, I would like to start and end transactions which would contain a fixed number (as an example) of transactions. First question : Will transactions increase speed ? In the first step, objects are stored and may be queried to retrieve some information. As a result, since objects load and store are performed regarding incoming events, queries are performed in an undeterministic way. Thus I have to define a strategy that hooks object load and store in order to perform begin and commit at the judicious time. Second question : When to perform begin and commit ? In this context, is it realistic and judicious to perform begin at the beginning of the first step and commit at the end of the first step ? Hope this is clear enough ... Thomas Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Lemon: Help on conflic resolution?
Ralf Junker <[EMAIL PROTECTED]> wrote: > I am writing to ask for help about how to solve The Lemon parser conflicts. > > As part of a larger grammar, I am need to implement this regular expression > in Lemon: > > (.+|'.+')+ > > I tried lots of grammars, but all of them generated Lemon warnings. > > Maybe someone could have a look at the grammar below and let me know > how the conflicts can be solved, and why they are generated in the > first place? > doc ::= inline_list. // One ore more CHARs. chars ::= CHAR. chars ::= chars CHAR. // Any sequence of just CHARs and 'CHARs' (surrounded by apostrophes). inline ::= CHAR. inline ::= APOS chars APOS. // The repeat. This causes conflicts. Isn't it allowed? Workarounds? inline_list ::= inline. inline_list ::= inline_list inline. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] In-memory database: manually modifying ROWID value increases memory.
Well, rough calculations indicate that the situation that sqlite preallocates about an additional 3 rows every time a new ROWID is assigned (on the fairly common use case that ROWID is always monotonically increasing). Assuming that this corner case is not accounted for, it could be that when assigning randomly or in decreasing order, the additional rows are kept around but unassigned, resulting in unused space. Without actually looking into the internals or profiling the code, this is just a guess and I would recommend that someone better familiar with the internals comment on this topic. Scott Hess wrote: Could it be that you're seeing the btree optimization around in-order insertion? From btree.c: #ifndef SQLITE_OMIT_QUICKBALANCE /* ** A special case: If a new entry has just been inserted into a ** table (that is, a btree with integer keys and all data at the leaves) ** and the new entry is the right-most entry in the tree (it has the ** largest key) then use the special balance_quick() routine for ** balancing. balance_quick() is much faster and results in a tighter ** packing of data in the common case. */ -scott On 10/16/07, Babu, Lokesh <[EMAIL PROTECTED]> wrote: Dear all, I'll reframe the question again, If ROWID(hidden column/b-tree key/internal to all table) is changed manually, means I'll insert some unique values in random order / in descending order (say from 1 to 1), the memory occupied increases more. why? I observed entire table is getting sorted based on ROWID. My results were, for 1 records if it is normally inserted (ie., not modifying ROWID) it takes 500KB. The same thing if I modify the ROWID it consumes 1.5MB. why? Is that all the columns are indexed or only ROWID is indexed? Memory measurement is done by sqlite3GenericMalloc using SQLITE_ENABLE_MEMORY_MANAGEMENT macro enabled. Calculating each allocation size gives total memory allocation. Thanks. On 10/17/07, Vitali Lovich <[EMAIL PROTECTED]> wrote: My question is how you're measuring the memory useage? Are you accounting for the space overhead of the various bookkeeping sqlite needs (i.e. master table)? The way you're creating you're table implies you're not using autoincrement for the integer field - are you accounting for the extra internal row id column? Joe Wilson wrote: It could be malloc fragmentation. Which sqlite version, operating system, and malloc implementation are you using? --- "Babu, Lokesh" <[EMAIL PROTECTED]> wrote: Say I have 3 columns in one Table, with one INTEGER, two TEXT columns, If ROWID is manually inserted and made descending for 1 records from 1 to 1, (or even if random number for ROWID - both these cases), the memory occupied is more. Why is this so? Be a better Globetrotter. Get better travel answers from someone who knows. Yahoo! Answers - Check it out. http://answers.yahoo.com/dir/?link=list=396545469 - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Behavior of SELECT queries during transaction
Thomas DILIGENT <[EMAIL PROTECTED]> wrote:
I would like to know what is the behavior of sqlite when performing a
SELECT query during a transaction ?
The same as outside the transaction.
CREATE TABLE thebeatles (_ID INTEGER PRIMARY KEY, name TEXT);
BEGIN TRANSACTION;
INSERT INTO thebeatles (name) VALUES ('john');
INSERT INTO thebeatles (name) VALUES ('paul');
SELECT * FROM thebeatles;
INSERT INTO thebeatles (name) VALUES ('george');
INSERT INTO thebeatles (name) VALUES ('ringo');
...
COMMIT TRANSATION;
So,
Does sqlite support this kind of sequence (and how ?)
Yes (easily).
or is it wrong ?
No, it is right.
What about the performance issue ?
What about it?
You need to provide some context. Your questions, standing alone, don't
make much sense, no offence intended. What problem are you trying to
solve, and what specifically are you concerned about?
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] sqlite 3.x lock states
"B V, Phanisekhar" <[EMAIL PROTECTED]> wrote: > > A database is in the EXCLUSIVE state if one of the processes has an > > EXCLUSIVE lock. Only *one* process at a time can hold an EX- CLUSIVE > > lock. The process holding the EXCLUSIVE lock is currently writing to > > the database file. Every other process must hold *no* lock. No other > > > process can acquire a lock of any kind. > > Does this apply to hot journal? > > http://www.sqlite.org/lockingv3.html > In the above link the following is mentioned as the last step in dealing > with hot journals. > Drop the EXCLUSIVE and PENDING locks but retain the SHARED lock. > > Does the above sentence means it can hold EXCLUSIVE, PENDING and SHARED > lock together? > If you are implementing your own private locking mechanism, you can do it either way. It makes no difference for the final outcome. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] In-memory database: manually modifying ROWID value increases memory.
Could it be that you're seeing the btree optimization around in-order insertion? From btree.c: #ifndef SQLITE_OMIT_QUICKBALANCE /* ** A special case: If a new entry has just been inserted into a ** table (that is, a btree with integer keys and all data at the leaves) ** and the new entry is the right-most entry in the tree (it has the ** largest key) then use the special balance_quick() routine for ** balancing. balance_quick() is much faster and results in a tighter ** packing of data in the common case. */ -scott On 10/16/07, Babu, Lokesh <[EMAIL PROTECTED]> wrote: > Dear all, > > I'll reframe the question again, If ROWID(hidden column/b-tree key/internal > to all table) is changed manually, means I'll insert some unique values in > random order / in descending order (say from 1 to 1), the memory > occupied increases more. why? I observed entire table is getting sorted > based on ROWID. > > My results were, for 1 records if it is normally inserted (ie., not > modifying ROWID) it takes 500KB. The same thing if I modify the ROWID it > consumes 1.5MB. why? > > Is that all the columns are indexed or only ROWID is indexed? > > Memory measurement is done by sqlite3GenericMalloc using > SQLITE_ENABLE_MEMORY_MANAGEMENT macro enabled. Calculating each allocation > size gives total memory allocation. > > Thanks. > > > On 10/17/07, Vitali Lovich <[EMAIL PROTECTED]> wrote: > > > > My question is how you're measuring the memory useage? Are you > > accounting for the space overhead of the various bookkeeping sqlite > > needs (i.e. master table)? The way you're creating you're table implies > > you're not using autoincrement for the integer field - are you > > accounting for the extra internal row id column? > > > > Joe Wilson wrote: > > > It could be malloc fragmentation. > > > > > > Which sqlite version, operating system, and malloc implementation are > > you using? > > > > > > --- "Babu, Lokesh" <[EMAIL PROTECTED]> wrote: > > > > > >> Say I have 3 columns in one Table, with one INTEGER, two TEXT columns, > > If > > >> ROWID is manually inserted and made descending for 1 records from > > 1 > > >> to 1, (or even if random number for ROWID - both these cases), the > > memory > > >> occupied is more. Why is this so? > > >> > > > > > > > > > > > > > > > > > > > > Be a better Globetrotter. Get better travel answers from someone who > > knows. Yahoo! Answers - Check it out. > > > http://answers.yahoo.com/dir/?link=list=396545469 > > > > > > > > - > > > To unsubscribe, send email to [EMAIL PROTECTED] > > > > > - > > > > > > > > > > > > > > > - > > To unsubscribe, send email to [EMAIL PROTECTED] > > > > - > > > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Lemon: Help on conflic resolution?
I am writing to ask for help about how to solve The Lemon parser conflicts. As part of a larger grammar, I am need to implement this regular expression in Lemon: (.+|'.+')+ I tried lots of grammars, but all of them generated Lemon warnings. Maybe someone could have a look at the grammar below and let me know how the conflicts can be solved, and why they are generated in the first place? Many thanks, Ralf -- %left CHAR. // Any character, except for apostrophe. %left APOS. // Apostrophe only. doc ::= inline. // One ore more CHARs. chars ::= CHAR. chars ::= chars CHAR. // Any sequence of just CHARs and 'CHARs' (surrounded by apostrophes). inline ::= chars. inline ::= APOS chars APOS. // The repeat. This causes conflicts. Isn't it allowed? Workarounds? inline ::= inline inline. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Behavior of SELECT queries during transaction
Hi all !
I would like to know what is the behavior of sqlite when performing a
SELECT query during a transaction ?
For example :
CREATE TABLE thebeatles (_ID INTEGER PRIMARY KEY, name TEXT);
BEGIN TRANSACTION;
INSERT INTO thebeatles (name) VALUES ('john');
INSERT INTO thebeatles (name) VALUES ('paul');
SELECT * FROM thebeatles;
INSERT INTO thebeatles (name) VALUES ('george');
INSERT INTO thebeatles (name) VALUES ('ringo');
...
COMMIT TRANSATION;
So,
Does sqlite support this kind of sequence (and how ?) or is it wrong ?
What about the performance issue ?
Thanks in advance,
Thomas
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] sqlite 3.x lock states
> A database is in the EXCLUSIVE state if one of the processes has an > EXCLUSIVE lock. Only *one* process at a time can hold an EX- CLUSIVE > lock. The process holding the EXCLUSIVE lock is currently writing to > the database file. Every other process must hold *no* lock. No other > process can acquire a lock of any kind. Does this apply to hot journal? http://www.sqlite.org/lockingv3.html In the above link the following is mentioned as the last step in dealing with hot journals. Drop the EXCLUSIVE and PENDING locks but retain the SHARED lock. Does the above sentence means it can hold EXCLUSIVE, PENDING and SHARED lock together? Regards, Phani -Original Message- From: Richard Klein [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 17, 2007 4:59 AM To: sqlite-users@sqlite.org Subject: [sqlite] sqlite 3.x lock states Hello all, I'm upgrading from sqlite 2 to 3, and am trying to get a handle on the new lock states. Here is my current understanding (by "process" I mean a process *or* thread that has opened the database): A database is in the UNLOCKED state if every process holds *no* lock of any kind on the database. A database is in the SHARED state if every process holds either no lock or a SHARED lock. Processes holding a SHARED lock may read, but not write, the database. A database is in the RESERVED state if one of the processes has a RESERVED lock. Only *one* process at a time can hold a RESERVED lock. The process holding the RESERVED lock intends to write to the database file by the time its current transaction ends. Every other process must hold either no lock or a SHARED lock. A process holding no lock can acquire a SHARED lock, but nothing stronger than that. Processes holding SHARED locks can continue to read. A database is in the PENDING state if one of the processes has a PENDING lock. Only *one* process at a time can hold a PENDING lock. The process holding the PENDING lock intends to write to the database file as soon as all other processes drop their SHARED locks. Every other process must hold either no lock or a SHARED lock. A process holding no lock cannot acquire a lock of any kind. Processes holding SHARED locks can continue to read. A database is in the EXCLUSIVE state if one of the processes has an EXCLUSIVE lock. Only *one* process at a time can hold an EX- CLUSIVE lock. The process holding the EXCLUSIVE lock is currently writing to the database file. Every other process must hold *no* lock. No other process can acquire a lock of any kind. Do I have this right? Thanks, - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] In-memory database: manually modifying ROWID value increases memory.
Dear all, I'll reframe the question again, If ROWID(hidden column/b-tree key/internal to all table) is changed manually, means I'll insert some unique values in random order / in descending order (say from 1 to 1), the memory occupied increases more. why? I observed entire table is getting sorted based on ROWID. My results were, for 1 records if it is normally inserted (ie., not modifying ROWID) it takes 500KB. The same thing if I modify the ROWID it consumes 1.5MB. why? Is that all the columns are indexed or only ROWID is indexed? Memory measurement is done by sqlite3GenericMalloc using SQLITE_ENABLE_MEMORY_MANAGEMENT macro enabled. Calculating each allocation size gives total memory allocation. Thanks. On 10/17/07, Vitali Lovich <[EMAIL PROTECTED]> wrote: > > My question is how you're measuring the memory useage? Are you > accounting for the space overhead of the various bookkeeping sqlite > needs (i.e. master table)? The way you're creating you're table implies > you're not using autoincrement for the integer field - are you > accounting for the extra internal row id column? > > Joe Wilson wrote: > > It could be malloc fragmentation. > > > > Which sqlite version, operating system, and malloc implementation are > you using? > > > > --- "Babu, Lokesh" <[EMAIL PROTECTED]> wrote: > > > >> Say I have 3 columns in one Table, with one INTEGER, two TEXT columns, > If > >> ROWID is manually inserted and made descending for 1 records from > 1 > >> to 1, (or even if random number for ROWID - both these cases), the > memory > >> occupied is more. Why is this so? > >> > > > > > > > > > > > > > Be a better Globetrotter. Get better travel answers from someone who > knows. Yahoo! Answers - Check it out. > > http://answers.yahoo.com/dir/?link=list=396545469 > > > > > - > > To unsubscribe, send email to [EMAIL PROTECTED] > > > - > > > > > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > >
