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.
Below are more details, gonna try it again with the pre-compiled CLI rather
than my self-compiled version to make sure it's not just something I managed to
screw up in my compilation process. Of course, if it's the same then it won't
finish by the time I send this.
pragma quick_check; is running at the moment. File is ~33 gigs with ~110
million records. If quick_check finishes with nothing before I leave for the
weekend I'll kick off a full integrity_check.
This is on 64 bit Windows 7 Enterprise. Both databases are in WAL mode.
The main data table is the only table in the main file. (Names have been
changed to protect the innocent):
create table bar
(
id integer primary key,
State text collate nocase,
Latitude real,
Longitude real,
someOtherID int,
bunch of other fields including between the above
);
create index idx_bar_1 on bar (State, field2, field3, field4);
create index idx_bar_2 on bar (someOtherID);
select * from sqlite_stat1;
tbl|idx|stat
bar|idx_bar_1|109801252 2152966 3766 2887 2
bar|idx_bar_2|109801252 1
In the side database file what I'm trying to populate is:
create table coordExtremes
(
State text not null primary key collate nocase
check (length(State) = 2),
minLat real,
maxLat real,
minLon real,
maxLon real
);
sqlite> select sqlite_version();
sqlite_version()
3.15.1
sqlite> select sqlite_source_id();
sqlite_source_id()
2016-11-04 12:08:49 1136863c76576110e710dd5d69ab6bf347c65e36
sqlite> pragma compile_options;
compile_option
COMPILER=gcc-4.9.3
DEFAULT_MMAP_SIZE=0
ENABLE_COLUMN_METADATA
ENABLE_DBSTAT_VTAB
ENABLE_MEMORY_MANAGEMENT
ENABLE_RTREE
LIKE_DOESNT_MATCH_BLOBS
MAX_MMAP_SIZE=0
OMIT_SHARED_CACHE
SYSTEM_MALLOC
THREADSAFE=0
attach database 'theBigOne.sqlite' as foo;
explain query plan insert into coordExtremes select State, min(Latitude),
max(Latitude), min(Longitude), max(Longitude) from foo.bar group by State;
selectid|order|from|detail
0|0|0|SCAN TABLE bar USING INDEX idx_bar_1
explain insert into coordExtremes select State, min(Latitude), max(Latitude),
min(Longitude), max(Longitude) from foo.bar group by State;
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 78 0 00 Start at 78
1 InitCoroutine 7 56 2 00
2 Noop 1 3 0 00
3 Integer 0 16 0 00 r[16]=0; clear abort
flag
4 Integer 0 15 0 00 r[15]=0; indicate
accumulator empty
5 Null 0 19 19 00 r[19..19]=NULL
6 Gosub 18 53 0 00
7 OpenRead 0 3 2 16 00 root=3 iDb=2; bar
8 OpenRead 2 4 2 k(5,NOCASE,NOCASE,NOCASE,NOCASE,) 00
root=4 iDb=2; idx_bar_1
9 Rewind 2 38 21 0 00
10 Seek 2 0 0 00 Move 0 to 2.rowid
11 Column 2 0 20 00 r[20]=bar.State
12 Compare 19 20 1 k(1,NOCASE) 00 r[19] <-> r[20]
13 Jump 14 18 14 00
14 Move 20 19 1 00 r[19]=r[20]
15 Gosub 17 44 0 00 output one row
16 IfPos 16 55 0 00 if r[16]>0 then
r[16]-=0, goto 55; check abort flag
17 Gosub 18 53 0 00 reset accumulator
18 Column 0 14 21 00 r[21]=bar.Latitude
19 RealAffinity 21 0 0 00
20 CollSeq 22 0 0 (BINARY) 00
21 AggStep0 0 21 9 min(1) 01 accum=r[9]
step(r[21])
22 Column 0 14 21 00 r[21]=bar.Latitude
23 RealAffinity 21 0 0 00
24 CollSeq 22 0 0 (BINARY) 00
25 AggStep0 0 21 10 max(1) 01 accum=r[10]
step(r[21])
26 Column 0 15 21 00 r[21]=bar.Longitude
27 RealAffinity 21 0 0 00
28 CollSeq 22 0 0 (BINARY) 00
29 AggStep0 0 21 11 min(1) 01 accum=r[11]
step(r[21])
30 Column 0 15 21 00 r[21]=bar.Longitude
31 RealAffinity 21 0 0 00
32 CollSeq 22 0 0 (BINARY) 00
33 AggStep0 0 21 12 max(1) 01 accum=r[12]
step(r[21])
34 If 22 36 0 00
35 Column 2 0 8 00 r[8]=bar.State
36 Integer 1 15 0 00 r[15]=1; indicate
data in accumulator
37 Next 2 10 0 01
38 Close 0 0 0 00
39 Close 2 0 0 00
40 Gosub 17 44 0 00 output final row
41 Goto 0 55 0 00
42 Integer 1 16 0 00 r[16]=1; set abort
flag
43 Return 17 0 0 00
44 IfPos 15 46 0 00 if r[15]>0 then
r[15]-=0, goto 46; Groupby result generator entry point
45 Return 17 0 0 00
46 AggFinal 9 1 0 min(1) 00 accum=r[9] N=1
47 AggFinal 10 1 0 max(1) 00 accum=r[10] N=1
48 AggFinal 11 1 0 min(1) 00 accum=r[11] N=1
49 AggFinal 12 1 0 max(1) 00 accum=r[12] N=1
50 Copy 8 2 4 00 r[2..6]=r[8..12]
51 Yield 7 0 0 00
52 Return 17 0 0 00 end groupby result
generator
53 Null 0 8 14 00 r[8..14]=NULL
54 Return 18 0 0 00
55 EndCoroutine 7 0 0 00
56 OpenWrite 3 6 0 5 00 root=6 iDb=0;
coordExtremes
57 OpenWrite 4 7 0 k(1,NOCASE) 00 root=7 iDb=0;
sqlite_autoindex_coordExtremes_1
58 Yield 7 75 0 00
59 NewRowid 3 1 0 00 r[1]=rowid
60 HaltIfNull 1299 2 2 coordExtremes.State 01 if r[2]=null
halt
61 Copy 2 25 0 00 r[25]=r[2]
62 Function0 0 25 24 length(1) 01 r[24]=func(r[25])
63 Eq 26 65 24 51 if r[24]==r[26]
goto 65
64 Halt 275 2 0 coordExtremes 03
65 Affinity 2 5 0 BEEEE 00 affinity(r[2..6])
66 SCopy 2 27 0 00 r[27]=r[2]; State
67 IntCopy 1 28 0 00 r[28]=r[1]; rowid
68 MakeRecord 27 2 23 00
r[23]=mkrec(r[27..28]); for sqlite_autoindex_coordExtremes_1
69 NoConflict 4 71 27 1 00 key=r[27]
70 Halt 1555 2 0 coordExtremes.State 02
71 IdxInsert 4 23 0 10 key=r[23]
72 MakeRecord 2 5 24 00 r[24]=mkrec(r[2..6])
73 Insert 3 24 1 coordExtremes 1b intkey=r[1]
data=r[24]
74 Goto 0 58 0 00
75 Close 3 0 0 00
76 Close 4 0 0 00
77 Halt 0 0 0 00
78 Transaction 0 1 3 0 01 usesStmtJournal=1
79 Transaction 2 0 4 0 01 usesStmtJournal=1
80 Integer 2 26 0 00 r[26]=2
81 Goto 0 1 0 00
sqlite> insert into coordExtremes select State, min(Latitude), max(Latitude),
min(Longitude), max(Longitude) from bar group by State;
<chirping cricket noises>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
