When you load all those objects, saying query(A, B, C, D).from_statement(huge statement), what you're not doing is loading collections on individual objects, and depending on what your mappings look like you may not be loading enough information for many-to-one associations to occur either. Even though the ultimate objects may be present in the session, the fact that they're attached to collections or attributes is not assembled by a straight load - collection and attribute loads, either lazy or eager, are the only way those get filled in.
A many-to-one association will load without using any SQL if the join condition from parent to child represents a simple find by primary key on the root table of the child object - otherwise it needs to reissue a SELECT. A collection when not yet loaded will always need to issue a SELECT. So if you'd like your huge query to populate collections or non-simple many-to-one relations during the "big load", you should look into using contains_eager() so that the rows returned by your from_statement are applied to those elements. For many-to-one relations, its possible that some tuning to the relation() can allow them to load without any SQL, you'd have to illustrate some here. Also there is limitless value to boiling your huge amount of code down to simpler test cases. Try a SELECT that loads two tables in a simple fashion, and load two objects at the same time. Look at the SQL logs and experiment with contains_eager() to get a feel for whats going on. Marcin Krol wrote: > > Hello everyone, > > Basically, I have some performance issues. Here's a somewhat complicated > story: > > I have a Host class with several relations to OS_family, OS_version, > OS_release, Project, Reservation, etc. > > The entire (sanitized) SQL log is too big to be posted here, so I copied > it into pastebin: > > http://pastebin.com/f45ea6b6c > > The search process is as follows: > > 1. A complicated query with multiple joins on those relations is ran at > the beginning of the search (query below). Now, that query and SQLA > processing takes under a second on 400 rows of Hosts. So it's not bad > overall. > > Lines in pastebin: 1-466 > > Hosts are collected into a list and passed on to Mako for rendering > HTML. Mako template accesses fields and relations like OS_version in > Host objects. > > While rendering HTML remaining steps happen in SQL log: > > 2. Several tables are read in for drop-down lists. > > Lines in pastebin: 467-608 > > 3. Lots of small queries happen filling in details of some objects. > Those small queries take at least several seconds! > > Lines in pastebin: 609-1087 > > Those queries look like: > > INFO:sqlalchemy.engine.base.Engine.0x...e66c:SELECT os_version.id AS > os_version_id, os_version.os_version AS os_version_os_version > FROM os_version > WHERE os_version.id = %(param_1)s > INFO:sqlalchemy.engine.base.Engine.0x...e66c:{'param_1': 15} > DEBUG:sqlalchemy.engine.base.Engine.0x...e66c:Col ('os_version_id', > 'os_version_os_version') > DEBUG:sqlalchemy.engine.base.Engine.0x...e66c:Row (15, 'SLED') > INFO:sqlalchemy.engine.base.Engine.0x...e66c:SELECT os_version.id AS > os_version_id, os_version.os_version AS os_version_os_version > FROM os_version > WHERE os_version.id = %(param_1)s > INFO:sqlalchemy.engine.base.Engine.0x...e66c:{'param_1': 30} > > I don't get it - all the os_version objects should have been read in > before? > > What I do not get is why after this query takes place, SQLA runs a lot > those small queries - I included all (most?) the necessary columns in > the big initial query, so Host data should be filled in by SQLA eager > loading (which is default except for self-referential objects according > to docs, and I have no self-referential objects here)? > > Is this result of subqueries in the big query? Smth else? > > > > Here's code just in case someone is patient enough to skim it: > > The big query function: > > ######################################################## > def retval(req, session, columnlist, field, ip, hostname, location, > architecture, os_kind, os_version, os_rel, os_update, kernel_bits, > physical_box, available, avtime, additional_info, email, virt, cpu, ram, > project, upnrunning, direction, hostsel=None, testfordates=True): > > if debug: > logger.debug('start_retval') > > qtmpl = """ > SELECT h.id AS hosts_id, h.ip AS hosts_ip, h.ip2 AS hosts_ip2, > h.hostname AS hosts_hostname, h.location AS hosts_location, > h.architecture_id AS hosts_architecture_id, h.os_kind_id AS > hosts_os_kind_id, h.os_version_id AS hosts_os_version_id, h.os_update AS > hosts_os_update, h.kernel_bits AS hosts_kernel_bits, h.additional_info > AS hosts_additional_info, h.column_12 AS hosts_column_12, h.column_13 AS > hosts_column_13, h.username AS hosts_username, h.password AS > hosts_password, h.alias AS hosts_alias, h.virtualization_id AS > hosts_virtualization_id, h.shareable AS hosts_shareable, > h.shareable_between_projects AS hosts_shareable_between_projects, > h.notes AS hosts_notes, h.cpu AS hosts_cpu, h.ram AS hosts_ram, > h.column_24 AS hosts_column_24, h.batch AS hosts_batch, h.asset AS > hosts_asset, h.owner AS hosts_owner, h.ssh_key_present AS > hosts_ssh_key_present, h.machine_type_model AS hosts_machine_type_model, > h.mac_address_eth_0 AS hosts_mac_address_eth_0, h.physical_box AS > hosts_physical_box, h.up_n_running AS hosts_up_n_running, h.available AS > hosts_available, h.project_id AS hosts_project_id, architecture.id AS > architecture_id, architecture.architecture AS architecture_architecture, > os_kind.id AS os_kind_id, os_kind.os_kind AS os_kind_os_kind, > os_version.id AS os_version_id, os_version.os_version AS > os_version_os_version, os_rel.id AS os_rel_id, os_rel.os_rel AS > os_rel_os_rel, virtualization.id AS virtualization_id, > virtualization.virtualization AS virtualization_virtualization, > virtualization.color AS virtualization_color, project.id AS project_id, > project.project AS project_project, email.id AS email_id, email.email AS > email_email, cr.reservation_id AS reservation_id, > cr.reservation_start_date AS reservation_start_date, > cr.reservation_end_date AS reservation_end_date, cr.reservation_status > AS reservation_status, cr.reservation_businessneed AS > reservation_businessneed, cr.reservation_notetohwrep AS > reservation_notetohwrep, cr.reservation_email_id AS > reservation_email_id, cr.reservation_project_id AS reservation_project_id > > > FROM hosts h > > LEFT OUTER JOIN > ( > SELECT h.id AS host_id, h.ip AS host_ip, r.id AS reservation_id, > r.start_date AS reservation_start_date, r.end_date AS > reservation_end_date, r.status AS reservation_status, r.businessneed AS > reservation_businessneed, r.notetohwrep AS reservation_notetohwrep, > r.email_id AS reservation_email_id, r.project_id AS > reservation_project_id FROM hosts h > LEFT OUTER JOIN reservation_hosts rh ON rh.host_id = h.id > LEFT OUTER JOIN reservation r ON r.id = rh.reservation_id > > INNER JOIN > ( > SELECT > h.id as host_id, MIN(r.start_date) AS min_start_date > FROM > hosts h > LEFT OUTER JOIN reservation_hosts rh ON rh.host_id = h.id > LEFT OUTER JOIN reservation r ON r.id = rh.reservation_id AND > r.start_date >= '%(today)s'::date > GROUP BY h.id > ) AS mindate(host_id, min_start_date) > ON mindate.host_id = h.id AND (mindate.min_start_date = r.start_date OR > mindate IS NULL) > ) AS cr(host_id, host_ip, reservation_id, reservation_start_date, > reservation_end_date, reservation_status, reservation_businessneed, > reservation_notetohwrep, reservation_email_id, reservation_project_id) > ON h.id = cr.host_id > > > INNER JOIN architecture ON architecture.id = h.architecture_id > %(architecture)s > INNER JOIN os_kind ON h.os_kind_id = os_kind.id %(os_kind)s > INNER JOIN os_version ON h.os_version_id = os_version.id %(os_version)s > INNER JOIN os_rel ON h.os_rel_id = os_rel.id %(os_rel)s > INNER JOIN virtualization ON h.virtualization_id = virtualization.id > %(virt)s > INNER JOIN project ON h.project_id = project.id %(project)s > %(emailjoin)s > > WHERE %(upnrunning)s > h.id NOT IN > ( > SELECT m.host_id > FROM reservation r, reservation_hosts m > WHERE r.id = m.reservation_id > AND (r.start_date <= '%(today)s'::date AND r.end_date > > '%(avtime)s'::date) > ) > > %(hostsel)s %(ip)s %(hostname)s %(location)s %(os_update)s > %(physical_box)s %(additional_info)s %(cpu)s %(ram)s > > ORDER BY %(field)s ip ASC""" > > if ip != '': > ip = """ AND h.ip LIKE '%%%s%%' """ % ip > if hostname !='': > hostname = """ AND lower(h.hostname) LIKE '%%%s%%' """ % > hostname.lower() > if location != '': > location = """ AND lower(h.location) LIKE '%%%s%%' """ % > location.lower() > if architecture != '': > architecture = """ AND architecture.architecture = '%s' """ % > architecture > if os_kind != '': > os_kind = """ AND os_kind.os_kind = '%s' """ % os_kind > if os_version != '': > os_version = """ AND os_version.os_version = '%s' """ % > os_version > if os_rel != '': > os_rel = """ AND os_rel.os_rel = '%s' """ % os_rel > if os_update != '': > os_update = """ AND h.os_update = '%s' """ % os_update > if physical_box != '': > physical_box = """ AND h.physical_box = %s """ % > str(physical_box) > if additional_info != '': > additional_info = """ AND lower(h.additional_info) LIKE > '%%%s%%' """ % > additional_info.lower() > if project != '': > project = """ AND project.project = '%s' """ % project > if email != '': > emailjoin = """ INNER JOIN email ON cr.reservation_email_id = > email.id > AND lower(email.email) LIKE '%%%s%%' """ % email.lower() > else: > emailjoin = """ LEFT OUTER JOIN email ON > cr.reservation_email_id = > email.id """ > if virt != '': > virt = """ AND virtualization.virtualization = '%s' """ % virt > if cpu != '': > cpu = """ AND lower(h.cpu) LIKE '%%%s%%' """ % cpu.lower() > if ram != '': > ram = """ AND h.ram LIKE '%%%s%%' """ % ram > if field != '': > if direction == 'ascending': > field = " %s ASC, " % field > elif direction == 'descending': > field = " %s DESC, " % field > else: > field = '' > if hostsel is not None: > hs = " AND h.id IN (" > for h in hostsel: > hs += "%d, " % h > hs = hs.rstrip(", ") > hs += ") " > hostsel = hs > else: > hostsel = '' > today = datetime.date.today().strftime("%Y-%m-%d") > nextyear = plusdays(datetime.date.today(), 365).strftime("%Y-%m-%d") > if available or avtime == 0: > avtime = plusdays(datetime.date.today(), > avtime).strftime("%Y-%m-%d") > else: > avtime = plusdays(datetime.date.today(), > 3650).strftime("%Y-%m-%d") > if httpses.get('hwrep',False) == False: > upnrunning = 'h.up_n_running = True AND h.shareable = True AND' > elif upnrunning == True: > upnrunning = 'h.up_n_running = True AND h.shareable = True AND' > elif upnrunning == False: > upnrunning = 'h.up_n_running = False AND' > else: > upnrunning = 'h.shareable = True AND' > > qdict = {'ip': ip, 'hostname': hostname, 'location': location, > 'architecture': architecture, 'os_kind': os_kind, 'os_version': > os_version, 'os_rel':os_rel, 'os_update': os_update, 'physical_box': > physical_box, 'available': available, 'avtime': avtime, > 'additional_info': additional_info, 'emailjoin': emailjoin, 'virt': > virt, 'cpu': cpu, 'ram': ram, 'project': project, 'direction': > direction, 'field': field, 'hostsel': hostsel, 'today': today, > 'nextyear':nextyear, 'upnrunning':upnrunning} > > q = qtmpl % qdict > > #qhtml = q.replace('\n', '<br>') > #req.write(qhtml) > > if debug: > logger.debug('start_sqla_query') > > selectexpr = session.query(Host, Architecture, OS_Kind, OS_version, > OS_rel, Virtualization, Project, Email, > Reservation).from_statement(q).all() > > if debug: > logger.debug('end_sqla_query') > > if debug: > logger.debug('start_formatting_outlist') > > outlist = [] > #req.write(str(columnlist)) > # ['ip', 'hostname', 'location', 'architecture', 'os_kind', > 'os_version', 'os_rel', 'os_update', 'physical_box', 'up_n_running', > 'available', 'additional_info', 'email', 'column_12', 'column_13', > 'username', 'password', 'alias', 'virtualization', 'shareable', > 'shareable_between_projects', 'notes', 'cpu', 'ram', 'column_24', > 'batch', 'asset', 'owner', 'ssh_key_present', 'machine_type_model', > 'mac_address_eth_0', 'id', 'project'] > for host, arch, osk, osv, osr, virt, project, em, rsv in selectexpr: > > outlist.append((host, em, rsv)) > > > if debug: > logger.debug('end_formatting_outlist') > logger.debug('end_retval') > return outlist > > > ######################################################## > > > The code utilizing big query function: > > ######################################################## > > def viewstatus(req, columnlist, page, ip, hostname, location, > architecture, os_kind, os_version, os_rel, os_update, kernel_bits, > physical_box, available, avtime, additional_info, email, virt, cpu, ram, > project, field, upnrunning, direction): > global httpses, adminmail > if debug: > logger.debug('start_viewstatus') > # list columns > # cl = [] > # for (idx, x) in enumerate(columnlist): > # cl.append(x + ' %d' % idx) > # req.write('<br>'.join(cl)) > > #req.write("<br>".join(dir(req))) > durl = createurl(req, page, ip, hostname, location, architecture, > os_kind, os_version, os_rel, os_update, kernel_bits, physical_box, > available, avtime, additional_info, email, virt, cpu, ram, project) > content = retval(req, session, columnlist, field, ip, hostname, > location, architecture, os_kind, os_version, os_rel, os_update, > kernel_bits, physical_box, available, avtime, additional_info, email, > virt, cpu, ram, project, upnrunning, direction) > > > architectures = [ x.architecture for x in > session.query(Architecture).order_by(Architecture.architecture).all() ] > if u'' not in architectures: > architectures.insert(0, u'') > os_kinds = [ x.os_kind for x in > session.query(OS_Kind).order_by(OS_Kind.os_kind).all() ] > if u'' not in os_kinds: > os_kinds.insert(0, u'') > os_versions = [ x.os_version for x in > session.query(OS_version).order_by(OS_version.os_version).all() ] > if u'' not in os_versions: > os_versions.insert(0, u'') > physical_boxes = [ u'', True, False ] > avtimes = [ 1, 7, 30, 3650 ] > dateselectors = [ u'', 'eq', 'lt', 'gt', 'dif' ] > dateselectorsvalues = [ u'', '=', '<', '>', '!=' ] > virtualizationcolors=dict() > for x in > session.query(Virtualization).order_by(Virtualization.virtualization).all(): > virtualizationcolors[x.virtualization] = x.color > virtualizations = [ x.virtualization for x in > session.query(Virtualization).order_by(Virtualization.virtualization).all() > ] > if u'' not in virtualizations: > virtualizations.insert(0, u'') > os_rels = [ x.os_rel for x in > session.query(OS_rel).order_by(OS_rel.os_rel).all() ] > if u'' not in os_rels: > os_rels.insert(0, u'') > projects = [ x.project for x in > session.query(Project).order_by(Project.project).all() ] > upnrunnings = [ u'', True, False ] > # if u'' not in projects: > # projects.insert(0, u'') > hostsel = [] > # try: > # hostsel=httpses['ids'] > # except KeyError: > # pass > if req.form.has_key('submit_search') or > req.form.has_key('submit_addtocart'): > for f in req.form: > if f.startswith('host_'): > hostsel.append(int(f.split('_')[1])) > httpses['backtostatusurl']=durl > httpses.save() > kwargs={ 'selectedpage':page, 'hosts':content, 'cl':columnlist, > 'archs':architectures, 'os_kind':os_kinds, 'os_version':os_versions, > 'os_rel':os_rels, 'physical_box':physical_boxes, 'avtime':avtimes, > 'virtualization':virtualizations, > 'virtualizationcolor':virtualizationcolors, 'locolor':'', > 'recno':len(content), 'dirurl':durl, 'selectedip':ip, > 'selectedhostname':hostname, 'selectedlocation':location, > 'selectedarch':architecture, 'selectedosk':os_kind, > 'selectedosv':os_version, 'selectedosr': os_rel, > 'selectedosupd':os_update, 'selectedpb':physical_box, > 'selectedav':available, 'selectedavtime': avtime, > 'selectedadditionalinfo':additional_info, 'selectedemail':email, > 'dateselectors':dateselectors, > 'dateselectorsvalues':dateselectorsvalues, > 'selectedvirtualization':virt, 'selectedcpu':cpu, 'selectedram':ram, > 'project':projects, 'selectedproject':project, 'datecolor':'', > 'hostsel':hostsel, 'adminmail':adminmail, > 'selectedupnrunning':upnrunning, 'upnrunning':upnrunnings } > if httpses.has_key('cn'): > kwargs['cn'] = httpses['cn'] > else: > kwargs['cn'] = '' > if httpses.has_key('hwrep'): > kwargs['hwrep']=httpses['hwrep'] > else: > kwargs['hwrep']=False > > if kwargs['hwrep']: > projids = httpses['hwrepprojectids'] > todaydt = datetime.date.today() > rsvs = > session.query(Reservation).filter(Reservation.project_id.in_(projids)).filter(Reservation.status > == 'pending').filter(Reservation.end_date > todaydt).all() > for r in rsvs: > if len(r.hosts) == 0 and len(r.newhosts) == 0: > rsvs.remove(r) > kwargs['pendingrsvno'] = len(rsvs) > useredits = > session.query(DelayedOp).filter(DelayedOp.project_id.in_(projids)).filter(DelayedOp.opid > == 1).all() > kwargs['usereditno'] = len(useredits) > > if httpses.has_key('hwrepspecial') and (httpses['hwrepspecial'] is > True): > kwargs['hwrepspecial'] = True > else: > kwargs['hwrepspecial'] = False > > if debug: > logger.debug('start_render_html_viewstatus') > renderhtml(req,'viewstatus.mako',**kwargs) > if debug: > logger.debug('end_viewstatus') > return apache.OK > ######################################################## > > > > > ############### Big query ################### > > SELECT h.id AS hosts_id, h.ip AS hosts_ip, h.ip2 AS hosts_ip2, > h.hostname AS hosts_hostname, h.location AS hosts_location, > h.architecture_id AS hosts_architecture_id, h.os_kind_id AS > hosts_os_kind_id, h.os_version_id AS hosts_os_version_id, h.os_update AS > hosts_os_update, h.kernel_bits AS hosts_kernel_bits, h.additional_info > AS hosts_additional_info, h.column_12 AS hosts_column_12, h.column_13 AS > hosts_column_13, h.username AS hosts_username, h.password AS > hosts_password, h.alias AS hosts_alias, h.virtualization_id AS > hosts_virtualization_id, h.shareable AS hosts_shareable, > h.shareable_between_projects AS hosts_shareable_between_projects, > h.notes AS hosts_notes, h.cpu AS hosts_cpu, h.ram AS hosts_ram, > h.column_24 AS hosts_column_24, h.batch AS hosts_batch, h.asset AS > hosts_asset, h.owner AS hosts_owner, h.ssh_key_present AS > hosts_ssh_key_present, h.machine_type_model AS hosts_machine_type_model, > h.mac_address_eth_0 AS hosts_mac_address_eth_0, h.physical_box AS > hosts_physical_box, h.up_n_running AS hosts_up_n_running, h.available AS > hosts_available, h.project_id AS hosts_project_id, architecture.id AS > architecture_id, architecture.architecture AS architecture_architecture, > os_kind.id AS os_kind_id, os_kind.os_kind AS os_kind_os_kind, > os_version.id AS os_version_id, os_version.os_version AS > os_version_os_version, os_rel.id AS os_rel_id, os_rel.os_rel AS > os_rel_os_rel, virtualization.id AS virtualization_id, > virtualization.virtualization AS virtualization_virtualization, > virtualization.color AS virtualization_color, project.id AS project_id, > project.project AS project_project, email.id AS email_id, email.email AS > email_email, cr.reservation_id AS reservation_id, > cr.reservation_start_date AS reservation_start_date, > cr.reservation_end_date AS reservation_end_date, cr.reservation_status > AS reservation_status, cr.reservation_businessneed AS > reservation_businessneed, cr.reservation_notetohwrep AS > reservation_notetohwrep, cr.reservation_email_id AS > reservation_email_id, cr.reservation_project_id AS reservation_project_id > > > FROM hosts h > > LEFT OUTER JOIN > ( > SELECT h.id AS host_id, h.ip AS host_ip, r.id AS reservation_id, > r.start_date AS reservation_start_date, r.end_date AS > reservation_end_date, r.status AS reservation_status, r.businessneed AS > reservation_businessneed, r.notetohwrep AS reservation_notetohwrep, > r.email_id AS reservation_email_id, r.project_id AS > reservation_project_id FROM hosts h > LEFT OUTER JOIN reservation_hosts rh ON rh.host_id = h.id > LEFT OUTER JOIN reservation r ON r.id = rh.reservation_id > > INNER JOIN > ( > SELECT > h.id as host_id, MIN(r.start_date) AS min_start_date > FROM > hosts h > LEFT OUTER JOIN reservation_hosts rh ON rh.host_id = h.id > LEFT OUTER JOIN reservation r ON r.id = rh.reservation_id AND > r.start_date >= '2009-05-25'::date > GROUP BY h.id > ) AS mindate(host_id, min_start_date) > ON mindate.host_id = h.id AND (mindate.min_start_date = r.start_date OR > mindate IS NULL) > ) AS cr(host_id, host_ip, reservation_id, reservation_start_date, > reservation_end_date, reservation_status, reservation_businessneed, > reservation_notetohwrep, reservation_email_id, reservation_project_id) > ON h.id = cr.host_id > > > INNER JOIN architecture ON architecture.id = h.architecture_id > INNER JOIN os_kind ON h.os_kind_id = os_kind.id > INNER JOIN os_version ON h.os_version_id = os_version.id > INNER JOIN os_rel ON h.os_rel_id = os_rel.id > INNER JOIN virtualization ON h.virtualization_id = virtualization.id > INNER JOIN project ON h.project_id = project.id AND project.project = > 'LMT' > LEFT OUTER JOIN email ON cr.reservation_email_id = email.id > > WHERE h.shareable = True AND > h.id NOT IN > ( > SELECT m.host_id > FROM reservation r, reservation_hosts m > WHERE r.id = m.reservation_id > AND (r.start_date <= '2009-05-25'::date AND r.end_date > > '2009-05-26'::date) > ) > > ORDER BY virtualization DESC, ip ASC > > ############### Big query ################### > > > > > > --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
