not - subconsultas sql oracle

Subconsulta con Exists 1 o Existe*

Question

Solía ​​escribir mis comprobaciones 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 cláusula EXISTS , use SELECT 1 lugar de SELECT *

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

¿Esto realmente hace la diferencia?

Answer 1

La mejor forma de saberlo es realizar pruebas de rendimiento en ambas versiones y consultar el plan de ejecución para ambas versiones. Elija una tabla con muchas columnas.

Answer 2

La razón de esta idea errónea es presumiblemente debido a 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

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

Entonces, está claro que la creencia anterior es incorrecta.

Sin embargo, Conor Cunningham, del equipo de Query Optimiser, explica here que, por lo general, utiliza SELECT 1 en este caso, ya que puede hacer una pequeña diferencia de rendimiento en la compilación de la consulta.

El QP tomará y expandirá todos los * al principio de la canalización y los vinculará a los objetos (en este caso, la lista de columnas). A continuación, eliminará las columnas innecesarias debido a la naturaleza de la consulta.

Entonces, para una subconsulta de EXISTS simple como esta:

SELECT col1 FROM MyTable WHERE EXISTS (SELECT * FROM Table2 WHERE MyTable.col1=Table2.col2) El * se ampliará a alguna lista de columnas potencialmente grande y luego se determinará que la semántica de EXISTS no requiere ninguna de esas columnas , así que básicamente todos pueden eliminarse.

" SELECT 1 " evitará tener que examinar cualquier metadato innecesario para esa tabla durante la compilación de consultas.

Sin embargo, en tiempo de ejecución, las dos formas de 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 1 vs SELECT * vs SELECT Primary_Key vs SELECT Other_Not_Null_Column .

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

+-------------+----------+---------+---------+--------------+
| 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 se puede ver, no hay un ganador consistente entre SELECT 1 y SELECT * y la diferencia entre los dos enfoques es insignificante. SELECT Not Null col embargo, SELECT Not Null col y SELECT PK aparecen un poco más rápido.

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

Como la tabla está vacía, esta relación parece solo explicable por la cantidad de metadatos de columna. Para COUNT(1) es fácil ver que esto se reescribe en COUNT(*) en algún punto del proceso desde abajo.

SET SHOWPLAN_TEXT ON;
GO
SELECT COUNT(1)
FROM master..spt_values

Lo cual 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 romper aleatoriamente al ejecutar el siguiente

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

Descubrí que en los casos en que la tabla tiene 1.024 columnas la mayoría de las veces la pila de llamadas se parece a la siguiente, lo que indica que de hecho está gastando una gran proporción de los metadatos de la columna de carga de tiempo incluso cuando se usa SELECT 1 (para el caso) donde la tabla tiene 1 columna rotando al azar no golpeó este bit 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 perfilado manual está respaldado por el generador de perfiles VS 2012 que muestra una selección muy diferente de funciones que consume el tiempo de compilación para los dos casos ( Top 15 Funciones 1024 columnas vs Top 15 Funciones 1 columna ).

Las versiones SELECT 1 y SELECT * terminan revisando los permisos de la columna y fallan si no se concede acceso al usuario a todas las columnas de la tabla.

Un ejemplo que descifré 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

Así que uno podría especular que la menor diferencia aparente al usar SELECT some_not_null_col es que solo termina revisando los permisos en esa columna específica (aunque todavía carga los metadatos para todos). Sin embargo, esto no parece encajar con los hechos como la diferencia porcentual entre los dos enfoques si algo se reduce a medida que aumenta el número de columnas en la tabla subyacente.

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

+-------------+-----------+------------+-----------+--------------+
| 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í

Answer 3

No hay diferencia en SQL Server y nunca ha sido un problema en SQL Server. El optimizador sabe que son lo mismo. Si miras los planes de ejecución, verás que son idénticos.

Answer 4

No hay ninguna diferencia real, pero puede haber un golpe de rendimiento muy pequeño. Como regla general, no debe solicitar más datos de los que necesita.

Answer 5

No. Esto ha sido cubierto un millón de veces. SQL Server es inteligente y sabe que se está utilizando para EXISTS, y devuelve NO DATA al sistema.

Quoth 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 consiste en un asterisco (*). No hay ninguna razón para enumerar nombres de columna porque solo está probando si existen filas que cumplen las condiciones especificadas en la subconsulta.

Además, no me creas? Intenta 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 div por error cero. No es así

EDITAR: Tenga en cuenta, el estándar SQL realmente 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 la <select list> "*" está simplemente contenida en una <subquery> que está contenida inmediatamente en un <exists predicate> , entonces <select list> es equivalente a una <value expression> que es un arbitrario <literal> .

Answer 6

Personalmente, me parece muy, muy difícil de creer que no se optimicen para el mismo plan de consulta. Pero la única forma de saber en su situación particular es probarlo. Si lo hace, informe de nuevo!