Queries Across 13 Models - How to improve efficiency?

[Shuba]

Hi!

I have a web application whose main feature is to query 13 different database tables when the user performs a search. I'm performing the query like this: https://gist.github.com/bmare/8c11ba6efcb97ba14dc30bc260aebc6c , and I'm wondering if there is a better method. I like having all the instances because it makes it pretty when going on to render the web page with all this information. At the same time, every time I render the page there are 13 queries being performed. I've indexed the columns I'm searching so the queries run quickly, but I'm wondering if I can make this more efficient somehow. Any ideas would be appreciated.

[Simon King]

It looks like all your models share the same "id" value - is that right? If so, you ought to be able to load all of them in a single query, something like this (untested):

def getmodels(dbsession, id):

    models = [M1, M2, M3]

    conditions = [(M.id == id) for M in models]

    instances = dbsession.query(*models).filter(*conditions).first()

    instancedict = {i.__table__.name: i for i in instances}

Hope that helps,

Simon

On Thu, Jan 26, 2023 at 3:05 PM Shuba <brandon...@gmail.com> wrote:

Hi!

I have a web application whose main feature is to query 13 different database tables when the user performs a search. I'm performing the query like this: https://gist.github.com/bmare/8c11ba6efcb97ba14dc30bc260aebc6c , and I'm wondering if there is a better method. I like having all the instances because it makes it pretty when going on to render the web page with all this information. At the same time, every time I render the page there are 13 queries being performed. I've indexed the columns I'm searching so the queries run quickly, but I'm wondering if I can make this more efficient somehow. Any ideas would be appreciated.

--
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+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/f427181d-655b-4806-9f28-8cd0afb513efn%40googlegroups.com.