[sqlite] PRAGMA cache_size problem

2011-03-23 Thread Jaco Breitenbach
Dear experts, I have compiled sqlite-autoconf-3070500 on a RedHat Linux machine. My application is written in C, so uses the SQLite C interface. At startup I connect to an admin datafile, and then attach 20 more datafiles to the connection. After creating the initial connection (before

Re: [sqlite] PRAGMA cache_size problem

2011-03-23 Thread Jaco Breitenbach
Hi Dan, That fixed the problem for me. Thanks a lot, much appreciated! Best regards, Jaco On 23 March 2011 09:10, Dan Kennedy <danielk1...@gmail.com> wrote: > On 03/23/2011 03:26 PM, Jaco Breitenbach wrote: > > Dear experts, > > > > I have compiled sqlite-autocon

[sqlite] PRAGMA cache_size problem

2011-03-23 Thread Jaco Breitenbach
Dear all, I have compiled sqlite-autoconf-3070500 on a RedHat Linux machine. My application is written in C, so uses the SQLite C interface. At startup I connect to an admin datafile, and then attach 20 more datafiles to the connection. After creating the initial connection (before attaching

[sqlite] Permanently disabling AutoCommit

2011-03-24 Thread Jaco Breitenbach
Dear Experts, Is there an easy way to permanently disable the default AutoCommit using the C API? The nature of my application is such that I don't know exactly where a new transaction begins. Records are inserted into the database, and at the end an explicit COMMIT is requested. It seems that

[sqlite] Disabling a unique index

2011-04-08 Thread Jaco Breitenbach
Dear experts, My application makes use of a SQLite table with a unique index. During normal processing, the unique index is used to ensure no duplicate records are entered into the table. However, at regular intervals large numbers of records (millions of records) that are already known to be

Re: [sqlite] Disabling a unique index

2011-04-08 Thread Jaco Breitenbach
Hi Dan, I haven't changed the vacuum setting from the default, so it should be 0/NONE. Having just connected to it using the command line tool, the value returned was 0 as well. Jaco On 8 April 2011 11:12, Dan Kennedy <danielk1...@gmail.com> wrote: > On 04/08/2011 04:42 PM, Jaco Br

[sqlite] Problem setting journal_mode=WAL

2011-04-12 Thread Jaco Breitenbach
Dear experts, My application makes use of several data files attached to the SQLite database. Data files are attached on demand as they become necessary. I'm experimenting with the WAL journalling mode as a speed/performance optimisation. Unfortunately I'm having some difficulty setting the

[sqlite] Backing up online SQLite data files

2011-05-06 Thread Jaco Breitenbach
Dear experts, My application has several data files open (one main, several other attached) during normal operation. The journaling mode is WAL. I need to perform an online backup of the data files without shutting down the application. What is the risk in simply copying all files, i.e. data

Re: [sqlite] Query question

2011-05-06 Thread Jaco Breitenbach
Hi Matthew, sqlite> select * from a; 10|1 10|2 10|2 11|2 11|2 11|3 sqlite> select c.a, count(*) from (select distinct a, b from a) c group by c.a; 10|2 11|2 Was that the result you were after? Jaco On 6 May 2011 15:03, Matthew Jones wrote: > A simple query question

[sqlite] AIX 6.1 Autoconf support

2011-06-07 Thread Jaco Breitenbach
Dear experts, I've been trying to get SQLite compiled on an AIX 6.1 system. The configure script that came with version 3.7.3 contains several exceptions for different AIX versions, notably versions 4 and 5, but not 6. This means for example that it is unable to generate a shared library

Re: [sqlite] Insert not working for sqlite3

2011-07-07 Thread Jaco Breitenbach
Are you missing a COMMIT perhaps? On 7 July 2011 09:11, Stephan Beal wrote: > On Thu, Jul 7, 2011 at 9:31 AM, James_21th wrote: > > > try { > > $result=$dbh->exec("INSERT INTO tbl1(one,two) VALUES ('new1','new2')"); > > } catch(PDOExecption $e) { >

[sqlite] Maximum number of tables in a data file

2011-08-09 Thread Jaco Breitenbach
Dear experts, Can anyone please tell me if there is a limit to the number of tables that can be held in a single data file? I am considering an application that will require a table for every minute in a day, i.e. 3600+ tables in a single database or data file. Regards, Jaco

Re: [sqlite] Maximum number of tables in a data file

2011-08-09 Thread Jaco Breitenbach
a higher insert rate. I'd appreciate any feedback and comments on my suggested approach. Regards, Jaco On 9 August 2011 14:13, Igor Tandetnik <itandet...@mvps.org> wrote: > Jaco Breitenbach <jjbreitenb...@gmail.com> wrote: > > Can anyone please tell me if there is a limit to

Re: [sqlite] Maximum number of tables in a data file

2011-08-09 Thread Jaco Breitenbach
Yes, but each input record also contains a timestamp that can be used to identify the relevant table. On 9 August 2011 14:43, Igor Tandetnik <itandet...@mvps.org> wrote: > Jaco Breitenbach <jjbreitenb...@gmail.com> wrote: > > I am building an application that filters out

Re: [sqlite] Maximum number of tables in a data file

2011-08-09 Thread Jaco Breitenbach
Hi Gabriel, Is there such a database that is both free and non-GPL that you can recommend? Jaco On 9 August 2011 14:38, gabriel.b...@gmail.com <gabriel.b...@gmail.com>wrote: > Heve you ever considere using a NOSQL database I think it would serve you > better > > 2011/8/

Re: [sqlite] Speeding up index creation

2011-09-05 Thread Jaco Breitenbach
Dear all, Any speculation on if and how the new code would affect inserts into an indexed table? Jaco On 5 September 2011 09:15, Filip Navara wrote: > On Mon, Sep 5, 2011 at 9:39 AM, Baruch Burstein > wrote: > > This is unrelated, but I have

[sqlite] SQLite disc and I/O usage

2011-09-19 Thread Jaco Breitenbach
Dear experts, I am creating a (simple) model of the disc and I/O usage of my SQLite database so I can advise my customers on hardware requirements. My database schema is very simple. It contains only one table with a single column with a primary key: CREATE TABLE T ( K varchar(22)

Re: [sqlite] SQLite disc and I/O usage

2011-09-19 Thread Jaco Breitenbach
Hi Simon, Thanks for the reply. On 19 September 2011 13:23, Simon Slavin <slav...@bigfraud.org> wrote: > On 19 Sep 2011, at 10:13am, Jaco Breitenbach wrote: > > > CREATE TABLE T ( K varchar(22) PRIMARY KEY ); > > Note that SQLite doesn't really do 'varchar'.

Re: [sqlite] SQLite disc and I/O usage

2011-09-19 Thread Jaco Breitenbach
, Simon Slavin <slav...@bigfraud.org> wrote: > > On 19 Sep 2011, at 2:42pm, Jaco Breitenbach wrote: > > > On 19 September 2011 13:23, Simon Slavin <slav...@bigfraud.org> wrote: > > > >> If you're actually concerned about filesize, then WAL mode is probably

Re: [sqlite] SQLite disc and I/O usage

2011-09-19 Thread Jaco Breitenbach
Hi Christian, Thanks for your response. On 19 September 2011 16:45, Christian Smith <csm...@thewrongchristian.org.uk > wrote: > On Mon, Sep 19, 2011 at 02:42:42PM +0100, Jaco Breitenbach wrote: > > Hi Simon, > > > > Thanks for the reply. > > > > On 19 S

Re: [sqlite] SQLite disc and I/O usage

2011-09-19 Thread Jaco Breitenbach
On 19 September 2011 19:25, Simon Slavin <slav...@bigfraud.org> wrote: > > On 19 Sep 2011, at 7:13pm, Jaco Breitenbach wrote: > > > While on the topic of performance, I've performed a test in which new > tables > > were created within an active tra

[sqlite] Cache question

2011-09-26 Thread Jaco Breitenbach
Dear experts, I have a question about the following cache related functions: - sqlite3_config(SQLITE_CONFIG_PAGECACHE, B, S, P) - sqlite3_status(SQLITE_STATUS_PAGECACHE_USED, C, H, 0) - sqlite3_status(SQLITE_STATUS_PAGECACHE_OVERFLOW, C, H, 0) -

Re: [sqlite] Cache question

2011-09-26 Thread Jaco Breitenbach
I just wanted to make a correction: On 26 September 2011 22:50, Jaco Breitenbach <jjbreitenb...@gmail.com>wrote: > Now as the cache usage increases, I find that it finally caps at 3226.381 > MB (of the total 4096 MB) before the overflow size starts to increase. At > an initial pa

[sqlite] WAL checkpointing

2011-09-29 Thread Jaco Breitenbach
Dear all, In the WAL documentation (http://www.sqlite.org/wal.html) it is hinted that the checkpoint operation, which is normally in the same thread if execution as the database commit, could be moved to a separate thread or process. If I were to run the checkpoint in a separate thread (using

Re: [sqlite] Shared in-memory SQLite database in shared memory

2012-11-03 Thread Jaco Breitenbach
. Best regards, Jaco On Saturday, November 3, 2012, Simon Slavin wrote: > > On 3 Nov 2012, at 12:26pm, Jaco Breitenbach > <jjbreitenb...@gmail.com<javascript:;>> > wrote: > > > My application consists of several indepent processes that must all > access &

[sqlite] Page cache block size problem

2012-02-09 Thread Jaco Breitenbach
Dear experts, In my application performance is of critical importance. I've chosen to preallocate a large contiguous block of memory for the page cache. I make use of the following function to configure the page cache: sqlite3_config(SQLITE_CONFIG_PAGECACHE, void*, int sz, int N); The