0

The following query takes 4 seconds to run from MySQL Workbench:

SELECT * FROM belgarath.match_result;

The following query from python takes 70 seconds:

session.query(MatchResult).all()

Are they the same thing? If yes, then why such a big difference?

Edit:

Should mention that I have Don't Limit selected in Workbench.

Jossy
  • 589
  • 2
  • 12
  • 36
  • 1
    We can't tell without `MatchResult`. It could be that the ORM is having to do multiple queries to make each instance of the object – roganjosh Oct 13 '20 at 17:51
  • 2
    Having an ORM over a raw query will always add some overhead anyway because you're forcing Python to create objects for every search result, even if there are no other queries involved in creating those objects – roganjosh Oct 13 '20 at 17:52
  • It might also help to look at the logs for your application -- this should tell you what SQL queries are actually being made. – Hannele Oct 13 '20 at 17:53
  • @roganjosh Hey. I stripped `MatchResult` back to just the fields for the test. What info would be useful? – Jossy Oct 13 '20 at 17:54
  • @Jossy any relationship that is eagerly loaded springs to mind – roganjosh Oct 13 '20 at 17:54
  • @Hannele - this is a personal python project - a bit of logging but certainly not on the queries themselves. Or did you mean from MySQL? – Jossy Oct 13 '20 at 17:55
  • @roganjosh - killed off every other table relationship too :) – Jossy Oct 13 '20 at 17:56
  • @roganjosh - there are 1.4 million records so thinking about it, that will take ORM a while to create all those objects! – Jossy Oct 13 '20 at 17:57
  • It would be interesting to add the python `mysql` module to the test. `cursor.execute(...)`. In fact, adding a quick enumeration of the results in both the ORM and mysql - because you wouldn't select them without using them. – tdelaney Oct 13 '20 at 18:03
  • ORMs are seductive because you don't have to learn how SQL works. Except you still do. Now the job is to know how python works, know how the ORM generates SQL and know SQL. Performance gains are had by fine tuning SQL statements for the task at hand. There is nothing that says an ORM will do that well in your case. – tdelaney Oct 13 '20 at 18:10
  • 3
    Related: https://stackoverflow.com/questions/23185319/why-is-loading-sqlalchemy-objects-via-the-orm-5-8x-slower-than-rows-via-a-raw-my – Ilja Everilä Oct 13 '20 at 18:45
  • 1
    Take a look at the sql SQLAlchemy is generating: str(session.query(MatchResult)) – Alex Oct 13 '20 at 19:53

0 Answers0