Re: [sqlite] SQLite Step Function
Dear Stephen, Thanks for the reply. As you said we checked the EXPLAIN QUERY PLAN with our query and it has shown that all the four tables we use in the query are using their indexes and there is no ORDER BY class in our query. So sqlite3_prepare compiles the query and sqlite3_step executes the query does it mean the execution time for our query is 40 secs because we are retrieving the records soon once gets executed. Regards Kartthi Stephen Oberholtzer wrote: > > On Tue, May 27, 2008 at 9:06 AM, sqlite <[EMAIL PROTECTED]> > wrote: > >> >> Dear All, >> >> We are using SQLite for our application development which would be >> deployed >> in a pocket pc. Here we are using a query which has three Inner joins >> ,while >> using sqlite3_prepare statement we can able to prepare the records soon >> where as in sqilte3_step function we are facing a problem to fetch first >> record which makes more time, it takes around 40 seconds to get the fetch >> the first record whereas all other records are fetched quickly with in a >> fraction of second. We facing similar kind of problem each time while >> getting first record using Where condition or inner joins, kindly help us >> in >> this regard. >> >> Thanks in Advance, >> >> Regards, >> Kartthi > > > With no information as to how your database is being formed, I would start > with: > > 0. "sqlite3_prepare" does not prepare the data, it just prepares the > program that will be used to fetch the data. > 1. Try EXPLAIN QUERY PLAN [your select statement here] and see what it > says. If any of the joins are *not* using an index, that would be a > problem. > 2. Are you using an ORDER BY? That would mean SQLite has to process the > entire query (in order to sort the results) before returning the first > row. > > > -- > -- Stevie-O > Real programmers use COPY CON PROGRAM.EXE > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/SQLite-Step-Function-tp17490036p17505065.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query help?
"Doug" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Thank you Igor. The GROUP BY was the secret (I was trying to do a > GROUP BY on the outer select, but it wasn't quite cutting it). > > GROUP BY is very powerful, but I notice it has a performance cost. > Is there a way to use an index with it? I have EventTime indexed and > that index is being used. I suppose creating a combined index of > EventTime, ProcessID and FileName might help because the underlying > record wouldn't need to be looked up. Any thoughts on that idea? No, I don't see how such an index would help. Since EventTime is the first component, this index can't be used to enumerate rows in the order defined by (ProcessID, FileName) pair. Imagine two records with the same ProcessID and FileName but with EventTimes that are far apart. Such records won't be adjacent in your index. You could create an index on (ProcessID, FileName). But SQLite can only use one index in a single query. You should test and measure which of the two indexes results in better performance. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query help?
Thank you Igor. The GROUP BY was the secret (I was trying to do a GROUP BY
on the outer select, but it wasn't quite cutting it).
GROUP BY is very powerful, but I notice it has a performance cost. Is there
a way to use an index with it? I have EventTime indexed and that index is
being used. I suppose creating a combined index of EventTime, ProcessID and
FileName might help because the underlying record wouldn't need to be looked
up. Any thoughts on that idea?
Thanks
Doug
> -Original Message-
> From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On
> Behalf Of Igor Tandetnik
> Sent: Tuesday, May 27, 2008 12:24 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Query help?
>
> Doug <[EMAIL PROTECTED]> wrote:
> > SELECT ProcessID, count(1), sum(BytesProcessed)
> > FROM FinishedWork
> > WHERE EventTime > {20 minutes ago}
> > GROUP BY ProcessID, FileName
> >
> > Unfortunately when a file is processed twice, it's counted twice (ie
> > added into the sum twice) and I need to show only unique work, so I
> > need to count each processID-FileName pair only once for the given
> > timeframe.
>
> Try this:
>
> SELECT ProcessID, 1, BytesProcessed
> FROM FinishedWork
> WHERE RowId IN (
> select RowId from FinishedWork
> WHERE EventTime > {20 minutes ago}
> GROUP BY ProcessID, FileName
> );
>
> Igor Tandetnik
>
>
>
> ___
> 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
Re: [sqlite] Why is SQLite so slow across a network (afp Mac OS X)?
> > > > > > BTW, those PRAGMAs made little difference. I resorted to caching the > remote > > file to the local drive via a fast OS-level file copy then doing the SQL > > R/W, then copying back to the remote in a bkgnd thread. A programming > > headache to keep everything in sync, but very acceptable performance. > > > Actually, you might want to try using a larger page size. SQLite uses, by > default, 1KB pages. Increasing that to 16KB or perhaps larger will not > only reduce the overhead of BLOBs, but also increase performance > significantly, as each page will be going across the network one by one. > Tried that too (I believe I set to the max of 32KB) with little improvement. In fact I had: PRAGMA page_size = SQLITE_MAX_PAGE_SIZE PRAGMA cache_size = 100 PRAGMA synchronous = OFF With little improvement. P. -- - Peter K. Stys, MD Dept. of Clinical Neurosciences Hotchkiss Brain Institute University of Calgary tel (403) 210-8646 - ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Checking for open transactions attach/detach database
I think I have found a solution After the commit I have set up a loop with a 1 sec sleep that checks for the existence of the file "ATTACHedDatabase-journal" Once it no longer exists the script continues, DETACHing the database without error. I would have thought there would be a similar SQL command to check for the existence of a ...-journal file. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of MoDementia Sent: Wednesday, 28 May 2008 6:10 AM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Checking for open transactions attach/detach database Thanks for the clarification. The error I am receiving must then be produced by the application rather than just reporting an SQLite error. Or The application is taking control of the commit wait time, allowing my script to continue. Or more correct perhaps; the script commands are passed to the application which allows the script to continue producing the DETACH error. * There was a problem querying the database: Error executing SQL statement "DETACH ExportDB " : SQL logic error or missing database (1,1) Cancel, Retry, Ignore The error is ambiguous at best but I believe the second scenario is what is happening given the consistent time before clicking "Retry" is accepted without error. I have posted the information received so far to the developers however, I'm not expecting the application to be corrected/changed in a hurry as ATTACH/DETACH are unusual events in user scripts. So the question remains: Is there a way to check for open transactions / locks from the command line? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote Sent: Tuesday, 27 May 2008 11:23 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Checking for open transactions attach/detach database MoDementia wrote: > The database file is ready to (copy) Detach as soon as the commit completes. > Yes. > This is the problem. > Detach: "This statement will fail if SQLite is in the middle of a > transaction." This is true. This is why you must commit your transaction before you can detach. > If I commit 10k row updates I cannot detach the database until it is > finished. > The database (SQLite) will be finished all its processing by the time it returns to your program after executing the commit statement. Attach Begin Loop to insert many rows Commit Detach > I need to be able to check some sort of table entry that will be clear once > the commit is finished. The commit is finished when it returns to your program. > > I don't have access to any of the higher level functions so it needs to be > something like > > SELECT Commit_Status FROM Active_Transactions > > Then I can wait till Commit_Status = something > Before I attempt to DETACH the database > > Obviously the application (written in some C language) can tell that it is > still committing and throws the error > But I need to do this check from the command line only The same logic applies to the command line. It has completed its processing by the time it displays the prompt after you enter the commit command. > > I hope this is making sense > Sort of. It seems like you are worrying about a non issue. HTH Dennis COte ___ 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite allows "RowID" to be the name of a column
>
> The normal proper way to do what you said is to declare a table like this:
>
> CREATE TABLE person (
> person_id INT PRIMARY KEY,
> name TEXT,
> birthdate DATE
> )
>
> In my example, you are using only the normal data, which is the 3 columns
> specified, and you are not referring to a column you didn't declare
> ("rowid" or whatever), but by a column you did declare, "person_id".
>
> By contrast, defining a table like this is inferior:
>
> CREATE TABLE person (
> name TEXT,
> birthdate DATE
> )
>
> In that example, the rowid would be generated and you can't use something
> externally meaningful (such as SSN) to distinguish one Joe from another.
I can use the generated rowid to link this table to another containing
meaningful data or I don't need other data besides the ones in this table.
I can use an explicitly defined rowid or the automatically generated one ...
> My point still stands. Or my other point of adding a LIMIT clause to
> UPDATE also stands if you want to create tables the second way.
you can use LIMIT in UPDATE only if data isn't meaningful (in this case you
can habe only one row), otherwise ignoring the generated rowid would break
the db
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Cross compiling sqlite3.c, anamolies.
Stephen Oberholtzer wrote:
> On Tue, May 27, 2008 at 3:59 PM, Richard Klein <[EMAIL PROTECTED]>
> wrote:
>
>>> On May 26, 2008, at 3:24 PM, A. H. Ongun wrote:
Now, when I change the compiler to ppc_82xx-g++ from ppc_82xx-gcc I
get hundreds of error messages.
I am puzzled to see why this is so.
>>> My guess would be because SQLite is written in C, not C++.
>>>
>>> D. Richard Hipp
>>> [EMAIL PROTECTED]
>> My company often needs to compile SQLite under C++, so we ran into the
>> same problem. It's easy to get rid of the error messages: Mostly it's
>> a matter of adding explicit typecasts, and of separating nested structs.
>
>
> An "extern C" wrapper doesn't work?
No, the syntax
extern "C" {
...
}
tells the C++ compiler to generate C-style linkage for all functions
declared within the curly braces, i.e. to allow the functions to be
callable from C programs. It doesn't mean "compile everything within
the braces as if it were C".
However, you may be able to use the appropriate command-line option
for your compiler. For example, the -xc option tells gcc to compile
the input file as C, regardless of the file's extension.
- Richard Klein
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite allows "RowID" to be the name of a column
Federico Granata wrote:
>> Your example doesn't counter my suggestion at all, which is to use the data
>> only and not a special rowid. So you put 2 identical rows in a table.
>> Since rows in a table are unordered, there isn't even an ordinal position
>> to distinguish the 2 occurrences of that same row. Since they are
>> identical, they are redundant, and so they are equivalent to just 1 such
>> row. So updating both copies is perfectly fine. Though better yet is to
>> not store a second copy in the first place.
>>
> LOL
> English isn't my first language but I think you are joking ...
>
> If I want to make a table with a list of people (name and age) I can have
> two or more row with the same name and age and they aren't redundant and the
> implicit rowid is different.
The normal proper way to do what you said is to declare a table like this:
CREATE TABLE person (
person_id INT PRIMARY KEY,
name TEXT,
birthdate DATE
)
In my example, you are using only the normal data, which is the 3 columns
specified, and you are not referring to a column you didn't declare
("rowid" or whatever), but by a column you did declare, "person_id".
By contrast, defining a table like this is inferior:
CREATE TABLE person (
name TEXT,
birthdate DATE
)
In that example, the rowid would be generated and you can't use something
externally meaningful (such as SSN) to distinguish one Joe from another.
My point still stands. Or my other point of adding a LIMIT clause to
UPDATE also stands if you want to create tables the second way.
-- Darren Duncan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can't create table from a trigger
Stephen Oberholtzer <[EMAIL PROTECTED]> wrote: > On Tue, May 27, 2008 at 4:15 PM, > [EMAIL PROTECTED] > <[EMAIL PROTECTED]> wrote: > One of the fields takes few values (say, company website id), but new > ids >> are >> added from time to time. I would prefer to dynamically create a set >> of tables >> when a new id shows up, for the improved locality of reference plus >> reduced overhead from the website id and its non-unique index. > > > This statement is really confusing me. "New IDs are added from time > to > time" sounds like "New IDs are added, but not very often" which > conflicts > with your "realtime" assertion. Lots of new records are added, but a particular field in these records takes on only a small number of distinct values. Occasionally - rarely - a new value appears in this field that never appeared before. For performance reasons (whether valid or imagined), the OP wants to keep a set of records that all share the same value in this field in a separate table, one for each possible value. This way, when a new such value appears, a new table needs to be created. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite on RAM
> > > > For windows the easiest thing to do would be setting up the ram disk > and > > > store database there. > > > > easier than using :memory: ??? > > Not easier, but sometimes more useful. Using a RAM disk means going > through the OSes file manager, which adds some overhead. On the > other hand, you can copy the database file to (or from) a more > traditional storage-backed filesystem at any time... something you > can't do with a :memory: database. > I can dump a :memory: db ... but I get your point ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Cross compiling sqlite3.c, anamolies.
On Tue, May 27, 2008 at 3:59 PM, Richard Klein <[EMAIL PROTECTED]> wrote: > > On May 26, 2008, at 3:24 PM, A. H. Ongun wrote: > >> Now, when I change the compiler to ppc_82xx-g++ from ppc_82xx-gcc I > >> get hundreds of error messages. > >> > >> I am puzzled to see why this is so. > > > > My guess would be because SQLite is written in C, not C++. > > > > D. Richard Hipp > > [EMAIL PROTECTED] > > My company often needs to compile SQLite under C++, so we ran into the > same problem. It's easy to get rid of the error messages: Mostly it's > a matter of adding explicit typecasts, and of separating nested structs. An "extern C" wrapper doesn't work? > > > - Richard Klein > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can't create table from a trigger
On Tue, May 27, 2008 at 05:15:27PM -0300, [EMAIL PROTECTED] scratched on the wall: > I need to handle tables with several million records, on realtime, from RAM. > for the improved locality of reference plus reduced Do you mean a :memory: database? RAM doesn't really have locality of reference the same way disk does... even in modern processors the cache lines are MUCH smaller than the default database block size, so there is no locality advantage to be found (assuming the DB isn't so large that the VM system starts to page it out). > overhead from the website id and its non-unique index. Unless you have a large number of unique ids, relative to the number of rows, the index wouldn't be used anyways. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "'People who live in bamboo houses should not throw pandas.' Jesus said that." - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite allows "RowID" to be the name of a column
> > Your example doesn't counter my suggestion at all, which is to use the data > only and not a special rowid. So you put 2 identical rows in a table. > Since rows in a table are unordered, there isn't even an ordinal position > to distinguish the 2 occurrences of that same row. Since they are > identical, they are redundant, and so they are equivalent to just 1 such > row. So updating both copies is perfectly fine. Though better yet is to > not store a second copy in the first place. > LOL English isn't my first language but I think you are joking ... If I want to make a table with a list of people (name and age) I can have two or more row with the same name and age and they aren't redundant and the implicit rowid is different. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can't create table from a trigger
On Tue, May 27, 2008 at 4:15 PM, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > >De: [EMAIL PROTECTED] > >Fecha: 27/05/2008 19:56 > > > >It's not supposed to, according to > >http://sqlite.org/lang_createtrigger.html . The syntax > >only allows select, insert, update and delete statements. > > > >What are you trying to achieve? > > I need to handle tables with several million records, on realtime, from > RAM. > One of the fields takes few values (say, company website id), but new ids > are > added from time to time. I would prefer to dynamically create a set of > tables > when a new id shows up, for the improved locality of reference plus reduced > overhead from the website id and its non-unique index. > Okay, this system seems to be screaming "doing something wrong" to me. First: "Handle tables with several million records in realtime" is incredibly vague and ambiguous. First off, I have no idea what constitutes "handling". Updating? Inserting? Selecting? Secondly, I have no idea what constitutes "realtime". How fast is real time? Once per second? One billion per second? CREATE TABLE ttt ( t INTEGER PRIMARY KEY ); > > CREATE TRIGGER ttt_new_trigger AFTER INSERT ON ttt FOR EACH ROW > BEGIN >CREATE TABLE uuu ( u INTEGER PRIMARY KEY ); > END; > This is really nonsensical. You can only have one table named 'uuu' at any given moment. Even if SQLite supported the syntax you're using, that table would be recreated on the first insert to 'ttt' and then subsequent attempts would fail with a 'table already exists' error. One of the fields takes few values (say, company website id), but new ids > are > added from time to time. I would prefer to dynamically create a set of > tables > when a new id shows up, for the improved locality of reference plus reduced > overhead from the website id and its non-unique index. This statement is really confusing me. "New IDs are added from time to time" sounds like "New IDs are added, but not very often" which conflicts with your "realtime" assertion. You say want to dynamically create a set of tables when a new ID shows up, yet your example only tries to add one table. My next quesiton would have been, "Why don't you just pregenerate your tables" except for the "locality of reference" explanation. Unfortunately, this indicates to me that you don't really understand how SQLite works. SQLite groups data in pages -- once data is split across two pages, you can't really assume those two pages are anywhere close to each other in the database file. Throw in filesystem fragmentation and even fewer assumptions can be made. So creating a table at the last minute doesn't mean its data will be in a different area of the file -- that all depends on when the data was added to the database. -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite on RAM
On Tue, May 27, 2008 at 10:41:18PM +0200, Federico Granata scratched on the wall: > > > > For windows the easiest thing to do would be setting up the ram disk and > > store database there. > > easier than using :memory: ??? Not easier, but sometimes more useful. Using a RAM disk means going through the OSes file manager, which adds some overhead. On the other hand, you can copy the database file to (or from) a more traditional storage-backed filesystem at any time... something you can't do with a :memory: database. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "'People who live in bamboo houses should not throw pandas.' Jesus said that." - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can't create table from a trigger
[EMAIL PROTECTED] wrote: >> De: [EMAIL PROTECTED] >> Fecha: 27/05/2008 19:56 >> >> It's not supposed to, according to >> http://sqlite.org/lang_createtrigger.html . The syntax >> only allows select, insert, update and delete statements. >> >> What are you trying to achieve? > > I need to handle tables with several million records, on realtime, > from RAM. One of the fields takes few values (say, company website > id), but new ids are added from time to time. I would prefer to > dynamically create a set of tables when a new id shows up, for the > improved locality of reference plus reduced overhead from the website > id and its non-unique index. Even if CREATE TABLE were allowed inside a trigger, how did you plan to come up with unique table names? A table name must be an identifier, not a calculated expression. In other words, you can't do things like CREATE TABLE 'prefix' || id ... ; -- or SELECT * FROM 'prefix' || id; You would have to do all such statements in your application code, buidling the strings on the fly. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite on RAM
> > For windows the easiest thing to do would be setting up the ram disk and > store database there. easier than using :memory: ??? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can't create table from a trigger
>De: [EMAIL PROTECTED] >Fecha: 27/05/2008 19:56 > >It's not supposed to, according to >http://sqlite.org/lang_createtrigger.html . The syntax >only allows select, insert, update and delete statements. > >What are you trying to achieve? I need to handle tables with several million records, on realtime, from RAM. One of the fields takes few values (say, company website id), but new ids are added from time to time. I would prefer to dynamically create a set of tables when a new id shows up, for the improved locality of reference plus reduced overhead from the website id and its non-unique index. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Checking for open transactions attach/detach database
Thanks for the clarification. The error I am receiving must then be produced by the application rather than just reporting an SQLite error. Or The application is taking control of the commit wait time, allowing my script to continue. Or more correct perhaps; the script commands are passed to the application which allows the script to continue producing the DETACH error. * There was a problem querying the database: Error executing SQL statement "DETACH ExportDB " : SQL logic error or missing database (1,1) Cancel, Retry, Ignore The error is ambiguous at best but I believe the second scenario is what is happening given the consistent time before clicking "Retry" is accepted without error. I have posted the information received so far to the developers however, I'm not expecting the application to be corrected/changed in a hurry as ATTACH/DETACH are unusual events in user scripts. So the question remains: Is there a way to check for open transactions / locks from the command line? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote Sent: Tuesday, 27 May 2008 11:23 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Checking for open transactions attach/detach database MoDementia wrote: > The database file is ready to (copy) Detach as soon as the commit completes. > Yes. > This is the problem. > Detach: "This statement will fail if SQLite is in the middle of a > transaction." This is true. This is why you must commit your transaction before you can detach. > If I commit 10k row updates I cannot detach the database until it is > finished. > The database (SQLite) will be finished all its processing by the time it returns to your program after executing the commit statement. Attach Begin Loop to insert many rows Commit Detach > I need to be able to check some sort of table entry that will be clear once > the commit is finished. The commit is finished when it returns to your program. > > I don't have access to any of the higher level functions so it needs to be > something like > > SELECT Commit_Status FROM Active_Transactions > > Then I can wait till Commit_Status = something > Before I attempt to DETACH the database > > Obviously the application (written in some C language) can tell that it is > still committing and throws the error > But I need to do this check from the command line only The same logic applies to the command line. It has completed its processing by the time it displays the prompt after you enter the commit command. > > I hope this is making sense > Sort of. It seems like you are worrying about a non issue. HTH Dennis COte ___ 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
Re: [sqlite] Cross compiling sqlite3.c, anamolies.
> On May 26, 2008, at 3:24 PM, A. H. Ongun wrote: >> Now, when I change the compiler to ppc_82xx-g++ from ppc_82xx-gcc I >> get hundreds of error messages. >> >> I am puzzled to see why this is so. > > My guess would be because SQLite is written in C, not C++. > > D. Richard Hipp > [EMAIL PROTECTED] My company often needs to compile SQLite under C++, so we ran into the same problem. It's easy to get rid of the error messages: Mostly it's a matter of adding explicit typecasts, and of separating nested structs. - Richard Klein ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can't create table from a trigger
[EMAIL PROTECTED] wrote: > Does not work: > > CREATE TABLE ttt ( t INTEGER PRIMARY KEY ); > > CREATE TRIGGER ttt_new_trigger AFTER INSERT ON ttt FOR EACH ROW > BEGIN >CREATE TABLE uuu ( u INTEGER PRIMARY KEY ); > END; It's not supposed to, according to http://sqlite.org/lang_createtrigger.html . The syntax only allows select, insert, update and delete statements. What are you trying to achieve? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Can't create table from a trigger
Does not work: CREATE TABLE ttt ( t INTEGER PRIMARY KEY ); CREATE TRIGGER ttt_new_trigger AFTER INSERT ON ttt FOR EACH ROW BEGIN CREATE TABLE uuu ( u INTEGER PRIMARY KEY ); END; SQL error: near "CREATE": syntax error If I try the CREATE TABLE outside the trigger, it succeds. If I replace CREATE TABLE... with SELECT 1, SQLite accepts the trigger. Is this a limitation, or am I missing something? Thanks! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite on RAM
On Wed, 28 May 2008 13:44:51 +1930, you wrote: >My data Base will run on Linux, Is it possible that? It works the same on all platforms. Igor Tandetnik already pointed out: If you use ":memory:" as a file name in the sqlite3_open() call SQLite creates an in-memory database not backed by disk storage. The same happens in the SQLite command line tool if you do not use a database file name as a commandline parameter. In-memory databases created in this way disappear completely after sqlite3_close(), or after exit program, or after the .quit command in the command line tool. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 'insert or ignore' vs self join?
On Tue, May 27, 2008 at 2:41 PM, Petite Abeille <[EMAIL PROTECTED]> wrote: > Hello, > > % sqlite3 -version > 3.5.9 > > I'm trying to figure out a frugal way to handle a unique key > constrain... > > I tried using both 'insert or ignore' and a self join. The self join > seems to be noticeably faster even though 'insert or ignore' would > empirically appear to be the better deal (shorter query plan, less VM > instructions). > > Specifically, given the following DML: > > insert or ignore > intotoken( name ) > select stage.token as name > fromstage > order bystage.token; > > One gets a query plan like such: > > 0|0|TABLE stage > > And 'explain' reports 58 VM instructions. > > > On the other hand, the following self join... > > insert > intotoken( name ) > select stage.token as name > fromstage > left join token on token.name = stage.token > where token.id is null > order bystage.token; > > ... uses a query plan like such: > > 0|0|TABLE stage > 1|1|TABLE token WITH INDEX token_name > > ... and 82 VM instructions. > > Nonetheless, the self join would appear to be around 10% faster than > the 'insert or ignore' flavor. > > Not sure why this is the case though... considering the apparent > overhead incurred by the join. > > Thoughts? > Well, the first thing you should bring away from this experience is that the number of VM instructions isn't really an indicator of how efficient the query is :) Now, I'm not sure exactly why one is faster than the other, especially since you didn't post your exact schema and indices, and I have no idea how many rows there are in either table. But if I had to guess, it's because of the ORDER BY clause. In general, an ORDER BY means that SQLite needs to generate a temporary table with all the rows to be selected/inserted, then sort that temporary table. The INSERT OR IGNORE version has to unconditionally sort the entire 'stage' table; your second query only has to sort those rows in 'stage' that don't already exist in 'table'. If each table fits comfortably in your computer's disk cache, the extra pass won't matter so much. In any case, I invite you to try the following: 1. Add an index: [[ create index stage_token_ix on stage(token); ]] SQLite will use that index to improve the ORDER BY. 2. Try the following variation: insert intotoken( name ) select stage.token as name fromstage where not exists(select 1 from token where token.name = stage.token) order bystage.token; -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] 'insert or ignore' vs self join?
Hello, % sqlite3 -version 3.5.9 I'm trying to figure out a frugal way to handle a unique key constrain... I tried using both 'insert or ignore' and a self join. The self join seems to be noticeably faster even though 'insert or ignore' would empirically appear to be the better deal (shorter query plan, less VM instructions). Specifically, given the following DML: insert or ignore intotoken( name ) select stage.token as name fromstage order bystage.token; One gets a query plan like such: 0|0|TABLE stage And 'explain' reports 58 VM instructions. On the other hand, the following self join... insert intotoken( name ) select stage.token as name fromstage left join token on token.name = stage.token where token.id is null order bystage.token; ... uses a query plan like such: 0|0|TABLE stage 1|1|TABLE token WITH INDEX token_name ... and 82 VM instructions. Nonetheless, the self join would appear to be around 10% faster than the 'insert or ignore' flavor. Not sure why this is the case though... considering the apparent overhead incurred by the join. Thoughts? -- PA. http://alt.textdrive.com/nanoki/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sqlite on RAM
My data Base will run on Linux, Is it possible that? -- Ing. Hildemaro Carrasquel Ingeniero de Proyectos Cel.: 04164388917/04121832139 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query help?
Doug <[EMAIL PROTECTED]> wrote:
> SELECT ProcessID, count(1), sum(BytesProcessed)
> FROM FinishedWork
> WHERE EventTime > {20 minutes ago}
> GROUP BY ProcessID, FileName
>
> Unfortunately when a file is processed twice, it's counted twice (ie
> added into the sum twice) and I need to show only unique work, so I
> need to count each processID-FileName pair only once for the given
> timeframe.
Try this:
SELECT ProcessID, 1, BytesProcessed
FROM FinishedWork
WHERE RowId IN (
select RowId from FinishedWork
WHERE EventTime > {20 minutes ago}
GROUP BY ProcessID, FileName
);
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite allows "RowID" to be the name of a column
On May 27, 2008, at 12:50 PM, Nicolas Williams wrote: > On Mon, May 26, 2008 at 11:20:27AM -0400, D. Richard Hipp wrote: >> SQLite already allows three different names for the rowid: "rowid", >> "oid", "_rowid_". If all three names are taken, for example if the >> user has a table like this: >> >> CREATE TABLE badidea( >> rowid TEXT, >> oid TEXT, >> _rowid_ TEXT >> ); >> >> Then you cannot access the rowid. It just cannot be done. But how >> often does that happen really? If it does happen, then perhaps >> SQLiteSpy could pop up a dialog box saying that it cannot display the >> content of the table and explaining why not. > > I agree. BUT, if there's also an INTEGER PRIMARY KEY column, then > there > should be an API by which to find out what that column's name is for a > given table. PRAGMA table_info(tablename); In the output of this pragma if there is only a single column with the "pk" set to 1 and if the "type" of that column is "integer", then that column is your integer primary key. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite on RAM
Hi, Hildemaro Carrasquel wrote: > How do i do for running on RAM? Unless I misunderstand your question, you've already asked: http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2008-May/003023.html Weren't the answers useful? -- Dimitri ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
