eddblink import tuning

[kfsone]

I did a little experimentation with the eddblink import to try and improve performance on import/use, and much of it was dead ends but I found a couple of things but I'm not certain about inflicting them on other people.

I was able to shave 3 minutes off an 11 minute import:

1- I changed the "csv.DictReader" for a "csv.reader()" which means accessing fields by number rather than name,
2- I removed some degenerate python in the hotpath: In Python, name lookups are never cached:

for i in range(1e6):
  prog.increment(1)

doesn't just call prog.bump() a million times, it has to look it up a million times:

for i in range(1e6):
  fn = getattr(prog, "bump")
  fn(1)

3- ditto for appending a list: (l.append vs l += []) and clearing (l[:] = [] is the in-place way)

4- datetime.datetime.from_timestamp() <- 2x lookups x N rows
5- int(from_live) -> from_live_val

The next big win here would be not using a dictionary to marshal the batch.

row = {
  "station_id": ...
}

because this is expensive compared to a tuple or a named tuple, and accessing it is far more expensive, when you're dealing with millions of rows; so -- just making the row= {...} is an extra 1us per 10 listing entry, or 1ms per 10,000, and gc pressure (since it has to create/store a string field for each column too).

@Tromador I'm guessing there was a catch to using the orm layer directly to just add/update all the rows? Or were you trying to minimize the diff from the original implementation? I'm thinking that maybe trying to do it all thru the orm has some nasty surprise when you're trying to zap-in 6 million items from listings.csv, hehe, but I thought it was supposed to do that cleverness for us (maybe it's tucked away in there somewhere, but I just don't expect that kind of capability from an orm)

[Tromador]

My whole approach when I did this refactor was to keep everything as much "the same" as possible. So build the database engine, that had to be from scratch, but after that every other module was kept as near to the original as I could manage. The idea was to get SQLA up and running with the same I dunno, algorithm for want of a better word, simply exchange SQLite data access for SQLA data access in the most basic way. The plan being to make that work, then at some later point think about how that might allow for further changes. I was very keen to not break stuff by doing too much at once. This is just a simple data source replacement. Hopefully it then unlocks the potential to do other stuff, but I very much envisaged that as a next thing for future work.