=?iso-8859-2?Q?=5BMS_SQL=5D_Czy_mo=BFna_wywo=B3a=E6_funkcje_t ylko_raz_dla?==?iso-8859-2?Q?_ca=B3ego_zbioru_=BCr=F3d=B3owego=3F?=
Grzegorz Danowski - 22-06-2006 00:31
=?iso-8859-2?Q?=5BMS_SQL=5D_Czy_mo=BFna_wywo=B3a=E6_funkcje_t ylko_raz_dla?==?iso-8859-2?Q?_ca=B3ego_zbioru_=BCr=F3d=B3owego=3F?=
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 01:05
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ć.
--
Pozdrawiam,
Marcin Guzowski
http://guzowski.info
Grzegorz Danowski - 27-06-2006 00:08
=?iso-8859-2?Q?Re:_=5BMS_SQL=5D_Czy_mo=BFna_wywo=B3a=E6_funkc je_tylko_raz?==?iso-8859-2?Q?_dla_ca=B3ego_zbioru_=BCr=F3d=B3owego=3F?=
Użytkownik "Marcin A. Guzowski" <tu_wstaw_moje_imie@guzowski.info> napisał w
wiadomości news:e7hstn$4vp$1@atlantis.news.tpi.pl...
>> postanowiłem zrobić bazowe "sparametryzowane" widoki, które będą podstawą
>> do prostszych zapytań.
(...)
> 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 = ...)
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
zanotowane.pldoc.pisz.plpdf.pisz.pleffulla.pev.pl
|
[oracle] - Oracle SQL Developer - co to jest SID?
MS Sql Server 2000 i Server 2003
[MS SQL + kontakty outlook]CZy kontakty moge być zewnętrzne?
[oracle] Baza danych do kursy Introduction to Oracle9i:PL/SQL ? Skąd ją pobrać ?
ms sql 2000 i ms server 2003 problem z logowaniem
[MS SQL 2000] - wielkosc strony danych a wielkosc klastra dyskowego
[ms sql] Przeniesienie bazy z wszystkimi dodatkami (dts, jobs, replikacja) na inny server ?
konkurs SQL Injection - 10 PLN na GSM dla pierwszej osoby, ktora dostanie sie do bazy
Re: Gdzie mozna jeszcze kupic Microsoft SQL 2000 Enterprise Edition??
[ps] eksport setow z automatu
zanotowane.pldoc.pisz.plpdf.pisz.platanvarne633.opx.pl
Cytat
Decede mihi sole - nie zasłaniaj mi słonca.
Gdy kogoś kochasz, jesteś jak stworzyciel świata - na cokolwiek spojrzysz, nabiera to kształtu, wypełnia się barwą, światłem. Powietrze przytula się do ciebie, choćby był mróz, a ty masz w sobie tyle radości, że musisz ją rozdawać wokoło, bo się w tobie nie mieści
Hoc fac - tak czyń.
A tergo - od tyłu; z tyłu.
I czarne włosy posiwieją. Safona |
|
