Fetching a single row and producing objects, even if a chain of eagerly loaded many-to-ones, is a relatively cheap operation, and it still sounds like network overhead of rows is the main problem. If any columns fetch large binary objects or large amounts textual data, that can take a long time over the network as well. The high degree in variability of time spent suggests network overhead as well rather than CPU time.
Also, I'd ensure eager loads aren't loading large collections, take off unnecessary eager loads, with_polymorphic settings, use innerjoin=True for many-to-one eagerloads where the foreign key is NOT NULL. Hope this helps. On Jun 26, 2011, at 11:13 PM, nospam wrote: > 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. > -- 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.
