Подзапрос с использованием Exists 1 или Exists *

Question

Подзапрос с использованием Exists 1 или Exists *

раньше я писал свои проверки EXISTS следующим образом:

IF EXISTS (SELECT * FROM TABLE WHERE Columns=@Filters)

BEGIN

   UPDATE TABLE SET ColumnsX=ValuesX WHERE Where Columns=@Filters

END

один из DBA в предыдущей жизни сказал мне, что когда я делаю EXISTS предложения, использовать SELECT 1 вместо SELECT *

IF EXISTS (SELECT 1 FROM TABLE WHERE Columns=@Filters)

BEGIN

   UPDATE TABLE SET ColumnsX=ValuesX WHERE Columns=@Filters

END

это действительно имеет значение?

536 6

sql-server sql tsql

6 ответов:

Comments

Ничего не найдено.

Matt Rogish · Accepted Answer · 2018-10-05 15:49:07

нет, SQL Server умный и знает, что он используется для существует, и не возвращает никаких данных в систему.

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

список выбора подзапроса введенный существует почти всегда состоит из звездочки (*). Есть нет причин перечислять имена столбцов, потому что вы просто проверяете, есть ли строки, которые соблюдайте условия, указанные в подзапрос существовать.

чтобы проверить себя, попробуйте выполнить следующее:
SELECT whatever
  FROM yourtable
 WHERE EXISTS( SELECT 1/0
                 FROM someothertable 
                WHERE a_valid_clause )
если бы он действительно что-то делал со списком выбора, он бы бросил div с нулевой ошибкой. Это не так.

редактировать: обратите внимание, стандарт SQL на самом деле говорит об этом.

ANSI SQL 1992 Standard, pg 191 http://www.contrib.andrew.cmu.edu / ~shadow/sql/sql1992.txt

3) Случай:
а) если <select list> " * " - это просто содержащиеся в элементе <subquery> что немедленно содержится в <exists predicate>, потом <select list> is эквивалентно <value expression> это произвольное <literal>.

Martin Smith · Accepted Answer · 2014-12-07 18:21:26

причина этого заблуждения, по-видимому, из-за убеждения, что он будет в конечном итоге читать все столбцы. Нетрудно видеть, что это не так.
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'
план

это показывает, что SQL Server смог использовать самый узкий индекс, доступный для проверки результата, несмотря на то, что индекс не включает все столбцы. Доступ к индексу находится под оператором semi join, что означает, что он может остановить сканирование как как только первая строка будет возвращена.

таким образом, ясно, что вышеуказанное убеждение неверно.

однако Конор Каннингем из команды оптимизатора запросов объясняет здесь что он обычно использует SELECT 1 в этом случае, как это может сделать незначительную разницу в производительности в сборнике запроса.

QP возьмет и развернет все * ' s в начале конвейера и привязать их к объекты (в данном случае, список столбцы.) Затем он будет удален ненужные столбцы из-за природы вопрос.

так что для простого EXISTS подзапрос, как это:

SELECT col1 FROM MyTable WHERE EXISTS (SELECT * FROM Table2 WHERE MyTable.col1=Table2.col2) The * будет расширен до некоторых потенциально больших список столбцов, а затем это будет установлено, что семантика EXISTS не требует ни одного из них колонки, так что в основном все они могут быть удалены.

"SELECT 1 " позволит избежать необходимости изучить все ненужные метаданные для этого таблицы во время компиляции запроса.

однако, во время выполнения двух форм запрос будет идентичен и будет имеют одинаковое время выполнения.

я проверил четыре возможных способа выражения этого запроса на пустой таблице с различным количеством столбцов. SELECT 1 vs SELECT * vs SELECT Primary_Key vs SELECT Other_Not_Null_Column.

я запускал запросы в цикле с помощью OPTION (RECOMPILE) и измерили среднее число казней в секунду. Результаты ниже
+-------------+----------+---------+---------+--------------+
| 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        |
+-------------+----------+---------+---------+--------------+
как видно нет последовательного победителя между SELECT 1 и SELECT * и разница между этими двумя подходами незначительна. Элемент SELECT Not Null col и SELECT PK появляются немного быстрее, хотя.

все четыре запроса ухудшают производительность по мере увеличения количества столбцов в таблице.

поскольку таблица пуста, эта связь кажется объяснимой только количеством метаданных столбца. Ибо COUNT(1) легко видеть, что это переписывается на COUNT(*) в какой-то момент в процессе С ниже.
SET SHOWPLAN_TEXT ON;

GO

SELECT COUNT(1)
FROM master..spt_values
что дает следующий план
  |--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]))
прикрепление отладчика к процессу SQL Server и случайное нарушение при выполнении ниже
DECLARE @V int 

WHILE (1=1)
    SELECT @V=1 WHERE EXISTS (SELECT 1 FROM ##T) OPTION(RECOMPILE)
я обнаружил, что в случаях, когда таблица имеет 1,024 столбца большую часть времени стек вызовов выглядит примерно так, как показано ниже, что он действительно тратит большая доля времени загрузки метаданных столбца даже при SELECT 1 используется (для случая, когда таблица имеет 1 столбец случайно ломать не ударил этот бит стека вызовов в 10 попыток)
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 
эта попытка профилирования вручную поддерживается профилировщиком кода VS 2012, который показывает очень разный выбор функций, потребляющих время компиляции для двух случаев (топ 15 функций 1024 колонки vs Топ 15 Функций 1 колонка).

и SELECT 1 и SELECT * версии завершают проверку разрешений столбцов и завершаются ошибкой, если пользователю не предоставлен доступ ко всем столбцам в таблице.

пример я списал из разговора на кучу
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
поэтому можно предположить, что незначительная кажущаяся разница при использовании SELECT some_not_null_col заключается в том, что он только проверяет разрешения на этот конкретный столбец (хотя по-прежнему загружает метаданные для всех). Однако это, похоже, не соответствует фактам, поскольку процентная разница между двумя подходами, если что-то становится меньше, поскольку количество столбцов в базовой таблице увеличивается.

в любом случае я не буду спешить и менять все мои запросы на эту форму, поскольку разница очень незначительна и очевидна только во время компиляции запроса. Удаление OPTION (RECOMPILE) так что последующие выполнения могут использовать кэшированный план дал следующий.
+-------------+-----------+------------+-----------+--------------+
| 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   |
+-------------+-----------+------------+-----------+--------------+
тестовый скрипт, который я использовал, можно найти здесь

HLGEM · Accepted Answer · 2009-10-21 00:32:56

лучший способ узнать, чтобы проверить производительность обеих версий и проверить план выполнения для обеих версий. Выбрать таблицу с большим количеством столбцов.

Cade Roux · Accepted Answer · 2009-10-21 01:19:35

нет никакой разницы в SQL Server, и это никогда не было проблемой в SQL Server. Оптимизатор знает, что они одинаковы. Если вы посмотрите на планы выполнения, вы увидите, что они идентичны.

Larry Lustig · Accepted Answer · 2009-10-21 00:36:09

лично мне очень, очень трудно поверить, что они не оптимизируются для одного и того же плана запроса. Но единственный способ узнать в вашей конкретной ситуации, чтобы проверить его. Если вы это сделаете, пожалуйста, сообщите обратно!

orjan · Accepted Answer · 2009-10-21 00:34:28

нет никакой реальной разницы, но там может быть очень небольшое падение производительности. Как правило, вы не должны запрашивать больше данных, чем вам нужно.