title: SQL Server Date Range Query Performance
body: <p>I am working with an Azure SQL Database.</p>
  
  <p>I have a problem where I have to join to where the matching value is based on a date falling within a date range. I have two tables which have a parent/child relationship. The parent is <code>TBL_BOX</code>, the child is <code>TBL_ITEM</code> where the join is on <code>BOX_ID</code>. There is a log of the status of the BOX_ID in the table <code>TBL_BOX_STATUS</code> where there is a start date of <code>EFF_DATETIME</code> and an end date of <code>EXP_DATETIME</code>. The <code>TBL_ITEM</code> table has a log table of the values the the ITEM_IDs map to. This table also has a start date of <code>EFF_DATETIME</code> and an end date of <code>EXP_DATETIME</code>.</p>
  
  <p>I have to be able to define a <code>@QUERY_DATE</code> of any DATETIME and be able to return the the boxes that did not have a status of 'DELETED' and the <code>ITEM_VALUE</code> that the <code>ITEM_ID</code> was mapped to at that time. I have provided a query for generating test data.</p>
  
  <pre><code>CREATE TABLE #NUMBERS 
  (
      N INT
  );
  
  INSERT INTO #NUMBERS
  SELECT TOP (1000000) 
      n = CONVERT(INT, ROW_NUMBER() OVER (ORDER BY s1.[object_id]))
  FROM sys.all_objects AS s1 
  CROSS JOIN sys.all_objects AS s2
  OPTION (MAXDOP 1);
  
  CREATE UNIQUE CLUSTERED INDEX N ON #NUMBERS(N);
  
  CREATE TABLE #TBL_BOX 
  (
      BOX_ID INT IDENTITY(1,1) PRIMARY KEY,
      BOX_ALT_ID NVARCHAR(50)
  );
  
  INSERT INTO #TBL_BOX (BOX_ALT_ID)
  SELECT
      N
  FROM #NUMBERS
  WHERE 
      N &lt;= 10000;
  
  CREATE TABLE #TBL_ITEM
  (
      ITEM_ID INT IDENTITY(1,1) PRIMARY KEY,
      ITEM_ALT_ID NVARCHAR(50),
      BOX_ID INT
  );
  
  INSERT INTO #TBL_ITEM (ITEM_ALT_ID, BOX_ID)
  SELECT
      N AS ITEM_ALT_ID,
      CEILING(CAST(N AS FLOAT) / 50.0) AS BOX_ID
  FROM #NUMBERS
  WHERE 
      CEILING(CAST(N AS FLOAT) / 50.0) &lt;= 10000;
  
  CREATE TABLE #TBL_BOX_STATUS
  (
      BOX_STATUS_ID INT IDENTITY(1,1) PRIMARY KEY,
      BOX_ID INT,
      BOX_STATUS NVARCHAR(50),
      EFF_DATETIME DATETIME,
      EXP_DATETIME DATETIME
  );
  
  INSERT INTO #TBL_BOX_STATUS (BOX_ID, BOX_STATUS, EFF_DATETIME, EXP_DATETIME)
  SELECT
  BOX_ID,
  'CREATED',
  '2016-01-01',
  '2016-01-02'
  FROM #TBL_BOX;
  
  INSERT INTO #TBL_BOX_STATUS (BOX_ID, BOX_STATUS, EFF_DATETIME, EXP_DATETIME)
  SELECT
  BOX_ID,
  'READY',
  '2016-01-02',
  '9999-12-31'
  FROM #TBL_BOX
  WHERE
      BOX_ID &lt;= 9000;
  
  INSERT INTO #TBL_BOX_STATUS (BOX_ID, BOX_STATUS, EFF_DATETIME, EXP_DATETIME)
  SELECT
  BOX_ID,
  'DELETED',
  '2016-01-02',
  '9999-12-31'
  FROM #TBL_BOX
  WHERE
      BOX_ID &gt; 9000;
  
  CREATE TABLE #TBL_ITEM_VALUE
  (
      ITEM_VALUE_ID INT IDENTITY(1,1) PRIMARY KEY,
      ITEM_ID INT,
      ITEM_VALUE NVARCHAR(50),
      EFF_DATETIME DATETIME,
      EXP_DATETIME DATETIME
  );
  
  INSERT INTO #TBL_ITEM_VALUE (ITEM_ID, ITEM_VALUE, EFF_DATETIME, EXP_DATETIME)
  SELECT
  ITEM_ID,
  'A',
  '2016-01-01',
  '2016-01-02'
  FROM #TBL_ITEM
  WHERE ITEM_ID &lt;= 1000;
  
  INSERT INTO #TBL_ITEM_VALUE (ITEM_ID, ITEM_VALUE, EFF_DATETIME, EXP_DATETIME)
  SELECT
  ITEM_ID,
  'B',
  '2016-01-02',
  '9999-12-31'
  FROM #TBL_ITEM
  WHERE ITEM_ID &lt;= 1000;
  
  INSERT INTO #TBL_ITEM_VALUE (ITEM_ID, ITEM_VALUE, EFF_DATETIME, EXP_DATETIME)
  SELECT
  ITEM_ID,
  'A',
  '2016-01-01',
  '9999-12-31'
  FROM #TBL_ITEM
  WHERE ITEM_ID &gt; 1000;
  
  CREATE NONCLUSTERED INDEX NCIDX_TBL_ITEM_VALUE ON #TBL_ITEM_VALUE (ITEM_ID, EFF_DATETIME, EXP_DATETIME) INCLUDE (ITEM_VALUE);
  CREATE NONCLUSTERED INDEX NCIDX_TBL_BOX_STATUS ON #TBL_BOX_STATUS (BOX_ID, EFF_DATETIME, EXP_DATETIME) INCLUDE (BOX_STATUS);
  CREATE NONCLUSTERED INDEX NCIDX_TBL_ITEM_BY_ITEM ON #TBL_ITEM (ITEM_ID, BOX_ID);
  CREATE NONCLUSTERED INDEX NCIDX_TBL_ITEM_BY_BOX ON #TBL_ITEM (BOX_ID, ITEM_ID);
  </code></pre>
  
  <p>And here is the query that I need to be fast. I am looking for what would be the best indexing scheme for this or a better way to compose this query. I am also open to refactoring these tables. I can modify them if necessary. The project I am working on requires extensive logging of the change in values and being able to query the database with a <code>@QUERY_DATE AS DATETIME</code> parameter which defines the moment in time we are wanting to observe the state. The use of beginning and end date columns is the best thing I have come across but I am open to different schemes if it provides better performance. And now the query:</p>
  
  <pre><code>DECLARE @QUERY_DATE AS DATETIME = GETDATE();
  SELECT
  BX.BOX_ID,
  ITM.ITEM_ID,
  IV.ITEM_VALUE
  FROM #TBL_BOX AS BX
  JOIN #TBL_BOX_STATUS AS BS 
      ON BX.BOX_ID = BS.BOX_ID AND BS.EFF_DATETIME &lt;= @QUERY_DATE AND BS.EXP_DATETIME &gt; @QUERY_DATE
  JOIN #TBL_ITEM AS ITM
      ON BS.BOX_ID = ITM.BOX_ID
  JOIN #TBL_ITEM_VALUE AS IV
      ON ITM.ITEM_ID = IV.ITEM_ID AND IV.EFF_DATETIME &lt;= @QUERY_DATE AND IV.EXP_DATETIME &gt; @QUERY_DATE
  WHERE
      BS.BOX_STATUS != 'DELETED'
  </code></pre>
  
  <p><strong>Update 2016-06-05</strong>
  I would like to constrain the complexity of this problem. When querying the state tables there is only ever a single Date value being considered. I do not need the full complexity of interval queries. I only ever have a single date being considered and there is only ever one match. For a given BOX_ID or ITEM_ID there is only one BOX_STATUS or ITEM_VALUE that matches. Date ranges do not overlap for a given BOX_ID or ITEM_ID. I don't know if that enables a better solution than what I have here but I wanted to constrain the scope.</p>