[sqlalchemy] Re: Many to many optimization
I actually tried to use query.instances, but it behaved quite oddly. I didn't debug or even echo the SQL calls yet, but it made accessing those instances very slow. The actual instances call was quick, but when accessing the objects from the resulting list it slowed down to crawl. I will recreate that situtation later and see where to slowdown is. I'll also give some more implementation details so that you can see if there just a stupid mistake i have made that makes SA slow. -- K On Apr 12, 6:17 pm, Michael Bayer <[EMAIL PROTECTED]> wrote: > On Apr 12, 2007, at 3:30 AM, Kaali wrote: > > > > > Thanks for the answers. > > > I implemented message loading with find_members() kind of method, as > > shown in the documentation link you gave, and it got twice as fast. > > But it's still nowhere near the speed without the ORM. > > i get the impression youre trying to do a partial eager load. any > query that you can execute by itself can be fed into the ORM and > turned into object-mapped results, including the (partial, > incomplete, whatever) fulfillment of whatever relationships you like, > using query.instances(). have you looked into that ? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [EMAIL PROTECTED] 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: Many to many optimization
Thanks for the answers.
I implemented message loading with find_members() kind of method, as
shown in the documentation link you gave, and it got twice as fast.
But it's still nowhere near the speed without the ORM. Makes me a bit
sad, as i really liked the ORM system. Maybe if i remove any automatic
relations and manually get them it would be faster.
Or maybe i should use ORM normally, but work without it on
bottlenecks.
--
K
On Apr 2, 8:20 pm, Michael Bayer <[EMAIL PROTECTED]> wrote:
> sqlalchemy relationships are currently only "fully" loading - meaning
> you cant directly filter on an instances collection attribute.
>
> however you can construct your own query based on the relationship
> and use that, and theres plenty of tools to make that easy.
>
> such as, if you set up a bi-directional relationship:
>
> class A(object):pass
> class B(object):pass
>
> mapper(B, b_table)
> mapper(A, a_table, properties={
> "b_collection" : relation(A, secondary=a_to_b_table,
> backref="a_collection")
>
> })
>
> if you load an instance of "A":
>
> mya = session.query(A).select(a.id=7)
>
> you can query the "B"s on an "A" via the backref:
>
> result = session.query(B).filter_by(a_collection=mya).filter
> (A.c.somecriterion=='foo').list()
>
> theres also some patterns for dealing with large collections at:
>
> http://www.sqlalchemy.org/docs/
> adv_datamapping.html#advdatamapping_properties_working
>
> On Apr 2, 2007, at 7:44 AM, Kaali wrote:
>
>
>
> > Can i use ORM many-to-many relations and filter SQL -side? If i can't,
> > can you give me an example on how i should use many-to-many relations
> > with filtering in SQLAlchemy?
>
> > --
> > K
>
> > On Apr 2, 1:36 pm, svilen <[EMAIL PROTECTED]> wrote:
> >>> After getting the results, i will filter them with Python filters,
> >>> as i don't know how to filter many-to-many queries directly.
>
> >>> Should i somehow make custom queries that handles many-to-many
> >>> relationships etc. or is there something else i'm missing that
> >>> makes the system slow? I have ran the bench with MySQL and
> >>> PostgreSQL engines, the result is the same. When running with a
> >>> profiler, at least ~90% of the time is taken by SQLAlchemy.
>
> >> one general suggestion, try to move the filtering on the sql side;
> >> thus u'll have less data transferred then instantiated then
> >> filtered - which is probably eating most of the time.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [EMAIL PROTECTED]
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] Designing a permission system
Hi, I'm designing a permission system to our messaging platform. The platform has different channels which contains messages. Every different user can have different permissions on a channel. The permissions are create, modify, delete. With these permissions, we can define that some users can create messages in a specific channel, and others can create, modify and delete them, etc. How would you design a database for this, and how would you implement it with SA? I'm thinking about making a many-to-many table which includes the permissions, but i have no idea on how to implement this in SA. Database schema: Channel = - Id (integer) - Title (unicode) - Messages (many-to-many) Channel_Permissions = - User Id (integer foreignkey) - Channel Id (integer foreignkey) - create (boolean) - modify (boolean) - delete (boolean) With simple many-to-many table, SA makes a Python list of the links. Is there a way to get these extra boolean variables somehow and still use the automatic list? Thanks and regards, K --~--~-~--~~~---~--~~ 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: Many to many optimization
Can i use ORM many-to-many relations and filter SQL -side? If i can't, can you give me an example on how i should use many-to-many relations with filtering in SQLAlchemy? -- K On Apr 2, 1:36 pm, svilen <[EMAIL PROTECTED]> wrote: > > After getting the results, i will filter them with Python filters, > > as i don't know how to filter many-to-many queries directly. > > > Should i somehow make custom queries that handles many-to-many > > relationships etc. or is there something else i'm missing that > > makes the system slow? I have ran the bench with MySQL and > > PostgreSQL engines, the result is the same. When running with a > > profiler, at least ~90% of the time is taken by SQLAlchemy. > > one general suggestion, try to move the filtering on the sql side; > thus u'll have less data transferred then instantiated then > filtered - which is probably eating most of the time. --~--~-~--~~~---~--~~ 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] Many to many optimization
Hi everyone,
I'm creating a web service using CherryPy + SQLAlchemy. So far, coding
has been a pleasure, but it seems that i have a bit of a performance
problem with my software. In short, the service is a messaging system,
where users can send, receive and browse messages in different
channels.
A single message can be inside multiple channels, so i use many-to-
many relationship to map the messages inside the channels. I use ORM
extensively.
I have been benchmarking the system with ApacheBench(-n 1000), and one
of the main bottlenecks is with getting messages from a channel. The
current system only handles about 10 requests per second with a
channel that has 51 messages in it.
The message table has two foreign columns pointing to two users, other
columns consists of strings(Unicode) and integers. Quite simple
tables.
The relationship is defined with the following table:
messages_channels_table = Table('messages_channels', metadata,
Column('message_id', Integer, ForeignKey('messages.id')),
Column('channel_id', Integer, ForeignKey('channels.id'))
)
And the ORM mapping is defined as:
mapper(Channel, channels_table,
properties={
'owner': relation(User),
'messages': relation(Message,
secondary=messages_channels_table,
backref='channels',
lazy=True, # False results in 5.4 requests per second
order_by=messages_table.c.creation_date
)
}
)
If i get all messages from the database without using ORM, the server
handles 40 requests per second (there are other database queries which
seems to be quite slow too):
s = message_table.select()
messages = s.execute() # No ORM
With ORM i use:
c = session.query(Channel).select(id=channel_id)
messages = c.messages
After getting the results, i will filter them with Python filters, as
i don't know how to filter many-to-many queries directly.
Should i somehow make custom queries that handles many-to-many
relationships etc. or is there something else i'm missing that makes
the system slow? I have ran the bench with MySQL and PostgreSQL
engines, the result is the same. When running with a profiler, at
least ~90% of the time is taken by SQLAlchemy.
Best regards,
K
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
