Sous-requête utilisant Existe 1 ou Existe *.

Question

J'avais l'habitude d'écrire mes contrôles EXISTS comme ceci :

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

Un des DBA dans une vie antérieure m'a dit que lorsque je fais un EXISTS utiliser la clause SELECT 1 au lieu de SELECT *

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

Cela fait-il vraiment une différence ?

Answer 1

Non, le serveur SQL est intelligent et sait qu'il est utilisé pour un EXISTS, et ne renvoie AUCUNE DONNÉE au système.

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

La liste de sélection d'une sous-requête introduite par EXISTS est presque toujours consiste presque toujours en un astérisque (*). Il n'y a raison de lister les noms des colonnes car car vous vérifiez simplement si les lignes qui qui répondent aux conditions spécifiées dans la sous-requête.

Pour vérifier vous-même, essayez d'exécuter ce qui suit :

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

S'il faisait réellement quelque chose avec la liste SELECT, il enverrait une erreur de division par zéro. Ce n'est pas le cas.

EDIT : Notez que le standard SQL parle effectivement de cela.

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

3) Cas :
a) Si le <select list> "*" est simplement contenu dans un <subquery> que est immédiatement contenu dans un <exists predicate> alors le <select list> est équivalent à un <value expression> qui est un <literal> .

Answer 2

Cette idée fausse s'explique sans doute par la croyance qu'il finira par lire toutes les colonnes. Il est facile de voir que ce n'est pas le cas.

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'

Donne le plan

Cela montre que SQL Server a pu utiliser l'index le plus étroit disponible pour vérifier le résultat malgré le fait que l'index n'inclut pas toutes les colonnes. L'accès à l'index est sous un opérateur de semi-jonction, ce qui signifie qu'il peut arrêter le balayage dès que la première ligne est retournée.

Il est donc clair que la croyance ci-dessus est fausse.

Cependant, Conor Cunningham, de l'équipe Query Optimiser, explique que aquí qu'il utilise généralement SELECT 1 dans ce cas, car cela peut faire une différence mineure dans les performances. dans la compilation de la requête.

Le QP prendra et développera tous les * 's au début du pipeline et les lient à (dans ce cas, la liste des colonnes). Il supprimera ensuite colonnes inutiles en raison de la nature de la requête. la requête.

Ainsi, pour un simple EXISTS sous-requête comme ceci :

SELECT col1 FROM MyTable WHERE EXISTS (SELECT * FROM Table2 WHERE MyTable.col1=Table2.col2) Le site * sera étendu à une liste de colonnes potentiellement grande liste de colonnes et ensuite il sera déterminé que la sémantique de la EXISTS ne nécessite aucune de ces colonnes, donc en fait, elles peuvent toutes être supprimées.

" SELECT 1 " évitera d'avoir à d'examiner toutes les métadonnées inutiles pour cette pendant la compilation de la requête.

Cependant, au moment de l'exécution, les deux formes de la requête seront identiques et auront et auront des temps d'exécution identiques.

J'ai testé quatre façons possibles d'exprimer cette requête sur une table vide avec différents nombres de colonnes. SELECT 1 vs SELECT * vs SELECT Primary_Key vs SELECT Other_Not_Null_Column .

J'ai exécuté les requêtes dans une boucle en utilisant OPTION (RECOMPILE) et mesuré le nombre moyen d'exécutions par seconde. Résultats ci-dessous

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

Comme on peut le constater, il n'y a pas de gagnant cohérent entre SELECT 1 y SELECT * et la différence entre les deux approches est négligeable. Le site SELECT Not Null col y SELECT PK semblent cependant légèrement plus rapides.

Les performances des quatre requêtes se dégradent à mesure que le nombre de colonnes de la table augmente.

Comme la table est vide, cette relation semble uniquement explicable par la quantité de métadonnées de la colonne. Pour COUNT(1) il est facile de voir que cela se réécrit en COUNT(*) à un moment donné du processus, à partir des éléments ci-dessous.

SET SHOWPLAN_TEXT ON;

GO

SELECT COUNT(1)
FROM master..spt_values

Ce qui donne le plan suivant

  |--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]))

Attachement d'un débogueur au processus SQL Server et rupture aléatoire lors de l'exécution de la commande suivante

DECLARE @V int 

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

J'ai constaté que dans les cas où la table a 1 024 colonnes, la plupart du temps, la pile d'appels ressemble à quelque chose comme ci-dessous, ce qui indique qu'elle passe une grande partie du temps à charger les métadonnées des colonnes, même lorsque SELECT 1 est utilisé (pour le cas où la table a 1 colonne, la rupture aléatoire n'a pas atteint cette partie de la pile d'appels en 10 tentatives).

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 

Cette tentative de profilage manuel est étayée par le profileur de code VS 2012 qui montre une sélection très différente de fonctions consommant le temps de compilation pour les deux cas ( Top 15 des fonctions 1024 colonnes vs Top 15 des fonctions 1 colonne ).

Les deux SELECT 1 y SELECT * se retrouvent à vérifier les autorisations des colonnes et échouent si l'utilisateur n'a pas accès à toutes les colonnes de la table.

Un exemple que j'ai tiré d'une conversation sur le site Web de la Commission européenne. le tas

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

On peut donc supposer que la différence mineure apparente lors de l'utilisation de SELECT some_not_null_col est qu'il ne vérifie les autorisations que pour cette colonne spécifique (tout en chargeant les métadonnées pour toutes). Cependant, cela ne semble pas correspondre aux faits, car la différence en pourcentage entre les deux approches se réduit au fur et à mesure que le nombre de colonnes de la table sous-jacente augmente.

Quoi qu'il en soit, je ne vais pas me précipiter pour modifier toutes mes requêtes sous cette forme, car la différence est très mineure et n'apparaît que lors de la compilation des requêtes. En supprimant le OPTION (RECOMPILE) afin que les exécutions suivantes puissent utiliser un plan mis en cache a donné le suivant.

+-------------+-----------+------------+-----------+--------------+
| Num of Cols |     *     |     1      |    PK     | Not Null col |
+-------------+-----------+------------+-----------+--------------+
| 2           | 144933.25 | 145292     | 146029.25 | 143973.5     |
| 4           | 146084    | 146633.5   | 146018.75 | 14681.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   |
+-------------+-----------+------------+-----------+--------------+

Le test script que j'ai utilisé peut être trouvé ici

Answer 3

La meilleure façon de le savoir est de tester les performances des deux versions et de vérifier le plan d'exécution des deux versions. Choisissez une table avec beaucoup de colonnes.

Answer 4

Il n'y a aucune différence dans SQL Server et cela n'a jamais été un problème dans SQL Server. L'optimiseur sait que ce sont les mêmes. Si vous regardez les plans d'exécution, vous verrez qu'ils sont identiques.

Answer 5

Personnellement, je trouve très, très difficile de croire qu'ils n'optimisent pas le même plan de requête. Mais la seule façon de le savoir dans votre situation particulière est de le tester. Si vous le faites, rapportez-vous !