Re: [sqlite] Can sqlite3_blob_write be done through SQL?

Yuri wrote: > So far sqlite is failing, because the handle returned by > sqlite3_blob_write is invalidated by the updates to the other fields, This would happen regardless of whether you call sqlite3_blob_write() yourself or through an SQL function. > and sqlite3_blob_open/sqlite3_blob_close are

Re: [sqlite] Multiple tables or not

Joshua Grauman wrote: > would it make sense to split the table into two tables, where the > first column (the ID) is identical, but the first table only has the > first 3 columns and the second table has the rest of the columns. > Would that make reading the smaller table with only the 3 columns >

Re: [sqlite] Multiple tables or not

Thanks! If I am able to run real world tests to see if it is perceptibly faster, I'll report back! Josh On 3/4/17, Joshua Grauman wrote: Hello all, I am wondering what would be faster for sqlite- Suppose I have a table with ~400,000 rows and ~20 columns. Suppose I only

Re: [sqlite] Multiple tables or not

On 3/4/17, Joshua Grauman wrote: > Hello all, > > I am wondering what would be faster for sqlite- Suppose I have a table > with ~400,000 rows and ~20 columns. Suppose I only care about read speed > of the table, and that each of the columns contains 8-bit or 32-bit > integers,

[sqlite] Multiple tables or not

Hello all, I am wondering what would be faster for sqlite- Suppose I have a table with ~400,000 rows and ~20 columns. Suppose I only care about read speed of the table, and that each of the columns contains 8-bit or 32-bit integers, and the first column is an ID for the row. Suppose I also

Re: [sqlite] confused getting started

On 5 Mar 2017, at 12:10am, John Albertini wrote: > I'm not a computer novice, but also not a nerd/geek. > > Been using PCs since the mid 1980s and have used dBase III+ and Approach > previously. > > I can't seem to grasp what I need to download / install to use

[sqlite] confused getting started

I'm not a computer novice, but also not a nerd/geek. Been using PCs since the mid 1980s and have used dBase III+ and Approach previously. I can't seem to grasp what I need to download / install to use SQLite? Can someone guide me through the process? Looking to use it with RootsMagic. Thank

Re: [sqlite] Not a Bug -- works as documented: [BUG REPORT] Open blob is invalidated by the update to unrelated fields in the same row

On 4 Mar 2017, at 11:25pm, Yuri wrote: > What is the reason to expire the blob handle when unrelated fields are > modified? Because the BLOB has moved in the file. Because SQLite stores all of a row’s data in one continuous chunk. When you change any field it has to write

Re: [sqlite] Can sqlite3_blob_write be done through SQL?

On 03/04/2017 01:22, Clemens Ladisch wrote: Do you have a concrete example for such a statement? Yes. I need to re-assemble the large data packet that comes in portions. I would like to use a blob field for this. I don't want to store parts in separate records, because this is essentially

Re: [sqlite] Not a Bug -- works as documented: [BUG REPORT] Open blob is invalidated by the update to unrelated fields in the same row

On 03/04/2017 00:08, Keith Medcalf wrote: If the row that a BLOB handle points to is modified by an UPDATE, DELETE, or by ON CONFLICT side-effects then the BLOB handle is marked as "expired". This is true if any column of the row is changed, even a column other than the one the BLOB handle is

Re: [sqlite] Index usefulness for GROUP BY

On 4 Mar 2017, at 10:16pm, Jeffrey Mattox wrote: > Thank you for your replies. I've found that my best index is on datetime > since it eliminates the most uninteresting rows. The query plan is > > SEARCH TABLE History USING INDEX Idx_datetime (datetime>?) > USE TEMP B-TREE

Re: [sqlite] Index usefulness for GROUP BY

Thank you for your replies. I've found that my best index is on datetime since it eliminates the most uninteresting rows. The query plan is SEARCH TABLE History USING INDEX Idx_datetime (datetime>?) USE TEMP B-TREE FOR GROUP BY USE TEMP B-TREE FOR ORDER BY In my case, it's also best to have

Re: [sqlite] Massively multithreaded SQLite queries

in my curiosity since any spin should be scheduled until something wake it; I wondered what __raw_spin_lock was and if sqlite could use it differently... it appears to be a linux kernel thing, wrapped around read/write accesses to devices probably the bottleneck is actually IO rather than

Re: [sqlite] Massively multithreaded SQLite queries

Hi, just my few cents: if you are using the RTree module I made very bad experiences running SQLite in parallel, even when running only two or three threads in parallel. In this case I use a single thread-safe queue that is handling all SQLite access. Regards, Hartwig > Am 2017-03-04 um

Re: [sqlite] Can sqlite3_blob_write be done through SQL?

> On Mar 4, 2017, at 8:50 AM, J Decker wrote: > > All blobs I've used in SQL are themselves atomic and partial updates would > never be useful. (images, fingerprints, etc.) If you're updating parts of > blobs then probably you mean that blob to be in multiple parts or broken

Re: [sqlite] Can sqlite3_blob_write be done through SQL?

On Fri, Mar 3, 2017 at 3:58 PM, Yuri wrote: > On 03/01/2017 23:41, Clemens Ladisch wrote: > > It would certainly be possible to add your own user-defined SQL function >> to call sqlite3_blob_write(). >> > > > I think this should be added to sqlite itself. Writing a portion of

Re: [sqlite] Massively multithreaded SQLite queries

On Fri, 3 Mar 2017 20:13:52 + Andrew Brown wrote: > once we start running them on a large server with a lot of > multithreading going on, I find that we spend a lot of time in > __raw_spin_lock - perhaps 75%+ of the time (In one case, 87% of 350 > seconds x

Re: [sqlite] Massively multithreaded SQLite queries

On Fri, 3 Mar 2017, Andrew Brown wrote: Have tried setting SQLITE_OPEN_NOMUTEX, SQLITE_OPEN_FULLMUTEX. Tried shared cache, read uncommitted. Tried without shared cache, read uncommitted. Tried WAL. If I write a less efficient query, I spend less time in __raw_spin_lock, but of course then it

Re: [sqlite] Massively multithreaded SQLite queries

Andrew Brown wrote: > I find that we spend a lot of time in __raw_spin_lock Please try to find out (stack trace, etc.) which particular resource is protected by that lock. If, for example, this would happen to be file lock operations, then you would need to use fewer transactions (by wrapping

Re: [sqlite] Can sqlite3_blob_write be done through SQL?

Yuri wrote: > On 03/01/2017 23:41, Clemens Ladisch wrote: >> It would certainly be possible to add your own user-defined SQL function >> to call sqlite3_blob_write(). > > I think this should be added to sqlite itself. So far, nobody except you has requested it. > Writing a portion of blob is

[sqlite] Not a Bug -- works as documented: [BUG REPORT] Open blob is invalidated by the update to unrelated fields in the same row

Working as coded (and as documented): https://sqlite.org/c3ref/blob_open.html In particular read the paragraph that says: If the row that a BLOB handle points to is modified by an UPDATE, DELETE, or by ON CONFLICT side-effects then the BLOB handle is marked as "expired". This is true if any