[sqlalchemy] Re: Unnecessary selects when cascade=all, delete-orphane and composite keys
I am building a grading system for students and got unexpected performance problems. I am using composite key for marks, which refer to students and subjects. When I am creating a mark (for student_1 and subject_1), unnecessary select operations are performed (select all marks for student_1 and select all marks for subject_1). Why these selects are generated and how to avoid them? The SQL issue looks like the marks collections on Student and Subject issuing a lazyload for their full collection of Mark items before the backref event appends the Mark object to each of them, i.e. the event that occurs when you issue mark.student = subject_1. Ordinary collections currently don't handle being present in a partial state, so in order for an append to occur, they load their contents. As a workaround, you can use lazy=dynamic relations for the collections, which is a special relation that can handle append operations without the full collection being available. lazy=noload would work as well but then you couldn't read from your collections. A future release may look into merging some of the dynamic relation behavior into an ordinary un-loaded collection so that this workaround would not be needed. Actually this might not be a bad idea for 0.4.2, so ive added ticket #871. Thanks a lot for your reply! Setting the relation to be lazy=dynamic really eliminated unnecessary selects when creating Mark. Making a default relation a bit dynamic is a great idea! There is problem when relation is both lazy=dynamic and cascade=all, delete-orphan: When parent (Subject or Student) object is deleted, its children (Marks) are not deleted. When relation is not dynamic, children are deleted correctly. Is this a proper behavior? Thanks for the attention. P.S. Congratulations to developers and users of SA with version 0.4.1! --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Unnecessary selects when cascade=all, delete-orphane and composite keys
On Nov 19, 2007, at 6:22 AM, Anton V. Belyaev wrote: Setting the relation to be lazy=dynamic really eliminated unnecessary selects when creating Mark. Making a default relation a bit dynamic is a great idea! There is problem when relation is both lazy=dynamic and cascade=all, delete-orphan: When parent (Subject or Student) object is deleted, its children (Marks) are not deleted. When relation is not dynamic, children are deleted correctly. Is this a proper behavior? Thanks for the attention. yes, this is the caveat of the dynamic relation; on the read side, all its doing is issuing a query to the database. Therefore, any data which hasn't been flushed will not show up at that end. For this reason, the dynamic relation was intended to produce a smoother experience in conjunction with a session that is autoflush=True, so that whenever you read from the relation, a flush occurs first and you get the latest state back from the database. So you can see that the new behavior ive proposed in trac would produce more specifically the behavior you're looking for in this case. I hope you can wait for it ! - mike --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Unnecessary selects when cascade=all, delete-orphane and composite keys
On Nov 18, 2007, at 4:54 PM, Anton V. Belyaev wrote: Hello all, I am building a grading system for students and got unexpected performance problems. I am using composite key for marks, which refer to students and subjects. When I am creating a mark (for student_1 and subject_1), unnecessary select operations are performed (select all marks for student_1 and select all marks for subject_1). Why these selects are generated and how to avoid them? The SQL issue looks like the marks collections on Student and Subject issuing a lazyload for their full collection of Mark items before the backref event appends the Mark object to each of them, i.e. the event that occurs when you issue mark.student = subject_1. Ordinary collections currently don't handle being present in a partial state, so in order for an append to occur, they load their contents. As a workaround, you can use lazy=dynamic relations for the collections, which is a special relation that can handle append operations without the full collection being available. lazy=noload would work as well but then you couldn't read from your collections. A future release may look into merging some of the dynamic relation behavior into an ordinary un-loaded collection so that this workaround would not be needed. Actually this might not be a bad idea for 0.4.2, so ive added ticket #871. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
