python - How can I optimize DAL web2py query? -

result = db((db.company.location.belongs(locations)) &             (db.company.emp_id.belongs(employee_ids)) &             (db.company.type.belongs(types))).select() 

locations list of location ids

employee_ids list of employee ids

types = ['general', 'office', 'e-commerce']

this query return 60,000 records , takes 1 minute complete. how can optimize or split it?

the reason why dal query takes ~1 minute because return of .select() method rows instance, contains 60,000 row objects. many instantiations of classes consists in time , memory consuming operation. solution, could:

• split query in pieces of x, using limit by parameter: .select(limitby=(0, x)) or;
• use executesql db().executesql(select * from...) construct query using sql. return tuples, theres isn't row or rows instantiations , faster, have downside of not being able enjoy benefits of row object;

if of above solved problem, , have time consuming operation, try use split solution along threads.