Subconsulta usando Existe 1 o Existe *

Solía escribir mis cheques EXISTS así:

IF EXISTS (SELECT * FROM TABLE WHERE Columns=@Filters)
BEGIN
   UPDATE TABLE SET ColumnsX=ValuesX WHERE Where Columns=@Filters
END

Uno de los DBA en una vida anterior me dijo que cuando hago una EXISTScláusula, uso SELECT 1en lugar deSELECT *

IF EXISTS (SELECT 1 FROM TABLE WHERE Columns=@Filters)
BEGIN
   UPDATE TABLE SET ColumnsX=ValuesX WHERE Columns=@Filters
END

¿Esto realmente hace una diferencia?

Oct 21 '09 04:10 Raj More

No, SQL Server es inteligente y sabe que se está utilizando para EXISTE y no devuelve NINGÚN DATO al sistema.

Dijo Microsoft: http://technet.microsoft.com/en-us/library/ms189259.aspx?ppud=4

La lista de selección de una subconsulta introducida por EXISTS casi siempre consta de un asterisco (*). No hay ninguna razón para enumerar los nombres de las columnas porque solo está probando si existen filas que cumplen con las condiciones especificadas en la subconsulta.

Para comprobarlo usted mismo, intente ejecutar lo siguiente:

SELECT whatever
  FROM yourtable
 WHERE EXISTS( SELECT 1/0
                 FROM someothertable 
                WHERE a_valid_clause )

Si realmente estuviera haciendo algo con la lista SELECT, arrojaría un error div por cero. No es así.

EDITAR: Tenga en cuenta que el estándar SQL en realidad habla de esto.

Estándar ANSI SQL 1992, página 191 http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

3) Caso:
a) Si el <select list>"*" simplemente está contenido en a <subquery> que está inmediatamente contenido en an <exists predicate>, entonces <select list> es equivalente a a <value expression> que es arbitrario <literal>.

Oct 20 '2009 21:10 Matt Rogish

La razón de este error es probablemente la creencia de que terminará leyendo todas las columnas. Es fácil ver que este no es el caso.

CREATE TABLE T
(
X INT PRIMARY KEY,
Y INT,
Z CHAR(8000)
)

CREATE NONCLUSTERED INDEX NarrowIndex ON T(Y)

IF EXISTS (SELECT * FROM T)
    PRINT 'Y'

da plan

Plan

Esto muestra que SQL Server pudo usar el índice más estrecho disponible para verificar el resultado a pesar de que el índice no incluye todas las columnas. El acceso al índice se realiza mediante un operador de semiunión, lo que significa que puede detener el escaneo tan pronto como se devuelve la primera fila.

Por tanto, está claro que la creencia anterior es errónea.

Sin embargo, Conor Cunningham del equipo de Query Optimiser explica aquí que normalmente lo usa SELECT 1en este caso, ya que puede marcar una pequeña diferencia en el rendimiento en la compilación de la consulta.

El QP tomará y expandirá todos *los al principio del proceso y los vinculará a objetos (en este caso, la lista de columnas). Luego eliminará las columnas innecesarias debido a la naturaleza de la consulta.

Entonces, para una subconsulta simple EXISTScomo esta:

SELECT col1 FROM MyTable WHERE EXISTS (SELECT * FROM Table2 WHERE MyTable.col1=Table2.col2)Se *expandirá a una lista de columnas potencialmente grande y luego se determinará que la semántica de EXISTSno requiere ninguna de esas columnas, por lo que básicamente se pueden eliminar todas.

" SELECT 1" evitará tener que examinar metadatos innecesarios para esa tabla durante la compilación de la consulta.

Sin embargo, en tiempo de ejecución las dos formas de la consulta serán idénticas y tendrán tiempos de ejecución idénticos.

Probé cuatro formas posibles de expresar esta consulta en una tabla vacía con varios números de columnas. SELECT 1vs SELECT *vs SELECT Primary_Keyvs. SELECT Other_Not_Null_Column_

Ejecuté las consultas en un bucle OPTION (RECOMPILE)y medí el número promedio de ejecuciones por segundo. Resultados a continuación

ingrese la descripción de la imagen aquí

+-------------+----------+---------+---------+--------------+
| Num of Cols |    *     |    1    |   PK    | Not Null col |
+-------------+----------+---------+---------+--------------+
| 2           | 2043.5   | 2043.25 | 2073.5  | 2067.5       |
| 4           | 2038.75  | 2041.25 | 2067.5  | 2067.5       |
| 8           | 2015.75  | 2017    | 2059.75 | 2059         |
| 16          | 2005.75  | 2005.25 | 2025.25 | 2035.75      |
| 32          | 1963.25  | 1967.25 | 2001.25 | 1992.75      |
| 64          | 1903     | 1904    | 1936.25 | 1939.75      |
| 128         | 1778.75  | 1779.75 | 1799    | 1806.75      |
| 256         | 1530.75  | 1526.5  | 1542.75 | 1541.25      |
| 512         | 1195     | 1189.75 | 1203.75 | 1198.5       |
| 1024        | 694.75   | 697     | 699     | 699.25       |
+-------------+----------+---------+---------+--------------+
| Total       | 17169.25 | 17171   | 17408   | 17408        |
+-------------+----------+---------+---------+--------------+

Como puede verse, no existe un ganador consistente entre SELECT 1y SELECT *y la diferencia entre los dos enfoques es insignificante. Sin embargo SELECT Not Null col, y SELECT PKparecen un poco más rápidos.

Las cuatro consultas pierden rendimiento a medida que aumenta el número de columnas de la tabla.

Como la tabla está vacía, esta relación sólo parece explicable por la cantidad de metadatos de la columna. Porque COUNT(1)es fácil ver que esto se reescribe COUNT(*)en algún momento del proceso a continuación.

SET SHOWPLAN_TEXT ON;

GO

SELECT COUNT(1)
FROM master..spt_values

Lo que da el siguiente plan.

  |--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1004],0)))
       |--Stream Aggregate(DEFINE:([Expr1004]=Count(*)))
            |--Index Scan(OBJECT:([master].[dbo].[spt_values].[ix2_spt_values_nu_nc]))

Adjuntar un depurador al proceso de SQL Server y interrumpir aleatoriamente mientras se ejecuta lo siguiente

DECLARE @V int 

WHILE (1=1)
    SELECT @V=1 WHERE EXISTS (SELECT 1 FROM ##T) OPTION(RECOMPILE)

Descubrí que en los casos en los que la tabla tiene 1024 columnas la mayor parte del tiempo, la pila de llamadas se parece a la siguiente, lo que indica que de hecho está gastando una gran proporción del tiempo cargando metadatos de columnas incluso cuando SELECT 1se usan (para el caso en el que la tabla tiene 1 columna que se rompe aleatoriamente y no alcanzó esta parte de la pila de llamadas en 10 intentos)

sqlservr.exe!CMEDAccess::GetProxyBaseIntnl()  - 0x1e2c79 bytes  
sqlservr.exe!CMEDProxyRelation::GetColumn()  + 0x57 bytes   
sqlservr.exe!CAlgTableMetadata::LoadColumns()  + 0x256 bytes    
sqlservr.exe!CAlgTableMetadata::Bind()  + 0x15c bytes   
sqlservr.exe!CRelOp_Get::BindTree()  + 0x98 bytes   
sqlservr.exe!COptExpr::BindTree()  + 0x58 bytes 
sqlservr.exe!CRelOp_FromList::BindTree()  + 0x5c bytes  
sqlservr.exe!COptExpr::BindTree()  + 0x58 bytes 
sqlservr.exe!CRelOp_QuerySpec::BindTree()  + 0xbe bytes 
sqlservr.exe!COptExpr::BindTree()  + 0x58 bytes 
sqlservr.exe!CScaOp_Exists::BindScalarTree()  + 0x72 bytes  
... Lines omitted ...
msvcr80.dll!_threadstartex(void * ptd=0x0031d888)  Line 326 + 0x5 bytes C
kernel32.dll!_BaseThreadStart@8()  + 0x37 bytes

Este intento de creación de perfiles manual está respaldado por el generador de perfiles de código VS 2012, que muestra una selección muy diferente de funciones que consumen el tiempo de compilación para los dos casos ( 15 funciones principales 1024 columnas frente a 15 funciones principales 1 columna ).

Tanto la versión SELECT 1como SELECT *terminan verificando los permisos de las columnas y fallan si al usuario no se le otorga acceso a todas las columnas de la tabla.

Un ejemplo que tomé de una conversación en el montón.

CREATE USER blat WITHOUT LOGIN;
GO
CREATE TABLE dbo.T
(
X INT PRIMARY KEY,
Y INT,
Z CHAR(8000)
)
GO

GRANT SELECT ON dbo.T TO blat;
DENY SELECT ON dbo.T(Z) TO blat;
GO
EXECUTE AS USER = 'blat';
GO

SELECT 1
WHERE  EXISTS (SELECT 1
               FROM   T); 
/*  ↑↑↑↑ 
Fails unexpectedly with 

The SELECT permission was denied on the column 'Z' of the 
           object 'T', database 'tempdb', schema 'dbo'.*/

GO
REVERT;
DROP USER blat
DROP TABLE T

Entonces, se podría especular que la menor diferencia aparente al usarlo SELECT some_not_null_coles que solo termina verificando los permisos en esa columna específica (aunque aún carga los metadatos para todos). Sin embargo, esto no parece encajar con los hechos, ya que la diferencia porcentual entre los dos enfoques se reduce a medida que aumenta el número de columnas en la tabla subyacente.

En cualquier caso, no me apresuraré a cambiar todas mis consultas a este formulario, ya que la diferencia es mínima y sólo aparente durante la compilación de la consulta. Al eliminarlo OPTION (RECOMPILE)para que las ejecuciones posteriores puedan utilizar un plan en caché, se obtuvo lo siguiente.

ingrese la descripción de la imagen aquí

+-------------+-----------+------------+-----------+--------------+
| Num of Cols |     *     |     1      |    PK     | Not Null col |
+-------------+-----------+------------+-----------+--------------+
| 2           | 144933.25 | 145292     | 146029.25 | 143973.5     |
| 4           | 146084    | 146633.5   | 146018.75 | 146581.25    |
| 8           | 143145.25 | 144393.25  | 145723.5  | 144790.25    |
| 16          | 145191.75 | 145174     | 144755.5  | 146666.75    |
| 32          | 144624    | 145483.75  | 143531    | 145366.25    |
| 64          | 145459.25 | 146175.75  | 147174.25 | 146622.5     |
| 128         | 145625.75 | 143823.25  | 144132    | 144739.25    |
| 256         | 145380.75 | 147224     | 146203.25 | 147078.75    |
| 512         | 146045    | 145609.25  | 145149.25 | 144335.5     |
| 1024        | 148280    | 148076     | 145593.25 | 146534.75    |
+-------------+-----------+------------+-----------+--------------+
| Total       | 1454769   | 1457884.75 | 1454310   | 1456688.75   |
+-------------+-----------+------------+-----------+--------------+

El script de prueba que utilicé se puede encontrar aquí.

May 26 '2011 14:05 Martin Smith