0

I have SQLAlchemy models in my Flask application:

class User(db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    photos = db.relationship('Photo', lazy='joined')

class Photo(db.Model):
     __tablename__ = 'photos'
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
    photo = db.Column(db.String(255))

When i query user i get user with his photos automatically. But i noticed if user has a lot of photos, query u = User.query.get(1) become very slow. I do the same but manually with lazy='noload' option

u = User.query.get(1)
photos = Photo.query.filter_by(user_id == 1)

and on the same data it works faster in times. Where is problem, is sql join slow(don`t think so, because it start hang on 100-1kk photo objects, not so big data) or something wrong in the SQLAlchemy?

fedorshishi
  • 1,467
  • 1
  • 18
  • 34
  • I think [this](http://stackoverflow.com/questions/1145905/sqlalchemy-scan-huge-tables-using-orm) may be useful to you. – pazitos10 Dec 09 '15 at 19:09
  • 1
    i think its not my problem, my case - join or not, in topic what you mentioned dude just load records in loop, when i make, something like this(pseudocode) 'u.photos = photos;ujosonify(u)' it`s ok, works good. But anyway thank you! – fedorshishi Dec 10 '15 at 05:47

1 Answers1

1

From my experiences I suggest you to get familiar with SQLAlchemy Loading Relationships. Sometimes even if relationship functionality is easy, usefull in larger datasets is better to do not use it, or even execute plain text SQL. This will be better from performance point of view on larger data sets.

Robert
  • 192
  • 2
  • 10
MobilePro.pl
  • 331
  • 1
  • 8
  • Yes i get it. But it was surprise that join start hang on so small data and i have to use filter by foreign key. I think maybe something wrong with my code:/ thank you – fedorshishi Dec 10 '15 at 05:52