Jak skutecznie sprawdzić ISTNIEJE w wielu kolumnach?

Jest to problem, z którym spotykam się okresowo i nie znalazłem jeszcze dobrego rozwiązania.

Zakładając następującą strukturę tabeli

CREATE TABLE T
(
A INT PRIMARY KEY,
B CHAR(1000) NULL,
C CHAR(1000) NULL
)

a warunkiem jest ustalenie, czy któraś z zerowalnych kolumn Blub Cfaktycznie zawiera jakieś NULLwartości (a jeśli tak, to które).

Załóżmy również, że tabela zawiera miliony wierszy (i że nie są dostępne żadne statystyki kolumn, które można by zajrzeć, ponieważ jestem zainteresowany bardziej ogólnym rozwiązaniem dla tej klasy zapytań).

Mogę wymyślić kilka sposobów podejścia do tego, ale wszystkie mają słabości.

Dwie oddzielne EXISTSwypowiedzi. Miałoby to tę zaletę, że pozwalało zapytaniom zatrzymać skanowanie wcześnie, jak tylko NULLzostanie znalezione. Ale jeśli obie kolumny faktycznie nie zawierają NULLs, to zostaną wykonane dwa pełne skany.

Pojedyncze zapytanie zagregowane

SELECT 
    MAX(CASE WHEN B IS NULL THEN 1 ELSE 0 END) AS B,
    MAX(CASE WHEN C IS NULL THEN 1 ELSE 0 END) AS C
FROM T

Może to przetwarzać obie kolumny jednocześnie, więc najgorszy przypadek jednego pełnego skanowania. Wadą jest to, że nawet jeśli NULLbardzo wcześnie napotka w obu kolumnach zapytanie, nadal skanuje całą resztę tabeli.

Zmienne użytkownika

I można myśleć o trzeciej drodze robi to

BEGIN TRY
DECLARE @B INT, @C INT, @D INT

SELECT 
    @B = CASE WHEN B IS NULL THEN 1 ELSE @B END,
    @C = CASE WHEN C IS NULL THEN 1 ELSE @C END,
    /*Divide by zero error if both @B and @C are 1.
    Might happen next row as no guarantee of order of
    assignments*/
    @D = 1 / (2 - (@B + @C))
FROM T  
OPTION (MAXDOP 1)       
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 8134 /*Divide by zero*/
    BEGIN
    SELECT 'B,C both contain NULLs'
    RETURN;
    END
ELSE
    RETURN;
END CATCH

SELECT ISNULL(@B,0),
       ISNULL(@C,0)

ale nie jest to odpowiednie dla kodu produkcyjnego, ponieważ poprawne zachowanie dla zagregowanego zapytania konkatenacji jest niezdefiniowane. a zakończenie skanowania przez zgłoszenie błędu jest i tak dość okropnym rozwiązaniem.

Czy istnieje inna opcja, która łączy w sobie zalety powyższych podejść?

Edytować

Aby to zaktualizować o wyniki, które otrzymałem w zakresie odczytów dla odpowiedzi przesłanych do tej pory (używając danych testowych @ ypercube)

+----------+------------+------+---------+----------+----------------------+----------+------------------+
|          | 2 * EXISTS | CASE | Kejser  |  Kejser  |        Kejser        | ypercube |       8kb        |
+----------+------------+------+---------+----------+----------------------+----------+------------------+
|          |            |      |         | MAXDOP 1 | HASH GROUP, MAXDOP 1 |          |                  |
| No Nulls |      15208 | 7604 |    8343 | 7604     | 7604                 |    15208 | 8346 (8343+3)    |
| One Null |       7613 | 7604 |    8343 | 7604     | 7604                 |     7620 | 7630 (25+7602+3) |
| Two Null |         23 | 7604 |    8343 | 7604     | 7604                 |       30 | 30 (18+12)       |
+----------+------------+------+---------+----------+----------------------+----------+------------------+

Na odpowiedź @ Thomasa zmieniłem TOP 3na, TOP 2aby potencjalnie umożliwić wcześniejsze wyjście. Domyślnie dostałem plan równoległy dla tej odpowiedzi, więc wypróbowałem go również ze MAXDOP 1wskazówką, aby liczba odczytów była bardziej porównywalna z innymi planami. Byłem nieco zaskoczony wynikami, ponieważ w moim wcześniejszym teście widziałem to zwarcie zapytania bez czytania całej tabeli.

Plan moich danych testowych, że zwarcia są poniżej

Plan dla danych ypercube jest

Dodaje więc do planu operatora sortowania blokującego. Próbowałem też z HASH GROUPpodpowiedzi, ale nadal kończy się czytanie wszystkich wierszy

Wydaje się więc, że kluczem jest skłonienie hash match (flow distinct)operatora do umożliwienia temu planowi zwarcia, ponieważ inne alternatywy i tak blokują i zużywają wszystkie rzędy. Nie sądzę, aby istniała wskazówka, aby wymusić to w sposób konkretny, ale najwyraźniej „ogólnie rzecz biorąc, optymalizator wybiera Flow Distinct, w którym określa, że ​​wymaganych jest mniej wierszy wyjściowych, niż w zestawie danych wejściowych są wyraźne wartości”. .

Dane @ ypercube mają tylko 1 wiersz w każdej kolumnie z NULLwartościami (liczność tabeli = 30300), a szacowane wiersze wchodzące i wychodzące z operatora są oba 1. Uczyniwszy predykat nieco nieprzejrzystym dla optymalizatora, wygenerował plan z operatorem Flow Distinct.

SELECT TOP 2 *
FROM (SELECT DISTINCT 
        CASE WHEN b IS NULL THEN NULL ELSE 'foo' END AS b
      , CASE WHEN c IS NULL THEN NULL ELSE 'bar' END AS c
  FROM test T 
  WHERE LEFT(b,1) + LEFT(c,1) IS NULL
) AS DT 

Edytuj 2

Ostatnia poprawka, która przyszła mi do głowy, polega na tym, że powyższe zapytanie może nadal przetwarzać więcej wierszy niż to konieczne, w przypadku gdy pierwszy napotkany wiersz NULLma wartość NULL w obu kolumnach Bi C. Będzie kontynuować skanowanie zamiast natychmiastowego wyjścia. Jednym ze sposobów uniknięcia tego byłoby rozłożenie wierszy podczas ich skanowania. Więc moja ostatnia poprawka do odpowiedzi Thomasa Kejsera znajduje się poniżej

SELECT DISTINCT TOP 2 NullExists
FROM test T 
CROSS APPLY (VALUES(CASE WHEN b IS NULL THEN 'b' END),
                   (CASE WHEN c IS NULL THEN 'c' END)) V(NullExists)
WHERE NullExists IS NOT NULL

Prawdopodobnie byłoby lepiej, gdyby predykat był, WHERE (b IS NULL OR c IS NULL) AND NullExists IS NOT NULLale w porównaniu z poprzednimi danymi testowymi, że nie daje mi się planu z Flow Distinct, podczas gdy NullExists IS NOT NULLten robi (plan poniżej).

Co powiesz na:

SELECT TOP 3 *
FROM (SELECT DISTINCT 
        CASE WHEN B IS NULL THEN NULL ELSE 'foo' END AS B
        , CASE WHEN C IS NULL THEN NULL ELSE 'bar' END AS C
  FROM T 
  WHERE 
    (B IS NULL AND C IS NOT NULL) 
    OR (B IS NOT NULL AND C IS NULL) 
    OR (B IS NULL AND C IS NULL)
) AS DT

Jak rozumiem pytanie, chcesz wiedzieć, czy w którejkolwiek z kolumn istnieje wartość null, a nie zwracać wiersze, w których B lub C ma wartość null. Jeśli tak, to dlaczego nie:

Select Top 1 'B as nulls' As Col
From T
Where T.B Is Null
Union All
Select Top 1 'C as nulls'
From T
Where T.C Is Null

Na moim stanowisku testowym z SQL 2008 R2 i milionem wierszy otrzymałem następujące wyniki w ms z zakładki Statystyka klienta:

Kejser                          2907,2875,2829,3576,3103
ypercube                        2454,1738,1743,1765,2305
OP single aggregate solution    (stopped after 120,000 ms) Wouldn't even finish
My solution                     1619,1564,1665,1675,1674

Jeśli dodasz wskazówkę nolock, wyniki będą jeszcze szybsze:

Select Top 1 'B as nulls' As Col
From T With(Nolock)
Where T.B Is Null
Union All
Select Top 1 'C as nulls'
From T With(Nolock)
Where T.C Is Null

My solution (with nolock)       42,70,94,138,120

W celach informacyjnych użyłem generatora SQL Red-gate do wygenerowania danych. Z mojego miliona wierszy 9 886 wierszy miało zerową wartość B, a 10 019 miało zerową wartość C.

W tej serii testów każdy wiersz w kolumnie B ma wartość:

Kejser                          245200  Scan count 1, logical reads 367259, physical reads 858, read-ahead reads 367278
                                250540  Scan count 1, logical reads 367259, physical reads 860, read-ahead reads 367280

ypercube(1)                     249137  Scan count 2, logical reads 367276, physical reads 850, read-ahead reads 367278
                                248276  Scan count 2, logical reads 367276, physical reads 869, read-ahead reads 368765

My solution                     250348  Scan count 2, logical reads 367276, physical reads 858, read-ahead reads 367278
                                250327  Scan count 2, logical reads 367276, physical reads 854, read-ahead reads 367278

Przed każdym testem (oba zestawy) biegałem CHECKPOINTi DBCC DROPCLEANBUFFERS.

Oto wyniki, gdy w tabeli nie ma wartości null. Zauważ, że 2 istniejące rozwiązanie dostarczone przez ypercube jest prawie identyczne z moim pod względem odczytów i czasu wykonania. Uważam (my), że wynika to z zalet wersji Enterprise / Developer korzystającej z zaawansowanego skanowania . Jeśli korzystasz tylko z wersji Standard lub niższej, rozwiązanie Kejsera może być najszybszym rozwiązaniem.

Kejser                          248875  Scan count 1, logical reads 367259, physical reads 860, read-ahead reads 367290

ypercube(1)                     243349  Scan count 2, logical reads 367265, physical reads 851, read-ahead reads 367278
                                242729  Scan count 2, logical reads 367265, physical reads 858, read-ahead reads 367276
                                242531  Scan count 2, logical reads 367265, physical reads 855, read-ahead reads 367278

My solution                     243094  Scan count 2, logical reads 367265, physical reads 857, read-ahead reads 367278
                                243444  Scan count 2, logical reads 367265, physical reads 857, read-ahead reads 367278

Czy IFoświadczenia są dozwolone?

Powinno to umożliwić potwierdzenie istnienia B lub C przy jednym przejściu przez tabelę:

DECLARE 
  @A INT, 
  @B CHAR(10), 
  @C CHAR(10)

SET @B = 'X'
SET @C = 'X'

SELECT TOP 1 
  @A = A, 
  @B = B, 
  @C = C
FROM T 
WHERE B IS NULL OR C IS NULL 

IF @@ROWCOUNT = 0 
BEGIN 
  SELECT 'No nulls'
  RETURN
END

IF @B IS NULL AND @C IS NULL
BEGIN
  SELECT 'Both null'
  RETURN
END 

IF @B IS NULL 
BEGIN
  SELECT TOP 1 
    @C = C
  FROM T
  WHERE A > @A
  AND C IS NULL

  IF @B IS NULL AND @C IS NULL 
  BEGIN
    SELECT 'Both null'
    RETURN
  END
  ELSE
  BEGIN
    SELECT 'B is null'
    RETURN
  END
END

IF @C IS NULL 
BEGIN
  SELECT TOP 1 
    @B = B
  FROM T 
  WHERE A > @A
  AND B IS NULL

  IF @C IS NULL AND @B IS NULL
  BEGIN
    SELECT 'Both null'
    RETURN
  END
  ELSE
  BEGIN
    SELECT 'C is null'
    RETURN
  END
END      

Testowane w SQL-Fiddle w wersjach: 2008 R2 i 2012 z 30 000 wierszy.

• Do EXISTSzapytania ilustruje ogromne korzyści w zakresie efektywności, gdy stwierdzi wartości null wcześnie - co jest spodziewane.
• Z EXISTSzapytaniem uzyskuję lepszą wydajność - we wszystkich przypadkach w 2012 r., Czego nie potrafię wyjaśnić.
• W 2008R2, kiedy nie ma wartości Null, jest wolniejszy niż pozostałe 2 zapytania. Im wcześniej znajdzie Nulls, tym szybciej się pojawi, a gdy obie kolumny mają nully wcześniej, jest to znacznie szybsze niż pozostałe 2 zapytania.
• Zapytanie Thomasa Kejsera wydaje się działać nieco, ale stale lepiej w 2012 roku, a gorzej w 2008R2 w porównaniu do CASEzapytania Martina .
• Wersja 2012 wydaje się mieć znacznie lepszą wydajność. Może to mieć jednak związek z ustawieniami serwerów SQL-Fiddle, a nie tylko z ulepszeniami optymalizatora.

Zapytania i terminy. Czasy, w których wykonano:

• 1. bez żadnych zer
• 2. z kolumną Bmającą jedną NULLpo małej id.
• 3 miejsce, w którym obie kolumny mają po jednej NULLprzy małych identyfikatorach.

Proszę bardzo (problem dotyczy planów, spróbuję ponownie później. Na razie skorzystaj z linków):

Zapytanie z 2 podzapytaniami EXISTS

SELECT 
      CASE WHEN EXISTS (SELECT * FROM test WHERE b IS NULL)
             THEN 1 ELSE 0 
      END AS B,
      CASE WHEN EXISTS (SELECT * FROM test WHERE c IS NULL)
             THEN 1 ELSE 0 
      END AS C ;

-------------------------------------
Times in ms (2008R2): 1344 - 596 -  1  
Times in ms   (2012):   26 -  14 -  2

Pojedyncze zapytanie agregacyjne Martina Smitha

SELECT 
    MAX(CASE WHEN b IS NULL THEN 1 ELSE 0 END) AS B,
    MAX(CASE WHEN c IS NULL THEN 1 ELSE 0 END) AS C
FROM test ;

--------------------------------------
Times in ms (2008R2):  558 - 553 - 516  
Times in ms   (2012):   37 -  35 -  36

Pytanie Thomasa Kejsera

SELECT TOP 3 *
FROM (SELECT DISTINCT 
        CASE WHEN B IS NULL THEN NULL ELSE 'foo' END AS b
      , CASE WHEN C IS NULL THEN NULL ELSE 'bar' END AS c
  FROM test T 
  WHERE 
    (B IS NULL AND C IS NOT NULL) 
    OR (B IS NOT NULL AND C IS NULL) 
    OR (B IS NULL AND C IS NULL)
) AS DT ;

--------------------------------------
Times in ms (2008R2):  859 - 705 - 668  
Times in ms   (2012):   24 -  19 -  18

Moja sugestia (1)

WITH tmp1 AS
  ( SELECT TOP (1) 
        id, b, c
    FROM test
    WHERE b IS NULL OR c IS NULL
    ORDER BY id 
  ) 

  SELECT 
      tmp1.*, 
      NULL AS id2, NULL AS b2, NULL AS c2
  FROM tmp1
UNION ALL
  SELECT *
  FROM
    ( SELECT TOP (1)
          tmp1.id, tmp1.b, tmp1.c,
          test.id AS id2, test.b AS b2, test.c AS c2 
      FROM test
        CROSS JOIN tmp1
      WHERE test.id >= tmp1.id
        AND ( test.b IS NULL AND tmp1.c IS NULL
           OR tmp1.b IS NULL AND test.c IS NULL
            )
      ORDER BY test.id
    ) AS x ;

--------------------------------------
Times in ms (2008R2): 1089 - 572 -  16   
Times in ms   (2012):   28 -  15 -   1

Wymaga dopracowania danych wyjściowych, ale wydajność jest podobna do EXISTSzapytania. Pomyślałem, że byłoby lepiej, gdy nie ma wartości zerowych, ale testy pokazują, że tak nie jest.

Sugestia (2)

Próbując uprościć logikę:

CREATE TABLE tmp
( id INT
, b CHAR(1000)
, c CHAR(1000)
) ;

DELETE  FROM tmp ;

INSERT INTO tmp 
    SELECT TOP (1) 
        id, b, c
    FROM test
    WHERE b IS NULL OR c IS NULL
    ORDER BY id  ; 

INSERT INTO tmp 
    SELECT TOP (1)
        test.id, test.b, test.c 
      FROM test
        JOIN tmp 
          ON test.id >= tmp.id
      WHERE ( test.b IS NULL AND tmp.c IS NULL
           OR tmp.b IS NULL AND test.c IS NULL
            )
      ORDER BY test.id ;

SELECT *
FROM tmp ;

Wydaje się, że działa lepiej w 2008R2 niż poprzednia sugestia, ale gorzej w 2012 r. (Być może drugą wersję INSERTmożna przepisać, używając IFodpowiedzi @ 8kb):

------------------------------------------
Times in ms (2008R2): 416+6 - 1+127 -  1+1   
Times in ms   (2012):  14+1 - 0+27  -  0+29

Podczas korzystania z EXISTS SQL Server wie, że przeprowadzasz kontrolę istnienia. Gdy znajdzie pierwszą pasującą wartość, zwraca PRAWDA i przestaje szukać.

po skonkatowaniu 2 kolumn i jeśli jakakolwiek jest pusta, wynik będzie zerowy

na przykład

null + 'a' = null

więc sprawdź ten kod

IF EXISTS (SELECT 1 FROM T WHERE B+C is null)
SELECT Top 1 ISNULL(B,'B ') + ISNULL(C,'C') as [Nullcolumn] FROM T WHERE B+C is null

Co powiesz na:

select 
    exists(T.B is null) as 'B is null',
    exists(T.C is null) as 'C is null'
from T;

Jeśli to zadziała (nie przetestowałem tego), dałoby tabelę z jednym wierszem i 2 kolumnami, każda z nich PRAWDA lub FAŁSZ. Nie przetestowałem wydajności.