I'm trying to decide on the most efficient way to sort various data values. Here's how the data arrives: Device X sends a text string "name=value&name2=value&name=value"
On arrival that string is stuffed into a sql row along with the unique address of the sending device. This keeps data flowing in easily to a SQLite database.
My parsing script first gets all unique device addresses. Those are put in a hash for the parser and inserted into a new database. (the hash contains the rowid from the db after the insert.) (with more logic to keep race conditions out of the mix)
Then each row of string data is split up by the
&, then by the
Here's the general table layout:
rawData(address, string, timestamp, processed)
Each row is read from the rawData, sorted and marked as processed. (makes it easy to muck around with the parsing script)
Data is placed into these:
devices(rowid, address) dataNames(rowid, devicesid, name) dataValues(nameId, value, timestamp)
I'm trying to decide on the most efficient method of inserting this data. It ends up as thousands of rows. I supposed I could keep a hash of known device/name pairs. Then if the hash doesn't know about a new name I can go ahead and insert it and refresh the hash...
Am I missing something totally obvious? The goal is to keep selects to a minimum for efficiency!