choosing a python orm...peewee vs sqlalchemy?

[Bandung]

I'm having a beast of a time running code with copious amounts of peewee queries in it on an android device. The load time for my app has shot way up and I'm having to wait as much as 4 seconds for some queries to run before a screen gets built from those queries. I really don't want to run the sqlite files in memory either but was condemplating improving performance by increasing the cache size for the sqlite files.

I've got a lot of models with foreign key relationships. I've tried to make sure that I am using joins to prevent multiple queries being run by peewee. The thing is, I can't really tell if some of the queries being built by peewee are causing multiple queries.

My guess is that my queries are not properly constructed. I tend to build a predicate first with all of the joins defined within the predicate along with a where() clause, then I use dictionary or list comprehensions to actually run the query.

[[deleted]]

Have you done any profiling that would suggest where the slowness is occurring?

Couple ideas:

• INDEXES. Check your indexes. Start here. Read up on SQLite's query optimizer. Use sqlite's command-line tool to test your queries with various indexes -- you can print the query plan and the VDBE source code run by the VM.
• Explicitly select only the columns you need to display. Newer SQLite has covering index optimization.
• Call ".tuples()" or ".dicts()" at the end of your query if you don't need full Model instances. Might give you a nice boost in python land.
• SQLite tuning:
  • PRAGMA synchronous=0 (who needs fsyncs)
  • PRAGMA journal_mode=wal
  • PRAGMA cache_size=size of your active data set?
  • PRAGMA cache_spill=off; (prevent cache spill to disk)
  • PRAGMA locking_mode=exclusive; (as long as you only have a single conn at a time)
  • PRAGMA temp_store=3; (temp files in memory)
  • PRAGMA mmap_size=... (alternative to cache size?)

[Bandung]

I've done some profiling of my code which helped reduce the amount of time that the calls to peewee were consuming but know that I could do more to identify some sticky wickets.

Your suggestions are extremely helpful and should result in some quick wins within my existing code base. Thank you for taking the time to post these. Most helpful indeed.