Re: [sqlite] Scaling of Cache

On Oct 19, 2010, at 9:01 AM, Doug wrote: > I'm not going to pretend to understand the SQLite source, but it seems > like having a mutex per PCache1 (ie the param passed in to > pcache1Fetch > and other cache functions) would be a good approach instead of the > global > mutex. But that

Re: [sqlite] Scaling of Cache

I'm not going to pretend to understand the SQLite source, but it seems like having a mutex per PCache1 (ie the param passed in to pcache1Fetch and other cache functions) would be a good approach instead of the global mutex. But that approach wasn't taken, and I've found everything to be very

Re: [sqlite] Insert or Update (was: ON DELETE CASCADE along with ON CONFLICT REPLACE)

On Tue, 19 Oct 2010 10:54:13 +1100 BareFeetWare wrote: > -- alternatively you could do this, which will update the existing > row, if exists, or insert a new one if it doesn't: > > update users set "name" = 'Joe C', "type" = 4, where "id" = 1; > insert or ignore into

[sqlite] Insert or Update (was: ON DELETE CASCADE along with ON CONFLICT REPLACE)

On 19/10/2010, at 8:10 AM, NSRT Mail account. wrote: > I would use the update if I knew the entry already existed. In my application > however, it doesn't know if the entry already exists. I was looking for > something to replace MySQL's ON DUPLICATE KEY UPDATE. > > I modified my application

Re: [sqlite] Issue using ON DELETE CASCADE along with ON CONFLICTREPLACE

I would use the update if I knew the entry already existed. In my application however, it doesn't know if the entry already exists. I was looking for something to replace MySQL's ON DUPLICATE KEY UPDATE. I modified my application to use two SQL statements instead.     if (!db.execute("INSERT

Re: [sqlite] Issue using ON DELETE CASCADE along with ON CONFLICTREPLACE

NSRT Mail account. wrote: > The entry in meetings is now gone. Should ON DELETE CASCADE be picking up an > UPDATE as a DELETE via INSERT INTO from ON CONFLICT > REPLACE? REPLACE involves deleting conflicting rows, followed by INSERT, as explained by the documentation at

[sqlite] Issue using ON DELETE CASCADE along with ON CONFLICT REPLACE

I'm not sure if this is an error on my end or on SQLite's. I'm using 3.7.2. I'm creating two tables as follows: PRAGMA foreign_keys=ON; CREATE TABLE 'users' (   'id' INTEGER NOT NULL,   'type' INTEGER NOT NULL,   'name' VARCHAR(64) NOT NULL,   PRIMARY KEY('id', 'type') ON CONFLICT REPLACE );

Re: [sqlite] Query hung. Any help. (sqlite & dbi)

On Oct 18, 2010, at 6:58 PM, Igor Tandetnik wrote: > In general, I found that the idiom > > TableA left join TableB on (TableA.idInTableB = TableB.someId) where > TableB.someId is null > > almost always performs worse than the equivalent NOT EXISTS or NOT IN query. Hmmm... in practice it

Re: [sqlite] Scaling of Cache

Jeff, I can agree that on Windows mutex performance can be awful especially in such frequently called place as pcache1Fetch. So you have only two options to solve the problem: 1) Split threads into different processes - make it one thread per process. 2) Make your own implementation of pcache.

Re: [sqlite] Full text search FTS3 of files

Take a look at the custom tokenizer API. I think tokens returned don't necessarily have to be substrings of the text. So, maybe the text you "tokenize" could be the file path, but the tokens could be things you pull from the contents of the file. Just a thought, Cheers, Sam

Re: [sqlite] Scaling of Cache

On 18 Oct 2010, at 6:25pm, Powell, Jeff wrote: > Yes, each thread does its own sqlite3_open(). In fact, I get the same > behavior when each thread is using completely separate files (for example, > making multiple copies of the database, with each thread using a different > copy). That

Re: [sqlite] Scaling of Cache

Yes, each thread does its own sqlite3_open(). In fact, I get the same behavior when each thread is using completely separate files (for example, making multiple copies of the database, with each thread using a different copy). -Jeff -Original Message- From:

Re: [sqlite] Scaling of Cache

I did some profiling of our current application through the Intel Parallel Studio tools, and it identified the mutex in pcache1Fetch as the primary source of waits. Each thread acts on its own, sharing nothing with the other threads, so I would expect that there is zero waiting. -Jeff

[sqlite] Query hung. Any help. (sqlite & dbi)

Nice Explanation Igor. As it turns out, virtually all of the rows will match up as you correctly suspected. Thanks again. Jack The information contained in this communication may be CONFIDENTIAL and is intended only for the use of the recipient(s) named above. If you are not the

Re: [sqlite] Scaling of Cache

> In pcache1Fetch, sqlite mutexes around the cache handling, which appears to > be causing significant waits/scalability issues in my application.  If I > disable this mutex, the application crashes. Why do you think that this mutex causes significant waits? Anyway ... > Is it possible to

Re: [sqlite] Query hung. Any help. (sqlite & dbi)

Tilghman, Jack wrote: > Thanks Igor, works great! > > Btw, was there something incorrect about the way I had the query setup? Not incorrect, just wasteful. I suspect the query was spending a lot of time working through rows where link and node do match up, only to

[sqlite] Query hung. Any help. (sqlite & dbi)

Thanks Igor, works great! Btw, was there something incorrect about the way I had the query setup? Thanks, Jack The information contained in this communication may be CONFIDENTIAL and is intended only for the use of the recipient(s) named above. If you are not the intended recipient, you

Re: [sqlite] Scaling of Cache

On 18 Oct 2010, at 5:25pm, Powell, Jeff wrote: > I have an application which utilizes multiple threads, each of which never > writes to the database, and none of which shares its cache. > > In pcache1Fetch, sqlite mutexes around the cache handling, which appears to > be causing significant

Re: [sqlite] How to optimize a multi-condition query

On Mon, 18 Oct 2010 15:07:35 +0200, Hilmar Berger wrote: > Hi, > >thanks to everybody that answered. I tried your suggestions but there >was no measurable improvement. Possibly this is the best what I can get >out of Sqlite. >However, I tried a similar query on

Re: [sqlite] Full text search FTS3 of files

On Sun, Oct 17, 2010 at 11:13 PM, Dami Laurent (PJ) wrote: >>Is it possible to use FTS3 for search without storing the actual file >>contents/search terms/keywords in a row. In other words, create a FTS3 >>tables with rows that only contains an ID and populate the

Re: [sqlite] Query hung. Any help. (sqlite & dbi)

Tilghman, Jack wrote: > sqlite trace: prepare statement: SELECT COUNT(*) FROM link LEFT OUTER JOIN > node ON node.pvid = link.ref_node_pvid WHERE >link.ref_node_pvid != -1 AND link.ref_node_pvid != -2 AND node.pvid IS > NULL; Try this instead: SELECT COUNT(*)

[sqlite] Scaling of Cache

I'm seeing some scaling issues (which I'm hoping someone else has encountered before). I have an application which utilizes multiple threads, each of which never writes to the database, and none of which shares its cache. In pcache1Fetch, sqlite mutexes around the cache handling, which appears

[sqlite] uniitialized memory with an unused custom tokenizer

There appears to be a problem in the FTS3 module relating to tokenizers. In my case if I register a custom tokenizer on a database connection that does not have a table using the custom tokenizer, then when I run my program with Valgrind I get a lot of "Use of uninitialised value of size 4"

Re: [sqlite] Incrementing a counter vs adding a row

On Mon, Oct 18, 2010 at 8:16 AM, Ian Hardingham wrote: >  Hey guys. > > If I wish to log how often a user does action x, I'm assuming I would be > best off doing something like: > > /SELECT whatever FROM actionPerfomedTable WHERE user = y, action = x > LIMIT 1/ > > If a record is

Re: [sqlite] Query critique

Thanks Simon. I have a further question on this topic. I would like to find out where my user ranks amongst all scores - so I want to SELECT name, score FROM scoreTable WHERE id=x And then I wish to know how many rows occur before the one where user=myuser. I can loop through them in code,

Re: [sqlite] Incrementing a counter vs adding a row

Please reply to the list, so that others may take part in the conversation, and so that others with similar future questions can search out the answers in the mailing list archive. On Mon, Oct 18, 2010 at 02:55:44PM +0100, Ian Hardingham scratched on the wall: > Thanks Jay. > > A

Re: [sqlite] Query critique

On 18 Oct 2010, at 4:09pm, Ian Hardingham wrote: > I also want to add selecting the highest score, and adding it to the > results assuming it isn't already in there (ie unless it happens your or > a friends' score is the highest). Could anyone advise me on how best to > do that? Can you not

[sqlite] Query critique

Hey guys. I'm kind of revisiting something I asked about before. I have a high scores table, and a table of friends, and I wish to select for user x: The score of x The scores of all of x's friends Ordered by score descending. I am using this: SELECT * FROM (SELECT * FROM cupPlayTable

[sqlite] Query hung. Any help. (sqlite & dbi)

Query hung. Any help. (sqlite & dbi) SQLite version 3.7.2 built on This is perl, v5.8.8 built for x86_64-linux-thread-multi DBI 1.611 DBD::SQLite 1.29 Centos Linux(2.6.18-164.11.1.el5xen) 64bit. Below are output snippets from two dbi trace files. In the first snippet, the sqlite.db is

Re: [sqlite] Incrementing a counter vs adding a row

On Mon, Oct 18, 2010 at 02:16:55PM +0100, Ian Hardingham scratched on the wall: > If I wish to log how often a user does action x, I'm assuming I would be > best off doing something like: > But it strikes me that an alternative is to have one row for each time > the user performs action x,

[sqlite] [BUG] 1200x slow-down running query in 3.7.3

I have a query which takes 17 minutes to run with 3.7.3 against 800ms with 3.7.2 The query is: SELECT x.sheep_no, x.registering_flock, x.date_of_registration FROM sheep x LEFT JOIN (SELECT s.sheep_no, prev.flock_no, prev.owner_person_id, s.date_of_registration, prev.owner_change_date

[sqlite] Incrementing a counter vs adding a row

Hey guys. If I wish to log how often a user does action x, I'm assuming I would be best off doing something like: /SELECT whatever FROM actionPerfomedTable WHERE user = y, action = x LIMIT 1/ If a record is returned, perform /UPDATE actionPerfomedTable SET number = incremented number WHERE

Re: [sqlite] How to optimize a multi-condition query

Hi, thanks to everybody that answered. I tried your suggestions but there was no measurable improvement. Possibly this is the best what I can get out of Sqlite. However, I tried a similar query on a larger table using both SQlite and Postgresql (same machine, same table structure + indices,

[sqlite] is it really ok to allow non-aggregates in an aggregate line?

I made an error in my SQL when I did not include one of my non-aggregate columns in my group. I was surprised that Sqlite did not catch this, and even more surprised when the docs spelled out this behavior. Is everyone ok with this? Do any other SQL engines allow this? (DB2 does not) Sent

Re: [sqlite] Time calculation bug?

Interesting. I get the same results as you when I use sqlite3.exe, but, in a database manager, the result comes back as I reported. I'll contact the developer of the utility and see if he can come up with something. On Mon, Oct 18, 2010 at 5:31 AM, Simon Davies

Re: [sqlite] Time calculation bug?

On 18 October 2010 09:28, Stephen Chrzanowski wrote: > I seem to be having an odd behavioral problem with calculating time stamps. > . . . > For instance: > > select strftime('%s','now') RealUTC,strftime('%s','now','localtime') > LocalTime, >       strftime('%s','now') -      

[sqlite] Time calculation bug?

I seem to be having an odd behavioral problem with calculating time stamps. The software I'm using drops data into a field as a local time stamp (IE: '2010-10-18 04:08:04.000') which is fine. However, when trying to pull that data back out and convert the Sqlite NOW time to local, I'm getting

Re: [sqlite] Full text search FTS3 of files

>Is it possible to use FTS3 for search without storing the actual file >contents/search terms/keywords in a row. In other words, create a FTS3 >tables with rows that only contains an ID and populate the B-Tree with >keywords for search. > Each FTS3 table t is stored internally within three