sqlalchemy backref slow

Question

Hi have have the following tables

nfiletable = Table(
    'NFILE', base.metadata,
    Column('fileid', Integer, primary_key=True),
    Column('path', String(300)),
    Column('filename', String(50)),
    Column('filesize', Integer),
    schema='NATIVEFILES')#,autoload=True,autoload_with=engine)

sheetnames_table=Table(
    'SHEETNAMES', base.metadata, schema='NATIVEFILES', 
    autoload=True, autoload_with=engine)


nfile_sheet_table=Table(
    'NFILE_SHEETNAME',base.metadata,
    Column('fileid', Integer, ForeignKey(nfiletable.c.fileid)), 
    Column('sheetid', Integer, ForeignKey(sheetnames_table.c.sheet_id)),
    schema='NATIVEFILES')

and mappers:

nfile_mapper=mapper(Nfile,nfiletable)

mapper(Sheet, sheetnames_table, properties={
    'files': relation(
        Nfile, secondary=nfile_sheet_table,
        primaryjoin=(sheetnames_table.c.sheet_id==nfile_sheet_table.c.sheetid),
        secondaryjoin=(nfile_sheet_table.c.fileid==nfiletable.c.fileid),
        foreign_keys=[nfile_sheet_table.c.sheetid,nfile_sheet_table.c.fileid],
        backref='sheets')
})

when i do the following

upl = Session.query(Nfile).filter_by(fileid=k).one()
sheetdb=[]
for sheet in sheetstoadd:
     s = sheetcache[sheetname]
     sheetdb.append(s)
upl.sheets = sheetdb
Session.save(upl)
Session.flush()

the line upl.sheets = sheetdb takes forever. It seems that all files for each sheet in sheetdb are loaded from the db. Anyway to prevent this. Thnks

Answer 1

if NFile.sheets references a huge collection, put "lazy='dynamic'" on the backref:

mapper(Sheet, sheetnames_table, properties={
    'files': relation(
        Nfile, secondary=nfile_sheet_table,
        backref=backref('sheets', lazy='dynamic'))
})

All the primaryjoin/secondaryjoin/foreign_keys stuff is also not needed since your nfile_sheet_table has ForeignKey constructs on it.