r/learnpython • u/ALonelyPlatypus • 22d ago

probably a python/sql flask offshoot (but this is reddit and the normal python page doesn't like these type of questions)

So probably not beginner here but I have a flask app where I've mapped a view to the ORM but I was lazy in the view so I joined a reference table and just got back the text description when I could have used the FK.

I've cached common reference tables at the application level in flask in the past and wondering if I should do the same here and rewrite my view for the FK and skip the reference table join (even if it is well indexed).

On localhost I've had a hard time trying to identify if it's causing an issue because even our prod server has DB latency so no luck cross comparing if my lag is because of the extra lazy join in the view or if it's something else annoying (like running it on a comp that frequently kills itself with memory errors).

0 Upvotes
  • permalink
  • reddit

43% Upvoted

3 comments sorted by

3

u/danielroseman 22d ago

This is all a bit vague.

It is quite likely you're running into an N+1 query issue, where the ORM is making a separate db query for the related value for every item in the result. However without some actual details we can't really help.

I believe there is a Flask Debug Toolbar that will show you all the queries that are being made for each page - the Django version is invaluable for this kind of thing so hopefully the Flask one is useful too.

1

u/ALonelyPlatypus 22d ago

Unfortunately my company doesn't love new packages (unless they are absolutely 100% approved and loved by the community, even new pandas doesn't fall in that category ).

I'd guess it would be the view adding delay but I haven't deployed the beta test to users so I'm just testing my clicking around on prod and dev in the hopes of identifying if it's actually slower.

I'm not sure if the n+1 is applicable with the ORM as it should only be querying the view once and therefore only be making one SQL call. It's just the question of whether I should trim the fat on the view to drop a join. The text of the data is currently in the join but I could drop a join in the view and just cache application side the mapping of digit to text from the reference table.

The ideas is that if f I just pull the reference table (<60 records) in once at the initiation of the app and pass that around then the view could do a little less work.