Kiedyś pisałem moje czeki EXISTS w ten sposób:
IF EXISTS (SELECT * FROM TABLE WHERE Columns=@Filters)
BEGIN
UPDATE TABLE SET ColumnsX=ValuesX WHERE Where Columns=@Filters
END
Jeden z administratorów DBA w poprzednim życiu powiedział mi, że kiedy robię EXISTSklauzulę, używaj SELECT 1zamiastSELECT *
IF EXISTS (SELECT 1 FROM TABLE WHERE Columns=@Filters)
BEGIN
UPDATE TABLE SET ColumnsX=ValuesX WHERE Columns=@Filters
END
Czy to naprawdę robi różnicę?
Odpowiedzi:
Nie, SQL Server jest inteligentny i wie, że jest używany jako ISTNIEJE i zwraca ŻADNE DANE do systemu.
Quoth Microsoft: http://technet.microsoft.com/en-us/library/ms189259.aspx?ppud=4
Lista wyboru podzapytania wprowadzona przez EXISTS prawie zawsze składa się z gwiazdki (*). Nie ma powodu, aby wymieniać nazwy kolumn, ponieważ testujesz tylko, czy istnieją wiersze spełniające warunki określone w podzapytaniu.
Aby sprawdzić siebie, spróbuj wykonać następujące czynności:
SELECT whatever
FROM yourtable
WHERE EXISTS( SELECT 1/0
FROM someothertable
WHERE a_valid_clause )
Jeśli faktycznie robił coś z listą SELECT, zwróciłby błąd div o zero. Tak nie jest.
EDYCJA: Uwaga, standard SQL faktycznie o tym mówi.
ANSI SQL 1992 Standard, str. 191 http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
3) Przypadek:
a) Jeśli<select list>„*” jest po prostu zawarte w a,<subquery>które jest bezpośrednio zawarte w a<exists predicate>, to<select list>jest równoważne a,<value expression>które jest arbitralne<literal>.
EXISTSsztuczka z 1/0 może być przedłużony nawet do tego SELECT 1 WHERE EXISTS(SELECT 1/0)... wydaje się krokiem bardziej abstrakcyjne następnie jako drugi SELECTma FROMklauzulę
SELECT COUNT(*) WHERE EXISTS(SELECT 1/0). A SELECTbez a FROMw SQL Server jest traktowane tak, jakby uzyskiwało dostęp do tabeli z jednym wierszem (np. Podobnie do wybierania z dualtabeli w innych systemach RDBMS)
SELECTtworzy 1-wierszową tabelę, zanim zrobi cokolwiek innego, więc nawet jeśli 1/0jest to śmieci, 1-wierszowa tabela nadal EXISTS?
Przyczyną tego błędnego przekonania jest przypuszczalnie przekonanie, że przeczyta on wszystkie kolumny. Łatwo zauważyć, że tak nie jest.
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'
Przedstawia plan
Pokazuje to, że SQL Server był w stanie użyć najwęższego dostępnego indeksu do sprawdzenia wyniku, mimo że indeks nie obejmuje wszystkich kolumn. Dostęp do indeksu odbywa się pod operatorem półłączenia, co oznacza, że może zatrzymać skanowanie, gdy tylko zostanie zwrócony pierwszy wiersz.
Jest więc jasne, że powyższe przekonanie jest błędne.
Jednak Conor Cunningham z zespołu Query Optimiser wyjaśnia tutaj , że zazwyczaj używa SELECT 1w tym przypadku, ponieważ może to spowodować niewielką różnicę w wydajności podczas kompilacji zapytania.
QP weźmie i rozszerzy wszystko na
*wczesnym etapie potoku i powiąże je z obiektami (w tym przypadku z listą kolumn). Następnie usunie niepotrzebne kolumny ze względu na charakter zapytania.Więc dla prostego
EXISTSpodzapytania, takiego jak to:
SELECT col1 FROM MyTable WHERE EXISTS (SELECT * FROM Table2 WHERE MyTable.col1=Table2.col2)*Zostanie poszerzona do pewnego potencjalnie dużym liście kolumn, a następnie zostanie ustalone, że semantykęEXISTSnie wymaga żadnej z tych kolumn, więc w zasadzie wszystkie z nich mogą być usunięte.„
SELECT 1” pozwoli uniknąć sprawdzania niepotrzebnych metadanych dla tej tabeli podczas kompilacji zapytania.Jednak w czasie wykonywania obie formy zapytania będą identyczne i będą miały identyczne środowiska wykonawcze.
Przetestowałem cztery możliwe sposoby wyrażenia tego zapytania na pustej tabeli z różną liczbą kolumn. SELECT 1vs SELECT *vs SELECT Primary_Keyvs SELECT Other_Not_Null_Column.
Uruchomiłem zapytania w pętli, używając OPTION (RECOMPILE)i zmierzyłem średnią liczbę wykonań na sekundę. Wyniki poniżej
+-------------+----------+---------+---------+--------------+
| 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 |
+-------------+----------+---------+---------+--------------+
Jak widać, nie ma stałego zwycięzcy między SELECT 1i, SELECT *a różnica między tymi dwoma podejściami jest znikoma. Jednak SELECT Not Null coli SELECT PKpojawiają się nieco szybciej.
Wydajność wszystkich czterech zapytań spada wraz ze wzrostem liczby kolumn w tabeli.
Ponieważ tabela jest pusta, relacja ta wydaje się możliwa do wyjaśnienia jedynie ilością metadanych kolumny. Za COUNT(1)to łatwo zauważyć, że ten zostanie przepisany do COUNT(*)w pewnym momencie w procesie od poniżej.
SET SHOWPLAN_TEXT ON;
GO
SELECT COUNT(1)
FROM master..spt_values
Co daje następujący 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]))
Dołączanie debugera do procesu SQL Server i losowe przerywanie podczas wykonywania poniższych czynności
DECLARE @V int
WHILE (1=1)
SELECT @V=1 WHERE EXISTS (SELECT 1 FROM ##T) OPTION(RECOMPILE)
Zauważyłem, że w przypadkach, w których tabela ma 1024 kolumny przez większość czasu, stos wywołań wygląda jak poniżej, wskazując, że rzeczywiście spędza dużą część czasu na ładowaniu metadanych kolumn, nawet gdy SELECT 1jest używany (w przypadku tabela ma 1 kolumnę, losowo łamana, nie trafiła tego bitu stosu wywołań w 10 próbach)
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
Ta próba ręcznego profilowania jest obsługiwana przez profiler kodu VS 2012, który pokazuje bardzo różny wybór funkcji zużywających czas kompilacji dla dwóch przypadków ( 15 najważniejszych funkcji, 1024 kolumn w porównaniu z 15 najpopularniejszymi funkcjami 1 kolumna ).
Obie wersje SELECT 1i SELECT *kończą sprawdzanie uprawnień do kolumn i kończą się niepowodzeniem, jeśli użytkownikowi nie udzielono dostępu do wszystkich kolumn w tabeli.
Przykład, który wyciągnąłem z rozmowy na stercie
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
Można więc spekulować, że niewielka widoczna różnica podczas używania SELECT some_not_null_col polega na tym, że kończy sprawdzanie uprawnień tylko do tej konkretnej kolumny (chociaż nadal ładuje metadane dla wszystkich). Jednak wydaje się, że nie pasuje to do faktów, ponieważ procentowa różnica między tymi dwoma podejściami, jeśli cokolwiek zmniejsza się, gdy wzrasta liczba kolumn w tabeli bazowej.
W każdym razie nie będę się spieszyć i zmieniać wszystkich moich zapytań do tego formularza, ponieważ różnica jest bardzo niewielka i widoczna tylko podczas kompilacji zapytań. Usunięcie OPTION (RECOMPILE)planu, aby kolejne wykonania mogły korzystać z planu buforowanego, dało następujące efekty.
+-------------+-----------+------------+-----------+--------------+
| 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 |
+-------------+-----------+------------+-----------+--------------+
Najlepszym sposobem jest przetestowanie wydajności obu wersji i sprawdzenie planu wykonania dla obu wersji. Wybierz tabelę z wieloma kolumnami.
Osobiście uważam, że bardzo, bardzo trudno jest uwierzyć, że nie optymalizują one tego samego planu zapytań. Ale jedynym sposobem, aby się dowiedzieć w swojej konkretnej sytuacji, jest przetestowanie tego. Jeśli tak, zgłoś się ponownie!