title: Highly dispair execution times when changing comparision value on query
body: <p>I have a problem with execution times on a query that is baffling me.
  I know a few ways to solve the problem and get better and acceptable execution times but still don't have a clue on WHY the problem occurs.</p>
  
  <h2>Sample Tables</h2>
  
  <p>We have TWO tables, related by a foreign key.</p>
  
  <p><strong>Table1</strong></p>
  
  <pre><code>| Id | IdTable2 |
  |:--:|:--------:|
  |  1 |     4    |
  |  2 |     7    |
  |  3 |     8    |
  |  4 |     6    |
  |  5 |     4    |
  |  6 |     1    |
  |  7 |     1    |
  |  8 |     6    |
  |  9 |     7    |
  | 10 |     1    |
  </code></pre>
  
  <p><strong>Table2</strong></p>
  
  <pre><code>| Id | ValueField |
  |:--:|:----------:|
  |  1 |      0     |
  |  2 |      0     |
  |  3 |      0     |
  |  4 |      1     |
  |  5 |      0     |
  |  6 |      1     |
  |  7 |      0     |
  </code></pre>
  
  <h2>Query</h2>
  
  <pre><code>SELECT * FROM Table1 WHERE IdTable2 IN (SELECT Id FROM Table2 WHERE ValueField = ?);
  </code></pre>
  
  <p>Where <code>?</code> can be <code>0</code> or <code>1</code></p>
  
  <h2>Real Data Count</h2>
  
  <p>The above tables are just a sample to simplify, but the real row counts of this tables is as follows:</p>
  
  <ul>
  <li>Table1: <strong>60420</strong> rows</li>
  <li><p>Table2: <strong>62</strong> rows</p></li>
  <li><p>Table2 with ValueField <code>0</code>: <strong>51</strong> rows</p></li>
  <li><p>Table2 with ValueField <code>1</code>: <strong>11</strong> rows </p></li>
  <li><p>Table1 with IdTable2 with ValueField <code>0</code>: <strong>599</strong> rows</p></li>
  <li>Table1 with IdTable2 with ValueField <code>1</code>: <strong>59821</strong> rows</li>
  </ul>
  
  <h2>The Issue</h2>
  
  <pre><code>SELECT * FROM Table1 WHERE IdTable2 IN (SELECT Id FROM Table2 WHERE ValueField = 0);
  -- Execution time LOW/INSTANT
  SELECT * FROM Table1 WHERE IdTable2 IN (SELECT Id FROM Table2 WHERE ValueField = 1);
  -- Execution time HIGH
  </code></pre>
  
  <p>Well, first of all I think that the subquery was the struggle, but if the subquery is the problem, different values would not execute in so dispair times, so I guess  that probably the amount of data retrieved was the problem, so I try this:</p>
  
  <pre><code>SELECT * FROM Table1 WHERE IdTable2 IN (1,2,3,5,7); -- Equivalent of ValueField 0
  -- Execution time LOW/INSTANT
  SELECT * FROM Table1 WHERE IdTable2 IN (4,6); -- Equivalent of ValueField 1
  -- Execution time LOW/INSTANT
  </code></pre>
  
  <p>Well... the data retrieved isn't it either, let's try something else:</p>
  
  <pre><code>SELECT * FROM Table1 WHERE IdTable2 IN (SELECT Id FROM Table2 WHERE ValueField = 0);
  -- Execution time LOW/INSTANT
  SELECT * FROM Table1 WHERE IdTable2 NOT IN (SELECT Id FROM Table2 WHERE ValueField = 0);
  -- Execution time LOW/INSTANT
  </code></pre>
  
  <p>What happen if I reverse it?</p>
  
  <pre><code>SELECT * FROM Table1 WHERE IdTable2 NOT IN (SELECT Id FROM Table2 WHERE ValueField = 1);
  -- Execution time LOW/INSTANT
  SELECT * FROM Table1 WHERE IdTable2 IN (SELECT Id FROM Table2 WHERE ValueField = 0);
  -- Execution time LOW/INSTANT
  </code></pre>
  
  <p>Hummm.... that's pretty much tells me that the problem isn't on subquery and either on the data, but Why comparision with <code>ValueField = 1</code> AND using <code>IN</code> is causing the problem and none of the alternatives can replicate the HIGH execution times?</p>
  
  <h2>Execution Plans</h2>
  
  <p>For SQL IN ValueField <code>1</code>:</p>
  
  <pre><code>SELECT * FROM Incidencias WHERE EstadoWorkflow in (SELECT IdEstadoWorkflow FROM EstadosWorkflows WHERE Final = 1);
  </code></pre>
  
  <p><a href="http://s000.tinyupload.com/index.php?file_id=19036217708532467879" rel="nofollow">http://s000.tinyupload.com/index.php?file_id=19036217708532467879</a></p>
  
  <p>For SQL IN ValueField <code>0</code>:</p>
  
  <pre><code>SELECT * FROM Incidencias WHERE EstadoWorkflow in (SELECT IdEstadoWorkflow FROM EstadosWorkflows WHERE Final = 0);
  </code></pre>
  
  <p><a href="http://s000.tinyupload.com/index.php?file_id=49593927895920014301" rel="nofollow">http://s000.tinyupload.com/index.php?file_id=49593927895920014301</a></p>
  
  <p>For SQL NOT IN ValueField <code>0</code>:</p>
  
  <pre><code>SELECT * FROM Incidencias WHERE EstadoWorkflow not in (SELECT IdEstadoWorkflow FROM EstadosWorkflows WHERE Final = 0);
  </code></pre>
  
  <p><a href="http://s000.tinyupload.com/index.php?file_id=03901091628843565847" rel="nofollow">http://s000.tinyupload.com/index.php?file_id=03901091628843565847</a></p>
  
  <p>For SQL NOT IN ValueField <code>1</code>:</p>
  
  <pre><code>SELECT * FROM Incidencias WHERE EstadoWorkflow not in (SELECT IdEstadoWorkflow FROM EstadosWorkflows WHERE Final = 1);
  </code></pre>
  
  <p><a href="http://s000.tinyupload.com/index.php?file_id=69996775965382534356" rel="nofollow">http://s000.tinyupload.com/index.php?file_id=69996775965382534356</a></p>
  
  <p>The queries are just the same I post in the example but with other names, this is the dictionary of equivalences of sample query vs real query.</p>
  
  <ul>
  <li><strong>Table1</strong>: Incidencias</li>
  <li><strong>Table2</strong>: EstadosWorkflows</li>
  <li><strong>IdTable2</strong>: EstadoWorkflow</li>
  <li><strong>Table2.Id</strong>: IdEstadoWorkflow</li>
  <li><strong>ValueField</strong>: Final</li>
  </ul>
  
  <p>And in reverse for better reading:</p>
  
  <ul>
  <li><strong>Incidencias</strong>: Table1</li>
  <li><strong>EstadosWorkflows</strong>: Table2</li>
  <li><strong>EstadoWorkflow</strong>: IdTable2</li>
  <li><strong>IdEstadoWorkflow</strong>: Table2.Id</li>
  <li><strong>Final</strong>: ValueField</li>
  </ul>
  
  <h2>Real Production Queries</h2>
  
  <p>This queries with the query plans show the same problem but with additional costly operations (like the huge exists and joins) and the problem get worse.
  I really hope I wasn't misleading you with the simplified examples.</p>
  
  <p><strong>Query <code>IN</code> with value <code>0</code></strong></p>
  
  <pre><code>SELECT distinct top 15 this_.IdIncidencia as y0_, this_.Fecha as y1_ 
  FROM Incidencias this_ inner join Usuarios usuario1_ on this_.Usuario=usuario1_.IdUsuario inner join Usuarios_Perfiles perfiles5_ on usuario1_.IdUsuario=perfiles5_.Usuario and (perfiles5_.perfil in (select perfiles.idperfil from perfiles where perfiles.borrado = 0)) inner join Perfiles prf2_ on perfiles5_.Perfil=prf2_.IdPerfil 
  WHERE 
  this_.Instancia = 4 and 
  this_.EstadoWorkflow in (SELECT this_0_.IdEstadoWorkflow as y0_ FROM EstadosWorkflows this_0_ WHERE this_0_.Final = 0) and 
  exists (SELECT this_0_.IdPerfilPermiso as y0_ FROM Perfiles_Permisos this_0_ inner join Permisos prm1_ on this_0_.Permiso=prm1_.IdPermiso WHERE this_0_.IdPerfilPermiso in (206558, 206559, 209393, 209394) and (this_0_.PerfilAutorizado = prf2_.IdPerfil and this_0_.TipologiaAutorizada = this_.Tipologia and prm1_.Controlador = 'Incidencias' and prm1_.Accion = 'Index')) 
  ORDER BY this_.Fecha desc
  </code></pre>
  
  <p>Execution time: <strong>266ms</strong>.
  Execution plan: <a href="http://s000.tinyupload.com/index.php?file_id=36115325682943356233" rel="nofollow">http://s000.tinyupload.com/index.php?file_id=36115325682943356233</a></p>
  
  <p><strong>Query <code>IN</code> with value <code>1</code></strong></p>
  
  <pre><code>SELECT distinct top 15 this_.IdIncidencia as y0_, this_.Fecha as y1_ 
  FROM Incidencias this_ inner join Usuarios usuario1_ on this_.Usuario=usuario1_.IdUsuario inner join Usuarios_Perfiles perfiles5_ on usuario1_.IdUsuario=perfiles5_.Usuario and (perfiles5_.perfil in (select perfiles.idperfil from perfiles where perfiles.borrado = 0)) inner join Perfiles prf2_ on perfiles5_.Perfil=prf2_.IdPerfil 
  WHERE 
  this_.Instancia = 4 and 
  this_.EstadoWorkflow in (SELECT this_0_.IdEstadoWorkflow as y0_ FROM EstadosWorkflows this_0_ WHERE this_0_.Final = 1) and 
  exists (SELECT this_0_.IdPerfilPermiso as y0_ FROM Perfiles_Permisos this_0_ inner join Permisos prm1_ on this_0_.Permiso=prm1_.IdPermiso WHERE this_0_.IdPerfilPermiso in (206558, 206559, 209393, 209394) and (this_0_.PerfilAutorizado = prf2_.IdPerfil and this_0_.TipologiaAutorizada = this_.Tipologia and prm1_.Controlador = 'Incidencias' and prm1_.Accion = 'Index')) 
  ORDER BY this_.Fecha desc
  </code></pre>
  
  <p>Execution time: <strong>28506ms</strong>.
  Execution plan: <a href="http://s000.tinyupload.com/index.php?file_id=72827687005228029776" rel="nofollow">http://s000.tinyupload.com/index.php?file_id=72827687005228029776</a></p>
  
  <p><strong>Query <code>NOT IN</code> with value <code>0</code></strong></p>
  
  <pre><code>SELECT distinct top 15 this_.IdIncidencia as y0_, this_.Fecha as y1_ 
  FROM Incidencias this_ inner join Usuarios usuario1_ on this_.Usuario=usuario1_.IdUsuario inner join Usuarios_Perfiles perfiles5_ on usuario1_.IdUsuario=perfiles5_.Usuario and (perfiles5_.perfil in (select perfiles.idperfil from perfiles where perfiles.borrado = 0)) inner join Perfiles prf2_ on perfiles5_.Perfil=prf2_.IdPerfil 
  WHERE 
  this_.Instancia = 4 and 
  this_.EstadoWorkflow not in (SELECT this_0_.IdEstadoWorkflow as y0_ FROM EstadosWorkflows this_0_ WHERE this_0_.Final = 0) and 
  exists (SELECT this_0_.IdPerfilPermiso as y0_ FROM Perfiles_Permisos this_0_ inner join Permisos prm1_ on this_0_.Permiso=prm1_.IdPermiso WHERE this_0_.IdPerfilPermiso in (206558, 206559, 209393, 209394) and (this_0_.PerfilAutorizado = prf2_.IdPerfil and this_0_.TipologiaAutorizada = this_.Tipologia and prm1_.Controlador = 'Incidencias' and prm1_.Accion = 'Index')) 
  ORDER BY this_.Fecha desc
  </code></pre>
  
  <p>Execution time: <strong>498ms</strong>.
  Execution plan: <a href="http://s000.tinyupload.com/index.php?file_id=35554889075362686964" rel="nofollow">http://s000.tinyupload.com/index.php?file_id=35554889075362686964</a></p>
  
  <p><strong>Query <code>NOT IN</code> with value <code>1</code></strong></p>
  
  <pre><code>SELECT distinct top 15 this_.IdIncidencia as y0_, this_.Fecha as y1_ 
  FROM Incidencias this_ inner join Usuarios usuario1_ on this_.Usuario=usuario1_.IdUsuario inner join Usuarios_Perfiles perfiles5_ on usuario1_.IdUsuario=perfiles5_.Usuario and (perfiles5_.perfil in (select perfiles.idperfil from perfiles where perfiles.borrado = 0)) inner join Perfiles prf2_ on perfiles5_.Perfil=prf2_.IdPerfil 
  WHERE 
  this_.Instancia = 4 and 
  this_.EstadoWorkflow not in (SELECT this_0_.IdEstadoWorkflow as y0_ FROM EstadosWorkflows this_0_ WHERE this_0_.Final = 1) and 
  exists (SELECT this_0_.IdPerfilPermiso as y0_ FROM Perfiles_Permisos this_0_ inner join Permisos prm1_ on this_0_.Permiso=prm1_.IdPermiso WHERE this_0_.IdPerfilPermiso in (206558, 206559, 209393, 209394) and (this_0_.PerfilAutorizado = prf2_.IdPerfil and this_0_.TipologiaAutorizada = this_.Tipologia and prm1_.Controlador = 'Incidencias' and prm1_.Accion = 'Index')) 
  ORDER BY this_.Fecha desc
  </code></pre>
  
  <p>Execution time: <strong>386ms</strong>.
  Execution plan: <a href="http://s000.tinyupload.com/index.php?file_id=11500314236594795220" rel="nofollow">http://s000.tinyupload.com/index.php?file_id=11500314236594795220</a></p>