Re: [sqlite] Improving Bulk Insert Speed (C/C++)

I have not discovered how to find internal memory throughput usage in OSX, and I agree that something is not allowing the system from maxing out the CPU or I/O. Also, the unix time command does reveal that part of the program’s running time is spent in the system, though I do not know yet how

Re: [sqlite] Improving Bulk Insert Speed (C/C++)

Hi Shane, The SQLite page_size is on its default setting (1024b), while I have not dug out the details on my specific SSD (Apple SSD SD512E, with early 2013 retina MBPs), a cursory Google search reveals SSDs report a 512b sector size for compatibility reasons while internally implementing a

Re: [sqlite] Test for pending writes ?

On Wed, Apr 2, 2014 at 5:59 PM, Adrian Soundy < adrian.sou...@integration.co.nz> wrote: > > > Is there a Sqlite method that will tell me if there is any pending writes > from another process so I can end and commit the current transaction > straight away ? > No. Doing that would require some

Re: [sqlite] Improving Bulk Insert Speed (C/C++)

Interesting. Does OSX provide any way to get a measure of the internal memory throughput usage? Clearly something has to be hitting the a limit since a free running program with no internal blocking should always push some physical constraint to the limit. I agree that the bursting you see

Re: [sqlite] Improving Bulk Insert Speed (C/C++)

I had a few suggestions and questions earlier in this thread that I don't think have been responded to. And yes, without seeing the source it will be difficult to make more suggestions. On Apr 2, 2014 4:57 PM, "Kevin Xu" wrote: > The app seems to use between 60-80% CPU while

Re: [sqlite] Join of two virtual tables returns incorrect result set in 3.8.4.1

Attachments can't appear on this list. You can use a shared file service and post a link, or for smallish amounts of text use something like pastbin.com. On Wed, Apr 2, 2014 at 6:42 PM, Andy Goth wrote: > On 4/2/2014 4:52 PM, Hinrichsen, John wrote: > >> sqlite

Re: [sqlite] Join of two virtual tables returns incorrect result set in 3.8.4.1

On 4/2/2014 4:52 PM, Hinrichsen, John wrote: sqlite 3.8.4.1 can return an incorrect result when joining two virtual tables that are themselves based on underlying sqlite tables. This problem does not happen with sqlite 3.8.3.1 or earlier. Please see the attached repro. Attachment appears to

[sqlite] Test for pending writes ?

Hello, I have a process on an embedded device that is continuously updating the database in WAL mode. To get the performance we require in the busy cases we put everything in to transactions. Sometimes another process needs to write to the same database in a timely manner. Is there

[sqlite] Join of two virtual tables returns incorrect result set in 3.8.4.1

sqlite 3.8.4.1 can return an incorrect result when joining two virtual tables that are themselves based on underlying sqlite tables. This problem does not happen with sqlite 3.8.3.1 or earlier. Please see the attached repro. -- This message contains confidential information and is intended

Re: [sqlite] SQLite3_create_collation

CoreData, last I have heard only use the sqlite's storage capability, "not really sqlite in the sense people might understand", BTW that's the reason they don't need metadata and some other stuff, because their sqlite-lib serves only CoreData purposes, "not really a public" wee wee, similar to the

Re: [sqlite] SQLite3_create_collation

On Apr 2, 2014, at 1:46 PM, James Berry wrote: > > On Apr 2, 2014, at 1:26 PM, Donald Steele wrote: > >> What I’m talking about that is part of Xcode is the SQLite framework not >> APIs. The access to SQLite is done with the Obj C APIs as defined by

Re: [sqlite] SQLite3_create_collation

Hello, "First off, there are no Obj-C APIs for SQLite built into iOS" yes or not that's named CoreData which saves its graph either to a plist or a sqlite db, Second off, don't use the sqlite-lib delivered by the sub-system; inline your own version, (don't ask why, just do), see build options

Re: [sqlite] SQLite3_create_collation

On 2 Apr 2014, at 8:51pm, Donald Steele wrote: > If I was having no problems I would not have asked the question. I will > continue to look for better information & will eventually get it done. Thanks > anyway. Well, your answer doesn't make sense and people are drawing

Re: [sqlite] Improving Bulk Insert Speed (C/C++)

The app seems to use between 60-80% CPU while it is running (from Activity Monitor) while disk use (using sudo iotop -P on OSX) seem to suggest about 20%-30% I/O use. (spikes to over 30million% every 10s or so, might be when sqlite decides to page out) Clearly my computations on the data are

Re: [sqlite] SQLite3_create_collation

On Apr 2, 2014, at 1:26 PM, Donald Steele wrote: > What I’m talking about that is part of Xcode is the SQLite framework not > APIs. The access to SQLite is done with the Obj C APIs as defined by SQLite. > I am a fairly new developer and may be having some problems

Re: [sqlite] SQLite3_create_collation

What I’m talking about that is part of Xcode is the SQLite framework not APIs. The access to SQLite is done with the Obj C APIs as defined by SQLite. I am a fairly new developer and may be having some problems keeping up with folks with more time. So if I’m using incorrect terms that is my

Re: [sqlite] SQLite3_create_collation

On Apr 2, 2014, at 1:12 PM, Donald Steele wrote: > Correct. I’m using basically SQLite’s Obj C APIs. the SQLite3 framework is > built into X code which saves set up time at the beginning. There are no built-in Obj-C APIs to SQLite. Are you using Core Data, which is an

Re: [sqlite] SQLite3_create_collation

Correct. I’m using basically SQLite’s Obj C APIs. the SQLite3 framework is built into X code which saves set up time at the beginning. On Apr 2, 2014, at 12:58 PM, Eric Sink wrote: > Does this mean all your interaction with SQLite is happening through Core > Data? > > E

Re: [sqlite] SQLite3_create_collation

Does this mean all your interaction with SQLite is happening through Core Data? E On Apr 2, 2014 1:05 PM, "Donald Steele" wrote: > I am working in iOS (aka ObjC) so I am using it's built in framework for > all my SQLite calls. > > > On Apr 2, 2014, at 10:58 AM, Simon

Re: [sqlite] SQLite3_create_collation

If I was having no problems I would not have asked the question. I will continue to look for better information & will eventually get it done. Thanks anyway. On Apr 2, 2014, at 12:13 PM, Simon Slavin wrote: > > On 2 Apr 2014, at 7:04pm, Donald Steele

Re: [sqlite] SQLite3_create_collation

On 2 Apr 2014, at 7:04pm, Donald Steele wrote: > I am working in iOS (aka ObjC) so I am using it’s built in framework for all > my SQLite calls. > > On Apr 2, 2014, at 10:58 AM, Simon Slavin wrote: > >> On 2 Apr 2014, at 6:56pm, Donald Steele

Re: [sqlite] Improving Bulk Insert Speed (C/C++)

On Wed, Apr 2, 2014 at 6:57 PM, Dominique Devienne wrote: > runtime, and parsing differently, avoid std::string, might reap more > benefits. > A related tip not yet mentioned: if your strings will outlive their statements' next insertion step(), you can use SQLITE_TRANSIENT

Re: [sqlite] SQLite3_create_collation

On Wed, Apr 2, 2014 at 7:45 PM, Richard Hipp wrote: > SELECT * FROM table ORDER BY column COLLATE naturalSortOrder; > > Where your special collating function has previously been registered under > the name "naturalSortOrder", then your comparison function will be used to >

Re: [sqlite] SQLite3_create_collation

I am working in iOS (aka ObjC) so I am using it’s built in framework for all my SQLite calls. On Apr 2, 2014, at 10:58 AM, Simon Slavin wrote: > > On 2 Apr 2014, at 6:56pm, Donald Steele wrote: > >> Registering of the custom collation is far from

Re: [sqlite] SQLite3_create_collation

On 2 Apr 2014, at 6:56pm, Donald Steele wrote: > Registering of the custom collation is far from intuitive (for me) and thus > the reason for my question. What programming language is your collation function written in ? How are you calling your SQLite functions ? Are

Re: [sqlite] SQLite3_create_collation

Correct I am only sorting strings, as in the list I provided in an earlier post. I 5, I 10, SR 34, US 101 …..etc. That’s where I’m getting hung up. The Xcompare part of the SQLite3_create_collation function. Registering of the custom collation is far from intuitive (for me) and thus the

Re: [sqlite] SQLite3_create_collation

On Wed, Apr 2, 2014 at 1:38 PM, Donald Steele wrote: > So am I misunderstanding how to get what I am after? > > I am using a select statement to produce a list of records "ordered by" > the highway column. > My understanding was that the select statement would include my

Re: [sqlite] SQLite3_create_collation

On 2 Apr 2014, at 6:38pm, Donald Steele wrote: > So am I misunderstanding how to get what I am after? > > I am using a select statement to produce a list of records “ordered by” the > highway column. > My understanding was that the select statement would include my

Re: [sqlite] SQLite3_create_collation

So am I misunderstanding how to get what I am after? I am using a select statement to produce a list of records “ordered by” the highway column. My understanding was that the select statement would include my collation as part of the order by function. Is this not the case? On Apr 2, 2014,

Re: [sqlite] SQLite3_create_collation

On 2 Apr 2014, at 6:08pm, Donald Steele wrote: > The first part is where my confusion is. First of course is where it takes > place but then since I would apply it to a column of a table as part of a > select statement what are the strings in the compare and how do I set

Re: [sqlite] SQLite3_create_collation

Going back to my origanal question, it was perhaps not stated correctly. The creation of a collation looks to be a two part process. 1) The defining the actual compare process. 2) The declaring of the custom collation. The first part is where my confusion is. First of course is where

Re: [sqlite] Improving Bulk Insert Speed (C/C++)

On Wed, 2 Apr 2014 09:09:58 -0700 Kevin Xu wrote: > > On Apr 2, 2014, at 9:01 AM, Simon Slavin wrote: > > > > > On 2 Apr 2014, at 4:55pm, Kevin Xu wrote: > > > >> PRAGMA journal_mode=MEMORY; > > > > Since you're not using WAL

Re: [sqlite] Improving Bulk Insert Speed (C/C++)

On Wed, Apr 2, 2014 at 6:09 PM, Kevin Xu wrote: > Yes, I have tried various combinations of WAL/OFF/MEMORY and they do not > appear to affect the insert speed in any meaningful manner (e.g. +/- 0.3s on > 500K inputs). > > [...] Thus, I am trying to see if there is anything I

Re: [sqlite] Improving Bulk Insert Speed (C/C++)

What is your page size? Make sure it matches the sector size of your device. SSDs can be picky about write sizes. As an experiment, you might try using larger page sizes that are multiples of the sector size. Try to reduce the size of the records you are writing. Ie. can you map any string

Re: [sqlite] Improving Bulk Insert Speed (C/C++)

On Wed, Apr 2, 2014 at 12:02 PM, Kevin Xu wrote: > Richard Hipp wrote: > > > So you are creating a 27.5 GB database in under a half hour? > > > > What is your database page size? > > Yes, to be exact, a 34.24GB database from (34GB+34GB) FASTQ files in that > time. I did not

Re: [sqlite] Improving Bulk Insert Speed (C/C++)

On Apr 2, 2014, at 9:01 AM, Simon Slavin wrote: > > On 2 Apr 2014, at 4:55pm, Kevin Xu wrote: > >> PRAGMA journal_mode=MEMORY; > > Since you're not using WAL mode, try it using WAL mode. If you were already > using WAL mode, I'd suggest trying

[sqlite] Improving Bulk Insert Speed (C/C++)

Richard Hipp wrote: > So you are creating a 27.5 GB database in under a half hour? > > What is your database page size? Yes, to be exact, a 34.24GB database from (34GB+34GB) FASTQ files in that time. I did not use any pragmas for swiching SQLite page size, so it should be the default (probably

Re: [sqlite] Improving Bulk Insert Speed (C/C++)

On 2 Apr 2014, at 4:55pm, Kevin Xu wrote: > PRAGMA journal_mode=MEMORY; Since you're not using WAL mode, try it using WAL mode. If you were already using WAL mode, I'd suggest trying with it off ! Given your answers to the questions so far, I can't think of anything else

Re: [sqlite] SQLite3_create_collation

Correct, both of those sorts are what I would call natural (or alphanumeric ) sorts and exactly what I am trying get. On Apr 2, 2014, at 8:13 AM, Richard Hipp wrote: > On Wed, Apr 2, 2014 at 11:09 AM, James Berry wrote: > >> One thing you might want to

[sqlite] Improving Bulk Insert Speed (C/C++)

Simon Slavin wrote: > Just wanted to check your phrasing. You process a while file into INSERT > commands, then put > BEGIN before the first one, and END after the last one, thus making the whole > file a single > transaction ? If so, then should make SQLite do things about as quickly as >

Re: [sqlite] SQLite3_create_collation

On Wed, Apr 2, 2014 at 11:09 AM, James Berry wrote: > One thing you might want to review is whether the results of the natural > sort algorithm here: http://sourcefrog.net/projects/natsort/ would be > what you want. If so, you should be able to incorporate that algorithm, or > a

Re: [sqlite] SQLite3_create_collation

On Apr 2, 2014, at 7:36 AM, James Berry wrote: > On Apr 2, 2014, at 7:28 AM, Donald Steele wrote: > >> I read some where in my searches that Apple has “canned” versions of that >> method but I can’t find those either. >> >> Could someone direct me to

Re: [sqlite] Any interest for open source multi-user 'SQLite database server' application?

so there is to client library to link to ? a simple layer that would mimic the sqlite interface and do all the xml encoding/decoding and communication could be handy ? On 2 April 2014 16:09, Harmen de Jong - CoachR Group B.V. wrote: > >that would be interesting. Is the C

Re: [sqlite] SQLite3_create_collation

On Wed, Apr 2, 2014 at 10:28 AM, Donald Steele wrote: > Greetings I am an iOS developer that is much more comfortable with iOS > than SQLite. I have an iOS app that uses a database with several tables. > These table the app user uses as directories. The user moves from

Re: [sqlite] SQLite3_create_collation

On Apr 2, 2014, at 7:28 AM, Donald Steele wrote: > Greetings I am an iOS developer that is much more comfortable with iOS than > SQLite. I have an iOS app that uses a database with several tables. These > table the app user uses as directories. The user moves from screen

[sqlite] SQLite3_create_collation

Greetings I am an iOS developer that is much more comfortable with iOS than SQLite. I have an iOS app that uses a database with several tables. These table the app user uses as directories. The user moves from screen to screen making selections from lists generated from my Sqlite database

Re: [sqlite] Improving Bulk Insert Speed (C/C++)

On Tue, Apr 1, 2014 at 11:58 PM, Kevin Xu wrote: > Hi everyone I have been working on a small bioinformatics project that > attempts to store FASTQ (http://en.wikipedia.org/wiki/FASTQ_format) data > into a SQLite database. > > The app works by reading in the FASTQ file

Re: [sqlite] Improving Bulk Insert Speed (C/C++)

On 2 Apr 2014, at 4:58am, Kevin Xu wrote: > The app works by reading in the FASTQ file (through Boost::memory_mapped > regions), running the parser through it, binding the variables using > sqlite3_bind_xxx, and inserting them in a prepared statement wrapped within >

Re: [sqlite] Sqlite Instead of Trigger on Views does not always fire..

Engin Guelen wrote: > The Following View and Trigger Code compiles w/o Errors. Instead of the DELETE, you could use "SELECT RAISE(FAIL, 'triggered');". > But when changing the SYNC column nothing happens to REF_TAB, that means the > trigger does not fire. With the following: CREATE TABLE

Re: [sqlite] Any interest for open source multi-user 'SQLite database server' application?

that would be interesting. Is the C interface from your client exactly like the C interface of sqlite ? On 2 April 2014 14:44, Harmen de Jong - CoachR Group B.V. wrote: > >Do you think it could be used with spatialite extension to build a > multi-user spatial database ? > >

Re: [sqlite] Any interest for open source multi-user 'SQLite database server' application?

>Do you think it could be used with spatialite extension to build a multi-user >spatial database ? > >Noel Frankinet We don't have experience with Spatialite. However, we had a quick look at it and think it should not be too hard to support this from our SQLite database server app. Best

Re: [sqlite] Sqlite Instead of Trigger on Views does not always fire..

On 04/02/2014 06:08 PM, Engin Guelen wrote: Hello, i noticed that Instead of Update Triggers on Views do not fire as soon as the Views get a bit more involved. The Following View and Trigger Code compiles w/o Errors. But when changing the SYNC column nothing happens to REF_TAB, that

Re: [sqlite] Unicode support in SQLite

Hey, According to previous discussion in this mailing list, i've updated nunicode SQLite extension not to override default NOCASE collation due to possible issues with database indexing. Version 1.2.1 removes nunicode-specific NOCASE and NUNICODE collations and introduces NU630 and

[sqlite] Improving Bulk Insert Speed (C/C++)

Hi everyone I have been working on a small bioinformatics project that attempts to store FASTQ (http://en.wikipedia.org/wiki/FASTQ_format) data into a SQLite database. The app works by reading in the FASTQ file (through Boost::memory_mapped regions), running the parser through it, binding the

[sqlite] Sqlite Instead of Trigger on Views does not always fire..

Hello, i noticed that Instead of Update Triggers on Views do not fire as soon as the Views get a bit more involved. The Following View and Trigger Code compiles w/o Errors. But when changing the SYNC column nothing happens to REF_TAB, that means the trigger does not fire. A drastically

[sqlite] rtree

static int nodeRelease(Rtree *pRtree, RtreeNode *pNode){ int rc = SQLITE_OK; if( pNode ){ assert( pNode->nRef>0 ); pNode->nRef--; if( pNode->nRef==0 ){ if( pNode->iNode==1 ){ // if pNode->iNode equals 1,then current search,call rtreeNext will crash! pRtree->iDepth =