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 [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
-~----------~----~----~----~------~----~------~--~---
