funkcja zawiesza się z operacją zerową

Utworzyłem funkcję, która akceptuje datę początkową i końcową, przy czym data końcowa jest opcjonalna. Następnie napisałem CASEw filtrze, aby użyć daty początkowej, jeśli nie minie data końcowa.

CASE WHEN @dateEnd IS NULL
    THEN @dateStart
    ELSE @dateEnd
END

Kiedy wywołuję funkcję dla ostatniego miesiąca danych:

SELECT * FROM theFunction ('2013-06-01', NULL)

... zapytanie zawiesza się. Jeśli podam datę końcową:

SELECT * FROM theFunction ('2013-06-01', '2013-06-01')

... wynik jest zwracany normalnie. Wyjąłem kod z funkcji i uruchomiłem go dobrze w oknie zapytania. Nie mogę też powielić problemu skrzypce. Zapytanie takie jak:

SELECT * FROM theFunction ('2013-04-01', '2013-06-01')

... działa również dobrze.

Czy w zapytaniu (poniżej) jest coś, co może powodować zawieszanie się funkcji po NULLprzekazaniu a dla daty końcowej?

SQL Fiddle

• Plan wykonania dlaSELECT * FROM theFunction ('2013-06-01', '2013-06-01')
• Szacowany plan dlaSELECT * FROM theFunction ('2013-06-01', NULL)

Część twojego początkowego zapytania jest następująca.

  FROM   [dbo].[calendar] a
          LEFT JOIN [dbo].[colleagueList] b
            ON b.[Date] = a.d
   WHERE  DAY(a.[d]) = 1
          AND a.[d] BETWEEN @dateStart AND COALESCE(@dateEnd,@dateStart) 

Ta sekcja planu jest pokazana poniżej

Twoje poprawione zapytanie BETWEEN @dateStart AND ISNULL(@dateEnd,@dateStart)ma to samo dla tego samego sprzężenia

Wydaje się, że różnica polega na tym, że ISNULLupraszcza to dalej, w wyniku czego otrzymujesz dokładniejsze statystyki liczności przechodząc do następnego łączenia. Jest to funkcja ceniona w tabeli wbudowanej i wywoływana jest z wartościami dosłownymi, aby mogła zrobić coś takiego.

 a.[d] BETWEEN @dateStart AND ISNULL(@dateEnd,@dateStart) 
 a.[d] BETWEEN '2013-06-01' AND ISNULL(NULL,'2013-06-01') 
 a.[d] BETWEEN '2013-06-01' AND '2013-06-01'
 a.[d] = '2013-06-01'

A ponieważ istnieje predykat sprzężenia równań, b.[Date] = a.dplan pokazuje również predykat równości b.[Date] = '2013-06-01'. W rezultacie oszacowanie liczności 28,393wierszy prawdopodobnie będzie dość dokładne.

W przypadku wersji CASE/, COALESCEgdy @dateStarti @dateEndsą tej samej wartości, upraszcza to OK do tego samego wyrażenia równości i daje ten sam plan, ale kiedy @dateStart = '2013-06-01'i @dateEnd IS NULLidzie tylko tak daleko, jak

a.[d]>='2013-06-01' AND a.[Date]<=CASE WHEN (1) THEN '2013-06-01' ELSE NULL END

które stosuje się również jako dorozumiany wyrok ColleagueList. Szacowana liczba wierszy tym razem to 79.8rzędy.

Następnym dołączeniem jest

   LEFT JOIN colleagueTime
     ON colleagueTime.TC_DATE = colleagueList.Date
        AND colleagueTime.ASSOC_ID = CAST(colleagueList.ID AS VARCHAR(10)) 

colleagueTimejest 3,249,590tabelą wierszy, która jest (ponownie) najwyraźniej stertą bez przydatnych indeksów.

Ta rozbieżność w szacunkach wpływa na zastosowany wybór łączenia. ISNULLPlan wybiera hash dołącz że właśnie przeszukuje tabelę raz. COALESCEPlan wybiera zagnieżdżone pętle sprzężenia i szacuje się, że będzie ona nadal wystarczy zeskanować tabelę raz i móc szpula wynik i powtórzyć go 78 razy. tj. szacuje, że skorelowane parametry nie zmienią się.

Z faktu, że plan zagnieżdżonych pętli nadal działał po dwóch godzinach, założenie o pojedynczym skanie colleagueTimewydaje się być wysoce niedokładne.

Co do tego, dlaczego szacunkowa liczba wierszy między dwoma złączeniami jest o wiele niższa, nie jestem pewien, nie widząc statystyk w tabelach. Jedynym sposobem, w jaki udało mi się przesunąć szacunkową liczbę wierszy tak dużo w moich testach, było dodanie obciążenia NULLwierszy (zmniejszyło to szacunkową liczbę wierszy, chociaż rzeczywista liczba zwróconych wierszy pozostała taka sama).

Szacowana liczba wierszy w COALESCEplanie z moimi danymi testowymi była rzędu

number of rows matching >= condition * 30% * (proportion of rows in the table not null)

Lub w SQL

SELECT 1E0 * COUNT([Date]) / COUNT(*) * ( COUNT(CASE
                                                  WHEN [Date] >= '2013-06-01' THEN 1
                                                END) * 0.30 )
FROM   [dbo].[colleagueList] 

ale to nie odpowiada twojemu komentarzowi, że kolumna nie ma NULLwartości.

Wygląda na to, że wystąpił problem z typami danych. ISNULLnaprawiono problem (dzięki ypercube ). Po kilku badań, COALESCEjest równoznaczne z CASEoświadczeniem, że byłem przy użyciu:

CASE
   WHEN (expression1 IS NOT NULL) THEN expression1
   WHEN (expression2 IS NOT NULL) THEN expression2
   ...
   ELSE expressionN
END

Paul White wyjaśnia, że:

COALESCE( expression [ ,...n ] ) zwraca typ danych wyrażenia o najwyższym priorytecie typu danych.

ISNULL(check_expression, replacement_value) zwraca ten sam typ co wyrażenie_kontrolne.

Aby uniknąć problemów z typem danych, wydaje się, że ISNULLjest to odpowiednia funkcja do obsługi tylko dwóch wyrażeń.

Fragmenty planu XML

Używając planu XMLCASE , wyrażenie 2 to NULL:

SELECT * FROM theFunction ('2013-06-01', NULL)

<ScalarOperator ScalarString="CASE WHEN (1) THEN '2013-06-01' ELSE NULL END">
  <IF>
    <Condition>
      <ScalarOperator>
        <Const ConstValue="(1)"/>
      </ScalarOperator>
    </Condition>
    <Then>
      <ScalarOperator>
        <Const ConstValue="'2013-06-01'"/>
      </ScalarOperator>
    </Then>
    <Else>
      <ScalarOperator>
        <Const ConstValue="NULL"/>
      </ScalarOperator>
    </Else>
  </IF>
</ScalarOperator>

Używając planu XMLCASE , wyrażenie 2 jest datą:

SELECT * FROM theFunction ('2013-06-01', '2013-06-01')

<ScalarOperator ScalarString="CASE WHEN [Expr1035]=(0) THEN NULL ELSE [Expr1036] END">
  <IF>
    <Condition>
      <ScalarOperator>
        <Compare CompareOp="EQ">
          <ScalarOperator>
            <Identifier>
              <ColumnReference Column="Expr1035"/>
            </Identifier>
          </ScalarOperator>
          <ScalarOperator>
            <Const ConstValue="(0)"/>
          </ScalarOperator>
        </Compare>
      </ScalarOperator>
    </Condition>
    <Then>
      <ScalarOperator>
        <Const ConstValue="NULL"/>
      </ScalarOperator>
      </Then>
    <Else>
      <ScalarOperator>
        <Identifier>
          <ColumnReference Column="Expr1036"/>
        </Identifier>
      </ScalarOperator>
    </Else>
  </IF>
</ScalarOperator>

Używając planu XMLISNULL , wyrażenie 2 to NULL:

SELECT * FROM theFunction ('2013-06-01', NULL)

<ScalarOperator ScalarString="CASE WHEN [Expr1035]=(0) THEN NULL ELSE [Expr1036] END">
  <IF>
    <Condition>
      <ScalarOperator>
        <Compare CompareOp="EQ">
          <ScalarOperator>
            <Identifier>
              <ColumnReference Column="Expr1035"/>
            </Identifier>
          </ScalarOperator>
          <ScalarOperator>
            <Const ConstValue="(0)"/>
          </ScalarOperator>
        </Compare>
      </ScalarOperator>
    </Condition>
    <Then>
      <ScalarOperator>
        <Const ConstValue="NULL"/>
      </ScalarOperator>
    </Then>
    <Else>
      <ScalarOperator>
        <Identifier>
          <ColumnReference Column="Expr1036"/>
        </Identifier>
      </ScalarOperator>
    </Else>
  </IF>
</ScalarOperator>

Używając planu XMLISNULL , wyrażenie 2 jest datą:

SELECT * FROM theFunction ('2013-06-01', '2013-06-01')

<ScalarOperator ScalarString="CASE WHEN [Expr1035]=(0) THEN NULL ELSE [Expr1036] END">
  <IF>
    <Condition>
      <ScalarOperator>
        <Compare CompareOp="EQ">
          <ScalarOperator>
            <Identifier>
              <ColumnReference Column="Expr1035"/>
            </Identifier>
          </ScalarOperator>
          <ScalarOperator>
            <Const ConstValue="(0)"/>
          </ScalarOperator>
        </Compare>
      </ScalarOperator>
    </Condition>
    <Then>
      <ScalarOperator>
        <Const ConstValue="NULL"/>
      </ScalarOperator>
    </Then>
    <Else>
      <ScalarOperator>
        <Identifier>
          <ColumnReference Column="Expr1036"/>
        </Identifier>
      </ScalarOperator>
    </Else>
  </IF>
</ScalarOperator>