Oprogramowanie generuje wiele nieoptymalnych zapytań:

SELECT
    <List of Columns>
FROM <Table>
WHERE(
      ([COL1] = UPPER('CONST_VALUE') AND [COL2] = UPPER('v1')) OR
      ([COL1] = UPPER('CONST_VALUE') AND [COL2] = UPPER('v2')) OR
      ([COL1] = UPPER('CONST_VALUE') AND [COL2] = UPPER('v4')) OR
      ([COL1] = UPPER('CONST_VALUE') AND [COL2] = UPPER('v6')) OR
      ([COL1] = UPPER('CONST_VALUE') AND [COL2] = UPPER('v8')) OR
      <...>
     )

Plan wykonania: https://www.brentozar.com/pastetheplan/?id=rJGtaBzSU

Wykonanie tego zapytania skutkuje wyszukiwaniem indeksu, którego wykonanie zajmuje około 1 s. Refaktoryzacja zapytania na następującą instrukcję skutkuje czasem wykonania 3ms:

SELECT
    <List of Columns>
FROM <Table>
WHERE([COL1] = UPPER('CONST_VALUE') AND (
    [COL2] = UPPER('v1') OR
    [COL2] = UPPER('v2') OR
    [COL2] = UPPER('v4') OR
    [COL2] = UPPER('v6') OR
    [COL2] = UPPER('v8') OR
    <...>
    ))

Indeksy wyglądają optymalnie afaik, indeks na COL1 i COL2 zawierający wszystkie wybrane inne kolumny. Skoro nie możemy na razie zmienić oprogramowania, czy istnieje sposób na przyspieszenie czasu wykonywania? Dodanie innego rodzaju indeksu. Myślałem również o czymś takim jak przepisywanie zapytań lub tym podobne, ale nie mogłem znaleźć czegoś takiego w SQL Server.

2
mash 8 marzec 2020, 02:36

2 odpowiedzi

Najlepsza odpowiedź

Jeśli możesz wprowadzić zmiany w zapytaniu, usuń UPPER - Można to łatwo usunąć, jeśli używasz sortowania bez rozróżniania wielkości liter (zdecydowanie najczęstszy przypadek) - w przeciwnym razie będziesz musiał dodać logikę, aby zapewnić wartości są pisane wielkimi literami przed dodaniem do zapytania. UPPER nie jest zwinięty na stałe i może dawać gorsze plany niż proste literały łańcuchowe, jak pokazano w różnych przykładach poniżej.

Przykładowe dane

CREATE TABLE [Table]
(
[COL1] VARCHAR(20),
[COL2] VARCHAR(10),
PRIMARY KEY ([COL1],[COL2])
)

INSERT INTO [Table]
SELECT TOP 100 'CONST_VALUE',  CONCAT('v', ROW_NUMBER() OVER (ORDER BY @@SPID))
FROM sys.all_columns

Zapytanie 1

SELECT *
FROM [Table]
WHERE(
      ([COL1] = 'CONST_VALUE' AND [COL2] = 'V1') OR
      ([COL1] = 'CONST_VALUE' AND [COL2] = 'V1') OR
      ([COL1] = 'CONST_VALUE' AND [COL2] = 'V4') 
     )

Plan wykonania tego ma operator przeszukiwania indeksu. Spojrzenie na właściwości planu pokazuje, że szukanie faktycznie zawiera dwa różne predykaty wyszukiwania wielokolumnowego (a nie trzy wyszukiwania. Byłoby błędem wykonanie wyszukiwania „V1” dwa razy i zwrócenie tych wierszy dwa razy, mimo że pojawia się w {{X0 }} dwukrotnie)

enter image description here

Zapytanie 2

SELECT *
FROM [Table] 
WHERE(
      ([COL1] = 'CONST_VALUE' AND [COL2] = UPPER('v1')) OR
      ([COL1] = 'CONST_VALUE' AND [COL2] = UPPER('V1')) OR
      ([COL1] = 'CONST_VALUE' AND [COL2] = UPPER('v2')) 
     )

Ten plan wykonania wygląda obiecująco, ale po dokładniejszym przyjrzeniu się, wyszukiwanie odbywa się tylko w jednej kolumnie COL1 - ponieważ wszystkie wiersze w tabeli mają wartość 'CONST_VALUE' w tym przypadku wyszukiwanie nic nie daje i cała praca jest wykonana z resztowym predykatem.

enter image description here

Zapytanie 3

SELECT *
FROM [Table] WITH (FORCESEEK)
WHERE(
      ([COL1] = 'CONST_VALUE' AND [COL2] = UPPER('v1')) OR
      ([COL1] = 'CONST_VALUE' AND [COL2] = UPPER('V1')) OR
      ([COL1] = 'CONST_VALUE' AND [COL2] = UPPER('v2')) 
     )

To jest to samo, co poprzednio, ale z dodaną wskazówką FORCESEEK. Wyniki UPPER nie są z jakiegoś powodu składane na stałe w czasie kompilacji, więc dodaje dodatkowe operatory do planu, aby oszacować UPPER, a następnie zwinąć identyczne wyniki, aby wykonać dwa potrzebne poszukiwania indeksów wielokolumnowych .

enter image description here

Zapytanie 4

SELECT *
FROM [Table] 
WHERE(
      ([COL1] = UPPER('CONST_VALUE') AND [COL2] = UPPER('v1')) OR
      ([COL1] = UPPER('CONST_VALUE') AND [COL2] = UPPER('V1')) OR
      ([COL1] = UPPER('CONST_VALUE') AND [COL2] = UPPER('v2')) 
     )

Teraz SQL Server poddaje się i wykonuje skanowanie

enter image description here

Zapytanie 5

SELECT *
FROM [Table] 
WHERE [COL1] = UPPER('CONST_VALUE') AND  
(
      [COL2] = UPPER('v1') OR
      [COL2] = UPPER('V1') OR
      [COL2] = UPPER('v2') 
)

To przepisanie daje taki sam plan wykonania jak zapytanie 2 - z poszukiwaniem na Col1 i predykatem resztowym w Col2, nie jest to przydatne w przypadku moich przykładowych danych, ale byłoby to w bardziej realistycznych przypadkach.

Zapytanie 6

SELECT *
FROM sys.all_objects
where 'v1' <> 'v1'

SQL Server wykrywa sprzeczność w czasie kompilacji i przedstawia bardzo prosty plan

enter image description here

Zapytanie 7

SELECT *
FROM sys.all_objects
where UPPER('v1') <> UPPER('v1')

Pomimo faktu, że wyrażenia są deterministyczne i mają dokładnie te same wartości wejściowe, nie następuje wykrycie sprzeczności

enter image description here

1
Community 20 czerwiec 2020, 09:12

Problem polega na tym, że optymalizator zapytań niewiele może zrobić w tym przypadku. Wpadasz w pułapkę podobną do IN z wieloma argumentami -> rozwiązanie awaryjne to skanowanie tabeli.

Umieść zmienną tabeli z wartościami porównawczymi, 2 polami i kluczem podstawowym, a następnie możesz użyć tej zmiennej tabeli w sprzężeniu, a indeks klucza podstawowego zapewnia statystyki do pracy z optymalizatorem zapytań.

0
TomTom 9 marzec 2020, 10:47