Yes, I'm using polymorphic mappers. It's actually only 1 row - querying the object by id. I have lazy=false for any referenced objects to the one I'm querying for.
On Jun 26, 12:16 pm, Michael Bayer <mike...@zzzcomputing.com> wrote: > You have a tremendous amount of LEFT OUTER JOINS in there, and its hard to > tell but it seems like you're doing lots of "with_polymorphic" queries as > well as "lazy='joined'" styles of relationships, making for a very cumbersome > query. That it returns the first result quickly (that's your 47 msec) but > takes a lot longer to actually return rows (thats your .15 sec) suggests it > returns a lot of rows. I'd turn off all the "with_polymorphic" stuff, as > well as not using any "lazy='joined'" for collections, "lazy='subquery'" is a > better choice for collections but even then, usually better as a query time > option rather than a fixed mapping configuration. > > On Jun 26, 2011, at 10:25 AM, nospam wrote: > > > > > > > I'm seeing a simple get taking a considerable amount of time. > > > a = session.query(Annotation).get(annotation.id) > > > This line can take anywhere between 0.15 secs to all the way up to 1.5 > > secs ... The query sqlalchemy produces is below. If I execute the > > query in pgadmin, it consistently runs in 47 msecs. > > > Any ideas? > > > It seems to run longer if I issue the 'get' right after an update and > > another select query. If I wait a little bit (5 secs), then it seems > > to run closer to the 0.15 secs. > > > SELECT (SELECT users.username > > FROM users > > WHERE annotations.user_id = users.id) AS created_by, annotations.id AS > > annotations_id, annotations.version AS annotations_version, > > annotations.message AS annotations_message, annotations.image_filename > > AS annotations_image_filename, annotations.user_id AS > > annotations_user_id, annotations.camera_id AS annotations_camera_id, > > annotations.game_id AS annotations_game_id, annotations.item_id AS > > annotations_item_id, annotations.creation_datetime AS > > annotations_creation_datetime, annotations.modified_datetime AS > > annotations_modified_datetime, annotations.camera_datetime AS > > annotations_persistent, anon_1.items_id AS anon_1_items_id, > > anon_1.items_type AS anon_1_items_type, anon_1.items_user_id AS > > anon_1_items_user_id, anon_1.items_game_id AS anon_1_items_game_id, > > anon_1.people_item_id AS anon_1_people_item_id, > > anon_1.vehicles_item_id AS anon_1_vehicles_item_id, > > anon_1.articles_item_id AS anon_1_articles_item_id, > > anon_1.relationships_item_id AS anon_1_relationships_item_id, > > anon_1.relationships_lower_item_id AS > > anon_1_relationships_lower_item_id, > > anon_1.relationships_higher_item_id AS > > anon_1_relationships_higher_item_id, anon_2.attributes_id AS > > anon_2_attributes_id, anon_2.attributes_name AS > > anon_2_attributes_name, anon_2.attributes_type AS > > anon_2_attributes_type, anon_2.attributes_annotation_id AS > > anon_2_attributes_annotation_id, anon_2.attributes_suspicion_level AS > > anon_2_attributes_suspicion_level, > > anon_2.vehicle_attributes_attributes_id AS > > anon_2_vehicle_attributes_attributes_id, > > anon_2.vehicle_attributes_vehicle_make AS > > anon_2_vehicle_attributes_vehicle_model, > > anon_2.relationship_attributes_attributes_id AS > > anon_2_relationship_attributes_attributes_id, > > anon_2.relationship_attributes_action AS > > anon_2_relationship_attributes_action, > > anon_2.police_report_attributes_attributes_id AS > > anon_2_police_report_attributes_attributes_id, > > anon_2.police_report_attributes_first_name AS > > anon_2_article_attributes_attributes_id, > > anon_2.article_attributes_stationary AS > > anon_2.person_attributes_attributes_id AS > > anon_2_person_attributes_attributes_id, > > anon_2.person_attributes_eye_color AS > > anon_2_person_attributes_eye_color, > > anon_2.person_attributes_hair_color AS > > anon_2_person_attributes_hair_color, anon_2.person_attributes_gender > > AS anon_2_person_attributes_gender, anon_2.person_attributes_height AS > > anon_2_person_attributes_height, anon_2.person_attributes_age AS > > anon_2_person_attributes_age, anon_2.person_attributes_build AS > > anon_2_person_attributes_build, anon_2.person_attributes_luggage AS > > anon_2_person_attributes_luggage, anon_2.person_attributes_mobility AS > > anon_2_person_attributes_mobility, > > anon_2.flight_checkin_attributes_attributes_id AS > > anon_2_flight_checkin_attributes_attributes_id, > > anon_2.flight_checkin_attributes_airline AS > > anon_2_flight_checkin_attributes_start_datetime_of_flight, > > anon_2.flight_checkin_attributes_end_datetime_of_flight AS > > anon_2_flight_checkin_attributes_end_datetime_of_flight, > > anon_2.flight_checkin_attributes_first_name AS > > anon_2_flight_checkin_attributes_first_name, > > anon_2.flight_checkin_attributes_last_name AS > > anon_2_lpr_attributes_attributes_id, > > anon_2.lpr_attributes_start_datetime AS > > anon_2_lpr_attributes_start_datetime, > > anon_2.lpr_attributes_end_datetime AS > > anon_2_lpr_attributes_end_datetime, > > anon_2.lpr_attributes_license_plate AS > > anon_2_lpr_attributes_license_plate, anon_2.lpr_attributes_action AS > > anon_2_lpr_attributes_action, anon_2.lpr_attributes_station AS > > anon_2_lpr_attributes_station, > > anon_2.gate_boarding_attributes_attributes_id AS > > anon_2_gate_boarding_attributes_attributes_id, > > anon_2.gate_boarding_attributes_airline AS > > anon_2_rmv_attributes_attributes_id, anon_2.rmv_attributes_first_name > > AS anon_2_rmv_attributes_first_name, anon_2.rmv_attributes_last_name > > AS anon_2_rmv_attributes_last_name, anon_2.rmv_attributes_address AS > > anon_2_rmv_attributes_address, anon_2.rmv_attributes_min_height_feet > > AS anon_2.rmv_attributes_min_age AS anon_2_rmv_attributes_min_age, > > anon_2.rmv_attributes_max_age AS anon_2_rmv_attributes_max_age, > > anon_2.rmv_attributes_eye_color AS anon_2_rmv_attributes_eye_color, > > anon_2.rmv_attributes_gender AS anon_2_rmv_attributes_gender, > > anon_2.rmv_attributes_license_plate AS > > anon_2_rmv_attributes_license_plate, > > anon_2.rmv_attributes_vehicle_color AS > > anon_2_rmv_attributes_vehicle_color, > > anon_2.rmv_attributes_vehicle_model AS > > anon_2_rmv_attributes_vehicle_model, > > anon_2.security_checkpoint_attributes_attributes_id AS > > anon_2_security_checkpoint_attributes_attributes_id, > > anon_2.security_checkpoint_attributes_end_datetime AS > > anon_2_security_checkpoint_attributes_end_datetime, > > anon_2.security_checkpoint_attributes_id_type AS > > anon_2_security_checkpoint_attributes_id_type > > FROM annotations LEFT OUTER JOIN (SELECT items.id AS items_id, > > items.type AS items_type, items.user_id AS items_user_id, > > items.game_id AS items_game_id, people.item_id AS people_item_id, > > vehicles.item_id AS vehicles_item_id, articles.item_id AS > > articles_item_id, relationships.item_id AS relationships_item_id, > > relationships.lower_item_id AS relationships_lower_item_id, > > relationships.higher_item_id AS relationships_higher_item_id > > FROM items LEFT OUTER JOIN people ON items.id = people.item_id LEFT > > OUTER JOIN vehicles ON items.id = vehicles.item_id LEFT OUTER JOIN > > articles ON items.id = articles.item_id LEFT OUTER JOIN relationships > > ON items.id = relationships.item_id) AS anon_1 ON anon_1.items_id = > > annotations.item_id LEFT OUTER JOIN (SELECT attributes.id AS > > attributes_id, attributes.name AS attributes_name, attributes.type AS > > attributes_type, attributes.annotation_id AS attributes_annotation_id, > > attributes.suspicion_level AS attributes_suspicion_level, > > vehicle_attributes.attributes_id AS vehicle_attributes_attributes_id, > > vehicle_attributes.vehicle_make AS vehicle_attributes_vehicle_make, > > vehicle_attributes.vehicle_type AS vehicle_attributes_vehicle_type, > > vehicle_attributes.vehicle_model AS vehicle_attributes_vehicle_model, > > relationship_attributes.attributes_id AS > > relationship_attributes_attributes_id, relationship_attributes.action > > AS relationship_attributes_action, > > police_report_attributes.attributes_id AS > > police_report_attributes_attributes_id, > > police_report_attributes.first_name AS > > police_report_attributes_first_name, > > police_report_attributes.last_name AS > > police_report_attributes_last_name, > > police_report_attributes.license_plate AS > > police_report_attributes_license_plate, > > police_report_attributes.location AS > > police_report_attributes_location, police_report_attributes.summary AS > > police_report_attributes_summary, > > police_report_attributes.start_datetime AS > > police_report_attributes_start_datetime, > > police_report_attributes.end_datetime AS > > police_report_attributes_end_datetime, > > article_attributes.attributes_id AS article_attributes_attributes_id, > > article_attributes.stationary AS article_attributes_stationary, > > article_attributes.article_type AS article_attributes_article_type, > > article_attributes.article_size AS article_attributes_article_size, > > article_attributes.article_color AS article_attributes_article_color, > > article_attributes.other AS article_attributes_other, > > person_attributes.attributes_id AS person_attributes_attributes_id, > > person_attributes.eye_color AS person_attributes_eye_color, > > person_attributes.luggage AS person_attributes_luggage, > > person_attributes.mobility AS person_attributes_mobility, > > flight_checkin_attributes.attributes_id AS > > flight_checkin_attributes_attributes_id, > > flight_checkin_attributes.airline AS > > flight_checkin_attributes_airline, > > flight_checkin_attributes.start_datetime_of_checkin AS > > flight_checkin_attributes.gate_number AS > > flight_checkin_attributes_gate_number, > > flight_checkin_attributes.destination AS > > flight_checkin_attributes_destination, lpr_attributes.attributes_id AS > > lpr_attributes_attributes_id, lpr_attributes.start_datetime AS > > lpr_attributes_start_datetime, lpr_attributes.end_datetime AS > > lpr_attributes_end_datetime, lpr_attributes.license_plate AS > > lpr_attributes_license_plate, lpr_attributes.action AS > > lpr_attributes_action, lpr_attributes.station AS > > lpr_attributes_station, gate_boarding_attributes.attributes_id AS > > gate_boarding_attributes_attributes_id, > > gate_boarding_attributes.airline AS gate_boarding_attributes_airline, > > gate_boarding_attributes.gate AS gate_boarding_attributes_gate, > > gate_boarding_attributes.flight_number AS > > gate_boarding_attributes_flight_number, > > gate_boarding_attributes.first_name AS > > ... > > read more »- Hide quoted text - > > - Show quoted text - -- 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.
