sqlite> SELECT dateTime, pm2_5 FROM archive WHERE pm2_5 IS NOT NULL ORDER BY dateTime DESC LIMIT 1; Run Time: real 193.630 user 3.783819 sys 8.312705 sqlite> SELECT dateTime, pm2_5 FROM archive WHERE pm2_5 IS NOT NULL ORDER BY dateTime DESC LIMIT 1; Run Time: real 147.827 user 3.478416 sys 8.748866 sqlite> SELECT COUNT(*) FROM archive; 1391014 Run Time: real 9.171 user 0.119921 sys 0.614045 sqlite> SELECT MIN(dateTime) from archive; 1367620800 Run Time: real 0.003 user 0.000164 sys 0.000395
-- Tom Mitchell [email protected] "The whole problem with the world is that fools and fanatics are always so certain of themselves, and wiser people so full of doubts." - Bertrand Russell On Tue, Aug 4, 2026 at 6:23 PM 'John Kline' via weewx-user < [email protected]> wrote: > I found what actually made beta4 faster — it isn’t the aqi_source gate. > > beta4 caches the search list across generated files. get_extension_list() > now builds everything once on the first file of a report run, saves the > result, and hands every subsequent file a copy, refreshing only the > per-template pieces (the timespan binder and the NOAA file index). The > comment in init says it plainly: one search-list extension instance per > report run. > > In beta3 the full build ran for every one of the 14 generated files — 28 > archive scans per cycle. In beta4 it runs once — 2 scans per cycle. That’s > the speedup, and it’s the right change on its own merits: rebuilding > forecasts and config for every file was waste regardless. > > But the scan itself is unchanged: still unbounded, still a full pass over > the table when the pm2_5 column is empty. On slow hardware that’s one > ~20-second query per cycle holding a read lock against the archive writer — > a smaller window than beta3’s, but the same mechanism. It should still be > bounded: a WHERE dateTime >= ? window of a day or two returns the same > answer for any station with current PM data and costs almost nothing no > matter how large the database is. > > I think this lays this thread to rest. If the OP gets back to me with the > sql timing (OP, please run it twice), we can’t look at those numbers; but I > don’t think there are any other open items. (And I want to get back to > weewx-skyfield.) > > > On Aug 4, 2026, at 6:00 PM, John Kline <[email protected]> wrote: > > > > The warm figure is the interesting one: 2.64 s wall, with user+sys > accounting for essentially all of it. Once the 242 MB is cached, the cost > is pure CPU — SQLite examining every row in the table. Storage speed stops > mattering; that 2.6 s is the floor on that hardware no matter what the > database sits on. > > > > That matters for the fix. Bounding the lookback isn’t an I/O > optimization — it caps the worst case. Unbounded, an empty column means a > full pass over the table: 700K rows on your database, which is where your > 2.6 s goes; 1.4M on the OP’s 13 years, and growing every five minutes. > Bounded to a couple of days, the query examines at most a few hundred rows > no matter how big the database gets — and for a station with current PM > data it still stops at the first row, same as your outTemp result. > > > > For scale: the search list extension runs once per generated file, not > once per report cycle, and beta3 ships 14 template sections. At your warm > number that’s around 37 s of scanning per cycle, and more when weewxd is > competing for the same page cache in 1 GB. > > > > Your outTemp comparison is the cleanest demonstration in this thread, > incidentally. Same query shape, same table, 30,000× difference — and the > only variable is whether the first row examined has a value in it. > > > >> On Aug 4, 2026, at 5:37 PM, Greg Troxel <[email protected]> wrote: > >> I rebooted the machine and then ran the query 3 times. > >> > >> $ sqlite3 weewx.sdb > >> SQLite version 3.53.2 2026-06-03 19:12:13 > >> Enter ".help" for usage hints. > >> sqlite> .timer on > >> sqlite> SELECT dateTime, pm2_5 FROM archive WHERE pm2_5 IS NOT NULL > ORDER BY dateTime DESC LIMIT 1; > >> Run Time: real 20.939687 user 1.811444 sys 1.555754 > >> sqlite> SELECT dateTime, pm2_5 FROM archive WHERE pm2_5 IS NOT NULL > ORDER BY dateTime DESC LIMIT 1; > >> Run Time: real 2.640085 user 1.682205 sys 0.956678 > >> sqlite> SELECT dateTime, pm2_5 FROM archive WHERE pm2_5 IS NOT NULL > ORDER BY dateTime DESC LIMIT 1; > >> Run Time: real 2.675249 user 1.669083 sys 1.004916 > >> sqlite> > >> > >> $ ls -lh weewx.sdb > >> -rw-r--r-- 1 gdt users 242M Aug 4 20:30 weewx.sdb > > -- > You received this message because you are subscribed to a topic in the > Google Groups "weewx-user" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/weewx-user/9AIS1DRHl00/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > [email protected]. > To view this discussion visit > https://groups.google.com/d/msgid/weewx-user/B54515BC-B58E-4A04-961F-EB8E915183CC%40johnkline.com > . > -- You received this message because you are subscribed to the Google Groups "weewx-user" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion visit https://groups.google.com/d/msgid/weewx-user/CAKVzASCkqR8J1Hc9cDQcmreuQRvFPN2f45MLxEGNd7-0vktK2w%40mail.gmail.com.
