Re: [sqlite] Referencing subquery several times

On 13 Nov 2011, at 1:47am, Alexandre Courbot wrote: > On Sat, Nov 12, 2011 at 11:59 PM, Black, Michael (IS) > wrote: >> Why are you contorting yourself into just one query? > > It is actually part of a larger query that joins against this result. > Granted, there are

Re: [sqlite] Referencing subquery several times

On Sat, Nov 12, 2011 at 11:59 PM, Black, Michael (IS) wrote: > Why are you contorting yourself into just one query? It is actually part of a larger query that joins against this result. Granted, there are ways to workaround this, but I would be surprised if it was not

Re: [sqlite] Any thoughts on optimizations for this query?

On 13/11/2011 3:45 a.m., Igor Tandetnik wrote: Josh Gibbs wrote: To reiterate from my original question, if we don't add the order by then the results come back at a very acceptable speed. But of course we'd then have to iterate through the results ourselves to siphon off

Re: [sqlite] Any thoughts on optimizations for this query?

On 13/11/2011 3:37 a.m., Igor Tandetnik wrote: Josh Gibbs wrote: The timestamps are already integers. We stumbled across that CAST operation optimization purely by accident. I don't remember what led to it, but we found that it gave a measurable performance boost casting

Re: [sqlite] How about a proper forum rather than an e-mail list

>> + Easy access, no setup (subscription, rules etc.) Oh, and some lists have a list ID header, some have another, some have none and I need to write mail filters by subject. That's pretty much guesswork every time I subscribe to a mailing list. And some lists echo my own mail back to me, some

Re: [sqlite] How about a proper forum rather than an e-mail list

>> + Easy access, no setup (subscription, rules etc.) Oh and did I mention the complicated confirmation and registration methods? I just subscribed to a PHP mailing list. At least I tried. I have no idea what happened. I received at least two confirmation e-mails, and of course you get no

Re: [sqlite] Referencing subquery several times

On 12-11-2011 19:13, Darren Duncan wrote: > Alexandre Courbot wrote: >> Hi everybody, >> >> Here is a simplified version of the statement I try to run (let a, b, >> and m be tables with only one column named c containing integers): >> >> SELECT * FROM m WHERE >> c IN (SELECT * FROM a) OR >>

Re: [sqlite] Referencing subquery several times

Alexandre Courbot wrote: Hi everybody, Here is a simplified version of the statement I try to run (let a, b, and m be tables with only one column named c containing integers): SELECT * FROM m WHERE c IN (SELECT * FROM a) OR c IN (SELECT * FROM b) AND (NOT c IN (SELECT * FROM a)

Re: [sqlite] Any thoughts on optimizations for this query?

On 12 Nov 2011, at 2:37pm, Igor Tandetnik wrote: > ANALYZE Yeah. Do an ANALYZE. Then test timing with and without the CAST. See if it helps. I don't understand why your query should be faster when you suppress a useful index. Oh wait. Your entire WHERE clause is about the joined table.

Re: [sqlite] inserts, performance, file lock...

CMON i put the code in my first post you should ask this question! actualy reopening database is an option i tried - and it keeps the speed at the good level. but after first close next commit failed cause database is busy. it is in code but i used it in later attempts. Kees Nuyt

Re: [sqlite] inserts, performance, file lock...

On Sat, 12 Nov 2011 08:21:15 -0800 (PST), yqpl wrote: > >ie im loading 1000 files. > >first rows it is even speeding up the initial speed - 25 files / sec - >speeds up to the 30 files/ sec in 50 files. >then is starts to slow down evenly (regular slow down for some number of

Re: [sqlite] inserts, performance, file lock...

ie im loading 1000 files. first rows it is even speeding up the initial speed - 25 files / sec - speeds up to the 30 files/ sec in 50 files. then is starts to slow down evenly (regular slow down for some number of files) until 2 files/ sec at the end - 1000 files. every next time it looks the

[sqlite] Wanted: Windows SQLite shell 3.6.x (not the current version!)

Could somebody please send me an older version 3.6.x of the SQLite Windows shell client? It's not available from the website but I'd like to compare it with other instances of that version 3.6 to see whether it returns the same wrong result or whether my query bug is somewhere else. 3.6 gives no

Re: [sqlite] Referencing subquery several times

Why are you contorting yourself into just one query? Your last clause would be a complete table scan seems to me. Sounds slow versus 2 queries. Why can't you just do this? (pseudo code here) -- no table scans involved at all. select docid from b where t match 'blah'; if (rowcount > 0) // the

Re: [sqlite] Any thoughts on optimizations for this query?

Josh Gibbs wrote: > To reiterate from my original question, if we don't add the order by then > the results come back at a very acceptable speed. But of course we'd > then have to iterate through the results ourselves to siphon off the > top item set that we're after. I'd

Re: [sqlite] Any thoughts on optimizations for this query?

Josh Gibbs wrote: > The timestamps are already integers. We stumbled across that CAST operation > optimization purely by accident. I don't remember what led to it, but > we found > that it gave a measurable performance boost casting the integer as an > integer. This works

Re: [sqlite] How about a proper forum rather than an e-mail list

Sorry for my late reply, I look into mailing lists only once every few weeks or when I'm posting something. On 27.10.2011 18:29 CE(S)T, Nico Williams wrote: > then setup a forum interface to the mailing list Then please take good care to use a frontend that uses compatible markup formatting (or

Re: [sqlite] Referencing subquery several times

On Sat, Nov 12, 2011 at 10:02 PM, Luuk wrote: > Should give same results as: > > SELECT * FROM m WHERE >    c IN (SELECT * FROM a) OR >    c IN (SELECT * FROM b) >    AND (c IN (SELECT * FROM b)); > > Because of the 'OR' on the second line > > This can be simplified to: > >

[sqlite] GROUP BY With ASC/DESC

Given the following table: DROP TABLE IF EXISTS key_table5; CREATE TABLE key_table5 ( name TEXT NOT NULL default '', color TEXT default NULL, price REAL default NULL, UNIQUE (name), PRIMARY KEY (color) ); I'm getting the following exception: SQLException: [SQLITE_ERROR] SQL error or missing

Re: [sqlite] Referencing subquery several times

On 12-11-2011 12:12, Alexandre Courbot wrote: > Hi Igor, > > On Nov 12, 2011 11:45 AM, "Igor Tandetnik" wrote: >> This query doesn't make much sense. It appears that quite a few > conditions are redundant, or else the parentheses are in the wrong places. > What logic were

Re: [sqlite] Referencing subquery several times

On 12-11-2011 03:43, Igor Tandetnik wrote: > On 11/11/2011 9:24 PM, Alexandre Courbot wrote: >> Here is a simplified version of the statement I try to run (let a, b, >> and m be tables with only one column named c containing integers): >> >> SELECT * FROM m WHERE >> c IN (SELECT * FROM a) OR

Re: [sqlite] Any thoughts on optimizations for this query?

On 12/11/2011 5:02 p.m., Simon Slavin wrote: On 12 Nov 2011, at 3:43am, Josh Gibbs wrote: We are struggling to find a way to rework this query in a way that performs efficiently for large data sets. It's all about the indexes. The problem with this query seems to be related to the

Re: [sqlite] Referencing subquery several times

Hi Igor, On Nov 12, 2011 11:45 AM, "Igor Tandetnik" wrote: > This query doesn't make much sense. It appears that quite a few conditions are redundant, or else the parentheses are in the wrong places. What logic were you trying to express here? Sorry, I tried to simplify it