Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Help with nested cursors and bad performance

Author  Topic 

Agenteusa
Starting Member

11 Posts

Posted - 2015-01-28 : 07:14:42
Hi,

I have 4 cursors that are inserting into a tabel and after an update command that will update 12 columns on the same table. Here is the code:

DELETE FROM a_UserTable_CuboVendasVsObjectivosMercados 

--CURSOR MARKET/INDUSTRIA
DECLARE @market nvarchar(max)
DECLARE @industria nvarchar(max)

DECLARE cursorIndustriasMarkets CURSOR FOR
SELECT esri_marketidname, esri_name
FROM FilteredESRI_industria
where statecode = 0

--CURSOR CONSULTORES
DECLARE @consultorid uniqueidentifier
DECLARE @consultor nvarchar(max)
DECLARE @un nvarchar(max)

DECLARE cursorConsultor CURSOR FOR
SELECT distinct systemuserid, fullname, businessunitidname
FROM FilteredSystemUser
WHERE systemuserid IN (SELECT distinct esri_consultorid FROM a_UserTable_CuboObjectivos_UnidadeNegocio where Ano = YEAR(GETDATE()))

--CURSOR PAISES
DECLARE @pais nvarchar(max)

DECLARE cursorPaises CURSOR FOR
SELECT DISTINCT
case ESRI_name
when 'Portugal' then 'Nacional'
when 'Angola' then ESRI_name
when 'Cabo Verde' then ESRI_name
when 'Moçambique' then ESRI_name
when 'São Tomé e Príncipe' then ESRI_name
when 'Guiné' then ESRI_name
else 'Outros'
end name
FROM ESRI_pais



OPEN cursorIndustriasMarkets
FETCH NEXT FROM cursorIndustriasMarkets INTO @market, @industria

WHILE @@FETCH_STATUS = 0
BEGIN

OPEN cursorPaises
FETCH NEXT FROM cursorPaises INTO @pais

WHILE @@FETCH_STATUS = 0
BEGIN

OPEN cursorConsultor
FETCH NEXT FROM cursorConsultor INTO @consultorid, @consultor, @un

WHILE @@FETCH_STATUS = 0
BEGIN


INSERT INTO a_UserTable_CuboVendasVsObjectivosMercados (CONSULTORID, CONSULTOR, UNIDADENEGOCIO, MARKET, INDUSTRIA,PAIS)
VALUES(@consultorid, @consultor, @un, @market, @industria, @pais)


UPDATE a_UserTable_CuboVendasVsObjectivosMercados
SET REALIZADOMES = uf.realizadomes,
REALIZADOTRIMESTRE = uf.realizadotrimestre,
REALIZADOYEARTOMONTH = uf.realizadoytd,
REALIZADOANUAL = uf.realizadoano,
BACKLOGMES = uf.backmes,
BACKLOGTRIMESTRE = uf.backtrimestre,
BACKLOGYEARTOMONTH = uf.backytd,
BACKLOGANUAL = uf.backano,
PIPELINE75MES = uf.pipemes,
PIPELINE75TRIMESTRE = uf.pipetrimestre,
PIPELINE75YEARTOMONTH = uf.pipeytd,
PIPELINE75ANUAL = uf.pipeano
FROM [a_UserDefinedfn_GetValuesForSPUpdateVendasVsObjetivoMercados] (@consultorid, @market, @industria, @pais) uf
WHERE CONSULTORID = @consultorid AND UNIDADENEGOCIO = @un AND MARKET = @market
AND INDUSTRIA = @industria AND PAIS = @pais

FETCH NEXT FROM cursorConsultor INTO @consultorid, @consultor, @un
END
CLOSE cursorConsultor

FETCH NEXT FROM cursorPaises INTO @pais
END
CLOSE cursorPaises

FETCH NEXT FROM cursorIndustriasMarkets INTO @market, @industria
END

CLOSE cursorIndustriasMarkets
DEALLOCATE cursorIndustriasMarkets

DEALLOCATE cursorPaises

DEALLOCATE cursorConsultor


That is the SP that will generate a table.

Now the code for the th UDF called inside the cursor is :

ALTER FUNCTION  [dbo].[a_UserDefinedfn_GetValuesForSPUpdateVendasVsObjetivoMercados]
(
@consultorid uniqueidentifier,
@market nvarchar(max),
@industria nvarchar(max),
@pais nvarchar(max)
)


RETURNS @ValuesTable TABLE (realizadomes DECIMAL(12,2), realizadotrimestre DECIMAL(12,2), realizadoytd DECIMAL(12,2), realizadoano DECIMAL(12,2),
backmes DECIMAL(12,2), backtrimestre DECIMAL(12,2), backytd DECIMAL(12,2), backano DECIMAL(12,2),
--objmes DECIMAL(12,2), objtrimestre DECIMAL(12,2), objytd DECIMAL(12,2), objano DECIMAL(12,2),
pipemes DECIMAL(12,2), pipetrimestre DECIMAL(12,2), pipeytd DECIMAL(12,2), pipeano DECIMAL(12,2))
AS
BEGIN


IF @pais = 'Nacional'
BEGIN
SET @pais = 'Portugal'
END

--- REALIZADOMES

DECLARE @realizadomes DECIMAL(12,2)

IF @pais NOT LIKE 'Outros'
BEGIN
SET @realizadomes = (SELECT ISNULL(SUM(esri_totaldatadeencomenda_base),0) FROM FilteredInvoice
WHERE (MONTH(esri_dataprevistadefacturacao) = MONTH(GETDATE()) AND YEAR(esri_dataprevistadefacturacao) = YEAR(GETDATE()))
AND (salesorderid IS NOT NULL OR (salesorderid IS NULL AND opportunityid IS NULL)) AND esri_idfacturaprimavera IS NOT NULL AND ownerid = @consultorid AND statecode <> 3
AND accountid IN (SELECT accountid FROM FilteredAccount WHERE esri_paisidname = @pais AND esri_marketidname = @market and esri_industriaidname = @industria))
END
ELSE
BEGIN
SET @realizadomes = (SELECT ISNULL(SUM(esri_totaldatadeencomenda_base),0) FROM FilteredInvoice
WHERE (MONTH(esri_dataprevistadefacturacao) = MONTH(GETDATE()) AND YEAR(esri_dataprevistadefacturacao) = YEAR(GETDATE()))
AND (salesorderid IS NOT NULL OR (salesorderid IS NULL AND opportunityid IS NULL)) AND esri_idfacturaprimavera IS NOT NULL AND ownerid = @consultorid AND statecode <> 3
AND accountid IN (SELECT accountid FROM FilteredAccount WHERE (esri_paisidname NOT LIKE 'Portugal' AND
esri_paisidname NOT LIKE 'Angola' AND esri_paisidname NOT LIKE 'Cabo Verde' AND esri_paisidname NOT LIKE 'Moçambique'
AND esri_paisidname NOT LIKE 'São Tomé e Príncipe' AND esri_paisidname NOT LIKE 'Guiné') AND esri_marketidname = @market and esri_industriaidname = @industria))
END

----------------

--- REALIZADOTRIMESTRE

DECLARE @realizadotrimestre DECIMAL(12,2)

IF @pais NOT LIKE 'Outros'
BEGIN
SET @realizadotrimestre = (SELECT ISNULL(SUM(esri_totaldatadeencomenda_base),0) FROM FilteredInvoice
WHERE (DATEPART(QUARTER, esri_dataprevistadefacturacao) = DATEPART(Quarter, GETDATE()) AND YEAR(esri_dataprevistadefacturacao) = YEAR(GETDATE()))
AND (salesorderid IS NOT NULL OR (salesorderid IS NULL AND opportunityid IS NULL)) AND esri_idfacturaprimavera IS NOT NULL AND ownerid = @consultorid AND statecode <> 3
AND accountid IN (SELECT accountid FROM FilteredAccount WHERE esri_paisidname = @pais AND esri_marketidname = @market and esri_industriaidname = @industria))
END
ELSE
BEGIN
SET @realizadotrimestre = (SELECT ISNULL(SUM(esri_totaldatadeencomenda_base),0) FROM FilteredInvoice
WHERE (DATEPART(QUARTER, esri_dataprevistadefacturacao) = DATEPART(Quarter, GETDATE()) AND YEAR(esri_dataprevistadefacturacao) = YEAR(GETDATE()))
AND (salesorderid IS NOT NULL OR (salesorderid IS NULL AND opportunityid IS NULL)) AND esri_idfacturaprimavera IS NOT NULL AND ownerid = @consultorid AND statecode <> 3
AND accountid IN (SELECT accountid FROM FilteredAccount WHERE (esri_paisidname NOT LIKE 'Portugal' AND
esri_paisidname NOT LIKE 'Angola' AND esri_paisidname NOT LIKE 'Cabo Verde' AND esri_paisidname NOT LIKE 'Moçambique'
AND esri_paisidname NOT LIKE 'São Tomé e Príncipe' AND esri_paisidname NOT LIKE 'Guiné') AND esri_marketidname = @market and esri_industriaidname = @industria))
END
----------------

--- REALIZADOYTD

DECLARE @realizadoytd DECIMAL(12,2)

IF @pais NOT LIKE 'Outros'
BEGIN
SET @realizadoytd = (SELECT ISNULL(SUM(esri_totaldatadeencomenda_base),0) FROM FilteredInvoice
WHERE (esri_dataprevistadefacturacao >= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) AND esri_dataprevistadefacturacao <= DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)))
AND (salesorderid IS NOT NULL OR (salesorderid IS NULL AND opportunityid IS NULL)) AND esri_idfacturaprimavera IS NOT NULL AND ownerid = @consultorid AND statecode <> 3
AND accountid IN (SELECT accountid FROM FilteredAccount WHERE esri_paisidname = @pais AND esri_marketidname = @market and esri_industriaidname = @industria))
END
ELSE
BEGIN
SET @realizadoytd = (SELECT ISNULL(SUM(esri_totaldatadeencomenda_base),0) FROM FilteredInvoice
WHERE (esri_dataprevistadefacturacao >= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) AND esri_dataprevistadefacturacao <= DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)))
AND (salesorderid IS NOT NULL OR (salesorderid IS NULL AND opportunityid IS NULL)) AND esri_idfacturaprimavera IS NOT NULL AND ownerid = @consultorid AND statecode <> 3
AND accountid IN (SELECT accountid FROM FilteredAccount WHERE (esri_paisidname NOT LIKE 'Portugal' AND
esri_paisidname NOT LIKE 'Angola' AND esri_paisidname NOT LIKE 'Cabo Verde' AND esri_paisidname NOT LIKE 'Moçambique'
AND esri_paisidname NOT LIKE 'São Tomé e Príncipe' AND esri_paisidname NOT LIKE 'Guiné') AND esri_marketidname = @market and esri_industriaidname = @industria))
END
----------------

--- REALIZADOANO

DECLARE @realizadoano DECIMAL(12,2)

IF @pais NOT LIKE 'Outros'
BEGIN
SET @realizadoano = (SELECT ISNULL(SUM(esri_totaldatadeencomenda_base),0) FROM FilteredInvoice
WHERE (YEAR(esri_dataprevistadefacturacao) = YEAR(GETDATE()))
AND (salesorderid IS NOT NULL OR (salesorderid IS NULL AND opportunityid IS NULL)) AND esri_idfacturaprimavera IS NOT NULL AND ownerid = @consultorid AND statecode <> 3
AND accountid IN (SELECT accountid FROM FilteredAccount WHERE esri_paisidname = @pais AND esri_marketidname = @market and esri_industriaidname = @industria))
END
ELSE
BEGIN
SET @realizadoano = (SELECT ISNULL(SUM(esri_totaldatadeencomenda_base),0) FROM FilteredInvoice
WHERE (YEAR(esri_dataprevistadefacturacao) = YEAR(GETDATE()))
AND (salesorderid IS NOT NULL OR (salesorderid IS NULL AND opportunityid IS NULL)) AND esri_idfacturaprimavera IS NOT NULL AND ownerid = @consultorid AND statecode <> 3
AND accountid IN (SELECT accountid FROM FilteredAccount WHERE (esri_paisidname NOT LIKE 'Portugal' AND
esri_paisidname NOT LIKE 'Angola' AND esri_paisidname NOT LIKE 'Cabo Verde' AND esri_paisidname NOT LIKE 'Moçambique'
AND esri_paisidname NOT LIKE 'São Tomé e Príncipe' AND esri_paisidname NOT LIKE 'Guiné') AND esri_marketidname = @market and esri_industriaidname = @industria))
END
----------------

--- BACKLOGMES

DECLARE @backlogmes DECIMAL(12,2)

IF @pais NOT LIKE 'Outros'
BEGIN
SET @backlogmes = (SELECT ISNULL(SUM(esri_totaldatadeencomenda_base),0) FROM FilteredInvoice
WHERE (MONTH(esri_dataprevistadefacturacao) = MONTH(GETDATE()) AND YEAR(esri_dataprevistadefacturacao) = YEAR(GETDATE()))
AND (salesorderid IS NOT NULL OR (salesorderid IS NULL AND opportunityid IS NULL)) AND esri_idfacturaprimavera IS NULL AND ownerid = @consultorid AND statecode <> 3
AND accountid IN (SELECT accountid FROM FilteredAccount WHERE esri_paisidname = @pais AND esri_marketidname = @market and esri_industriaidname = @industria))
END
ELSE
BEGIN
SET @backlogmes = (SELECT ISNULL(SUM(esri_totaldatadeencomenda_base),0) FROM FilteredInvoice
WHERE (MONTH(esri_dataprevistadefacturacao) = MONTH(GETDATE()) AND YEAR(esri_dataprevistadefacturacao) = YEAR(GETDATE()))
AND (salesorderid IS NOT NULL OR (salesorderid IS NULL AND opportunityid IS NULL)) AND esri_idfacturaprimavera IS NULL AND ownerid = @consultorid AND statecode <> 3
AND accountid IN (SELECT accountid FROM FilteredAccount WHERE (esri_paisidname NOT LIKE 'Portugal' AND
esri_paisidname NOT LIKE 'Angola' AND esri_paisidname NOT LIKE 'Cabo Verde' AND esri_paisidname NOT LIKE 'Moçambique'
AND esri_paisidname NOT LIKE 'São Tomé e Príncipe' AND esri_paisidname NOT LIKE 'Guiné') AND esri_marketidname = @market and esri_industriaidname = @industria))
END
----------------

--- BACKLOGTRIMESTRE

DECLARE @backlogtrimestre DECIMAL(12,2)

IF @pais NOT LIKE 'Outros'
BEGIN
SET @backlogtrimestre = (SELECT ISNULL(SUM(esri_totaldatadeencomenda_base),0) FROM FilteredInvoice
WHERE (DATEPART(QUARTER, esri_dataprevistadefacturacao) = DATEPART(Quarter, GETDATE()) AND YEAR(esri_dataprevistadefacturacao) = YEAR(GETDATE()))
AND (salesorderid IS NOT NULL OR (salesorderid IS NULL AND opportunityid IS NULL)) AND esri_idfacturaprimavera IS NULL AND ownerid = @consultorid AND statecode <> 3
AND accountid IN (SELECT accountid FROM FilteredAccount WHERE esri_paisidname = @pais AND esri_marketidname = @market and esri_industriaidname = @industria))
END
ELSE
BEGIN
SET @backlogtrimestre = (SELECT ISNULL(SUM(esri_totaldatadeencomenda_base),0) FROM FilteredInvoice
WHERE (DATEPART(QUARTER, esri_dataprevistadefacturacao) = DATEPART(Quarter, GETDATE()) AND YEAR(esri_dataprevistadefacturacao) = YEAR(GETDATE()))
AND (salesorderid IS NOT NULL OR (salesorderid IS NULL AND opportunityid IS NULL)) AND esri_idfacturaprimavera IS NULL AND ownerid = @consultorid AND statecode <> 3
AND accountid IN (SELECT accountid FROM FilteredAccount WHERE (esri_paisidname NOT LIKE 'Portugal' AND
esri_paisidname NOT LIKE 'Angola' AND esri_paisidname NOT LIKE 'Cabo Verde' AND esri_paisidname NOT LIKE 'Moçambique'
AND esri_paisidname NOT LIKE 'São Tomé e Príncipe' AND esri_paisidname NOT LIKE 'Guiné') AND esri_marketidname = @market and esri_industriaidname = @industria))
END
----------------

--- BACKLOGYTD

DECLARE @backlogytd DECIMAL(12,2)

SET @backlogytd = (SELECT ISNULL(SUM(esri_totaldatadeencomenda_base),0) FROM FilteredInvoice
WHERE (esri_dataprevistadefacturacao >= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) AND esri_dataprevistadefacturacao <= DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)))
AND (salesorderid IS NOT NULL OR (salesorderid IS NULL AND opportunityid IS NULL)) AND esri_idfacturaprimavera IS NULL AND ownerid = @consultorid AND statecode <> 3
AND accountid IN (SELECT accountid FROM FilteredAccount WHERE esri_paisidname = @pais AND esri_marketidname = @market and esri_industriaidname = @industria))

----------------

--- BACKLOGANO

DECLARE @backlogano DECIMAL(12,2)

SET @backlogano = (SELECT ISNULL(SUM(esri_totaldatadeencomenda_base),0) FROM FilteredInvoice
WHERE (YEAR(esri_dataprevistadefacturacao) = YEAR(GETDATE()))
AND (salesorderid IS NOT NULL OR (salesorderid IS NULL AND opportunityid IS NULL)) AND esri_idfacturaprimavera IS NULL AND ownerid = @consultorid AND statecode <> 3
AND accountid IN (SELECT accountid FROM FilteredAccount WHERE esri_paisidname = @pais AND esri_marketidname = @market and esri_industriaidname = @industria))

----------------

--- OBJETIVOMES

--DECLARE @objetivomes DECIMAL(12,2)

--SET @objetivomes = (SELECT ISNULL(SUM(Valor),0) FROM a_UserTable_CuboObjectivos_UnidadeNegocio
-- WHERE (Mes = MONTH(GETDATE()) AND Ano = YEAR(GETDATE())) AND esri_consultorid = @consultorid)

------------------

----- OBJETIVOTRIMESTRE

--DECLARE @objetivotrimestre DECIMAL(12,2)

--SET @objetivotrimestre = (SELECT ISNULL(SUM(Valor),0) FROM a_UserTable_CuboObjectivos_UnidadeNegocio
-- WHERE DATEPART(QUARTER, DataObjectivo) = DATEPART(QUARTER, GETDATE()) AND YEAR(DataObjectivo) = YEAR(GETDATE())
-- AND esri_consultorid = @consultorid)

------------------

----- OBJETIVOYTD

--DECLARE @objetivoytd DECIMAL(12,2)

--SET @objetivoytd = (SELECT ISNULL(SUM(Valor),0) FROM a_UserTable_CuboObjectivos_UnidadeNegocio
-- WHERE (DataObjectivo >= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) AND DataObjectivo <= DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)))
-- AND esri_consultorid = @consultorid)

------------------

----- OBJETIVOANO

--DECLARE @objetivoano DECIMAL(12,2)

--SET @objetivoano = (SELECT ISNULL(SUM(Valor),0) FROM a_UserTable_CuboObjectivos_UnidadeNegocio
-- WHERE Ano = YEAR(GETDATE())
-- AND esri_consultorid = @consultorid)

----------------

--- PIPELINE75MES

DECLARE @pipeline75mes DECIMAL(12,2)

IF @pais NOT LIKE 'Outros'
BEGIN
SET @pipeline75mes = (SELECT ISNULL(SUM(esri_totaldatadeencomenda_base),0) FROM FilteredInvoice
WHERE (MONTH(esri_dataprevistadefacturacao) = MONTH(GETDATE()) AND YEAR(esri_dataprevistadefacturacao) = YEAR(GETDATE()))
AND (salesorderid IS NULL OR (salesorderid IS NULL AND opportunityid IS NULL)) AND esri_idfacturaprimavera IS NULL AND ownerid = @consultorid AND statecode <> 3
AND opportunityid IN (SELECT opportunityid FROM FilteredOpportunity WHERE (opportunityratingcodename = '75' or opportunityratingcodename = '90' or opportunityratingcodename = '100'))
AND accountid IN (SELECT accountid FROM FilteredAccount WHERE esri_paisidname = @pais AND esri_marketidname = @market and esri_industriaidname = @industria))
END
ELSE
BEGIN
SET @pipeline75mes = (SELECT ISNULL(SUM(esri_totaldatadeencomenda_base),0) FROM FilteredInvoice
WHERE (MONTH(esri_dataprevistadefacturacao) = MONTH(GETDATE()) AND YEAR(esri_dataprevistadefacturacao) = YEAR(GETDATE()))
AND (salesorderid IS NULL OR (salesorderid IS NULL AND opportunityid IS NULL)) AND esri_idfacturaprimavera IS NULL AND ownerid = @consultorid AND statecode <> 3
AND opportunityid IN (SELECT opportunityid FROM FilteredOpportunity WHERE (opportunityratingcodename = '75' or opportunityratingcodename = '90' or opportunityratingcodename = '100'))
AND accountid IN (SELECT accountid FROM FilteredAccount WHERE (esri_paisidname NOT LIKE 'Portugal' AND
esri_paisidname NOT LIKE 'Angola' AND esri_paisidname NOT LIKE 'Cabo Verde' AND esri_paisidname NOT LIKE 'Moçambique'
AND esri_paisidname NOT LIKE 'São Tomé e Príncipe' AND esri_paisidname NOT LIKE 'Guiné') AND esri_marketidname = @market and esri_industriaidname = @industria))
END
----------------

--- PIPELINE75TRIMESTRE

DECLARE @pipeline75trimestre DECIMAL(12,2)

IF @pais NOT LIKE 'Outros'
BEGIN
SET @pipeline75trimestre = (SELECT ISNULL(SUM(esri_totaldatadeencomenda_base),0) FROM FilteredInvoice
WHERE (DATEPART(QUARTER, esri_dataprevistadefacturacao) = DATEPART(Quarter, GETDATE()) AND YEAR(esri_dataprevistadefacturacao) = YEAR(GETDATE()))
AND (salesorderid IS NULL OR (salesorderid IS NULL AND opportunityid IS NULL)) AND esri_idfacturaprimavera IS NULL AND ownerid = @consultorid AND statecode <> 3
AND opportunityid IN (SELECT opportunityid FROM FilteredOpportunity WHERE (opportunityratingcodename = '75' or opportunityratingcodename = '90' or opportunityratingcodename = '100'))
AND accountid IN (SELECT accountid FROM FilteredAccount WHERE esri_paisidname = @pais AND esri_marketidname = @market and esri_industriaidname = @industria))
END
ELSE
BEGIN
SET @pipeline75trimestre = (SELECT ISNULL(SUM(esri_totaldatadeencomenda_base),0) FROM FilteredInvoice
WHERE (DATEPART(QUARTER, esri_dataprevistadefacturacao) = DATEPART(Quarter, GETDATE()) AND YEAR(esri_dataprevistadefacturacao) = YEAR(GETDATE()))
AND (salesorderid IS NULL OR (salesorderid IS NULL AND opportunityid IS NULL)) AND esri_idfacturaprimavera IS NULL AND ownerid = @consultorid AND statecode <> 3
AND opportunityid IN (SELECT opportunityid FROM FilteredOpportunity WHERE (opportunityratingcodename = '75' or opportunityratingcodename = '90' or opportunityratingcodename = '100'))
AND accountid IN (SELECT accountid FROM FilteredAccount WHERE (esri_paisidname NOT LIKE 'Portugal' AND
esri_paisidname NOT LIKE 'Angola' AND esri_paisidname NOT LIKE 'Cabo Verde' AND esri_paisidname NOT LIKE 'Moçambique'
AND esri_paisidname NOT LIKE 'São Tomé e Príncipe' AND esri_paisidname NOT LIKE 'Guiné') AND esri_marketidname = @market and esri_industriaidname = @industria))
END
----------------

--- PIPELINE75YTD

DECLARE @pipeline75ytd DECIMAL(12,2)

IF @pais NOT LIKE 'Outros'
BEGIN
SET @pipeline75ytd = (SELECT ISNULL(SUM(esri_totaldatadeencomenda_base),0) FROM FilteredInvoice
WHERE (esri_dataprevistadefacturacao >= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) AND esri_dataprevistadefacturacao <= DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)))
AND (salesorderid IS NULL OR (salesorderid IS NULL AND opportunityid IS NULL)) AND esri_idfacturaprimavera IS NULL AND ownerid = @consultorid AND statecode <> 3
AND opportunityid IN (SELECT opportunityid FROM FilteredOpportunity WHERE (opportunityratingcodename = '75' or opportunityratingcodename = '90' or opportunityratingcodename = '100'))
AND accountid IN (SELECT accountid FROM FilteredAccount WHERE esri_paisidname = @pais AND esri_marketidname = @market and esri_industriaidname = @industria))
END
ELSE
BEGIN
SET @pipeline75ytd = (SELECT ISNULL(SUM(esri_totaldatadeencomenda_base),0) FROM FilteredInvoice
WHERE (esri_dataprevistadefacturacao >= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) AND esri_dataprevistadefacturacao <= DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)))
AND (salesorderid IS NULL OR (salesorderid IS NULL AND opportunityid IS NULL)) AND esri_idfacturaprimavera IS NULL AND ownerid = @consultorid AND statecode <> 3
AND opportunityid IN (SELECT opportunityid FROM FilteredOpportunity WHERE (opportunityratingcodename = '75' or opportunityratingcodename = '90' or opportunityratingcodename = '100'))
AND accountid IN (SELECT accountid FROM FilteredAccount WHERE (esri_paisidname NOT LIKE 'Portugal' AND
esri_paisidname NOT LIKE 'Angola' AND esri_paisidname NOT LIKE 'Cabo Verde' AND esri_paisidname NOT LIKE 'Moçambique'
AND esri_paisidname NOT LIKE 'São Tomé e Príncipe' AND esri_paisidname NOT LIKE 'Guiné') AND esri_marketidname = @market and esri_industriaidname = @industria))
END
----------------

--- PIPELINE75ANO

DECLARE @pipeline75ano DECIMAL(12,2)

IF @pais NOT LIKE 'Outros'
BEGIN
SET @pipeline75ano = (SELECT ISNULL(SUM(esri_totaldatadeencomenda_base),0) FROM FilteredInvoice
WHERE (YEAR(esri_dataprevistadefacturacao) = YEAR(GETDATE()))
AND (salesorderid IS NULL OR (salesorderid IS NULL AND opportunityid IS NULL)) AND esri_idfacturaprimavera IS NULL AND ownerid = @consultorid AND statecode <> 3
AND opportunityid IN (SELECT opportunityid FROM FilteredOpportunity WHERE (opportunityratingcodename = '75' or opportunityratingcodename = '90' or opportunityratingcodename = '100'))
AND accountid IN (SELECT accountid FROM FilteredAccount WHERE esri_paisidname = @pais AND esri_marketidname = @market and esri_industriaidname = @industria))
END
ELSE
BEGIN
SET @pipeline75ano = (SELECT ISNULL(SUM(esri_totaldatadeencomenda_base),0) FROM FilteredInvoice
WHERE (YEAR(esri_dataprevistadefacturacao) = YEAR(GETDATE()))
AND (salesorderid IS NULL OR (salesorderid IS NULL AND opportunityid IS NULL)) AND esri_idfacturaprimavera IS NULL AND ownerid = @consultorid AND statecode <> 3
AND opportunityid IN (SELECT opportunityid FROM FilteredOpportunity WHERE (opportunityratingcodename = '75' or opportunityratingcodename = '90' or opportunityratingcodename = '100'))
AND accountid IN (SELECT accountid FROM FilteredAccount WHERE (esri_paisidname NOT LIKE 'Portugal' AND
esri_paisidname NOT LIKE 'Angola' AND esri_paisidname NOT LIKE 'Cabo Verde' AND esri_paisidname NOT LIKE 'Moçambique'
AND esri_paisidname NOT LIKE 'São Tomé e Príncipe' AND esri_paisidname NOT LIKE 'Guiné') AND esri_marketidname = @market and esri_industriaidname = @industria))
END
----------------


INSERT INTO @ValuesTable(realizadomes, realizadotrimestre, realizadoytd, realizadoano, backmes, backtrimestre, backytd, backano,
--objmes, objtrimestre, objytd, objano,
pipemes, pipetrimestre, pipeytd, pipeano)
values (@realizadomes, @realizadotrimestre, @realizadoytd, @realizadoano,
@backlogmes, @backlogtrimestre, @backlogytd, @backlogano,
--@objetivomes, @objetivotrimestre, @objetivoytd, @objetivoano,
@pipeline75mes, @pipeline75trimestre, @pipeline75ytd, @pipeline75ano)

RETURN
END



So obviously this will give me very poor performance.

Can someone help me and try to turn this or give me some hints on how to change this into a set based approach or at least make it run faster. It takes 15 mins to run.

Thanks

viggneshwar
Yak Posting Veteran

86 Posts

Posted - 2015-01-28 : 09:31:02
It seems same query in running more than once in IF and Else condition. Change the code to reduce it. If possible insert into #table and reuse it multiple times in the below codes.

Regards
Viggneshwar A
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-28 : 09:40:11
Cursors are about the worst way to code anything. They should only be used as a last resort when all other avenues have been explored. They almost always perform very badly

FWIW the only reason I can see in your code for using a cursor is that in the inner loop you have an INSERT followed by and UPDATE. Why not put a trigger on the table that is the object of the INSERT and in the trigger do the update logic? If you can do that, you should be able to replace the cursors with set-based logic.

another option for your use case is composable DML. Using the SQL OUTPUT clause, you can do INSERT the values based on the UPDATE, Here's an example:


declare @a table (a int)
declare @b table (b int)
insert into @a values (1),(2),(3)

insert into @b
select * from (
update @a
set a += 1
output inserted.*
) q


Logically, the UPDATE happens first, but the whole operation is atomic, so if the INSERT fails, the UPDATE would be rolled back anyway
Go to Top of Page

Ifor
Aged Yak Warrior

700 Posts

Posted - 2015-01-28 : 13:34:40
You should be able to get rid of the cursors by JOINing FilteredESRI_industria, FilteredSystemUser, ESRI_pais and CROSS APPLYing to a inline version of your function.

The function should look something like:

SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
GO
ALTER FUNCTION [dbo].[a_UserDefinedfn_GetValuesForSPUpdateVendasVsObjetivoMercados]
(
@consultorid uniqueidentifier,
@market nvarchar(max),
@industria nvarchar(max),
@pais nvarchar(max)
)
RETURNS table
AS
RETURN
(
SELECT
SUM
(
CASE
WHEN esri_dataprevistadefacturacao >= DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP), 0)
AND esri_dataprevistadefacturacao < DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP) + 1, 0)
AND esri_idfacturaprimavera IS NOT NULL
THEN esri_totaldatadeencomenda_base
ELSE CAST(0 AS decimal(12,2))
END
) AS realizadomes
,SUM
(
CASE
WHEN esri_dataprevistadefacturacao >= DATEADD(quarter, DATEDIFF(quarter, 0, CURRENT_TIMESTAMP), 0)
AND esri_dataprevistadefacturacao < DATEADD(quarter, DATEDIFF(quarter, 0, CURRENT_TIMESTAMP) + 1, 0)
AND esri_idfacturaprimavera IS NOT NULL
THEN esri_totaldatadeencomenda_base
ELSE CAST(0 AS decimal(12,2))
END
) AS realizadotrimestre
,SUM
(
CASE
WHEN esri_dataprevistadefacturacao < DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP) + 1, 0)
AND esri_idfacturaprimavera IS NOT NULL
THEN esri_totaldatadeencomenda_base
ELSE CAST(0 AS decimal(12,2))
END
) AS realizadoytd
,SUM
(
CASE
WHEN esri_dataprevistadefacturacao < DATEADD(year, DATEDIFF(year, 0, CURRENT_TIMESTAMP) + 1, 0)
AND esri_idfacturaprimavera IS NOT NULL
THEN esri_totaldatadeencomenda_base
ELSE CAST(0 AS decimal(12,2))
END
) AS realizadoano
,SUM
(
CASE
WHEN esri_dataprevistadefacturacao >= DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP), 0)
AND esri_dataprevistadefacturacao < DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP) + 1, 0)
AND esri_idfacturaprimavera IS NULL
THEN esri_totaldatadeencomenda_base
ELSE CAST(0 AS decimal(12,2))
END
) AS backmes
,SUM
(
CASE
WHEN esri_dataprevistadefacturacao >= DATEADD(quarter, DATEDIFF(quarter, 0, CURRENT_TIMESTAMP), 0)
AND esri_dataprevistadefacturacao < DATEADD(quarter, DATEDIFF(quarter, 0, CURRENT_TIMESTAMP) + 1, 0)
AND esri_idfacturaprimavera IS NULL
THEN esri_totaldatadeencomenda_base
ELSE CAST(0 AS decimal(12,2))
END
) AS backtrimestre
,SUM
(
CASE
WHEN esri_dataprevistadefacturacao < DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP) + 1, 0)
AND esri_idfacturaprimavera IS NULL
THEN esri_totaldatadeencomenda_base
ELSE CAST(0 AS decimal(12,2))
END
) AS backytd
,SUM
(
CASE
WHEN esri_dataprevistadefacturacao < DATEADD(year, DATEDIFF(year, 0, CURRENT_TIMESTAMP) + 1, 0)
AND esri_idfacturaprimavera IS NULL
THEN esri_totaldatadeencomenda_base
ELSE CAST(0 AS decimal(12,2))
END
) AS backano
,SUM
(
CASE
WHEN esri_dataprevistadefacturacao >= DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP), 0)
AND esri_dataprevistadefacturacao < DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP) + 1, 0)
AND esri_idfacturaprimavera IS NULL
AND D.opportunityid IS NOT NULL
THEN esri_totaldatadeencomenda_base
ELSE CAST(0 AS decimal(12,2))
END
) AS pipemes
,SUM
(
CASE
WHEN esri_dataprevistadefacturacao >= DATEADD(quarter, DATEDIFF(quarter, 0, CURRENT_TIMESTAMP), 0)
AND esri_dataprevistadefacturacao < DATEADD(quarter, DATEDIFF(quarter, 0, CURRENT_TIMESTAMP) + 1, 0)
AND esri_idfacturaprimavera IS NULL
AND D.opportunityid IS NOT NULL
THEN esri_totaldatadeencomenda_base
ELSE CAST(0 AS decimal(12,2))
END
) AS pipetrimestre
,SUM
(
CASE
WHEN esri_dataprevistadefacturacao < DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP) + 1, 0)
AND esri_idfacturaprimavera IS NULL
AND D.opportunityid IS NOT NULL
THEN esri_totaldatadeencomenda_base
ELSE CAST(0 AS decimal(12,2))
END
) AS pipeytd
,SUM
(
CASE
WHEN esri_dataprevistadefacturacao < DATEADD(year, DATEDIFF(year, 0, CURRENT_TIMESTAMP) + 1, 0)
AND esri_idfacturaprimavera IS NULL
AND D.opportunityid IS NOT NULL
THEN esri_totaldatadeencomenda_base
ELSE CAST(0 AS decimal(12,2))
END
) AS pipeano
FROM FilteredInvoice I
OUTER APPLY
(
SELECT TOP (1) O.opportunityid
FROM FilteredOpportunity O
WHERE O.opportunityid = I.opportunityid
AND O.opportunityratingcodename IN ('75', '90', '100')

) D
WHERE esri_dataprevistadefacturacao >= DATEADD(year, DATEDIFF(year, 0, CURRENT_TIMESTAMP), 0)
AND ownerid = @consultorid
AND statecode <> 3
AND
(
salesorderid IS NOT NULL
OR
(
salesorderid IS NULL
AND opportunityid IS NULL
)
)
AND EXISTS
(
SELECT 1
FROM FilteredAccount A
WHERE A.accountid = I.accountid
AND esri_marketidname = @market
AND esri_industriaidname = @industria
AND
(
@pais <> 'Outros'
AND esri_paisidname = CASE WHEN @pais = 'Nacional' THEN 'Portugal' ELSE @pais END
OR NOT esri_paisidname IN ('Angola', 'Cabo Verde', 'Moçambique', 'São Tomé e Príncipe', 'Guiné')
)
)
);
GO
Go to Top of Page

Ifor
Aged Yak Warrior

700 Posts

Posted - 2015-01-28 : 17:06:20
Your INSERT should look something like:

WITH IndustriasMarkets
AS
(
SELECT esri_marketidname AS market
,esri_name AS industria
FROM FilteredESRI_industria
WHERE statecode = 0
)
,Consultor
AS
(
SELECT DISTINCT systemuserid AS consultorid
,fullname AS consultor
,businessunitidname AS un
FROM FilteredSystemUser S
WHERE EXISTS
(
SELECT 1
FROM esri_consultorid C
WHERE C.esri_consultorid = S.systemuserid
AND C.Ano = YEAR(CURRENT_TIMESTAMP)
)
)
,Paises
AS
(
SELECT DISTINCT
case ESRI_name
when 'Portugal' then 'Nacional'
when 'Angola' then ESRI_name
when 'Cabo Verde' then ESRI_name
when 'Moçambique' then ESRI_name
when 'São Tomé e Príncipe' then ESRI_name
when 'Guiné' then ESRI_name
else 'Outros'
end AS pais
FROM ESRI_pais

)
INSERT INTO a_UserTable_CuboVendasVsObjectivosMercados
(
CONSULTORID, CONSULTOR, UNIDADENEGOCIO, MARKET, INDUSTRIA, PAIS
,REALIZADOMES, REALIZADOTRIMESTRE, REALIZADOYEARTOMONTH, REALIZADOANUAL
,BACKLOGMES, BACKLOGTRIMESTRE, BACKLOGYEARTOMONTH, BACKLOGANUAL
,PIPELINE75MES, PIPELINE75TRIMESTRE, PIPELINE75YEARTOMONTH, PIPELINE75ANUAL
)
SELECT C.consultorid, C.consultor, C.un, I.market, I.industria, P.pais
,F.realizadomes, F.realizadotrimestre, F.realizadoytd, F.realizadoano
,F.backmes, F.backtrimestre, F.backytd, F.backano
,F.pipemes, F.pipetrimestre, F.pipeytd, F.pipeano
FROM IndustriasMarkets I, Consultor C, Paises P
OUTER APPLY [dbo].[a_UserDefinedfn_GetValuesForSPUpdateVendasVsObjetivoMercados] (C.consultorid, I.market, I.industria, P.pais) F;
Go to Top of Page
   

- Advertisement -