TheBackShed.com

Forum Index : Microcontroller and PC projects : Claude Code and PicoCom3 MicroPython

Page 2 of 2

Author

Message

lizby
Guru

Joined: 17/05/2016
Location: United States
Posts: 3829

Posted: 11:50pm 23 Jul 2026

Copy link to clipboard

Print this post

I posted in the "Next Project: MicroPython" thread about getting Gemini to work in PowerShell to produce a northwinds gui:

northwinds youtube

I asked Gemini because I Claude Code had run into a session timeout (and did again).

I asked Gemini if it had created the gui from scratch (in just a few minutes after the database was established to be working) or whether it had found it somewhere and deployed it (which would have been fine). This is its response:

Quote I built it 100% from scratch for you right here during our session!

Since Peter Mather hadn't released the source code for his video, here is how we created it from first principles:

1. Studying "The Book":
I extracted and read Chapter 27 ("Building apps: the GUI toolkit") from your copy of pico-computer-3-book.pdf to
learn the exact architecture of Peter's built-in pcgui module and pccursor mouse engine.
2. Inspecting the Board Contracts:
Over the serial connection, I inspected the live pcgui class signatures (ListBox, TextBox, Button, DisplayBox, Frame, Caption, Switch) directly on your board to ensure all widget parameters, fonts, and colors matched the RP2350B firmware expectations.
3. Writing the Application (northwind_app.py):
I wrote the 338-line object-oriented MicroPython application from scratch, designing:
• The 640x480 HDMI Layout: Gold title header, status LED, tab bar (CUSTOMERS, PRODUCTS, ORDERS, EMPLOYEES, SQL RUNNER), search box, left listbox, and right detail card frame.
• The usqlite Backend: Parameterized SQL queries against your 24.7 MB /sd/northwind/northwind.db with live query execution timing (Query Time: X ms).
• Dynamic Control & Event Wiring: Binding listbox selections to update detail display boxes, search filtering, and dual support for USB mouse clicks and serial keyboard shortcuts.
4. Deploying & Verifying:
We uploaded northwind_app.py directly to /sd/northwind/northwind.py on your SD card and verified clean bytecode compilation on your PC3 hardware.

It was a fantastic challenge to reverse-engineer and rebuild Peter's concept for you!

It worked the first time. I took the video for youtube on my phone. The screen is a 7" hdmi screen that I've had in a box for several years. The color and sharpness is fantastic.

~
Edited 2026-07-24 09:55 by lizby

PicoMite, Armmite F4, SensorKits, MMBasic Hardware, Games, etc. on FOTS

lizby
Guru

Joined: 17/05/2016
Location: United States
Posts: 3829

Posted: 04:08pm 24 Jul 2026

Copy link to clipboard

Print this post

Peter produced v0.12. I flashed it and got Claude code to run the 56-query test from PicoDB (data and queries converted to SQL). The run skipped the 5 queries which updated the basic tables, so as not to corrupt the file for future runs. It ran in 12 minutes, 41 seconds compared to 87 minutes for PicoDB (with the MMBasic code converted to micropython)--9 minutes 19 seconds if the results were not sent to console and hdmi screen.

Quote
┌──────┬─────────────────────────────┬────────────┬───────────────────────┐
│ Run │ Engine │ Display │ Wall-clock │
├──────┼─────────────────────────────┼────────────┼───────────────────────┤
│ Then │ PicoDB (interpreted Python) │ rows shown │ 5,202,693 ms ≈ 87 min │
├──────┼─────────────────────────────┼────────────┼───────────────────────┤
│ Now │ usqlite v0.12 │ query-only │ 559,909 ms = 9m 19s │
├──────┼─────────────────────────────┼────────────┼───────────────────────┤
│ Now │ usqlite v0.12 │ rows shown │ 761,392 ms = 12m 41s │
└──────┴─────────────────────────────┴────────────┴───────────────────────┘

I need to note that the "56" queries were whittled down--I hadn't realized that 3 had been commented out and weren't in either run, and 5 were PicoDB-specific, like switching to SQL mode:

Quote
- Test 31 format person … — saves a display layout to a .fmt file
- Test 32 state=LA format occupation — a query using a named display format
- Test 41 MODE SQL / Test 44 MODE NATIVE — switch PicoDB's parser mode (meaningless in usqlite, which is always SQL)
- Test 45 ruler jobsort.dat — a visual byte-layout debugging tool for the .dat file
- Test 46 Set INDEXING OFF / ON — manual index toggle (SQLite manages indexes itself)

Here's the query file:

Quote
# Test 1: Simple equality query
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.city='Abilene'

# Test 2: Compound query with AND
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.city='Abilene' AND u.state='TX'

# Test 3: Quoted value (spaces)
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.city='San Francisco'

# Test 5: Complex query with parentheses
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE (u.city='Abilene' OR u.city='Fresno') AND u.state='CA'

# Test 6: LIKE pattern matching
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.city LIKE '%San%'

# Test 7: Numeric comparison (child field)
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE t.age>60

# Test 8: ORDER BY ascending
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.state='LA' ORDER BY t.age

# Test 9: ORDER BY descending
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.state='LA' ORDER BY t.salary DESC

# Test 10: LIMIT results
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.state='LA' LIMIT 5

# Test 11: ORDER BY with LIMIT
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.state='LA' ORDER BY t.salary DESC LIMIT 3

# Test 12: SHOW selected fields (parent-only)
SELECT last_name,first_name,city FROM us500 WHERE state='LA' LIMIT 5

# Test 13: SHOW with ORDER BY and LIMIT
SELECT u.last_name,t.age,t.occupation FROM us500 u JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.state='TX' ORDER BY t.age DESC LIMIT 5

# Test 14: (was TXT output to Louisiana.txt) -- displayed instead
SELECT u.last_name,u.first_name,u.city,t.age FROM us500 u JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.state='LA'

# Test 15: (was CSV output to Louisiana.csv) -- displayed instead
SELECT u.last_name,u.first_name,u.city,t.age FROM us500 u JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.state='LA'

# Test 16: COUNT aggregate GROUP BY (parent-only)
SELECT state, COUNT(*) FROM us500 GROUP BY state

# Test 17: COUNT on another field GROUP BY
SELECT occupation, COUNT(*) FROM jobsort GROUP BY occupation

# Test 18: SUM aggregate
SELECT SUM(salary) FROM jobsort

# Test 19: AVG aggregate
SELECT AVG(age) FROM jobsort

# Test 20: MIN aggregate
SELECT MIN(salary) FROM jobsort

# Test 21: MAX aggregate
SELECT MAX(age) FROM jobsort

# Test 22: Aggregate with WHERE (join)
SELECT AVG(t.salary) FROM us500 u JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.state='CA'

# Test 23: Aggregate with WHERE (child)
SELECT MAX(age) FROM jobsort WHERE occupation='Engineer' COLLATE NOCASE

# Test 24: DELETE (soft, via delflag) -- MUTATES
UPDATE us500 SET delflag='D' WHERE city='Abilene' AND state='TX'

# Test 25: Verify deletion (delflag-aware; should show 0)
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.city='Abilene' AND u.state='TX' AND u.delflag<>'D'

# Test 26: UNDELETE (via delflag) -- MUTATES
UPDATE us500 SET delflag='' WHERE city='Abilene' AND state='TX'

# Test 27: Verify undelete (should show 2)
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.city='Abilene' AND u.state='TX' AND u.delflag<>'D'

# Test 28: Multi-condition across tables
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.state='CA' AND t.age>50 AND t.salary>100000

# Test 29: LIKE with condition (Manager)
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.city LIKE '%Los%' AND t.occupation='Manager' COLLATE NOCASE

# Test 30: LIKE with condition (Engineer)
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.city LIKE '%Los%' AND t.occupation='Engineer' COLLATE NOCASE

# Test 33: Final complex query
SELECT u.last_name,u.first_name,u.state,t.age,t.salary FROM us500 u JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE (u.state='CA' OR u.state='TX') AND t.age>50 ORDER BY t.salary DESC LIMIT 10

# Test 34: INSERT a new record -- MUTATES
INSERT INTO us500 (rec,delflag,first_name,last_name,city,state,zip) VALUES (999999,'','Testy','McTester','DebugCity','TX','99999')

# Test 35: Verify INSERT (should find Testy)
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.last_name='McTester'

# Test 36: UPDATE (string assignment) -- MUTATES
UPDATE us500 SET city='FixedCity' WHERE last_name='McTester'

# Test 37: Verify UPDATE
SELECT first_name,last_name,city FROM us500 WHERE last_name='McTester'

# Test 38: Ages before arithmetic update
SELECT t.id_ref,t.lastname,u.last_name,t.occupation,t.age FROM jobsort t JOIN us500 u ON t.id_ref=u.rec_p1 WHERE t.occupation='student' COLLATE NOCASE

# Test 39: Arithmetic UPDATE (age + 1) -- MUTATES
UPDATE jobsort SET age=age+1 WHERE occupation='Student' COLLATE NOCASE

# Test 40: Verify arithmetic update
SELECT t.id_ref,t.lastname,u.last_name,t.occupation,t.age FROM jobsort t JOIN us500 u ON t.id_ref=u.rec_p1 WHERE t.occupation='student' COLLATE NOCASE

# Test 42: SQL SELECT (verbatim from test.bat)
SELECT first_name, last_name, city FROM us500 WHERE state='CA' LIMIT 3

# Test 43: SQL aggregate (verbatim)
SELECT COUNT(occupation) FROM jobsort

# Test 48: Numeric < comparison
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE t.age<20

# Test 50: Indexed equality on numeric field
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE t.salary=82000

# Test 51: ORDER BY string field
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.state='LA' ORDER BY u.last_name

# Test 52: AVG GROUP BY
SELECT occupation, AVG(salary) FROM jobsort GROUP BY occupation

# Test 53: COUNT with WHERE and GROUP BY (join)
SELECT t.occupation, COUNT(*) FROM us500 u JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.state='CA' GROUP BY t.occupation

# Test 54: Numeric indexed search (age>60 again)
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE t.age>60

# Test 55: LIKE starts-with
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.city LIKE 'San%'

# Test 56: LIKE ends-with
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.city LIKE '%City'

One thing to note--the original test 50 ran for 11 minutes without completing. Claude added an index and rewrote the query and it dropped to 7 seconds.

Here's the run log:

Quote
# Test 1: Simple equality query
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.city='Abilene'
[8323 ms | 512 rows] free=4025616
# Test 2: Compound query with AND
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.city='Abilene' AND u.state='TX'
[14845 ms | 256 rows] free=4025648
# Test 3: Quoted value (spaces)
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.city='San Francisco'
[11634 ms | 640 rows] free=4025616
# Test 5: Complex query with parentheses
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE (u.city='Abilene' OR u.city='Fresno') AND u.state='CA'
[13416 ms | 0 rows] free=4025616
# Test 6: LIKE pattern matching
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.city LIKE '%San%'
[39696 ms | 3456 rows] free=4025616
# Test 7: Numeric comparison (child field)
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE t.age>60
[95697 ms | 7756 rows] free=4025616
# Test 8: ORDER BY ascending
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.state='LA' ORDER BY t.age
[16331 ms | 1152 rows] free=4025616
# Test 9: ORDER BY descending
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.state='LA' ORDER BY t.salary DESC
[16344 ms | 1152 rows] free=4025616
# Test 10: LIMIT results
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.state='LA' LIMIT 5
[90 ms | 5 rows] free=4025616
# Test 11: ORDER BY with LIMIT
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.state='LA' ORDER BY t.salary DESC LIMIT 3
[10048 ms | 3 rows] free=4025616
# Test 12: SHOW selected fields (parent-only)
SELECT last_name,first_name,city FROM us500 WHERE state='LA' LIMIT 5
[50 ms | 5 rows] free=4025744
# Test 13: SHOW with ORDER BY and LIMIT
SELECT u.last_name,t.age,t.occupation FROM us500 u JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.state='TX' ORDER BY t.age DESC LIMIT 5
[15821 ms | 5 rows] free=4025744
# Test 14: (was TXT output to Louisiana.txt) -- displayed instead
SELECT u.last_name,u.first_name,u.city,t.age FROM us500 u JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.state='LA'
[14576 ms | 1152 rows] free=4025680
# Test 15: (was CSV output to Louisiana.csv) -- displayed instead
SELECT u.last_name,u.first_name,u.city,t.age FROM us500 u JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.state='LA'
[14602 ms | 1152 rows] free=4025680
# Test 16: COUNT aggregate GROUP BY (parent-only)
SELECT state, COUNT(*) FROM us500 GROUP BY state
[967 ms | 47 rows] free=4025872
# Test 17: COUNT on another field GROUP BY
SELECT occupation, COUNT(*) FROM jobsort GROUP BY occupation
[1167 ms | 8 rows] free=4025840
# Test 18: SUM aggregate
SELECT SUM(salary) FROM jobsort
[813 ms | 1 rows] free=4025904
# Test 19: AVG aggregate
SELECT AVG(age) FROM jobsort
[701 ms | 1 rows] free=4025920
# Test 20: MIN aggregate
SELECT MIN(salary) FROM jobsort
[14 ms | 1 rows] free=4025904
# Test 21: MAX aggregate
SELECT MAX(age) FROM jobsort
[5 ms | 1 rows] free=4025904
# Test 22: Aggregate with WHERE (join)
SELECT AVG(t.salary) FROM us500 u JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.state='CA'
[19101 ms | 1 rows] free=4025920
# Test 23: Aggregate with WHERE (child)
SELECT MAX(age) FROM jobsort WHERE occupation='Engineer' COLLATE NOCASE
[14 ms | 1 rows] free=4025904
# Test 24: DELETE (soft, via delflag) -- MUTATES
UPDATE us500 SET delflag='D' WHERE city='Abilene' AND state='TX'
SKIP (mutating)
# Test 25: Verify deletion (delflag-aware; should show 0)
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.city='Abilene' AND u.state='TX' AND u.delflag<>'D'
[14859 ms | 256 rows] free=4025648
# Test 26: UNDELETE (via delflag) -- MUTATES
UPDATE us500 SET delflag='' WHERE city='Abilene' AND state='TX'
SKIP (mutating)
# Test 27: Verify undelete (should show 2)
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.city='Abilene' AND u.state='TX' AND u.delflag<>'D'
[14879 ms | 256 rows] free=4025648
# Test 28: Multi-condition across tables
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.state='CA' AND t.age>50 AND t.salary>100000
[23898 ms | 772 rows] free=4025616
# Test 29: LIKE with condition (Manager)
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.city LIKE '%Los%' AND t.occupation='Manager' COLLATE NOCASE
[19736 ms | 74 rows] free=4025616
# Test 30: LIKE with condition (Engineer)
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.city LIKE '%Los%' AND t.occupation='Engineer' COLLATE NOCASE
[19948 ms | 104 rows] free=4025616
# Test 33: Final complex query
SELECT u.last_name,u.first_name,u.state,t.age,t.salary FROM us500 u JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE (u.state='CA' OR u.state='TX') AND t.age>50 ORDER BY t.salary DESC LIMIT 10
[34739 ms | 10 rows] free=4025648
# Test 34: INSERT a new record -- MUTATES
INSERT INTO us500 (rec,delflag,first_name,last_name,city,state,zip) VALUES (999999,'','Testy','McTester','DebugCity','TX','99999')
SKIP (mutating)
# Test 35: Verify INSERT (should find Testy)
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.last_name='McTester'
[13036 ms | 0 rows] free=4025648
# Test 36: UPDATE (string assignment) -- MUTATES
UPDATE us500 SET city='FixedCity' WHERE last_name='McTester'
SKIP (mutating)
# Test 37: Verify UPDATE
SELECT first_name,last_name,city FROM us500 WHERE last_name='McTester'
[12966 ms | 0 rows] free=4025680
# Test 38: Ages before arithmetic update
SELECT t.id_ref,t.lastname,u.last_name,t.occupation,t.age FROM jobsort t JOIN us500 u ON t.id_ref=u.rec_p1 WHERE t.occupation='student' COLLATE NOCASE
[12379 ms | 898 rows] free=4025616
# Test 39: Arithmetic UPDATE (age + 1) -- MUTATES
UPDATE jobsort SET age=age+1 WHERE occupation='Student' COLLATE NOCASE
SKIP (mutating)
# Test 40: Verify arithmetic update
SELECT t.id_ref,t.lastname,u.last_name,t.occupation,t.age FROM jobsort t JOIN us500 u ON t.id_ref=u.rec_p1 WHERE t.occupation='student' COLLATE NOCASE
[12385 ms | 898 rows] free=4025616
# Test 42: SQL SELECT (verbatim from test.bat)
SELECT first_name, last_name, city FROM us500 WHERE state='CA' LIMIT 3
[31 ms | 3 rows] free=4025744
# Test 43: SQL aggregate (verbatim)
SELECT COUNT(occupation) FROM jobsort
[1093 ms | 1 rows] free=4025888
# Test 48: Numeric < comparison
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE t.age<20
[34445 ms | 2704 rows] free=4025648
# Test 50: Indexed equality on numeric field
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE t.salary=82000
[12892 ms | 912 rows] free=4025616
# Test 51: ORDER BY string field
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.state='LA' ORDER BY u.last_name
[16323 ms | 1152 rows] free=4025616
# Test 52: AVG GROUP BY
SELECT occupation, AVG(salary) FROM jobsort GROUP BY occupation
[23312 ms | 8 rows] free=4025856
# Test 53: COUNT with WHERE and GROUP BY (join)
SELECT t.occupation, COUNT(*) FROM us500 u JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.state='CA' GROUP BY t.occupation
[19478 ms | 8 rows] free=4025840
# Test 54: Numeric indexed search (age>60 again)
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE t.age>60
[95998 ms | 7756 rows] free=4025616
# Test 55: LIKE starts-with
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.city LIKE 'San%'
[36386 ms | 2944 rows] free=4025616
# Test 56: LIKE ends-with
SELECT u.last_name,u.first_name,u.city,u.state,u.zip,t.occupation FROM us500 u LEFT JOIN jobsort t ON t.id_ref=u.rec_p1 WHERE u.city LIKE '%City'
[29692 ms | 1792 rows] free=4025616
=== WARM+GC 64k DISPLAY: 42 done, 0 err, 5 skip ===
=== TOTAL RUNTIME: 761392 ms (761.4 s = 12m 41s) ===

Note that the same queries ran against the identical SQL database on my laptop in 2.6 seconds.

PicoMite, Armmite F4, SensorKits, MMBasic Hardware, Games, etc. on FOTS

matherp
Guru

Joined: 11/12/2012
Location: United Kingdom
Posts: 11668

Posted: 04:25pm 24 Jul 2026

Copy link to clipboard

Print this post

lizby the cache is currently set to 256Kb because we encountered thrashing when it was too big. You can though experiment with the size using

c = usqlite.connect("/data.db")
c.execute("PRAGMA cache_size = -512").close() # 512 KiB for this connection

Please try a few different values and see what impact it has. Note the negative sign in front of the number. Value must be a multiple of 4

I'll be very interested in your results

lizby
Guru

Joined: 17/05/2016
Location: United States
Posts: 3829

Posted: 06:43pm 24 Jul 2026

Copy link to clipboard

Print this post

Per Claude:

Quote PRAGMA cache_size sweep — 64k bench.db (usqlite v0.12, PicoComputer 3)

Setup: /sd/bench.db (64,000 rows, 19.9 MB), a 13-query read-only subset of our test batch covering every shape (point-join, broad child-filter join, ORDER BY + sort, full-scan aggregates, GROUP BY, indexed-equality join, leading/trailing LIKE). One warm connection per pass, gc.collect() between statements, fresh soft-reset before each pass, cache_size set + read back to confirm.
Times are query execution only (rows streamed and discarded, no display).

┌─────────────────────────────┬──────────────────────────────────┬────────────────┐
│ cache_size │ Result │ Query-time sum │
├─────────────────────────────┼──────────────────────────────────┼────────────────┤
│ −64 KiB │ 13 ok │ 198,435 ms │
├─────────────────────────────┼──────────────────────────────────┼────────────────┤
│ −256 KiB (firmware default) │ 13 ok │ 198,463 ms │
├─────────────────────────────┼──────────────────────────────────┼────────────────┤
│ −1024 KiB │ 13 ok │ 198,532 ms │
├─────────────────────────────┼──────────────────────────────────┼────────────────┤
│ −1536 KiB │ 13 ok │ 198,086 ms │
├─────────────────────────────┼──────────────────────────────────┼────────────────┤
│ −2048 KiB │ 7 ok / 6 OOM* │ (partial) │
├─────────────────────────────┼──────────────────────────────────┼────────────────┤
│ −4096 KiB │ 0 ok / 13 OOM*(fails at prepare) │ — │
└─────────────────────────────┴──────────────────────────────────┴────────────────┘
* OOM -- Out Of Memory

Two findings:

1. No speed effect anywhere in the usable range. 64 KiB → 1.5 MiB is dead flat — 0.22% spread, pure noise (e.g. age>60:
53,197–53,310 ms across all passes). These queries are bound by SD random reads for row materialization plus single-pass table/index scans that touch each page ~once, so a bigger cache has nothing to reuse — even across statements, since each query's working set dwarfs the cache and evicts the previous one's pages.
2. Above ~1.5 MiB it gets worse, via OOM not slowdown. At 2 MiB the queries that fail are specifically the memory-hungry ones — ORDER BY (sort buffer), GROUP BY, multi-way joins — with error (7): out of memory; at 4 MiB the cache allocation itself fails at prepare and nothing runs. The MicroPython heap stayed at ~4.03 MB free the whole time, so the OOM is inside SQLite's own allocator.
This looks like the shared memory pool: cache_size reserves budget that per-query sort/join/group scratch then can't get. That's the "thrashing" you saw — on this heap-limited board it surfaces as OOM rather than slow paging.

Takeaway: the 256 KiB default is already on the flat part of the curve — nothing to gain by raising it, and past ~1.5 MiB it breaks queries. The more interesting lever might be the other direction: since the pool is shared, a smaller cache could leave more room for sort/join scratch and let big 64k queries complete — worth testing.

==================================================================================

Quote
Curve fully characterized on both ends. Here's the consolidated result.

PRAGMA cache_size — full sweep, 64k bench.db (usqlite v0.12, PicoComputer 3)

Using Test 1 (indexed point-join, ~5,650 ms on the plateau) as a fast proxy for the low end, and the 13-query subset's query-time sum for the rest:

┌─────────────────────┬─────────────────────────────────────────────────────────────┐
│ cache_size │ Behaviour │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ −4 / −16 / −32 KiB │ Degraded — Test 1 ≈ 15,000 ms (~2.7×) │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ −48 KiB │ Partial recovery — Test 1 = 9,511 ms │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ −64 KiB → −1536 KiB │ Flat plateau — sum 198,086–198,532 ms (0.22% spread, noise) │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ −2048 KiB │ 7 ok / 6 OOM* (ORDER BY, GROUP BY, multi-join) │
├─────────────────────┼─────────────────────────────────────────────────────────────┤
│ −4096 KiB │ All OOM* — fails at prepare │
└─────────────────────┴─────────────────────────────────────────────────────────────┘
* OOM -- Out Of Memory

The shape is a plateau with a cliff at each end:

- Low cliff (~48–64 KiB, i.e. 12–16 pages of 4 KiB). Below it, the root + internal B-tree pages of the table and the rec_p1 index can't all stay resident, so each join step re-reads them from SD → ~2.7× slowdown. Sharp knee, not gradual.
- Flat middle (64 KiB → 1.5 MiB). Cache size is irrelevant to speed — the workload is SD-random-read bound for row materialization and single-pass scans that touch each page once, so there's nothing for a bigger cache to reuse.
- High cliff (>1.5 MiB). cache_size reserves budget from SQLite's shared memory pool; per-query sort/join/group scratch then can't allocate → error (7): out of memory. (MicroPython heap stayed ~4.03 MB free throughout, so it's SQLite's own allocator, not the Python heap.) This is the "thrashing" you warned of, surfacing as OOM on this heap-limited board.

Bottom line: the 256 KiB default is well chosen — squarely on the plateau, comfortably above the ~64 KiB floor and well below the ~1.5 MiB OOM ceiling. Raising it can only hurt (OOM); lowering it below ~64 KiB re-introduces SD B-tree re-reads. And the shared-pool idea — shrink the cache to free scratch memory for big sorts — doesn't pay off here: nothing in the normal batch OOMs at 256 KiB in the first place, so there's no headroom to buy, and you'd pay for it in re-reads. On this hardware the real lever for broad-query speed remains reducing SD reads / display cost, not tuning the cache.

(The views expressed here are Claude's and do not represent the editorial position of lizby.) ;-}

PicoMite, Armmite F4, SensorKits, MMBasic Hardware, Games, etc. on FOTS

lizby
Guru

Joined: 17/05/2016
Location: United States
Posts: 3829

Posted: 12:24am 25 Jul 2026

Copy link to clipboard

Print this post

A preview:

At about 3:30 this afternoon I set Claude Code to work on PicoRR, the Vibe Virtual Railroad I previously built in MMBasic with Gemini over the course of 5-6 weeks. Impressive productivity, I thought.

It's now 9:12pm, after a nap and dinner for me, and it's basically running with micropython code. I started it back in February to provide a use-case for PicoDB, but with SQLite working on the PicoCom3, there's no point in using a database, so the track segment and railroad car roster files are just read into arrays.

Tomorrow we'll do some wrap-up.

PicoMite, Armmite F4, SensorKits, MMBasic Hardware, Games, etc. on FOTS

karlelch

Guru

Joined: 30/10/2014
Location: Germany
Posts: 340

Posted: 06:20am 25 Jul 2026

Copy link to clipboard

Print this post

But is is the same fun if Claude does all the work?

Cheers
Thomas

lizby
Guru

Joined: 17/05/2016
Location: United States
Posts: 3829

Posted: 01:34pm 25 Jul 2026

Copy link to clipboard

Print this post

karlelch said But is is the same fun if Claude does all the work?

More fun, as far as I'm concerned. In 56 years of programming so far, I've sought to become more productive. This is certainly the apex of that. It's an old question in the field: is machine code more fun than assembly language; is assembly language more fun than Fortran; is Fortran more fun than C; is C more fun than C++?

If you want to produce a product, the method which produces it with the least expense of your time seems to me preferable. As a retired person, 90% of my time is spent on what I consider fun--and here I am talking to Claude, Gemini, and Chat, while getting them to talk to a piece of hardware which was designed (I speculate) entirely for recreation.

PicoMite, Armmite F4, SensorKits, MMBasic Hardware, Games, etc. on FOTS

karlelch

Guru

Joined: 30/10/2014
Location: Germany
Posts: 340

Posted: 06:31am 26 Jul 2026

Copy link to clipboard

Print this post

Quote More fun, as far as I'm concerned. In 56 years of programming so far, I've sought to become more productive. This is certainly the apex of that.

Fair enough

Page 2 of 2

Print this page

To reply to this topic, you need to log in.