Help with nested cursors and bad performance

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

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

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

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;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources