We've been finding the SA ORM to be a large component in our application execution time. Our application makes frequent queries, and most of them simply query whole rows from a single table with some filters, but no joins (therefore, very basic queries). We've found that an alarming amount of CPU time is being spent on three parts:
1. Query compilation. Even though the compiled_cache execution option exists, the way Query objects are commonly constructed makes compiled statements non-cacheable. For example, the statements created often have the argument values baked in, and in any case, compiled_cache is keyed by instance-equality statements, so unless we pass in the exact same statement, we have to compile again. We hacked around this by manually managing statement instances for some subset of our most common queries so that they can be looked up by compiled_cache. This resulted in ~2x improvement. 2. Instance mapping. For these same relatively simple queries that don't need fancy mapping but are run over tables with a lot of columns, it takes a long time to create the ORM instances from rows. We worked around this by adding our own MapperExtension with a populate_instance that runs much faster, but makes several assumptions (single-table, all columns, etc.). This resulted in ~2x improvement after applying 1. 3. ResultMetaData creation. Since our tables have a lot of columns, a significant amount of time is actually spent in creating processors that read data off the cursor. This work is sadly not reused in between queries. We hacked around this by building our own dialect execution_ctx_cls that caches ResultMetaData._keymap/keys for certain queries. This resulted in ~3x performance improvement after applying 1 and 2. All this feels like a lot of work, and we can't help but feel that we might be doing something wrong or missing something obvious. We're wondering if there are any tips or anything built in to SA that could help improve the performance situation. Thanks! -- Yang Zhang http://yz.mit.edu/ -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
