Hello everyone,
I nailed the problem with performance, it wasn't the 'big query' not
loading collections, but this one:
rsvs =
session.query(Reservation).filter(Reservation.project_id.in_(projids)).filter(Reservation.status
== 'pending').filter(Reservation.end_date > todaydt).all()
The problem with this is that it generates lots of single queries,
selecting Reservations one by one according to id (debug log below).
I have absolutely no idea why this happens instead of INNER JOIN on
NewHosts.
I have relations defined as follows:
newhosts_table = Table('newhosts',md,
Column('id',Integer,primary_key=True),
Column('ip',String),
Column('hostname',String),
Column('location',String),
Column('architecture_id',Integer,ForeignKey('architecture.id')),
Column('os_kind_id',Integer,ForeignKey('os_kind.id')),
Column('os_version_id',Integer,ForeignKey('os_version.id')),
Column('virtualization_id',Integer,ForeignKey('virtualization.id')),
Column('shareable',SLBoolean),
Column('shareable_between_projects',SLBoolean),
Column('cpu',String),
Column('ram',String),
Column('notes',String),
Column('physical_box',SLBoolean),
Column('project_id',Integer,ForeignKey('project.id')))
reservation_table = Table('reservation', md,
Column('id',Integer,primary_key=True),
Column('start_date',SLDate),
Column('end_date',SLDate),
Column('status', String),
Column('businessneed', String),
Column('notetohwrep',String),
Column('email_id',Integer,ForeignKey('email.id')),
Column('project_id',Integer,ForeignKey('project.id'))
)
reservation_newhosts_assoc_table = Table('reservation_newhosts', md,
Column('reservation_id',Integer,ForeignKey('reservation.id')),
Column('host_id',Integer,ForeignKey('newhosts.id'))
)
mapper(Reservation, reservation_table,
properties={'email':relation(Email,order_by=Email.id),
'project':relation(Project, order_by=Project.id),
'hosts':relation(Host,
secondary=reservation_hosts_assoc_table,backref='reservation'),
'newhosts':relation(NewHost,
secondary=reservation_newhosts_assoc_table,backref='reservationnh')}
)
mapper(NewHost, newhosts_table,
properties={
'architecture':relation(Architecture,order_by=Architecture.id,backref='newhosts'),
'os_kind':relation(OS_Kind,order_by=OS_Kind.id,backref='newhosts'),
'os_version':relation(OS_version,
order_by=OS_version.id,backref='newhosts'),
'virtualization':relation(Virtualization,order_by=Virtualization.id,
backref='newhosts'),
'project':relation(Project,order_by=Project.id, backref='newhosts'),
'reservations':relation(Reservation,secondary=reservation_newhosts_assoc_table,
backref='newhost_reservations')}
)
SQL:
INFO:sqlalchemy.engine.base.Engine.0x...584c:SELECT newhosts.id AS
newhosts_id, newhosts.ip AS newhosts_ip, newhosts.hostname AS
newhosts_hostname, newhosts.location AS newhosts_location,
newhosts.architectu
re_id AS newhosts_architecture_id, newhosts.os_kind_id AS
newhosts_os_kind_id, newhosts.os_version_id AS newhosts_os_version_id,
newhosts.virtualization_id AS newhosts_virtualization_id,
newhosts.shareable AS newhosts_shareable,
newhosts.shareable_between_projects AS
newhosts_shareable_between_projects, newhosts.cpu AS newhosts_cpu,
newhosts.ram AS newhosts_ram, newhosts.notes AS newhosts_notes,
newhosts.physic
al_box AS newhosts_physical_box, newhosts.project_id AS newhosts_project_id
FROM newhosts, reservation_newhosts
WHERE %(param_1)s = reservation_newhosts.reservation_id AND newhosts.id
= reservation_newhosts.host_id
INFO:sqlalchemy.engine.base.Engine.0x...584c:{'param_1': 902}
DEBUG:sqlalchemy.engine.base.Engine.0x...584c:Col ('newhosts_id',
'newhosts_ip', 'newhosts_hostname', 'newhosts_location',
'newhosts_architecture_id', 'newhosts_os_kind_id',
'newhosts_os_version_id', 'newhos
ts_virtualization_id', 'newhosts_shareable',
'newhosts_shareable_between_projects', 'newhosts_cpu', 'newhosts_ram',
'newhosts_notes', 'newhosts_physical_box', 'newhosts_project_id')
INFO:sqlalchemy.engine.base.Engine.0x...584c:SELECT reservation.id AS
reservation_id, reservation.start_date AS reservation_start_date,
reservation.end_date AS reservation_end_date, reservation.status AS res
ervation_status, reservation.businessneed AS reservation_businessneed,
reservation.notetohwrep AS reservation_notetohwrep, reservation.email_id
AS reservation_email_id, reservation.project_id AS reservation_
project_id
FROM reservation
WHERE reservation.id = %(id_1)s
LIMIT 1 OFFSET 0
INFO:sqlalchemy.engine.base.Engine.0x...584c:{'id_1': 903}
DEBUG:sqlalchemy.engine.base.Engine.0x...584c:Col ('reservation_id',
'reservation_start_date', 'reservation_end_date', 'reservation_status',
'reservation_businessneed', 'reservation_notetohwrep', 'reservatio
n_email_id', 'reservation_project_id')
DEBUG:sqlalchemy.engine.base.Engine.0x...584c:Row (903,
datetime.date(2009, 10, 28), datetime.date(2009, 11, 1), 'pending',
'#1', '1', 1, 13)
INFO:sqlalchemy.engine.base.Engine.0x...584c:SELECT newhosts.id AS
newhosts_id, newhosts.ip AS newhosts_ip, newhosts.hostname AS
newhosts_hostname, newhosts.location AS newhosts_location,
newhosts.architectu
re_id AS newhosts_architecture_id, newhosts.os_kind_id AS
newhosts_os_kind_id, newhosts.os_version_id AS newhosts_os_version_id,
newhosts.virtualization_id AS newhosts_virtualization_id,
newhosts.shareable AS newhosts_shareable,
newhosts.shareable_between_projects AS
newhosts_shareable_between_projects, newhosts.cpu AS newhosts_cpu,
newhosts.ram AS newhosts_ram, newhosts.notes AS newhosts_notes,
newhosts.physic
al_box AS newhosts_physical_box, newhosts.project_id AS newhosts_project_id
FROM newhosts, reservation_newhosts
WHERE %(param_1)s = reservation_newhosts.reservation_id AND newhosts.id
= reservation_newhosts.host_id
INFO:sqlalchemy.engine.base.Engine.0x...584c:{'param_1': 903}
DEBUG:sqlalchemy.engine.base.Engine.0x...584c:Col ('newhosts_id',
'newhosts_ip', 'newhosts_hostname', 'newhosts_location',
'newhosts_architecture_id', 'newhosts_os_kind_id',
'newhosts_os_version_id', 'newhos
ts_virtualization_id', 'newhosts_shareable',
'newhosts_shareable_between_projects', 'newhosts_cpu', 'newhosts_ram',
'newhosts_notes', 'newhosts_physical_box', 'newhosts_project_id')
INFO:sqlalchemy.engine.base.Engine.0x...584c:SELECT reservation.id AS
reservation_id, reservation.start_date AS reservation_start_date,
reservation.end_date AS reservation_end_date, reservation.status AS res
ervation_status, reservation.businessneed AS reservation_businessneed,
reservation.notetohwrep AS reservation_notetohwrep, reservation.email_id
AS reservation_email_id, reservation.project_id AS reservation_
project_id
FROM reservation
WHERE reservation.id = %(id_1)s
LIMIT 1 OFFSET 0
INFO:sqlalchemy.engine.base.Engine.0x...584c:{'id_1': 904}
DEBUG:sqlalchemy.engine.base.Engine.0x...584c:Col ('reservation_id',
'reservation_start_date', 'reservation_end_date', 'reservation_status',
'reservation_businessneed', 'reservation_notetohwrep', 'reservatio
n_email_id', 'reservation_project_id')
DEBUG:sqlalchemy.engine.base.Engine.0x...584c:Row (904,
datetime.date(2009, 10, 28), datetime.date(2009, 11, 1), 'pending',
'#58', '58', 58, 13)
INFO:sqlalchemy.engine.base.Engine.0x...584c:SELECT newhosts.id AS
newhosts_id, newhosts.ip AS newhosts_ip, newhosts.hostname AS
newhosts_hostname, newhosts.location AS newhosts_location,
newhosts.architectu
re_id AS newhosts_architecture_id, newhosts.os_kind_id AS
newhosts_os_kind_id, newhosts.os_version_id AS newhosts_os_version_id,
newhosts.virtualization_id AS newhosts_virtualization_id,
newhosts.shareable AS newhosts_shareable,
newhosts.shareable_between_projects AS
newhosts_shareable_between_projects, newhosts.cpu AS newhosts_cpu,
newhosts.ram AS newhosts_ram, newhosts.notes AS newhosts_notes,
newhosts.physic
al_box AS newhosts_physical_box, newhosts.project_id AS newhosts_project_id
FROM newhosts, reservation_newhosts
WHERE %(param_1)s = reservation_newhosts.reservation_id AND newhosts.id
= reservation_newhosts.host_id
INFO:sqlalchemy.engine.base.Engine.0x...584c:{'param_1': 904}
DEBUG:sqlalchemy.engine.base.Engine.0x...584c:Col ('newhosts_id',
'newhosts_ip', 'newhosts_hostname', 'newhosts_location',
'newhosts_architecture_id', 'newhosts_os_kind_id',
'newhosts_os_version_id', 'newhos
ts_virtualization_id', 'newhosts_shareable',
'newhosts_shareable_between_projects', 'newhosts_cpu', 'newhosts_ram',
'newhosts_notes', 'newhosts_physical_box', 'newhosts_project_id')
INFO:sqlalchemy.engine.base.Engine.0x...584c:SELECT reservation.id AS
reservation_id, reservation.start_date AS reservation_start_date,
reservation.end_date AS reservation_end_date, reservation.status AS res
ervation_status, reservation.businessneed AS reservation_businessneed,
reservation.notetohwrep AS reservation_notetohwrep, reservation.email_id
AS reservation_email_id, reservation.project_id AS reservation_
project_id
FROM reservation
WHERE reservation.id = %(id_1)s
LIMIT 1 OFFSET 0
INFO:sqlalchemy.engine.base.Engine.0x...584c:{'id_1': 905}
--~--~---------~--~----~------------~-------~--~----~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
