PenguinTV optimization

I’ve been going through the crufty and scary ptvDB.py looking for ways to improve performance. Armed with the trust profile and pstats modules, I cut polling time in half by removing redundant and unnecessary database hits.

I then got the idea of generating a print statement every time I hit the DB, so I can see exactly what the database is doing during regular operation.

The result is not always pretty:

Basic article read:

SELECT title, creator, link, description, feed_id, date FROM entries WHERE id=? (69443,) ok
SELECT id,entry_id,url,file,download_status,viewed,length,mimetype FROM media WHERE entry_id = ? (69443,) ok
SELECT read FROM entries WHERE id=? (69443,) ok

Ok that’s fine, we get the entry, any associated media, and then the read state.

What about an unread entry?
SELECT title, creator, link, description, feed_id, date FROM entries WHERE id=? (67523,) ok
SELECT id,entry_id,url,file,download_status,viewed,length,mimetype FROM media WHERE entry_id = ? (67523,) ok
SELECT read FROM entries WHERE id=? (67523,) ok
UPDATE entries SET read=? WHERE id=? (1, 67523) ok
UPDATE media SET viewed=? WHERE entry_id=? (1, 67523) ok
SELECT read FROM entries WHERE id=? (67523,) ok
SELECT id,entry_id,url,file,download_status,viewed,length,mimetype FROM media WHERE entry_id = ? (67523,) ok
SELECT title, creator, link, description, feed_id, date FROM entries WHERE id=? (67523,) ok
SELECT id,entry_id,url,file,download_status,viewed,length,mimetype FROM media WHERE entry_id = ? (67523,) ok
SELECT read FROM entries WHERE id=? (67523,) ok
SELECT feed_pointer,description FROM feeds WHERE id=? (233,) ok
SELECT id,title,fakedate FROM entries WHERE feed_id=? ORDER BY fakedate DESC (233,) ok
SELECT id FROM entries WHERE feed_id=? (233,) ok
SELECT feed_pointer FROM feeds WHERE id=? (233,) ok
SELECT pollfail FROM feeds WHERE id=? (233,) ok
SELECT feed_pointer FROM feeds WHERE id=? (233,) ok
SELECT read FROM entries WHERE feed_id=? (233,) ok
SELECT id FROM feeds WHERE feed_pointer=? (233,) ok

Oops. It’s whacking the database three times for the entry data, and updates the whole feed as well. Time to figure out what I’m doing wrong!

Share

Where are the Granny Smith apples?

Char and I have been trying to find Granny Smith apples for a couple weeks now, and no store we’ve been to has them. We’ve looked in Shaw’s, Whole Foods, and even Market Basket, to no avail. Where did they all go? Are they out of season or something?

As a spoiled westerner I’m not used to growing seasons actually mattering, so this has been a weird experience. The stores have plenty of other varieties of apple, but I much prefer the tart Granny Smith. My current theory is that there is one boat that brings all the imported apples to the north east. Sometime last month it sank off the coast of South America, leaving us without green apples for the forseeable future.

edit: success! Harvest Coop had Granny Smiths, although many were bruised. I think it was the leftovers from the last successful shipment before the boat sank.

Share