Re: [sqlite] SELECT and UPDATE in single query

> Why process only N at a time, Richard? There are a number of workers who request unprocessed jobs from the queue. But the queue is too big to hold in memory all at once. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-b

Re: [sqlite] SELECT and UPDATE in single query

Thanks for tip about the redundant index. How to find which keys have been updated from this query? On Thu, Jan 24, 2013 at 3:32 PM, Keith Medcalf wrote: >> I have a table like this: >> >> CREATE TABLE queue ( >> key TEXT NOT NULL PRIMARY KEY UNIQUE, >> status INTEGER >> ); >> CREATE IN

Re: [sqlite] SELECT and UPDATE in single query

On Wed, 23 Jan 2013 21:32:20 -0700 "Keith Medcalf" wrote: > > And then I process it like this, N keys at a time: > > > > SELECT key FROM queue WHERE status=0 LIMIT N; > > BEGIN TRANSACTION; > > for key in keys: > > UPDATE queue SET status=1 WHERE key=key; > > END TRANSACTION; > > > > How ca

Re: [sqlite] SELECT and UPDATE in single query

On 1/23/2013 11:22 PM, Richard Baron Penman wrote: And then I process it like this, N keys at a time: SELECT key FROM queue WHERE status=0 LIMIT N; BEGIN TRANSACTION; for key in keys: UPDATE queue SET status=1 WHERE key=key; END TRANSACTION; How can this SELECT and UPDATE be combined more

Re: [sqlite] SELECT and UPDATE in single query

> I have a table like this: > > CREATE TABLE queue ( > key TEXT NOT NULL PRIMARY KEY UNIQUE, > status INTEGER > ); > CREATE INDEX IF NOT EXISTS keys ON queue (key); Your index is redundant. There is already a unique index on key since it is a primary key. It should probably be: create

[sqlite] SELECT and UPDATE in single query

Hello, I have a table like this: CREATE TABLE queue ( key TEXT NOT NULL PRIMARY KEY UNIQUE, status INTEGER ); CREATE INDEX IF NOT EXISTS keys ON queue (key); And then I process it like this, N keys at a time: SELECT key FROM queue WHERE status=0 LIMIT N; BEGIN TRANSACTION; for key in k

Re: [sqlite] SQL query

> I have a database with many million rows with in it each representing a > file. There are many duplicate files in the database and all files are > hashed. > > The files are sub categorised into a number of sets, numbered 0 to 10 for > example. Files do not need to be in every set. > > I need to

Re: [sqlite] performance regression when using "insert or replace"

I use "insert or replace" heavily. In debug mode, I set it to use temp file on disk so, I can watch the disk IO, in release mode I set it to temp file in memory. The danger is that if you ever do anything that requires a bunch of temp file, you can easily run out of RAM. Adding and deleting inde

Re: [sqlite] performance regression when using "insert or replace"

Thank you so much!! This is great news. One question: according to the tempfiles.html doc... Section 3.0 states that "The rollback journal, master journal, and statement journal files are always written to disk. But the other kinds of temporary files might be stored in memory only and never wr

Re: [sqlite] SQL query

On Wed, Jan 23, 2013 at 12:17 PM, Paul Sanderson < sandersonforens...@gmail.com> wrote: > I have a database with many million rows with in it each representing a > file. There are many duplicate files in the database and all files are > hashed. > > The files are sub categorised into a number of se

Re: [sqlite] SQL query

If you simply want a list of all files that are present and are not also present in set 0 (I'm not sure how 'duplicated' means anything different...) SELECT f.name, f.set, f.hash FROM files f LEFT OUTER JOIN files f2 ON f2.name = f.name and f2.set = 0 WHERE f.set != 0 and f2.name is null -

Re: [sqlite] Change in behavior between 1.0.79.0 and1.0.83.0 in System.Data.SQLite

Ok. So how does this get fixed? -- Michael Russell MTI Film, LLC michael.russ...@mtifilm.com http://www.mtifilm.com/ Providence, RI 02906 USA +1 (401) 831-1315 On Thu, Jan 10, 2013 at 8:49 PM, Joe Mistachkin wrote: > > Michael Russell wrote: > > > > 1) The break seems to have happened between

Re: [sqlite] performance regression when using "insert or replace"

On 01/23/2013 11:04 PM, Heiles, Katrina wrote: Hi Dan, Yes, this resolves the problem. performance comes back up to 31K/sec. What are the risks of using this as a workaround? Data integrity is very important to us so I'm curious what effect this pragma would have. No effect on data integrit

Re: [sqlite] performance regression when using "insert or replace"

Hi Dan, Yes, this resolves the problem. performance comes back up to 31K/sec. What are the risks of using this as a workaround? Data integrity is very important to us so I'm curious what effect this pragma would have. thanks, katrina > Out of interest, is performance improved any with 3.7.15.2

Re: [sqlite] Connection philosophy

On Jan 22, 2013, at 8:18 PM, Keith Medcalf wrote: > I presume that you are using some kind of input-driven or event driven > application which may get a request to process a query "in the middle" of > your update transaction. That is correct. > One of the advantages of WAL and using a sepa

Re: [sqlite] performance regression when using "insert or replace"

On 01/23/2013 04:20 AM, Heiles, Katrina wrote: I am in the process of updating from 3.6.4 to the latest and greatest version (finally :-) ). While running performance unit tests, i found a major regression (10K/sec vs 30k/sec) on a common use case for us. The regression occurs when using "inse

Re: [sqlite] Sqlite aggregate cold/hot boot performance

On 23 Jan 2013, at 9:01am, Pierre Chatelier wrote: > [what I do not understand] > I perform a computer cold boot, launch my app, opens a doc, perform the > query. The sqlite3_step() takes some time (a few seconds).It's ok, the > query is rather complex. > Now, I close my doc, reopens it. The sam

Re: [sqlite] Sqlite aggregate cold/hot boot performance

Hi, Is there some global cache retained by sqlite3.dll that makes my query faster ? Could it be rather related to some hard disk cache ? SQLite uses normal file accesses, so the operating system will try to cache the data. OK And how many GB is the system using for file caching? :) I don

Re: [sqlite] Insert record in c++

On Wed, Jan 23, 2013 at 2:36 PM, wrote: > String Data = "INSERT INTO friend(name, address, age) > VALUES('John','Anywhere here','25')"; > > How would you actually pull the VALUES from variables and use them. A > complete example will be very helpful. > Google for "sqlite3_bind example" and you'

Re: [sqlite] Are Foreign Keys indexed?

Thanks for answer! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Insert record in c++

Hi Everyone, I am extremely new to sqlite and using sql syntax.  I have been searching the web for examples but I am not finding any that actually helps me understand what I am doing.  Can someone show me in c++ how to insert a record into the database utilizing variables?  The example

Re: [sqlite] Are Foreign Keys indexed?

On Wed, Jan 23, 2013 at 02:12:39PM +0100, Krzysztof scratched on the wall: > Hi, > > I'm reading http://www.sqlite.org/foreignkeys.html but can't find answer. > Are SQLite FK indexed? For example: Not automatically. From that page: 3. Required and Suggested Database Indexes

Re: [sqlite] Are Foreign Keys indexed?

Krzysztof wrote: > Are SQLite FK indexed? says: | Indices are not required for child key columns but they are almost | always beneficial. They are if you create one manually. Regards, Clemens ___ sql

[sqlite] Are Foreign Keys indexed?

Hi, I'm reading http://www.sqlite.org/foreignkeys.html but can't find answer. Are SQLite FK indexed? For example: CREATE TABLE artists ( id_artist INTEGER PRIMARY KEY // <- PK are indexed, that I know ) CREATE TABLE songs ( id_song INTEGER PRIMARY KEY, id_artist INTEGER, // <- Is this FK i

[sqlite] minor bug: EXPLAIN statement should fail when it is not compiled

As written on http://www.sqlite.org/compile.html#omit_explain when explain is disabled executing it should fail. But I do get back the result of the query instead. Thanks for the great software by the way. Greetings, Simon H. Examples of unexpected result = sqlite> EXP

[sqlite] performance regression when using "insert or replace"

I am in the process of updating from 3.6.4 to the latest and greatest version (finally :-) ). While running performance unit tests, i found a major regression (10K/sec vs 30k/sec) on a common use case for us. The regression occurs when using "insert or replace". I narrowed this down as follows

Re: [sqlite] Sqlite aggregate cold/hot boot performance

Pierre Chatelier wrote: > Is there some global cache retained by sqlite3.dll that makes my query > faster ? Could it be rather related to some hard disk cache ? SQLite uses normal file accesses, so the operating system will try to cache the data. > (please note that my database files where the qu

Re: [sqlite] integrity_check "out of memory"

On Tue, Jan 22, 2013 at 12:33 PM, Dominique Pellé wrote: > Max Vlasov wrote: > > > Hi, > > > > I found with the web search that this error (out of memory for PRAGMA > > integrity_check) appeared in the wild and there were some fixes related > to > > it.It looks perhaps like the bug that was fixe

[sqlite] Sqlite aggregate cold/hot boot performance

Hello, I can observe a behaviour that I do not understand. [context] I have an application that links whith sqlite3.dll. I can open a document, and perform a query on it. That query is using an aggregate function, that I have created myself using the callbacks like sqlite_aggr_indexset_step... I