Table Value Function VS ORs

Question

Table Value Function VS ORs

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

sven.treffinger SSC Enthusiast Points: 181 More actions · Answer 1

The Inline Function will be a LOT faster than a multi statement Function.

The reason you think the multi Statement Version is faster, is that in your Execution Plan the multi statement Function does not show the cost of the execution that function. Hence it appears to be faster (its hard to be less costly then nothing).

Execute your query with the MSTVF and run a trace, you will see a very different picture then.

Just trust me on this, the Inline TVF is a LOT faster since the code it contains gets fully integrated in your query, and by that also optimized by the Query Analyzer.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

PiMané (3/5/2013)
Is SQL Sentry Plan more accurate than the Execution Plan from SSMS?!
I ask this cause in SSMS the TVF is better but in Sentry Plan iTVF is much better...
Thanks,
Pedro

You can't compare performance of queries in this way. Nada!

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

PiMané SSChampion Points: 13965 More actions · Answer 3

ChrisM@Work (3/5/2013)
PiMané (3/5/2013)
Is SQL Sentry Plan more accurate than the Execution Plan from SSMS?!
I ask this cause in SSMS the TVF is better but in Sentry Plan iTVF is much better...
Thanks,
Pedro
You can't compare performance of queries in this way. Nada!

On the same database I created an SP:

CREATE PROCEDURE [dbo].[TestSP]

(

@TipoLancamento NVARCHAR(3),

@Conta NVARCHAR(20),

@Ano SMALLINT,

@Mes SMALLINT,

@Valor MONEY,

@MoedaBase NVARCHAR(3)

) AS

BEGIN

SET NOCOUNT ON;

DECLARE @CampoMes NVARCHAR(10), @StrSQL NVARCHAR(MAX)

SET @CampoMes = 'Mes12DB'

DECLARE @params NVARCHAR(MAX) = '@ValorIn MONEY, @TipoLancamentoIn NVARCHAR(3), @AnoIn SMALLINT, @MoedaIn NVARCHAR(3), @ContaIn NVARCHAR(20)'

SET @StrSQL = 'UPDATE a SET ' + @CampoMes + ' = ' + @CampoMes + ' + @ValorIn FROM AcumuladosContas a JOIN dbo.GetSubContasiTVF(@ContaIn) t ON a.Conta = t.SubConta WHERE TipoLancamento = @TipoLancamentoIn AND Ano = @AnoIn AND Moeda = @MoedaIn'

EXEC sp_executeSQL @StrSQL, @params, @ValorIn = @Valor, @TipoLancamentoIn = @TipoLancamento, @AnoIn = @Ano, @MoedaIn = @MoedaBase, @ContaIn = @Conta

END

When executing the queries:

EXEC [TestSP] '000','111', '2012', 12, 5000, 'EUR'

UPDATE a SET Mes12DB = Mes12DB + 5000 FROM AcumuladosContas a JOIN dbo.GetSubContasiTVF('111') t ON a.Conta = t.SubConta WHERE TipoLancamento = '000' AND Ano = 2012 AND Moeda = 'EUR'

With parameterization forced I get the same execution plan but with different values.

With parameterization simple I get different exectuion plans (not very different).

But with forced the EXEC takes 15% and the UPDATE 85%, with simple is the other way around...

Why? How can this options influence performance on a database?

I read that forced should be used for ERP systems with lots of adhoc queries...

Thanks,

Pedro

If you need to work better, try working less...