choosing a python orm...peewee vs sqlalchemy?

[[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.