znaczacy > comp.* > comp.bazy-danych

Grzegorz Danowski (21.06.2006, 22:15)
Witam!

Mam wiele różnych zapytań odwołujących się do tabeli z fakturami. Są one
bardzo do siebie podobne, więc postanowiłem zrobić bazowe "sparametryzowane"
widoki, które będą podstawą do prostszych zapytań.

W tym celu stworzyłem tabelę z kryteriami:

Create Table Criteria(
CriteriaName Varchar(50) Not Null,
UserName Varchar(50) Not Null,
CriteriaValue Varchar(100) Not Null,
Constraint UK_Criteria Unique
(UserName, CriteriaValue)
)
Go

Dwa przykładowe kryteria:

Insert Into Criteria(UserName, CriteriaName, CriteriaValue)
Values('dbo', 'DateFrom', '20050101')
Go

Insert Into Criteria(UserName, CriteriaName, CriteriaValue)
Values('dbo', 'DateTo', '20051231')
Go

A następnie funkcję zwracającą wartość wybranego kryterium:

Create Function GetCriteriaByName(
@CriteriaName Varchar(50))
Returns Varchar(100)
As
Begin
Declare @CriteriaValue Varchar(100)
Select @CriteriaValue = CriteriaValue
From
Criteria
Where
UserName = User --kryteria bieżącego usera
And
CriteriaName = @CriteriaName
Return @CriteriaValue
End
Go

Mając tę funkcję zrobiłem "sparametryzowany" widok:

Create View SelectedInvoices
As
Select *
From
Invoices
Where
InvoiceDate Between
dbo.GetCriteriaByName('DateFrom')
And
dbo.GetCriteriaByName('DateTo')
Go

Wszystko działa, ale... strasznie wolno.
Proste zapytanie oparte na powyższym widoku (Select Top 1 * From
SelectedInvoices) wykonuje się ponad 20 sekund.
Oczywiście pole InvoiceDate jest zaindeksowane, poza tym w tabeli jest też
PK.

Dla porównania analogiczne zapytanie oparte na widoku z parametrami
wstawionymi na sztywno:

Create View SelectedInvoicesOld
As
Select *
From
Invoices
Where
InvoiceDate Between
'20050101' And '20051231'

wykonuje się w ułamku sekundy.

Najwyraźniej SQL Server wykonuje moją funkcję (GetCriteriaByName) dla
każdego rekordu, mimo, że parametrem tej funkcji jest ta sama wartość. Czy
da się zmienić ten stan rzeczy?
Widzę, że jest coś takiego jak właściwość funkcji "IsDeterministic", może to
jest jakiś trop?

A może zamiast kombinować ze "sparametryzowanymi" widokami, powinienem
zrobić funkcje ściągające odpowiednie dane, czyli np.:

Create Function SelectedInvoicesF(
@DateFrom DateTime, @DateTo DateTime)
Returns Table
As
Return
(
Select *
From
Invoices
Where
InvoiceDate Between @DateFrom And @DateTo
)

Proste zapytania korzystające z takiej funkcji wykonują się szybko. Ale czy
w przypadku dużej liczby rekordów wybranych przez funkcje oraz
skomplikowanych złączeń na zbiorze wynikowym SQL Server skorzysta z indeksów
założonych w tabeli Invoices? Miałem wrażenie, że w 2000 był z tym problem,
ale może się myle, albo też coś się zmieniło w 2005?

Pozdrawiam
Grzegorz
Marcin A. Guzowski (24.06.2006, 03:18)
Grzegorz Danowski napisał(a):
> Witam!
> Mam wiele różnych zapytań odwołujących się do tabeli z fakturami. Są one
> bardzo do siebie podobne, więc postanowiłem zrobić bazowe
> "sparametryzowane" widoki, które będą podstawą do prostszych zapytań.
> (..)


Generalnie źle rozwiązałeś całą sprawę stosując funkcje w taki sposób.
Deterministyczność funkcji użytkownika nie może być narzucona, aby funkcja
została przez optimizera za takową uznana, musi spełnić cały szerego warunków,
których w opisanym przypadku nie spełnisz (choćby dlatego, że masz w ciele
funkcji odwołanie do tabeli). SQL Server wywołuje więc funkcję per wiersz,
a biorąc pod uwagę jeszcze kilka kwiatków optymalizacyjnych zw. z funkcjami -
działa jak działa.

Proponuję przebudowanie koncepcji:
Cel - uproszenie zapytań końcowych.

Wariant 1

Tworzysz więc tabelę z kryteriami (dbo.Criteria), a następnie - widoki.
Etap z funkcjami jest niepotrzebny. Gdzie możesz - użyjesz joinów z tabelą kryteriów,
gdzie nie możesz - użyjesz podzapytań do tabeli z kryteriami. Wtedy całość będzie
zoptymalizowana - włącznie z podzapytaniami, (czyli m.in. podzapytanie wykona się raz,
a nie jak funkcja - per wiersz).

CREATE VIEW dbo.SelectedInvoices
AS
SELECT * FROM dbo.Invoices
WHERE
InvoiceDate BETWEEN
(SELECT CriteriaValue FROM dbo.Criteria WHERE CriteriaName = 'DateFrom' AND UserName = ...)
AND
(SELECT CriteriaValue FROM dbo.Criteria WHERE CriteriaName = 'DateTo' AND UserName = ...)

(dla pewności możesz dodać TOP 1 w podzapytaniach)

Wariant 2

Procedury. Zamiast widoków i warunków w WERE - procedury z tymi parametrami w wywołaniu.
Wtedy nie masz problemu z pobieraniem z dbo.Criteria, w ciele możesz definiować sobie
różne zmienne itd. Plany wykonań procedur też są trzymane, więc jeśli nie będzie następować
ich ciągła rekompilacja i zoptymalizujesz zapytania - będzie śmigać.
Grzegorz Danowski (26.06.2006, 15:24)
Użytkownik "Marcin A. Guzowski" <tu_wstaw_moje_imie> napisał w
wiadomości news:4vp1
[..]
> AND
> (SELECT CriteriaValue FROM dbo.Criteria WHERE CriteriaName = 'DateTo'
> AND UserName = ...)


Przed wysłaniem na grupę swego pierwszego postu testowałem i takie
rozwiązanie (niemal identyczne) i było ono kilkakrotnie wolniejsze (1,5 s.)
w stosunku do sztywnego wpisania parametrów do zapytania (0,1 s.).

> (dla pewności możesz dodać TOP 1 w podzapytaniach)


Dodanie Top 1 dodało skrzydeł zapytaniu - teraz jest już niewiele wolniejsze
(0,2 s.) od zapytania ze sztywnymi parametrami. Dzięki.

> Wariant 2
> Procedury. Zamiast widoków i warunków w WERE - procedury z tymi
> parametrami w wywołaniu.
> Wtedy nie masz problemu z pobieraniem z dbo.Criteria, w ciele możesz
> definiować sobie
> różne zmienne itd. Plany wykonań procedur też są trzymane, więc jeśli nie
> będzie następować
> ich ciągła rekompilacja i zoptymalizujesz zapytania - będzie śmigać.


Z procedurami jest ten problem, że zapytania są dość złożone a równocześnie
jest wiele bardzo podobnych zapytań do siebie, np. filtrowanie faktur tylko
z określonego okresu, tylko określonej grupy towarowej i wybranego klienta.
Stąd "sparametryzowane" widoki były pomysłem na uniknięcie dublowania kodu.

Pozdrawiam
Grzegorz
Podobne wątki