On Fri, Jun 30, 2017 at 12:44 PM, mvidalgarcia <marcovida...@gmail.com> wrote: > Hi, I'm performing a query to some data models but the ORM response time > looks much higher compared to the SQL one. I have a script to reproduce the > problem here. > Currently getting values like > >> sql time: 0.068972826004 >> orm time: 0.915776014328 > > > and I consider there's a notable overhead in the ORM time. > > Do you know how I can optimize this query to reduce the overhead? >
Your "sql time" measurement only includes the time it takes to send the query to the database. I don't think it includes the time that the database has to evaluate the query and return the rows. To measure that, you ought to take the SQL string that the query produces, execute that string outside of the ORM, and fetch all the rows back. You should do that first with a bare PG connection, then with SQLAlchemy Core, and compare those 2 times to the time you get with the ORM. It's also worth noting that your query returns a lot of rows. I haven't run your script, but it looks like you've got 254 Registrations, each with 88 RegistrationData items, so that'll be 22352 rows. For each of those rows, SA has to pick out the columns representing the primary keys of the Registration, RegistrationData, RegistrationFormFieldData and RegistrationFormItem classes, then check them to see if they correspond to objects that are already in the session. If they aren't, it has to construct those objects and link them together. Processing 22352 rows in 0.91 seconds doesn't seem bad to me. If you can reply with the bare PG and SA core timings, we may have a better idea of how to optimise your query. Simon -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.