Table spool la apelul unui UDF
Mai demult, in cadrul unui proiect, a trebuit sa implementam o functionalitate ce cauta in rezultatele aplicarii unui UDF pe toate liniile dintr-o tabela foarte mare.
Ne asteptam la un timp de executie mare - se executa un table scan iar tabela nu putea fi cache-ata cu totul in memorie, mai ales in conditii de concurenta mare deoarece alte parti din baza de date erau folosite mai intens in utilizarea normala. Estimarile ne-au fost insa depasite cu mult. Era timpul studierii planului de executie.
In acesta aparea un operator fizic la care nu ne-am fi asteptat : Table Spool (Eager Spool). Desi acesta prezenta un cost I/O foarte mic, in realitate acesta era mult mai mare deoarece aceste rezultate temporare nu incapeau in memorie si trebuiau scrise pe disc.
Scenariu:
1. Cream tabela de test:
CREATE TABLE [dbo].[test](
[codloc] int primary key,
[codjud] [char](2) NULL,
[tip] [char](1) NULL,
[localit] [varchar](25) NULL
) ON [PRIMARY]
2. Umplem tabela test cu 2^20 linii in 20 pasi, fiecare dubland numarul de linii:
insert into test values(1, 'AB', 'O', 'Alba Iulia')
declare @step int, @maxkey int
set @step = 1
while @step <= 19
begin
select @maxkey = max(codloc) from test
insert into test
select codloc + @maxkey, codjud, tip, localit from test
set @step = @step + 1
end
3. Cream functia user-defined pe care o vom folosi ca exemplu:
create function MyUDF
(
@str varchar(25)
)
returns varchar(50)
as
begin
return reverse(@str) + @str
end
4. Folosind functia definita pentru a filtra datele :
declare @t table (codloc int)
insert into @t
select codloc from test where dbo.MyUDF(localit) like '%z%'
obtinem planul de executie (ce are costul total estimat de 8.95):
Deoarece am creat UDF-ul fara SCHEMABINDING database engine-ul seteaza intern proprietatea USERDATAACCESS pe 1 chiar daca functia noastra nu acceseaza direct tabele. Pentru ca aceasta proprietate este 1, pentru orice statement de insert/delete/update database engine-ul stocheaza temporar informatii de localizare si rezultatul functiei inainte de a face orice modificare pe date. Daca functia noastra ar fi accesat date din tabela modificata atunci rezultatul sau putea fi afectat de modificarile precedente.
5. Schimbam definitia functiei astfel incat sa includa SCHEMABINDING
alter function MyUDF
(
@str varchar(25)
)
returns varchar(50)
with schemabinding
as
begin
return reverse(@str) + @str
end
In momentul executiei engine-ul de optimizare stie ca functia noastra nu foloseste direct date din tabele. Astfel planul de executie pentru select-ul nostru este:
Desi planul are un cost estimat de 10.2 el ruleaza intr-un timp cu 30% mai mic.
posted @ Wednesday, August 22, 2007 4:31 PM