Re: [sqlite] SQLite performance woe

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Brown, Daniel wrote: > I am not using the amalgamation version of the source as I have our my > VFS implementations for two of the platforms I work with based on the > original win_os.c VFS and the amalgamation does not provide the > necessary header

[sqlite] SQLite performance woe

Good evening list, I have been profiling the performance of SQLite version 3.6.1 against my current custom (hacktastic) runtime database solution (which I am hoping to replace with SQLite) and I just got a nasty and unexpected result: SQLite is a lot slower! I am running SQLite completely in

Re: [sqlite] please help with NULL and NOTHING?

Thnaks, Mohd and Dan, Dan, Your suggestion worked OK! Both on sqlite and MS SQL SERVER 2005. The whole select statement as an argument to coalesce function. Thanks Aivars 2008/12/1 Dan <[EMAIL PROTECTED]>: > If you are sure there is at most one entry in bilance1 where the account > and year

Re: [sqlite] query optimization for inner table join

"Jos van den Oever" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > 2008/12/1 Igor Tandetnik <[EMAIL PROTECTED]>: >> Try this: >> >> select n from map >> group by n >> having >>count(case when m=3 then 1 else null end) != 0 and >>count(case when m=5 then 1 else null end) != 0

Re: [sqlite] multiple tables within a database

Loosely speaking, I put things together in the same file if some of those things are necessary to understand or interpret the other things, or if there is a logical dependency between things (say, a foreign key), they go in the same file. Being in one file ensures that all the interdependent

[sqlite] multiple tables within a database

what's the general rule for deciding when to put multiple tables within a single sqlite db file? I think the answer is something like you put tables together in one database file if they refer to different aspects of the same data element and you put them in separate database files if there's no

Re: [sqlite] Journal files

Cheers adding the extra pragma has stopped the temporary file activity :) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp Sent: Monday, December 01, 2008 6:14 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Journal files

Re: [sqlite] Journal files

On Dec 1, 2008, at 6:49 PM, Stephen Abbamonte wrote: > I just tried this line also and the journal files are still being > created > here is the code I am running: > > >int32_t ret = sqlite3_open(filename, m_DatabaseRef); > if( ret == SQLITE_OK ) >{ >

Re: [sqlite] Journal files

On Dec 1, 2008, at 9:11 PM, Brown, Daniel wrote: > I'm using "PRAGMA journal_mode = MEMORY" combined with an in memory > database ":memory:" on version 3.6.1. And I too am seeing lots of > temporary file activity, which is really killing our performance as > our > storage medium is so slow.

Re: [sqlite] Journal files

I'm using "PRAGMA journal_mode = MEMORY" combined with an in memory database ":memory:" on version 3.6.1. And I too am seeing lots of temporary file activity, which is really killing our performance as our storage medium is so slow. -Original Message- From: [EMAIL PROTECTED]

Re: [sqlite] Journal files

I just tried this line also and the journal files are still being created here is the code I am running: int32_t ret = sqlite3_open(filename, m_DatabaseRef); if( ret == SQLITE_OK ) { sqlite3_stmt* sqlStmt = NULL; ret =

Re: [sqlite] Journal files

> I tried that on sqlite3 version 3.6.6 and the return value was "OFF" > but the > journals are still being created. Any reason why this wouldn't work? Did you set PRAGMA journal_mode = OFF; ? The way I read the documentation (on a second or third close reading, I think), this only

Re: [sqlite] Journal files

I tried that on sqlite3 version 3.6.6 and the return value was "OFF" but the journals are still being created. Any reason why this wouldn't work? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp Sent: Monday, December 01, 2008 9:56 AM To:

Re: [sqlite] Journal files

Does that control the creation of all temporary files created at runtime? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp Sent: Monday, December 01, 2008 7:56 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Journal

Re: [sqlite] Generating CRC values for tables

Hello! В сообщении от Wednesday 26 November 2008 22:37:19 Brown, Daniel написал(а): > Is there any functionality built into SQLite to generate CRC values for > tables?  We would like to be able to verify that the contents of the > table we just updated matches the intended contents.  Currently

Re: [sqlite] query optimization for inner table join

2008/12/1 Igor Tandetnik <[EMAIL PROTECTED]>: > Try this: > > select n from map > group by n > having >count(case when m=3 then 1 else null end) != 0 and >count(case when m=5 then 1 else null end) != 0 and >count(case when m=7 then 1 else null end) = 0; > > Having an index on map(n)

[sqlite] Missing TCL/TEA source code

I think the source code for SQLite with TCL for version 3.6.6.2 is missing from sqlite.org. Thanks, glauber ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite aggregate functions by Tcl

Hello! В сообщении от Monday 01 December 2008 18:16:04 D. Richard Hipp написал(а): > The current TCL interface for SQLite does not provide the ability to   > add aggregate functions written in TCL.  So in that sense, it is not   > possible.  However, the TCL interface could be extended to add

Re: [sqlite] Journal files

On Dec 1, 2008, at 10:50 AM, Eric Minbiole wrote: >>I am looking for a way to completely turn off the >> creation >> of journal files. Any help is much appreciated. > > http://www.sqlite.org/pragma.html And in particular http://www.sqlite.org/pragma.html#pragma_journal_mode >

Re: [sqlite] Journal files

> I am looking for a way to completely turn off the creation > of journal files. Any help is much appreciated. http://www.sqlite.org/pragma.html ___ sqlite-users mailing list sqlite-users@sqlite.org

[sqlite] Journal files

Hello all, I am looking for a way to completely turn off the creation of journal files. Any help is much appreciated. Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] SQLite aggregate functions by Tcl

On Dec 1, 2008, at 9:56 AM, Alexey Pechnikov wrote: > Hello! > > Is it possible? > The current TCL interface for SQLite does not provide the ability to add aggregate functions written in TCL. So in that sense, it is not possible. However, the TCL interface could be extended to add this

[sqlite] SQLite aggregate functions by Tcl

Hello! Is it possible? Best regards, Alexey. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Arranging of ids in Sqlite3

Hello! В сообщении от Wednesday 26 November 2008 13:57:02 Nikhil Kansal написал(а): > But if I delete a chunk of data then how can I know the id number. You can get rowid for last inserted row as select last_insert_rowid(); Best regards, Alexey. ___

Re: [sqlite] query optimization for inner table join

"Jos van den Oever" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I've trouble optimizing for an N:M mapping table. The schema of the > table is this: > > CREATE TABLE map (n INTEGER NOT NULL, m INTEGER NOT NULL); > > I want to retrieve a list of n filtered on the presence of

Re: [sqlite] Why must one write a mini SQL parser to read the columnnames?

Thanks, that's perfect! On Mon, Dec 1, 2008 at 3:06 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > "Ben Harper" <[EMAIL PROTECTED]> wrote in > message > news:[EMAIL PROTECTED] >> To the best of my findings, it seems to me that one needs to write a >> mini SQL parser in order to read the 'sql'

Re: [sqlite] Why must one write a mini SQL parser to read the columnnames?

"Ben Harper" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > To the best of my findings, it seems to me that one needs to write a > mini SQL parser in order to read the 'sql' field from sqlite_master, > in order to discover the fields in an Sqlite table. Have you looked at PRAGMA

[sqlite] Why must one write a mini SQL parser to read the column names?

To the best of my findings, it seems to me that one needs to write a mini SQL parser in order to read the 'sql' field from sqlite_master, in order to discover the fields in an Sqlite table. Is this really a necessary design? Would it not be better if sqlite3_table_column_metadata had a mode that

[sqlite] Partial search with fts

Previously someone advised that I use the "*" char to achieve partial search results with fts. eg ver* will match version. This works ok, but only for end parts of a word.   Is there anyway to get partial matches for beginning or middle parts of a word?   e.g. *sion - to match version or *si*

Re: [sqlite] please help with NULL and NOTHING?

If you are sure there is at most one entry in bilance1 where the account and year match then you could do this: SELECT coalesce( (SELECT dbs from bilance1 where account='13100' and pYear=?), 0 ) AS summadeb; On Dec 1, 2008, at 3:26 PM, aivars wrote: > Hello, > > The simple

[sqlite] query optimization for inner table join

Hi all, I've trouble optimizing for an N:M mapping table. The schema of the table is this: CREATE TABLE map (n INTEGER NOT NULL, m INTEGER NOT NULL); I want to retrieve a list of n filtered on the presence of certain values of m, e.g. give me all n for which there is an m = 3 and m = 5, but no

Re: [sqlite] please help with NULL and NOTHING?

You should handle this in your client program. Even in MS SQL or Oracle, it will not return any resultset. If you were to use left join, you may get it as NULL for any missing links. rgd, Radzi. - Original Message - From: "aivars" <[EMAIL PROTECTED]> To: Sent:

[sqlite] please help with NULL and NOTHING?

Hello, The simple query is like this: SELECT dbs as summadeb from bilance1 where account='13100' and pYear=?; Account number 13100 is not present in the table bilance1 when pYear=2005 and it should be like this and therefore dbs is also not present. Other years account number 13100 is present