Restricted LEFT JOIN

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  January 15, 2015 at 1:43 am

  #1770342

  Aaron N. Cutshall (1/14/2015)

  ---

  Chris' solution works well in environments with low to medium data volume, but in the very high data volume environment I have it's too slow. I'm considering other alternatives.

  Can you post up an Actual execution plan, preferably from a very high data volume environment?

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  January 15, 2015 at 1:46 am

  #1770344

  Aaron N. Cutshall (1/14/2015)

  ---

  Yet, not surprising when you consider that the parent table has 1.85M rows, the child table has 7.5M rows, and the validation table has 54K. The indexes were optimized to access the child table with the parent record known. The sub-query joins the child table with the validation table and then after that joins to the parent table. This forced a complete table scan join between the child and the validation tables without narrowing it down to the correct parent first. Combine that with the number of records needed from the parent table and you could understand why it took some time. It worked for sure, but the time involved was just too long for acceptable performance (at least 10 times longer).

  These are very modest row counts. Here, we're used to having results returned from a couple of hundred million rows in a primary table joined to several tables with 10-20 million rows, in a second or so.

  The sub-query joins the child table with the validation table and then after that joins to the parent table.

  Are you guessing, or have you established this from the execution plan?

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden