Re: [sqlite] Slow query, with correlated sub-sub-query

Keith, this definitely explains the observed time as it is relative to count(a)*count (ab)**2, thus non-linear. And a correlated sub-query is generally recalculated for each row. But I do not agree with everything. In my example it is correlated to the outermost query, and not to the

Re: [sqlite] Slow query, with correlated sub-sub-query

Well of course. You are aware that a correlated subquery means "for each candidate result execute the query"? So as you have formulated the query it means: for each row in a compute the result count which for each ab candidate row calculate whether it is the minimum

Re: [sqlite] Slow query, with correlated sub-sub-query

Also of note is that when you make an index on ab (size), your original query, unchanged, becomes about 3 times faster than my modification. I'm not sure on what it looks at to decide if a temporary autoindex is worth it, but in this case it would have been. When you move back to your more

Re: [sqlite] Slow query, with correlated sub-sub-query

I acknowledge you said you weren't so much interested in an alternative solution, but... How about something like select a, min(size) as minSize, recCount from (select a, size, count(*) as recCount from a inner join ab using (a) group by a, size) group by a; The inner one will group by

Re: [sqlite] Slow Query on large database Help

On Aug 8, 2013, at 3:40 PM, Christopher W. Steenwyk wrote: > This database is generated once, and then queried and interrogated multiple > times. So I am most concerned with read speed and not with writing or > updating. Ohohoho… in that case… I have some snake oil to sell

Re: [sqlite] Slow Query on large database Help

I just uploaded the output from sqlite3_analyze to dropbox. On Thu, Aug 8, 2013 at 9:40 AM, Christopher W. Steenwyk wrote: > Here is the data from stat1: > > "tbl", "idx", "stat" > "metrics", "metrics_idx", "68682102 2 2 2" > "metrics", "metrics_frame_idx", "68682102 2" >

Re: [sqlite] Slow Query on large database Help

Here is the data from stat1: "tbl", "idx", "stat" "metrics", "metrics_idx", "68682102 2 2 2" "metrics", "metrics_frame_idx", "68682102 2" "metrics", "sqlite_autoindex_mobileye_ldw_metrics_1", "68682102 230 1" "object_characteristics", "object_characteristics_idx", "1148344 164050 31899 1"

Re: [sqlite] Slow Query on large database Help

On Wed, 7 Aug 2013 23:13:41 +0200 Petite Abeille wrote: > On Aug 7, 2013, at 9:25 PM, Christopher W. Steenwyk > wrote: > > > Ah, sorry about the attachments, you can find the files here: > > https://www.dropbox.com/l/fpFfcjwwcyhXZgduswCggb > >

Re: [sqlite] Slow Query on large database Help

On Wed, Aug 7, 2013 at 2:54 PM, Christopher W. Steenwyk wrote: > Hi, > > I have been working on a large database and its queries now for several > weeks and just can't figure out why my query is so slow. I've attached the > schema, my query, and the results of EXPLAIN QUERY

Re: [sqlite] Slow Query on large database Help

On Aug 7, 2013, at 9:25 PM, Christopher W. Steenwyk wrote: > Ah, sorry about the attachments, you can find the files here: > https://www.dropbox.com/l/fpFfcjwwcyhXZgduswCggb Ah, also, your schema has a very, hmmm, Entity–attribute–value (EAV) smell to it (object,

Re: [sqlite] Slow Query on large database Help

On Aug 7, 2013, at 9:25 PM, Christopher W. Steenwyk wrote: > Ah, sorry about the attachments, you can find the files here: > https://www.dropbox.com/l/fpFfcjwwcyhXZgduswCggb > > And yes, as the final part of the DB creation I do run ANALYZE. And I do > think the indexes

Re: [sqlite] Slow Query on large database Help

Ah, sorry about the attachments, you can find the files here: https://www.dropbox.com/l/fpFfcjwwcyhXZgduswCggb And yes, as the final part of the DB creation I do run ANALYZE. And I do think the indexes are correct for the query. On Wed, Aug 7, 2013 at 3:07 PM, Petite Abeille

Re: [sqlite] Slow Query on large database Help

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 07/08/13 11:54, Christopher W. Steenwyk wrote: > I have been working on a large database and its queries now for > several weeks and just can't figure out why my query is so slow. I've > attached the schema, my query, and the results of EXPLAIN

Re: [sqlite] Slow Query on large database Help

On 7 Aug 2013, at 7:54pm, Christopher W. Steenwyk wrote: > I've attached the > schema, my query, and the results of EXPLAIN QUERY from sqliteman. Sorry, attachments don't work on this list (we don't want everyone sending us their homework). Could you instead just post

Re: [sqlite] Slow Query on large database Help

On Aug 7, 2013, at 8:54 PM, "Christopher W. Steenwyk" wrote: > The attached query takes over 6 days to run. “Patience – A minor form of despair, disguised as a virtue.” Also… attachments are stripped out by the mailing list. You may want to try to inline them instead.

Re: [sqlite] Slow query

If your table doesn't have a primary key, this look like your table aren't normalized, maybe you can try broke this table in 2 tables like: Your definition: CREATE TABLE table1(id INTEGER, name VARCHAR, id2 INTEGER, name2 VARCHAR, year INTEGER); indexes : index1( name ), index2( id2 ),

Re: [sqlite] Slow query

> The year can also be different but Im interested only in the latest year. I > use the GROUP because I want unique id and if I add year in GROUP BY I can > get it repeated. If you're interested in the latest year then your query is totally wrong because it returns random year. If you saw what

Re: [sqlite] Slow query

On Wed, Jun 30, 2010 at 2:40 AM, Pavel Ivanov wrote: > > The query return the apropiate values as always the id -> id2 relation is > > the same and id -> name and id2 -> name2. > > So your id maps uniquely to id2, name and name2. But what about year? > What value of year do

Re: [sqlite] Slow query

> The query return the apropiate values as always the id -> id2 relation is > the same and id -> name and id2 -> name2. So your id maps uniquely to id2, name and name2. But what about year? What value of year do you want to be used in sorting? Anyway try to change query as "GROUP BY id, name2,

Re: [sqlite] Slow query

On 30 Jun 2010, at 12:32am, J. Rios wrote: > On Wed, Jun 30, 2010 at 12:13 AM, Simon Slavin wrote: >> >> A good index would be >> >> name2, year, id >> >> That's all three columns in the same index, not three separate indexes one >> on each column. > > I did the test

Re: [sqlite] Slow query

On Wed, Jun 30, 2010 at 12:13 AM, Simon Slavin wrote: > > None of your indexes are much use for this SELECT command. Imagine trying > to do the SELECT command yourself and you'll quickly see that whichever of > the supplied indexes you use you're left trying to sort a great

Re: [sqlite] Slow query

On 30 Jun 2010, at 12:05am, J. Rios wrote: > Sorry for posting on top of the message, No problem. Adding new text at the bottom of a post, and trimming what you quote just enough that people understand your new text, make your own message clear and encourage people to reply to it. Look at

Re: [sqlite] Slow query

On Wed, Jun 30, 2010 at 12:02 AM, Simon Slavin wrote: > > On 29 Jun 2010, at 11:57pm, J. Rios wrote: > > >>> I have created the next indexes : index1( name ), index2( id2 ), > index3( > >>> name2 ); > > Those are very unlikely to be of any use at all. They're probably a

Re: [sqlite] Slow query

The query return the apropiate values as always the id -> id2 relation is the same and id -> name and id2 -> name2. I keep them in the same table to speed up other queries that are now very quick as uses indexes for the ordering but in this SELECT the GROUP BY makes the difference and the SORT is

Re: [sqlite] Slow query

On 29 Jun 2010, at 11:57pm, J. Rios wrote: >>> I have created the next indexes : index1( name ), index2( id2 ), index3( >>> name2 ); Those are very unlikely to be of any use at all. They're probably a waste of space. > Its not the primary Key. There are more fields but the index on id is >

Re: [sqlite] Slow query

Its not the primary Key. There are more fields but the index on id is created also. Sorry I missed it in the post. If I do a EXPLAIN QUERY PLAN sqlite tells me its going to use the id INDEX. But the sorting is slow. Thanks in advance On Tue, Jun 29, 2010 at 5:32 PM, Jim Morris

Re: [sqlite] Slow query

You also haven't specified a primary key, i.e. on "id" On 6/28/2010 11:24 PM, J. Rios wrote: > I have the next table > > table1( id INTEGER, name VARCHAR, id2 INTEGER, name2 VARCHAR, year INTEGER > ); > > I have created the next indexes : index1( name ), index2( id2 ), index3( > name2 ); > >

Re: [sqlite] Slow query

> SELECT id, name, id2, name2 GROUP BY id ORDER BY name2, year DESC LIMIT 0, > 15 > > How can I make it faster? First of all your query should return nonsense in any field except id. I bet it will also return different results (for the same ids) depending on what LIMIT clause you add or don't add

Re: [sqlite] Slow Query with LEFT OUTER JOIN

On Tue, 23 Sep 2008 14:37:11 -0400, Enrique Ramirez wrote: >On Tue, Sep 23, 2008 at 1:16 PM, Kees Nuyt <[EMAIL PROTECTED]> wrote: >> Steps to take (you need all of them, except 1): >> >> 1) Use v6.2.3 >> > >Probably meant to say 1) Use v3.6.2? Oops, yes. Or even better: v3.6.3 -- ( Kees

Re: [sqlite] Slow Query with LEFT OUTER JOIN

On Mon, 22 Sep 2008 13:48:42 -0700, Jason wrote: >Hello everyone, > >Hoping that I could get some help with a performance problem. >Using version 3.5.2 > >Here are the tables: >CREATE TABLE Objects (ObjectId INTEGER PRIMARY KEY, Name TEXT) >CREATE TABLE Keywords4Objects (ObjectId INTEGER,

RE: [sqlite] Slow query on one machine

at that particular point on that particular machine, all else behaves normal. RBS -Original Message- From: Jay Sprenkle [mailto:[EMAIL PROTECTED] Sent: 19 January 2008 14:12 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Slow query on one machine On Jan 18, 2008 3:32 PM, RB Smissaert <[EMAIL PROTEC

Re: [sqlite] Slow query on one machine

On Jan 18, 2008 3:32 PM, RB Smissaert <[EMAIL PROTECTED]> wrote: > The application > that runs this is exactly the same on both machines. The slow machine is > actually slightly slower specification wise, but that can't explain the huge > differences in timings. > Have you run spinrite ( a disk

RE: [sqlite] slow query

Ignore this as I found out what the trouble was. I dropped 2 indices from the table ADDRESSLINK as they were on fields where the values were nearly all the same. Forgot now what the technical term for it is, but it solved it all and now very fast. Great software this SQLite! RBS -Original

Re: [sqlite] Slow query after reboot

Geoff Simonds wrote: Thanks to everyone for all the help on this problem. I am going to try creating a new thread to touch the tables at startup. Chris Schirlinger wrote: We have the same issue, to get around it we fire a thread when the program starts, intelligently "touching" every table

Re: [sqlite] Slow query after reboot

Thanks to everyone for all the help on this problem. I am going to try creating a new thread to touch the tables at startup. Chris Schirlinger wrote: We have the same issue, to get around it we fire a thread when the program starts, intelligently "touching" every table that the user is

Re: [sqlite] Slow query after reboot

We have the same issue, to get around it we fire a thread when the program starts, intelligently "touching" every table that the user is likely to access (As Michael Sizaki already mentioned a select count(last_column) from big_table; will do it) Since a user is very unlikely to run a program

Re: [sqlite] Slow query after reboot

Geoff Simonds wrote: The app is running on Windows XP machines Is it possible that indexing services are enabled and XP is trying to index the database file?

Re: [sqlite] Slow query after reboot

<[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Thursday, January 19, 2006 7:52:55 AM Subject: Re: [sqlite] Slow query after reboot My table contains about 500,000 rows and 4 columns, not all that much data. The overall size of the db file is 35 mb. Does 15 - 20 seconds sound right t

Re: [sqlite] Slow query after reboot

Thanks for the info and suggestions Michael. I will give this a try. Michael Sizaki wrote: Geoff Simonds wrote: My table contains about 500,000 rows and 4 columns, not all that much data. The overall size of the db file is 35 mb. Does 15 - 20 seconds sound right to load from disk into

Re: [sqlite] Slow query after reboot

Geoff Simonds wrote: My table contains about 500,000 rows and 4 columns, not all that much data. The overall size of the db file is 35 mb. Does 15 - 20 seconds sound right to load from disk into memory? Yes it does. The problem is, that your query is probably not reading sequentially from

Re: [sqlite] Slow query after reboot

- Original Message - From: "Geoff Simonds" <[EMAIL PROTECTED]> My table contains about 500,000 rows and 4 columns, not all that much data. The overall size of the db file is 35 mb. Does 15 - 20 seconds sound right to load from disk into memory? I can't tell you that until the

Re: [sqlite] Slow query after reboot

My table contains about 500,000 rows and 4 columns, not all that much data. The overall size of the db file is 35 mb. Does 15 - 20 seconds sound right to load from disk into memory? Robert Simpson wrote: - Original Message - From: "Geoff Simonds" <[EMAIL PROTECTED]> The app is

Re: [sqlite] Slow query after reboot

- Original Message - From: "Geoff Simonds" <[EMAIL PROTECTED]> The app is running on Windows XP machines and I assume that disk files are cached. The strange thing is that the time it takes for the initial read into RAM after install and first use is significantly shorter than

Re: [sqlite] Slow query after reboot

The app is running on Windows XP machines and I assume that disk files are cached. The strange thing is that the time it takes for the initial read into RAM after install and first use is significantly shorter than after a reboot. For example, if you just installed the app and start it, the

Re: [sqlite] Slow query after reboot

On 1/19/06, Geoff Simonds <[EMAIL PROTECTED]> wrote: > I have created a client application that is always running on a users > desktop. The application accepts user input and then uses SQLite to > perform a few simple queries against a single db file that contains 4 > tables. The performance is