Re: [sqlite] Hung query question

[Eduardo Morras]

On Fri, 18 Nov 2016 19:20:06 +
David Raymond  wrote:

> I've got a query that I've tried a few times here that seems like
> it's hanging up on something and I'm wondering if it's just some
> brain dead thing I'm forgetting or doing wrong.
> 
> I've got a database with a bunch of records, and am trying to
> populate a table in another database with coordinate extremes for
> each state. Using the CLI I open up the side database, attach the
> main one and run
> 
> insert into coordExtremes select State, min(Latitude), max(Latitude),
> min(Longitude), max(Longitude) from foo.bar group by State;
> 
> Twice I've left that running overnight and it's still been sitting
> there not completed the next day when I came in. I gave up on using
> the CLI to do it and wrote a quick Python script to scan through the
> whole big table once and keep track of the extremes, and it finished
> in all of 15 minutes. So I'm left scratching my head as to what's up.
> If anyone can think of something or sees where I'm being an idiot,
> please let me know.

Is python using bigger cache? Increase cache with pragma in CLI. 

Set it to 100-500MB for foo db, don't need to waste cache on destiny db
(where you insert the data). Change foo with the real origin db name.

pragma foo.cache_size=-50

Why don't use R*Tree virtual table?  http://www.sqlite.org/rtree.html
---   ---
Eduardo Morras 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hung query question

[Simon Slavin]

On 18 Nov 2016, at 7:20pm, David Raymond  wrote:

> insert into coordExtremes select State, min(Latitude), max(Latitude), 
> min(Longitude), max(Longitude) from foo.bar group by State;

Apologies.  Correction to my earlier post:


Create indexes on table foo for the State, latitude and longitude columns.

CREATE INDEX idx_foo_lat ON foo (State, Latitude);
CREATE INDEX idx_foo_long ON foo (State, Longitude);
ANALYZE;

Then try the SELECT again.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hung query question

[Simon Slavin]

On 18 Nov 2016, at 7:20pm, David Raymond  wrote:

> insert into coordExtremes select State, min(Latitude), max(Latitude), 
> min(Longitude), max(Longitude) from foo.bar group by State;

Create indexes on table foo for the latitude and longitude columns.

CREATE INDEX idx_foo_lat ON foo (Latitude);
CREATE INDEX idx_foo_long ON foo (Longitude);
ANALYZE;

Then try the SELECT again.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users