How to load large python object and allow other program to access it

Question

I have a function that does the following:

cursor.execute('SELECT runtime, fingerprint FROM itemstable')
items = cursor.fetchall()
for item in items:
    do_something()

It takes about 3s to return the SQL query because there are 500k results or so and the data is about 500MB. I run this operation about a few million times per day, so would like to have the items object in memory, so I can grab it when I run the operation. Something like:

items = get_item_from_daemon(name='FingerPrint') # loaded instantly, in memory
for item in items:
    do_something()

How would I do this? Note that I am using python2.7.

Answer 1

I have a complicate solution - caching: You can load it once from the DB, adding layer between the DB and the request of change (insert, update, delete), when occur change/changes on you table/tables add the changes or the new object (as you want to implement) into temporary queue. If the transaction succeeded send the temporary queue to an updating queue (each element in the queue is list of all changes in one transaction) that will run with separate thread (if you want..) and will update your items in the memory (the cache). When your function (do_somthing()) have to run lock the updating queue after it finish his current loading transaction into your cache.

Another way (for easy synchronize caching from different machines) is to add the transaction changes into external table in the DB and your caching will synchronize from this table.