> I think we're on the same page. The general case is truly expensive:
> loading arbitrary child table rows requires lookups on the director
> table. We can certainly batch this, but again, if child table rows
> come in an arbitrary order, and don't have the one-to-many
> (object<->forcedsource) relationship, it's really expensive.

> and we'll just have a note in the usage guide for completely
> different data domains that, hey, sort your input by the director's
> key column or your ingest performance could be terrible.

	First, you can remove redundant queries by only querying once
for each objectId.  This can be done either by caching the results or by
pre-sorting the child table rows by objectId or by just pre-scanning the
child table rows and accumulating a list of distinct objectIds.  (The
partitioner should be allowed to do whatever is necessary to the input
data, including reading it multiple times; if we're streaming the data
in, it could be allowed to store a copy on disk.)

	Second, you can reduce the total number of queries by batching
multiple objectIds into the same query -- "SELECT ra, decl FROM
secondaryIndex WHERE objectId IN (..., ..., ...)" or its equivalent if
you're using a non-SQL index.

	As you say, for the LSST use cases with up to 1000 child table
entries per director table entry, this can reduce the number of queries
by several orders of magnitude.  But even for arbitrary inputs I think
you can still reduce the queries by one or two orders of magnitude.

-- 
Kian-Tat Lim, LSST Data Management, [log in to unmask]

########################################################################
Use REPLY-ALL to reply to list

To unsubscribe from the QSERV-L list, click the following link:
https://listserv.slac.stanford.edu/cgi-bin/wa?SUBED1=QSERV-L&A=1