the Query.all() call only generates a single SQL statement at all
times. Its only when you access attributes on individual rows that a
second statement would occur. If the multiple queries truly occur
within the scope of the all() call, I'd check to see if you have a
@reconstructor or __new__ of some kind that may be causing this.
On May 27, 2009, at 10:19 AM, Marcin Krol wrote:
>
> 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
-~----------~----~----~----~------~----~------~--~---
