March 5, 2013 at 5:22 am
Hi,
I'm executing the following queries:
UPDATE AcumuladosContas SET MES12DB = MES12DB + 5000.00 WHERE TipoLancamento = '000' AND Ano=2012 AND Moeda = 'EUR' AND (Conta = '111' OR Conta = '11')
UPDATE AcumuladosContas SET MES12DB = MES12DB + 5000.00 FROM (SELECT SubConta FROM dbo.GetSubContasTVF('111')) t WHERE TipoLancamento = '000' AND Ano = 2012 AND Moeda = 'EUR' AND Conta = t.SubConta
UPDATE AcumuladosContas SET MES12DB = MES12DB + 5000.00 WHERE TipoLancamento = '000' AND Ano = 2012 AND Moeda = 'EUR' AND Conta IN (SELECT SubConta FROM dbo.GetSubContasTVF('111'))
The TVF is:
ALTER FUNCTION [dbo].[GetSubContasTVF](@Conta NVARCHAR(20))
RETURNS @SubContas TABLE (SubConta NVARCHAR(20) NOT NULL PRIMARY KEY)
AS
BEGIN
INSERT INTO @SubContas SELECT SUBSTRING(@Conta, ID, LEN(@Conta)) SubConta FROM PriTally WHERE ID BETWEEN 1 AND LEN(@Conta) - 1
RETURN;
END
In SSMS the execution plan for the queries states that the OR takes 30% and the other two 35% each.
In SQL Sentry Plan Explorer the OR takes 22%, the 2nd 52% and the 3rd 26%...
When using SQLStress the OR is faster with only one thread but when using multiple threads the other ones are faster...
What's the best query?
Is there any other query to perform the operation?
Thanks,
Pedro
March 5, 2013 at 5:27 am
Forgot to mention that I'm using the TVF since the query is build dynamically and I'm using EXEC sp_executeSQL with the query and parameters and if I use the OR the query statement won't have as many parameters as using the function, hence the procedure cache won't be as good as it could be:
SET @stm = 'UPDATE AcumuladosContas SET ' + @MonthField + ' = ' + @MonthField + ' + @Value WHERE TipoLancamento = @TipoLancamento AND Ano= @Ano AND Moeda = @Moeda AND ' + @ContasOr
-- vs --
SET @stm = 'UPDATE AcumuladosContas SET ' + @MonthField + ' = ' + @MonthField + ' + @Value WHERE TipoLancamento = @TipoLancamento AND Ano= @Ano AND Moeda = @Moeda AND Conta IN (SELECT SubConta FROM dbo.GetSubContasTVF(@Conta))'
March 5, 2013 at 5:59 am
Try using a proper iTVF, the performance will be far better:
ALTER FUNCTION [dbo].[GetSubContasTVF](@Conta NVARCHAR(20))
RETURNS TABLE
AS
RETURN SELECT SUBSTRING(@Conta, ID, LEN(@Conta)) SubConta FROM PriTally WHERE ID BETWEEN 1 AND LEN(@Conta) - 1
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
March 5, 2013 at 7:01 am
I created a new function:
CREATE FUNCTION [dbo].[GetSubContasiTVF](@Conta NVARCHAR(20))
RETURNS TABLE
AS
RETURN SELECT SUBSTRING(@Conta, ID, LEN(@Conta)) SubConta FROM PriTally WHERE ID BETWEEN 1 AND LEN(@Conta) - 1
According to Execution Plan the queries using the new function are slower..
UPDATE AcumuladosContas SET MES12DB = MES12DB + 5000.00 WHERE TipoLancamento = '000' AND Ano=2012 AND Moeda = 'EUR' AND (Conta = '111' OR Conta = '11')
-- 3%
UPDATE AcumuladosContas SET MES12DB = MES12DB + 5000.00 FROM (SELECT SubConta FROM dbo.GetSubContasTVF('111')) t WHERE TipoLancamento = '000' AND Ano = 2012 AND Moeda = 'EUR' AND Conta = t.SubConta
-- 4%
UPDATE AcumuladosContas SET MES12DB = MES12DB + 5000.00 WHERE TipoLancamento = '000' AND Ano = 2012 AND Moeda = 'EUR' AND Conta IN (SELECT SubConta FROM dbo.GetSubContasTVF('111'))
-- 4%
UPDATE AcumuladosContas SET MES12DB = MES12DB + 5000.00 FROM (SELECT SubConta FROM dbo.GetSubContasiTVF('111')) t WHERE TipoLancamento = '000' AND Ano = 2012 AND Moeda = 'EUR' AND Conta = t.SubConta
-- 24%
UPDATE AcumuladosContas SET MES12DB = MES12DB + 5000.00 WHERE TipoLancamento = '000' AND Ano = 2012 AND Moeda = 'EUR' AND Conta IN (SELECT SubConta FROM dbo.GetSubContasiTVF('111'))
-- 66%
Am I using the new function the wrong way?!
I use iTVF when the parameter to the function is a field from a table so it's used in a JOIN.. much faster than msTVF ou UDF.. but this isn't the case...
Thanks,
Pedro
March 5, 2013 at 7:07 am
Please post the DDL for the table(s), sample data for the tables, expected results after the update(s) are completed, and the code used for the updates (including code for the table valued functions).
March 5, 2013 at 7:09 am
You can't compare queries like this - you have to time them using a realistically-sized data set. "% of the batch" is 100% unreliable.
If a function contains a BEGIN/END block then it's not an iTVF, it's an msTVF. Lynn Pettis ran some tests on different function types recently. His properly-written iTVF cost about the same as a calculation in the SELECT and was about 200 times faster than the msTVF (same type as your function above).
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
March 5, 2013 at 7:39 am
Lynn Pettis (3/5/2013)
Please post the DDL for the table(s), sample data for the tables, expected results after the update(s) are completed, and the code used for the updates (including code for the table valued functions).
The structure is this:
SET NOCOUNT ON
GO
-- create tally table
CREATE TABLE PriTally (ID INT IDENTITY(1,1) NOT NULL, CONSTRAINT PK_PriTally PRIMARY KEY CLUSTERED (ID))
GO
INSERT INTO PriTally DEFAULT VALUES
GO 100000
GO
--create iTVF
CREATE FUNCTION [dbo].[GetSubContasiTVF](@Conta NVARCHAR(20))
RETURNS TABLE
AS
RETURN SELECT SUBSTRING(@Conta, ID, LEN(@Conta)) SubConta FROM PriTally WHERE ID BETWEEN 1 AND LEN(@Conta) - 1
GO
--create msTVF
CREATE FUNCTION [dbo].[GetSubContasTVF](@Conta NVARCHAR(20))
RETURNS @SubContas TABLE (SubConta NVARCHAR(20) NOT NULL PRIMARY KEY)
AS
BEGIN
INSERT INTO @SubContas SELECT SUBSTRING(@Conta, ID, LEN(@Conta)) SubConta FROM PriTally WHERE ID BETWEEN 1 AND LEN(@Conta) - 1
RETURN;
END
GO
--create table
CREATE TABLE [dbo].[AcumuladosContas](
[Ano] [smallint] NOT NULL,
[Conta] [nvarchar](20) NOT NULL,
[Moeda] [nvarchar](3) NOT NULL,
[Mes00CR] [money] NULL DEFAULT 0,
[Mes01CR] [money] NULL DEFAULT 0,
[Mes02CR] [money] NULL DEFAULT 0,
[Mes03CR] [money] NULL DEFAULT 0,
[Mes04CR] [money] NULL DEFAULT 0,
[Mes05CR] [money] NULL DEFAULT 0,
[Mes06CR] [money] NULL DEFAULT 0,
[Mes07CR] [money] NULL DEFAULT 0,
[Mes08CR] [money] NULL DEFAULT 0,
[Mes09CR] [money] NULL DEFAULT 0,
[Mes10CR] [money] NULL DEFAULT 0,
[Mes11CR] [money] NULL DEFAULT 0,
[Mes12CR] [money] NULL DEFAULT 0,
[Mes13CR] [money] NULL DEFAULT 0,
[Mes14CR] [money] NULL DEFAULT 0,
[Mes15CR] [money] NULL DEFAULT 0,
[Mes00DB] [money] NULL DEFAULT 0,
[Mes01DB] [money] NULL DEFAULT 0,
[Mes02DB] [money] NULL DEFAULT 0,
[Mes03DB] [money] NULL DEFAULT 0,
[Mes04DB] [money] NULL DEFAULT 0,
[Mes05DB] [money] NULL DEFAULT 0,
[Mes06DB] [money] NULL DEFAULT 0,
[Mes07DB] [money] NULL DEFAULT 0,
[Mes08DB] [money] NULL DEFAULT 0,
[Mes09DB] [money] NULL DEFAULT 0,
[Mes10DB] [money] NULL DEFAULT 0,
[Mes11DB] [money] NULL DEFAULT 0,
[Mes12DB] [money] NULL DEFAULT 0,
[Mes13DB] [money] NULL DEFAULT 0,
[Mes14DB] [money] NULL DEFAULT 0,
[Mes15DB] [money] NULL DEFAULT 0,
[Mes01OR] [money] NULL DEFAULT 0,
[Mes02OR] [money] NULL DEFAULT 0,
[Mes03OR] [money] NULL DEFAULT 0,
[Mes04OR] [money] NULL DEFAULT 0,
[Mes05OR] [money] NULL DEFAULT 0,
[Mes06OR] [money] NULL DEFAULT 0,
[Mes07OR] [money] NULL DEFAULT 0,
[Mes08OR] [money] NULL DEFAULT 0,
[Mes09OR] [money] NULL DEFAULT 0,
[Mes10OR] [money] NULL DEFAULT 0,
[Mes11OR] [money] NULL DEFAULT 0,
[Mes12OR] [money] NULL DEFAULT 0,
[TipoLancamento] [varchar](3) NOT NULL,
[NaturezaOR] [varchar](1) NOT NULL,
CONSTRAINT [AcumuladosContas01] PRIMARY KEY CLUSTERED
(
[TipoLancamento] ASC,
[Ano] ASC,
[Conta] ASC,
[Moeda] ASC
)
)
GO
--create index
CREATE NONCLUSTERED INDEX [IDX_AcumuladosContas_Teste] ON [dbo].[AcumuladosContas]
(
[Ano] ASC,
[Moeda] ASC,
[Conta] ASC,
[TipoLancamento] ASC
)
INCLUDE ([Mes12DB])
GO
-- insert data
DELETE FROM AcumuladosContas
GO
-- the "target" rows
INSERT INTO AcumuladosContas (Ano, Moeda, TipoLancamento, NaturezaOR, Conta, Mes12DB) VALUES (2012, 'EUR', '000', 'C', '111', 100)
INSERT INTO AcumuladosContas (Ano, Moeda, TipoLancamento, NaturezaOR, Conta, Mes12DB) VALUES (2012, 'EUR', '000', 'C', '11', 100)
GO
-- insert 10.000 misc rows (can occour duplicate erros but batch continues and still inserts some considerable data)
INSERT INTO AcumuladosContas (Ano, Moeda, TipoLancamento, NaturezaOR, Conta, Mes12DB) VALUES (2012, 'EUR', '000', 'C', CAST(RAND()*100000000 AS NVARCHAR(20)), 100)
GO 10000
GO
SELECT COUNT(1) FROM AcumuladosContas
GO
The indexes will be quite fragmented so should run:
ALTER INDEX [IDX_AcumuladosContas_Teste] ON AcumuladosContas REBUILD
ALTER INDEX [AcumuladosContas01] ON AcumuladosContas REBUILD
The update statements being compared are the following:
UPDATE AcumuladosContas SET MES12DB = MES12DB + 5000.00 WHERE TipoLancamento = '000' AND Ano=2012 AND Moeda = 'EUR' AND (Conta = '111' OR Conta = '11')
UPDATE AcumuladosContas SET MES12DB = MES12DB + 5000.00 FROM dbo.GetSubContasTVF('111') t WHERE TipoLancamento = '000' AND Ano = 2012 AND Moeda = 'EUR' AND Conta = t.SubConta
UPDATE AcumuladosContas SET MES12DB = MES12DB + 5000.00 WHERE TipoLancamento = '000' AND Ano = 2012 AND Moeda = 'EUR' AND Conta IN (SELECT SubConta FROM dbo.GetSubContasTVF('111'))
UPDATE AcumuladosContas SET MES12DB = MES12DB + 5000.00 FROM dbo.GetSubContasiTVF('111') t WHERE TipoLancamento = '000' AND Ano = 2012 AND Moeda = 'EUR' AND Conta = t.SubConta
UPDATE AcumuladosContas SET MES12DB = MES12DB + 5000.00 WHERE TipoLancamento = '000' AND Ano = 2012 AND Moeda = 'EUR' AND Conta IN (SELECT SubConta FROM dbo.GetSubContasiTVF('111'))
By each execution of an UPDATE the row should increase 5000. So after a run with the 5 statements the rows for Conta 111 and 11 should have Mes12DB with 25100.
SELECT Ano,Conta,Moeda,Mes12DB FROM AcumuladosContas WHERE Conta IN ('111', '11')
Thanks,
Pedro
March 5, 2013 at 7:44 am
I have to head to work. I may look at while eating lunch else it will be tonight.
March 5, 2013 at 7:48 am
You should avoid this syntax:
UPDATE AcumuladosContas SET MES12DB = MES12DB + 5000.00
FROM dbo.GetSubContasTVF('111') t
WHERE TipoLancamento = '000' AND Ano = 2012 AND Moeda = 'EUR'
AND Conta = t.SubConta
UPDATE ...FROM... should list the update target (AcumuladosContas ) first. SQL Server won't raise an error, however, some folks claim to have observed very poor performance when this rule has been broken.
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
March 5, 2013 at 7:50 am
Lynn Pettis (3/5/2013)
I have to head to work. I may look at while eating lunch else it will be tonight.
Thanks.
I believe that in this case, since the parameter to the iTVF isn't a table column but a variable, the msTVF is faster. At least that's what the plan says..
Also the OR can be faster but for short values.. For a value of 10 or more chars the functions are much faster, also there's also the WHILE cycle that "generates" the OR.
In the example I just put the OR already built but in the SP is a code for building the OR statement..
...
DECLARE @params NVARCHAR(MAX) = '@ValorIn MONEY, @TipoLancamentoIn NVARCHAR(3), @AnoIn SMALLINT, @MoedaIn NVARCHAR(3), @ContaIn NVARCHAR(20)'
SET @StrSQL = 'UPDATE AcumuladosContas SET ' + @CampoMes + ' = ' + @CampoMes + ' + @ValorIn FROM dbo.GetSubContasiTVF(@ContaIn) t WHERE TipoLancamento = @TipoLancamentoIn AND Ano = @AnoIn AND Moeda = @MoedaIn AND Conta = t.SubConta'
EXEC sp_executeSQL @StrSQL, @params, @ValorIn = @Valor, @TipoLancamentoIn = @TipoLancamento, @AnoIn = @Ano, @MoedaIn = @MoedaBase, @ContaIn = @Conta
--- vs ---
SELECT @SqlWHEREConta = '(Conta = ''' + @Conta + ''''
WHILE Len(@Conta) > 2
BEGIN
SELECT @Conta = Substring(@Conta,1,Len(@Conta) - 1)
SELECT @SqlWHEREConta = @SqlWHEREConta + ' OR Conta = ''' + @Conta + ''''
END
SELECT @SqlWHEREConta = @SqlWHEREConta + ')'
SELECT @StrSQL = 'UPDATE AcumuladosContas SET ' + @CampoMes + ' = ' + @CampoMes + ' + ' + Convert(NVARCHAR,@Valor) + ' WHERE TipoLancamento = ''' + @TipoLancamento + ''' AND Ano=' + Convert(NVARCHAR,@Ano) + ' AND Moeda = ''' + @MoedaBase + ''' AND ' + @SqlWHEREConta
EXECUTE( @StrSQL)
I'm using the function approach so the sp_executeSQL has only one cached plan...
Thanks,
Pedro
March 5, 2013 at 7:54 am
ChrisM@Work (3/5/2013)
You should avoid this syntax:
UPDATE AcumuladosContas SET MES12DB = MES12DB + 5000.00FROM dbo.GetSubContasTVF('111') t
WHERE TipoLancamento = '000' AND Ano = 2012 AND Moeda = 'EUR'
AND Conta = t.SubConta
UPDATE ...FROM... should list the update target (AcumuladosContas ) first. SQL Server won't raise an error, however, some folks claim to have observed very poor performance when this rule has been broken.
And this approach (I use this when I want a SELECT for UPDATE):
UPDATE sq SET Mes12DB = Mes12DB + 5000 FROM (SELECT Mes12DB FROM AcumuladosContas a JOIN dbo.GetSubContasTVF('111') t ON a.Conta = t.SubConta WHERE TipoLancamento = '000' AND Ano = 2012 AND Moeda = 'EUR') sq
Thanks,
Pedro
March 5, 2013 at 7:56 am
PiMané (3/5/2013)
Lynn Pettis (3/5/2013)
I have to head to work. I may look at while eating lunch else it will be tonight.Thanks.
I believe that in this case, since the parameter to the iTVF isn't a table column but a variable, the msTVF is faster. At least that's what the plan says..
Also the OR can be faster but for short values.. For a value of 10 or more chars the functions are much faster, also there's also the WHILE cycle that "generates" the OR.
In the example I just put the OR already built but in the SP is a code for building the OR statement..
...
DECLARE @params NVARCHAR(MAX) = '@ValorIn MONEY, @TipoLancamentoIn NVARCHAR(3), @AnoIn SMALLINT, @MoedaIn NVARCHAR(3), @ContaIn NVARCHAR(20)'
SET @StrSQL = 'UPDATE AcumuladosContas SET ' + @CampoMes + ' = ' + @CampoMes + ' + @ValorIn FROM dbo.GetSubContasiTVF(@ContaIn) t WHERE TipoLancamento = @TipoLancamentoIn AND Ano = @AnoIn AND Moeda = @MoedaIn AND Conta = t.SubConta'
EXEC sp_executeSQL @StrSQL, @params, @ValorIn = @Valor, @TipoLancamentoIn = @TipoLancamento, @AnoIn = @Ano, @MoedaIn = @MoedaBase, @ContaIn = @Conta
--- vs ---
SELECT @SqlWHEREConta = '(Conta = ''' + @Conta + ''''
WHILE Len(@Conta) > 2
BEGIN
SELECT @Conta = Substring(@Conta,1,Len(@Conta) - 1)
SELECT @SqlWHEREConta = @SqlWHEREConta + ' OR Conta = ''' + @Conta + ''''
END
SELECT @SqlWHEREConta = @SqlWHEREConta + ')'
SELECT @StrSQL = 'UPDATE AcumuladosContas SET ' + @CampoMes + ' = ' + @CampoMes + ' + ' + Convert(NVARCHAR,@Valor) + ' WHERE TipoLancamento = ''' + @TipoLancamento + ''' AND Ano=' + Convert(NVARCHAR,@Ano) + ' AND Moeda = ''' + @MoedaBase + ''' AND ' + @SqlWHEREConta
EXECUTE( @StrSQL)
I'm using the function approach so the sp_executeSQL has only one cached plan...
Thanks,
Pedro
I truly disbelieve this statement:
I believe that in this case, since the parameter to the iTVF isn't a table column but a variable, the msTVF is faster.
A mTVF function is a black box and must completely populate the table variable that is returned from the function before processing may continue. A true iTVF is expanded as part of the query similar to a view, CTE, or derived table.
March 5, 2013 at 8:00 am
PiMané (3/5/2013)
ChrisM@Work (3/5/2013)
You should avoid this syntax:
UPDATE AcumuladosContas SET MES12DB = MES12DB + 5000.00FROM dbo.GetSubContasTVF('111') t
WHERE TipoLancamento = '000' AND Ano = 2012 AND Moeda = 'EUR'
AND Conta = t.SubConta
UPDATE ...FROM... should list the update target (AcumuladosContas ) first. SQL Server won't raise an error, however, some folks claim to have observed very poor performance when this rule has been broken.
And this approach (I use this when I want a SELECT for UPDATE):
UPDATE sq SET Mes12DB = Mes12DB + 5000 FROM (SELECT Mes12DB FROM AcumuladosContas a JOIN dbo.GetSubContasTVF('111') t ON a.Conta = t.SubConta WHERE TipoLancamento = '000' AND Ano = 2012 AND Moeda = 'EUR') sq
Thanks,
Pedro
I'd expect the execution plan to be identical to this:
UPDATE a SET Mes12DB = Mes12DB + 5000
--FROM (
--SELECT Mes12DB
FROM AcumuladosContas a
JOIN dbo.GetSubContasTVF('111') t
ON a.Conta = t.SubConta
WHERE TipoLancamento = '000' AND Ano = 2012 AND Moeda = 'EUR'
--) sq
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
March 5, 2013 at 8:13 am
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
[Edited to add the attachment with SQL Sentry Plan results]
March 5, 2013 at 8:16 am
ChrisM@Work (3/5/2013)
PiMané (3/5/2013)
ChrisM@Work (3/5/2013)
You should avoid this syntax:
UPDATE AcumuladosContas SET MES12DB = MES12DB + 5000.00FROM dbo.GetSubContasTVF('111') t
WHERE TipoLancamento = '000' AND Ano = 2012 AND Moeda = 'EUR'
AND Conta = t.SubConta
UPDATE ...FROM... should list the update target (AcumuladosContas ) first. SQL Server won't raise an error, however, some folks claim to have observed very poor performance when this rule has been broken.
And this approach (I use this when I want a SELECT for UPDATE):
UPDATE sq SET Mes12DB = Mes12DB + 5000 FROM (SELECT Mes12DB FROM AcumuladosContas a JOIN dbo.GetSubContasTVF('111') t ON a.Conta = t.SubConta WHERE TipoLancamento = '000' AND Ano = 2012 AND Moeda = 'EUR') sq
Thanks,
Pedro
I'd expect the execution plan to be identical to this:
UPDATE a SET Mes12DB = Mes12DB + 5000--FROM (
--SELECT Mes12DB
FROM AcumuladosContas a
JOIN dbo.GetSubContasTVF('111') t
ON a.Conta = t.SubConta
WHERE TipoLancamento = '000' AND Ano = 2012 AND Moeda = 'EUR'
--) sq
Yep. Same plan... 🙂
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply