StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POWhat are the best practices when joining tables against ids from an external system?
text
Body
copied!<p>Our group frequently has situations where we need to join our tables against identifiers that come from outside of the database, e.g. downloading data with matching associations found in a public data repository.</p> <p>I know of basic two approaches. The one we most commonly use involves batching IN clauses. We have a utility for transparently wrapping long IN statements in separate queries when we are joining against >1000 items.</p> <p>With 5 items and batch limit of 4 the query :</p> <pre class="lang-sql prettyprint-override"><code>SELECT foo.id, foo.data FROM foo WHERE foo.id IN ($MANY) </code></pre> <p>becomes:</p> <pre class="lang-sql prettyprint-override"><code>SELECT foo.id, foo.data FROM foo WHERE foo.id IN (?,?,?,?) SELECT foo.id, foo.data FROM foo WHERE foo.id IN (?) </code></pre> <p>This method works but seems quite kludgy. </p> <p>The alternative sometimes employed involves creating temporary tables, inserting the values, and joining against the normal tables. This solution seems a bit more standard with respect to the final query as you're simply joining as you would if the data were in your database. However, the temporary table creation doesn't seem to be something that can done in an ANSI SQL compliant way.</p> <p>Performance seems to be about equal with the IN method winning favor with few external values as expected.</p> <p>What is the best practice for solving this problem in an ANSI standard way?</p> <p>Edit: With respect to performance measures, we were benchmarking the application code. This included the overhead of inserting into the temp tables. Likewise, for the IN clauses it included the overhead of batching them. </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload