Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

unnecessary selects with relationships... #7

Closed
bastianh opened this issue Jun 18, 2015 · 3 comments

Comments

@bastianh
Copy link

commented Jun 18, 2015

It looks like marshmallow-sqlalchemy makes no use of lazy='joined' loaded objects?

I'm using a schema dump with many=True to create a list of all objects and I'm loading the only relationship (list of url's) joined to not produce a query for every row but creates a select anyways.

@bastianh

This comment has been minimized.

Copy link
Author

commented Jun 18, 2015

Ok.. apparently I was wrong .. if I use joined load it is creating a select for every row... if I disable the lazy load it does two selects for every row.
The additional query selects the whole table without a where clause... I'll try to create a simple example project

@bastianh

This comment has been minimized.

Copy link
Author

commented Jun 18, 2015

ok ... here is my example script:


from flask import Flask, jsonify
from flask.ext.sqlalchemy import SQLAlchemy, get_debug_queries
from marshmallow_sqlalchemy import ModelSchema

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/test.db'
app.config['SQLALCHEMY_ECHO'] = False
app.config['SQLALCHEMY_RECORD_QUERIES'] = True
db = SQLAlchemy(app)


class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True)
    email = db.Column(db.String(120), unique=True)

    def __repr__(self):
        return '<User %r>' % self.username

class URL(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey(User.id))
    url = db.Column(db.String())

    user = db.relationship(User, backref=db.backref('urls'))

    def __repr__(self):
        return '<URL %r>' % self.url

def create_user():
    user1 = User(username='user1', email="user1@example.com")
    user1.urls.append(URL(url="http://a"))
    user1.urls.append(URL(url="http://b"))
    user2 = User(username='user2', email="user2@example.com")
    user2.urls.append(URL(url="http://c"))
    user2.urls.append(URL(url="http://d"))

    db.session.add(user1)
    db.session.add(user2)
    db.session.commit()

class UserSchema(ModelSchema):
    class Meta:
        model = User
        sqla_session = db.session

user_schema = UserSchema()

@app.route("/")
def hello():
    users = User.query.all()
    response = jsonify(user=user_schema.dump(users, many=True))
    for query in get_debug_queries():
        print("%s %r\n------" % (query.statement, query.parameters))
    return response

if __name__ == "__main__":
    db.drop_all()
    db.create_all()
    create_user()
    app.run()

the executed statements for the hello_world endpoint:

SELECT user.id AS user_id, user.username AS user_username, user.email AS user_email FROM user ()
------
SELECT "URL".id AS "URL_id", "URL".user_id AS "URL_user_id", "URL".url AS "URL_url" FROM "URL" WHERE ? = "URL".user_id (1,)
------
SELECT "URL".id AS "URL_id", "URL".user_id AS "URL_user_id", "URL".url AS "URL_url" FROM "URL" ()
------
SELECT "URL".id AS "URL_id", "URL".user_id AS "URL_user_id", "URL".url AS "URL_url" FROM "URL" WHERE ? = "URL".user_id (2,)
------
SELECT "URL".id AS "URL_id", "URL".user_id AS "URL_user_id", "URL".url AS "URL_url" FROM "URL" ()

as you can see there are 2 duplicate selects that don't make sense in my eyes...

@bastianh bastianh changed the title lazy loading of relationships unnecessary selects with relationships... Jun 18, 2015

@hartror

This comment has been minimized.

Copy link

commented Jun 18, 2015

I am seeing this too. It looks like this line is the source of the .all() query: https://github.com/marshmallow-code/marshmallow-sqlalchemy/blob/dev/marshmallow_sqlalchemy/convert.py#L180

jmcarp added a commit to jmcarp/marshmallow-sqlalchemy that referenced this issue Jun 21, 2015

Model related rows with `Related` field.
The current implementation of relationship fields based on `QuerySelect`
and `QuerySelectList` fields makes many potentially expensive queries.
This patch adds a SQLAlchemy-specific field, `Related`, and uses it for
relationships.

[Resolves marshmallow-code#7]
@jmcarp jmcarp referenced this issue Jun 21, 2015

@sloria sloria closed this in #8 Jun 29, 2015

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
2 participants
You can’t perform that action at this time.