SQL SERVER – TVFs – ITVFs x MTVFs

Table-valued Functions: Inline x Multi-statement.

“… But SQL Server 2000 also offers two types of table-valued functions: inline and multi-statement…” Woooow, isto parece velho!!

Mas eis que, em plena versão 2017 do SGBD, ainda chama atenção a diferença entre planos de execução / performance / escalabilidade, de uma função parametrizada que – em suma – tem em sua declaração a definição de sua relação resultado vrs. sua coirmã “inline”.

Pesquisando a respeito, resume-se ao fato de inline table-valued function ser tratada como uma view parametrizada pelo otimizador. O que não acontece com a multi-statement por, por exemplo, o fato de poder apresentar passos internos a serem processados (ifs, exists…) e que, muitas vezes por vícios de linguagem ou ignorância, resume-se ao insert na table variable resultado seguido do return que encerra à encerra. Exemplo: Ctrl+c nele!

use tempdb
go

create table dbo.TesteData (id int not null primary key, nome varchar(100));
go

insert into dbo.TesteData (id, nome) values (1, 'D. Isabel')
insert into dbo.TesteData (id, nome) values (2, 'D. Pedro de Alcântara')
insert into dbo.TesteData (id, nome) values (3, 'D. Luís Maria')
insert into dbo.TesteData (id, nome) values (4, 'D. Antônio Gastão')
insert into dbo.TesteData (id, nome) values (5, 'Pedro Augusto')
insert into dbo.TesteData (id, nome) values (6, 'Augusto Leopoldo')
insert into dbo.TesteData (id, nome) values (7, 'D.ª Januária')
insert into dbo.TesteData (id, nome) values (8, 'D.ª Francisca')
insert into dbo.TesteData (id, nome) values (9, 'Thiago Timm');
go

create or alter function dbo.MTVFObterCidadao (@id int)
returns @result table (id int, nome varchar(100))
as
begin
    insert into @result (id, nome)
        select id, nome
        from dbo.TesteData
        where id = @id;

    return;
end
go

create or alter function dbo.ITVFObterCidadao (@id int)
returns table
as
    return
        select id, nome
        from dbo.TesteData
        where id = @id;

go

set statistics xml on;
select id, nome from dbo.MTVFObterCidadao(9);
select id, nome from dbo.ITVFObterCidadao(9);

Se neste simples exemplo já é possível perceber planos diferenciados para a versão inline em detrimento à multi-statement, potencialize para situações de nosso cotidiano, onde encontramos selects/applys com table-valued functions que, com algum “twitch around” podem ser promovidas de multi-statement à inline, e passar a contar com um perceptível salto de performance e escalabilidade.

T+

Referências bibliográficas:
https://technet.microsoft.com/en-us/library/aa175924(v=sql.80).aspx
http://www.sqlservercentral.com/blogs/discussionofsqlserver/2012/02/15/comparing-inline-and-multistatement-table-valued-functions/

Anúncios

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s