High delivery time when changing the comparison value on request

Question

High delivery time when changing the comparison value on request

I have a problem with the query execution time, which puzzles me. I know several ways to solve the problem and get the best and acceptable runtime, but still don't know why the problem arises.

Sample Tables

We have two tables associated with a foreign key.

Table1

| Id | IdTable2 |
|:--:|:--------:|
|  1 |     4    |
|  2 |     7    |
|  3 |     8    |
|  4 |     6    |
|  5 |     4    |
|  6 |     1    |
|  7 |     1    |
|  8 |     6    |
|  9 |     7    |
| 10 |     1    |

Table2

| Id | ValueField |
|:--:|:----------:|
|  1 |      0     |
|  2 |      0     |
|  3 |      0     |
|  4 |      1     |
|  5 |      0     |
|  6 |      1     |
|  7 |      0     |

Query

SELECT * FROM Table1 WHERE IdTable2 IN (SELECT Id FROM Table2 WHERE ValueField = ?);

Where ?can it be 0or1

The number of real data

The above tables are just a model for simplification, but the values of the actual rows of these tables are as follows:

Table 1: 60,420 rows
Table 2: 62 rows
Table 2 with ValueField 0: 51 rows
2 ValueField 1: 11 rows
1 IdTable2 ValueField 0: 599
1 IdTable2 ValueField 1: 59821

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

, , , , , , , , , , , , :

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

... , - :

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

, ?

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

Hummm...., , , ValueField = 1 IN , HIGH ?

SQL IN ValueField 1:

SELECT * FROM Incidencias WHERE EstadoWorkflow in (SELECT IdEstadoWorkflow FROM EstadosWorkflows WHERE Final = 1);

http://s000.tinyupload.com/index.php?file_id=19036217708532467879

SQL IN ValueField 0:

SELECT * FROM Incidencias WHERE EstadoWorkflow in (SELECT IdEstadoWorkflow FROM EstadosWorkflows WHERE Final = 0);

http://s000.tinyupload.com/index.php?file_id=49593927895920014301

SQL NOT IN ValueField 0:

SELECT * FROM Incidencias WHERE EstadoWorkflow not in (SELECT IdEstadoWorkflow FROM EstadosWorkflows WHERE Final = 0);

http://s000.tinyupload.com/index.php?file_id=03901091628843565847

SQL NOT IN ValueField 1:

SELECT * FROM Incidencias WHERE EstadoWorkflow not in (SELECT IdEstadoWorkflow FROM EstadosWorkflows WHERE Final = 1);

http://s000.tinyupload.com/index.php?file_id=69996775965382534356

- , , , .

1: Incidencias
2: EstadosWorkflows
IdTable2: EstadoWorkflow
2.Id: IdEstadoWorkflow
ValueField: Final

:

Incidencias: 1
EstadosWorkflows: 2
EstadoWorkflow: IdTable2
IdEstadoWorkflow: Table2.Id
: ValueField

, ( ), . , .

IN 0

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

: 266 . : http://s000.tinyupload.com/index.php?file_id=36115325682943356233

IN 1

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

: 28506ms. : http://s000.tinyupload.com/index.php?file_id=72827687005228029776

NOT IN 0

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

: 498 . : http://s000.tinyupload.com/index.php?file_id=35554889075362686964

NOT IN 1

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