Forum Archive

I am a young player (beginner). Just wanted timeshare a trap I feel into the other night.
I had a SQLite database of 72,000 people. All fake names, created using faker.
Anyway, the primary key was called id. Wanted to show the records in a ui.Table. So the idea was just to get a list of the ids from the database. Then as each row issued a draw request , use the id, recover the record and draw the row. Pretty straightforward.
So I wanted was this a reasonable idea. The only bottle neck really should be retrieving a list of the ids.
It was slow like hell. I played with different row_factory types, try tweaking PRAGMA's etc.
I had done one stupid mistake. I was printing the result of my sql query. Was just a list of 72,000 integers. Well that takes a lot to prepare and print to the console. I don't believe or say this is a Pythonista thing. It's just a reality about consoles.
Once I removed the print, it was instant. I know there are all sorts of timing/profiling methods out there. Sometimes you just get sucked in.
I should have know better, been recently following msg threads about console writing. But still fell into this trap of not being methodical and scientific in my conclusions

Yeah, print can slow down your program quite a bit on Pythonista. I don't think it's as bad when you run it on a computer in a normal terminal window - when you use anything GUI-based as standard output, it is going to be slower than a simple terminal. IDLE can be slow at printing too, and it especially doesn't like long lines, kind of like the Pythonista editor.

@dgelessus , yeah. My idea was just print anything anywhere, can make you go looking for problems that don't exist. I just made that SQLite table 250,000 entries. It's still instant. It's quite impressive.

This code returns a list of 250,000 integers instantly from a database,

```
def get_all_ids(self):
# return a list of all ids(pk) for this table
sql = 'select id from {}'.format(self.tbl_name)

    with sqlite.connect(self.db_name) as cnn:
        cur = cnn.execute(sql)
        return [id[0] for id in cur]```

@Phuket2 Actually there is something you can do to make this even faster (and more memory-efficient). Use a generator comprehension instead of a list comprehension, i. e. (id[0] for id in cur) with round instead of square brackets. This only works when you want to iterate over self.get_all_ids() with a for loop (if you need random access like self.get_all_ids()[42] then you still need a list), but it means that the list isn't generated all at once. Instead, every time the for loop goes over self.get_all_ids(), it gets the next id[0]. This is faster than with a list, and if you were to do this with e. g. strings instead of numbers then a generator expression is also much more memory-efficient, because only one entry needs to be kept in memory at a time, and not all of them.

@dgelessus , yes I know what you mean with a generator. But as you pointed out, if you need a random index then you need a list, one way or another.
So I did need a list of integers. But I experimented with different row_factory's. In the case of getting a list of id's, this seems to be the fastest. The default row factory, returns Tuples. Elsewhere I use SQLite3.Row. Is very optimized for memory and speed and functionality. But again depends on context. When retrieving a db record by id, if using row_factory = SQLite.Row, it returns a sqliteRow object. But the object is re-used many times over. It's doing a lot of tricks under the hood. It's nice.
I still know little about Python. But with my ignorance, I would say a SQLite in memory database could provide some very efficient solutions to some medium data problems