CPU intensive query #1

Please attache the .sqlplan file

http://msdn.microsoft.com/en-us/library/ms190646.aspx

Please post the entire batch and the execution plan.

Otherwise I can only guess: You're using only temporary tables. Seems that there are so much rows involved that this explains the tempdb usage. Your query uses two range conditions. Have you created indices on your temporary tables to support these?

here you go:

ExecutionPlan1.sqlplan

one thing:

1) #DD_Upload will use tempdb space?
2) if we don't want to use tempdb space but memory, how can we change the code for that?

I would want to see the full code to make more comment on this, but even then I may not see much. (the procedure code is truncated in the .sqlplan file)

Can you tell us something about the functional purpose of the procedure bspImportDDDelta?

it is not our application but we always see that from daily CPU chart, some query cause CPU hight and we would like to see why by what cause it/.

if it is truncated, I don't think I can recover it back as it is record by WhoisACtive and that's what I can get!

any way to improve that by WhoIsActive?

so without that, can't dia. that further ?

can't you just read the procedure itself?

this is it:

CREATE PROCEDURE [dbo].[bspImportDDDelta]
	@OfferID INT
	, @SourceID INT
	, @UserID INT
	, @NumberPlanID INT
	, @OfferContent VARCHAR(20)
	, @OfferDate DATETIME
	, @MultipleDatesAZFlag INT
	, @EnhancedDialedDigitEndDateLogic INT
	, @EnhanceVOExpirationLogic INT
	, @ExpirationDate DATETIME
	, @LogInfo VARCHAR(100)
	, @BaseOffer INT
AS
BEGIN

DECLARE @EffectiveDate DATETIME
DECLARE @MinVOEffDate DATETIME
DECLARE @MyResult VARCHAR(200)
DECLARE @EDate DATETIME

-- to store date for each country. For A-Z and Full-Country you can have only one date within a country 
CREATE TABLE #CountryDate(
	CountryCode VARCHAR(100)
	, EffectiveDate DATETIME
)
CREATE INDEX IDX_CountryDate ON #CountryDate(CountryCode)

CREATE TABLE #MstrCountryDate(
	CountryCode VARCHAR(100)
	, EffectiveDate DATETIME
)
CREATE INDEX idx_CC_MCC ON #MstrCountryDate(CountryCode)

-- Compare count also in case the DDs in the new set are a subset of the DDs in the old set
DECLARE @Count TABLE(
	DestinationID INT
	, CountPerm INT
	, CountTemp INT
	, PRIMARY KEY(DestinationID)
)

-- Data for the offer being uploaded
CREATE TABLE #IncomingDD(
	UploadDestinationID INT
	, DestinationID INT
	, OfferDate DATETIME
	, EffectiveDate DATETIME
	, DD VARCHAR(15)
	, CountryCode VARCHAR(100) 
	, Flag INT
	, PRIMARY KEY(DestinationID, DD)
)
CREATE INDEX idx_IncomingDD1 ON #IncomingDD(DD)
CREATE INDEX idx_IncomingDD2 ON #IncomingDD(DestinationID)

-- Data to be processed FROM the incoming offer
CREATE TABLE #IncomingOffer(
	UploadDestinationID INT
	, DestinationID INT
	, OfferDate DATETIME
	, EffectiveDate DATETIME
	, DD VARCHAR(15)
	, CountryCode VARCHAR(100) 
	, Flag INT
	, PRIMARY KEY(DestinationID, DD)
)
CREATE INDEX IncomingOffer_DD ON #IncomingOffer(DD)

-- Destinations FROM incoming offer that will be processed. Used later in the cursor for later offers
CREATE TABLE #IncomingDest(
	DestinationID INT
	, DD VARCHAR(15)
	, PRIMARY KEY(DestinationID, DD)
)

-- Data for the latest records before the one being uploaded
CREATE TABLE #PrevOffer(
	OfferID INT
	, DestinationID INT
	, BeginDate DATETIME
	, EndDate DATETIME
	, DD VARCHAR(15)
	, Flag INT
	, PRIMARY KEY(DestinationID, DD)
)

-- Prev DD's compared to this offer. Used to mark changed flag in tbUploadDD
CREATE TABLE #PrevDD (
	OfferID INT
	, DestinationID INT
	, BeginDate DATETIME
	, EndDate DATETIME
	, DD VARCHAR(15)
	, Flag INT
	, PRIMARY KEY(DestinationID, DD)
)
CREATE INDEX PrevDD_Dest ON #PrevDD(DestinationID)
CREATE INDEX PrevDD_DD ON #PrevDD(DD)

-- Create temp table to store duplicate DD's
CREATE TABLE #tmpImportDDDelta(
	 DialedDigits VARCHAR(15)
	, EndDate DATETIME
	, MinBegDate DATETIME
	, ModDate DATETIME
	, MinModDate DATETIME
)
CREATE INDEX IX_TmpImportDDDElta ON #tmpImportDDDelta (DialedDigits, MinBegDate, MinModDate)

CREATE TABLE #tbUploadDestination(
	UploadDestinationID INT
	, OfferID INT
	, DestinationID INT
	, OfferDate DATETIME
)

INSERT INTO #tbUploadDestination 
SELECT UploadDestinationID
	, OfferID
	, DestinationID
	, OfferDate 
FROM tbUploadDestination 
WHERE SourceID = @SourceID AND OfferDate >= OfferDate

CREATE INDEX idx_tbUploadDestination ON #tbUploadDestination(UploadDestinationID)

-- Data FROM uplaod tables for this source. To avoid repeated queries ON upload tables
CREATE TABLE #Upload (
	UploadDestinationID INT
	, OfferID INT
	, DestinationID INT
	, OfferDate DATETIME
	, EffectiveDate DATETIME		-- DD Effective date
	, DD varchar(15)
)

-- Get All the Dialed digits for the Source with Offer Date > Current Offer Date
INSERT #Upload(
	UploadDestinationID
	, OfferID
	, DestinationID
	, OfferDate
	, EffectiveDate		-- DD Effective date
	, DD
)
SELECT D.UploadDestinationID
	, D.OfferID
	, D.DestinationID
	, D.OfferDate
	, DD.EffectiveDate
	, DD.DD
FROM #tbUploadDestination D 
JOIN tbUploadDD DD ON D.UploadDestinationID = DD.UploadDestinationID
WHERE D.OfferDate > @OfferDate

CREATE INDEX Upload_DD ON #Upload(DD)
CREATE INDEX Upload_DestDD ON #Upload(DestinationID, DD)

-- create temp table to store uploadDD for this offer
CREATE TABLE #tempUploadDD (
	UploadDestinationID int
	, DD varchar(15)
	, CountryCode varchar(100)
	, EffectiveDate datetime
)

INSERT #tempUploadDD (
	UploadDestinationID
	, DD
	, CountryCode
	, EffectiveDate
)
SELECT
	DD.UploadDestinationID
	, DD.DD
	, DD.CountryCode
	, DD.EffectiveDate
FROM tbUploadDD DD
WHERE OfferID = @OfferID

CREATE INDEX TUDD_UploadDestinationID ON #tempUploadDD(UploadDestinationID)

-- Data for the offer being uploaded. Used for A-Z and Full Country retiring
CREATE TABLE #ODD(
	UploadDestinationID INT
	, DestinationID INT
	, OfferDate DATETIME
	, EffectiveDate DATETIME
	, DD VARCHAR(15)
	, CountryCode VARCHAR(100)
	, Flag INT
)

-- get data from the offer 
Insert #ODD(
	UploadDestinationID
	, DestinationID
	, OfferDate
	, EffectiveDate
	, DD
	, CountryCode
)
SELECT DISTINCT D.UploadDestinationID
	, D.DestinationID
	, D.OfferDate
	, DD.EffectiveDate
	, DD.DD
	, DD.CountryCode
FROM #tbUploadDestination D
JOIN #tempUploadDD DD ON DD.UploadDestinationID = D.UploadDestinationID

CREATE INDEX ODD_DD ON #ODD(DD)
CREATE INDEX idx_ODD1 ON #ODD(EffectiveDate) INCLUDE(DestinationID)

SELECT @MinVOEffDate = MIN(EffectiveDate) FROM #ODD

DECLARE	Date_Cursor CURSOR FOR
SELECT DISTINCT EffectiveDate
FROM #tempUploadDD
ORDER BY EffectiveDate

OPEN Date_Cursor	
FETCH NEXT FROM	Date_Cursor
INTO @EffectiveDate

WHILE	@@FETCH_STATUS = 0
BEGIN
	DROP INDEX idx_IncomingDD1 ON #IncomingDD
	DROP INDEX idx_IncomingDD2 ON #IncomingDD
	
	TRUNCATE TABLE #IncomingDD
	TRUNCATE TABLE #IncomingOffer
	TRUNCATE TABLE #PrevOffer
	TRUNCATE TABLE  #PrevDD
	DELETE FROM @Count
	TRUNCATE TABLE #tmpImportDDDelta
	IF OBJECT_ID('tempdb..#DDEff') IS NOT NULL
		DROP TABLE #DDEff

	-- get data from the incoming offer 
	INSERT #IncomingDD(
		UploadDestinationID
		, DestinationID
		, OfferDate
		, EffectiveDate
		, DD
		, CountryCode
	)
	SELECT UploadDestinationID
		, DestinationID
		, OfferDate
		, EffectiveDate
		, DD
		, CountryCode
	FROM #ODD 
	WHERE EffectiveDate = @EffectiveDate
	
	CREATE INDEX idx_IncomingDD1 ON #IncomingDD(DD)
	CREATE INDEX idx_IncomingDD2 ON #IncomingDD(DestinationID)
	
	SELECT *
	INTO #DDEff
	FROM #tbDialedDigits
	WHERE @EffectiveDate BETWEEN BeginDate AND ISNULL(EndDate, @EffectiveDate)
	AND Deleted = 0
	
	-- Get prev DD's for the destinations in the offer
	INSERT #PrevDD(
		DestinationID
		, BeginDate
		, EndDate
		, DD
		, Flag
	)
	SELECT DISTINCT DD.DestinationID
		, DD.BeginDate
		, DD.EndDate
		, DD.DialedDigits
		, 0
	FROM #DDEff DD
	join #IncomingDD I ON DD.DestinationID = I.DestinationID

	-- Get prev DD's for the destinations in the offer
	INSERT #PrevDD(
		DestinationID
		, BeginDate
		, EndDate
		, DD
		, Flag
	)
	SELECT DISTINCT DD.DestinationID
		, DD.BeginDate
		, DD.EndDate
		, DD.DialedDigits
		, 0
	FROM #DDEff DD 
	JOIN #IncomingDD I ON DD.DialedDigits = I.DD
	LEFT JOIN #PrevDD P ON DD.DialedDigits = P.DD
	WHERE P.DD IS NULL

	-- Call procedure to update changed flag in tbUploadDD
	IF @BaseOffer = 1 
	BEGIN
		EXEC @MyResult = aspUploadDDSetChanged 
		IF (@MyResult <> 0 OR @@ERROR <> 0) GOTO ERROR_HANDLER
	END	

	SELECT DISTINCT U.DestinationID
	INTO #Dest_Upload
	FROM #Upload U
	JOIN #IncomingDD I ON I.DestinationID = U.DestinationID
	WHERE U.OfferDate > I.OfferDate
	AND U.EffectiveDate <= I.EffectiveDate
	
	SELECT DISTINCT U.DestinationID
	INTO #DD_Dest_Upload
	FROM #Upload U
	JOIN #IncomingDD I ON U.DD = I.DD
	WHERE U.OfferDate > I.OfferDate
	AND U.EffectiveDate <= I.EffectiveDate

	
	SELECT DISTINCT U.DD
	INTO #DD_Upload
	FROM #Upload U
	JOIN #IncomingDD I ON U.DD = I.DD
	WHERE U.OfferDate > I.OfferDate
	AND U.EffectiveDate <= I.EffectiveDate
	
	-- get data FROM the incoming offer by comparing it with later offers
	-- omit record FROM the incoming offer if there is a later offer with a previous effective date
	INSERT #IncomingOffer (
		UploadDestinationID
		, DestinationID
		, OfferDate
		, EffectiveDate
		, DD
		, CountryCode
		, Flag
	)
	SELECT DD.UploadDestinationID
		, DD.DestinationID
		, DD.OfferDate
		, DD.EffectiveDate
		, DD.DD
		, DD.CountryCode
		, 0
	FROM #IncomingDD DD
	LEFT JOIN #Dest_Upload D_U ON D_U.DestinationID = DD.DestinationID
	LEFT JOIN #DD_Dest_Upload DD_D_U ON DD_D_U.DestinationID = DD.DestinationID
	LEFT JOIN #DD_Upload DD_U ON DD_U.DD = DD.DD
	WHERE D_U.DestinationID IS NULL
	AND DD_D_U.DestinationID IS NULL
	AND DD_U.DD IS NULL 
	
	DROP TABLE 	#Dest_Upload, #DD_Dest_Upload, #DD_Upload
	
	-- get destinations to be processed
	INSERT #IncomingDest(DestinationID, DD)
	SELECT I.DestinationID, I.DD 
	FROM #IncomingOffer I 
	LEFT JOIN #IncomingDest D ON I.DestinationID = D.DestinationID AND I.DD = D.DD
	WHERE D.DestinationID IS NULL
	
	INSERT #PrevOffer(
		OfferID
		, DestinationID
		, BeginDate
		, EndDate
		, DD
		, Flag
	)
	SELECT DISTINCT DD.OfferID
		, DD.DestinationID
		, DD.BeginDate
		, DD.EndDate
		, DD.DD
		, 0
	FROM #PrevDD DD 
	JOIN (SELECT DISTINCT DestinationID, EffectiveDate FROM #IncomingOffer) I ON DD.DestinationID = I.DestinationID
	
	IF @@ERROR <> 0 GOTO ERROR_HANDLER

	INSERT #PrevOffer(
		OfferID
		, DestinationID
		, BeginDate
		, EndDate
		, DD
		, Flag
	)
	SELECT DISTINCT DD.OfferID
		, DD.DestinationID
		, DD.BeginDate
		, DD.EndDate
		, DD.DD
		, 0
	FROM #PrevDD DD 
	JOIN #IncomingOffer I ON DD.DD = I.DD
	WHERE DD.DD NOT IN (SELECT DD FROM #PrevOffer)
	
	IF @@ERROR <> 0 GOTO ERROR_HANDLER
	
	-- Remove Future Dialed Digit Records from table, they will be added later again
	UPDATE DD
	SET DD.Deleted = 1
		, DD.ModifiedDate = GETDATE()
	FROM #tbDialedDigits DD
	JOIN (SELECT DISTINCT DestinationID, EffectiveDate FROM #IncomingOffer) I ON DD.DestinationID = I.DestinationID
	LEFT JOIN #IncomingOffer iDD ON  DD.DialedDigits = iDD.DD 
	WHERE DD.Deleted = 0 AND (DD.BeginDate > I.EffectiveDate OR (DD.BeginDate = I.EffectiveDate AND iDD.DD IS NULL))
		
	IF (@@ERROR <> 0) GOTO ERROR_HANDLER
	
	UPDATE DD
	SET DD.Deleted = 1
		, DD.ModifiedDate = GETDATE()
	FROM #tbDialedDigits DD 
	JOIN #IncomingOffer I ON DD.DialedDigits = I.DD
	WHERE DD.Deleted = 0 AND (DD.BeginDate > I.EffectiveDate OR (DD.BeginDate = I.EffectiveDate AND DD.DestinationID <> I.DestinationID))
	
	IF (@@ERROR <> 0) GOTO ERROR_HANDLER 

	-- set changed flag
	UPDATE #IncomingOffer
	SET Flag = Flag | 64
	WHERE UploadDestinationID IN(
		SELECT T.UploadDestinationID
		FROM #IncomingOffer T 
		LEFT JOIN #PrevOffer P ON P.DestinationID = T.DestinationID AND P.DD = T.DD 
		WHERE P.DD IS NULL
	)
	
	INSERT @Count(
		DestinationID
		, CountPerm
		, CountTemp
	)
	SELECT T.DestinationID
		, CountPerm
		, CountTemp
	FROM (SELECT DestinationID, COUNT(*) AS CountTemp FROM #IncomingOffer GROUP BY DestinationID) T
	JOIN (SELECT DestinationID, COUNT(*) AS CountPerm FROM #PrevOffer GROUP BY DestinationID) P ON T.DestinationID = P.DestinationID

    UPDATE T
	SET T.Flag = Flag | 64
	FROM @Count C
	JOIN #IncomingOffer T ON C.DestinationID = T.DestinationID
	WHERE CountTemp <> CountPerm
	
	IF (@@ERROR <> 0) GOTO ERROR_HANDLER 
	
	IF @EnhancedDialedDigitEndDateLogic = 1
	BEGIN
		-- Cases where dialed digit is there in offer but got swapped with other destination then expire the dialed digit with effective date in offer - 1
		-- Retire all records for each destination and this source (set enddate)  in delta
		UPDATE DD
		SET EndDate = T.EffectiveDate - 1
			, ModifiedDate = GETDATE()
			, ModifiedByID = @UserID
			, Updated = 1
		FROM #IncomingOffer T 
		JOIN #tbDialedDigits DD ON DD.DestinationID = T.DestinationID
		WHERE DD.Deleted = 0
		AND DD.EndDate IS NULL 
		AND DD.DialedDigits NOT IN (SELECT DD FROM #ODD WHERE EffectiveDate > @EffectiveDate)	-- Do not put end date on digits with future effective date	
		AND T.Flag & 64 = 64
		AND T.EffectiveDate = @EffectiveDate
	END
	ELSE
	BEGIN
		-- set prev dd end date to null
		UPDATE DD
		SET EndDate = NULL
			, Updated = 1
			, ModifiedDate = GETDATE()
		FROM #tbDialedDigits DD 
		JOIN #PrevOffer A ON DD.DestinationID = A.DestinationID AND DD.DialedDigits = A.DD AND DD.BeginDate = A.BeginDate
		WHERE DD.Deleted = 0 AND DD.EndDate IS NOT NULL
	
		-- Retire all records for each destination and this source (set enddate)  in delta
		UPDATE DD
		SET EndDate = T.EffectiveDate - 1
			, ModifiedDate = GETDATE()
			, ModifiedByID = @UserID
			, Updated = 1
		FROM #IncomingOffer T 
		JOIN #tbDialedDigits DD ON DD.DestinationID = T.DestinationID
		WHERE DD.Deleted = 0
		AND DD.EndDate IS NULL 
		AND T.Flag & 64 = 64
		AND T.EffectiveDate = @EffectiveDate
	END
	
	IF (@@ERROR <> 0) GOTO ERROR_HANDLER

	-- deleted records with EndDate < BeginDate to avoid dup digits during insert
	UPDATE #tbDialedDigits
	SET Deleted = 1
	WHERE EndDate < BeginDate

	IF (@@ERROR <> 0) GOTO ERROR_HANDLER

	-- Insert new records for each destination in delta
	-- DD's for new destination
	INSERT #tbDialedDigits(
		NumberPlanID
		, DestinationID
		, IntIndicator
		, DialedDigits
		, BeginDate
		, Inserted
		, ModifiedDate
		, ModifiedByID
		, Flag
	)
	SELECT @NumberPlanID
		, T.DestinationID
		, 1
		, T.DD
		, T.EffectiveDate
		, 1
		, GETDATE()
		, @UserID
		, 0
	FROM #IncomingOffer T
	WHERE T.Flag & 64 = 64 AND T.EffectiveDate = @EffectiveDate

	IF (@@ERROR <> 0) GOTO ERROR_HANDLER

	INSERT INTO #tmpImportDDDelta
	SELECT DialedDigits
		, MAX(BeginDate) EndDate
		, MIN(BeginDate) MinBegDate
		, MAX(ModifiedDate) ModDate
		, MIN(ModifiedDate) MinModDate
    FROM #tbDialedDigits
	WHERE EndDate IS NULL AND Deleted = 0
    GROUP BY DialedDigits, NumberPlanID
    HAVING COUNT(*) > 1

	-- solution for Duplicate DD's
	UPDATE D
	SET EndDate = A.EndDate - 1
		, ModifiedDate = A.ModDate
		, Updated = 1
	FROM #tbDialedDigits D 
	JOIN #tmpImportDDDelta A ON	D.DialedDigits = A.DialedDigits AND	D.BeginDate = A.MinBegDate AND D.ModifiedDate = A.MinModDate
	WHERE D.BeginDate < ISNULL(D.EndDate, D.BeginDate + 1) AND D.Deleted = 0
    
	IF (@@ERROR <> 0) GOTO ERROR_HANDLER	

	FETCH NEXT FROM	Date_Cursor
	INTO @EffectiveDate
END

CLOSE Date_Cursor
DEALLOCATE Date_Cursor

IF @OfferContent = 'Full Country'
BEGIN
	-- Retire all destinations that are not there in the offer for a country
	-- Do not retire shared countrycode(1,7)

    -- If the country has has only one effectivedate then use that Effectivedate to retire the expired digits. Otherwise
	-- *******Logic for retiring expired digits with multiple Effectivedates********* (added in #8735)
	-- The following is a list of options based ON the MultipleDatesAZFlag values(1,2,3)
	-- 1.	Maximum of (Offer Date,Minimum(Effective Date)) in a country
	-- 2.	Use the Offer Date
	-- 3.	Use the Maximum Effective Date in a country

	-- EffectiveDate in #countrydate is the date used to enddate the retired destinations
	INSERT #CountryDate(CountryCode, EffectiveDate)
	SELECT DISTINCT CountryCode
		, CASE WHEN MAX(EffectiveDate) = MIN(EffectiveDate) THEN MIN(EffectiveDate)
			   WHEN MAX(EffectiveDate) <> MIN(EffectiveDate) THEN CASE
				WHEN @MultipleDatesAZFlag = 1  THEN (CASE WHEN MIN(EffectiveDate) > CONVERT(VARCHAR(10),@OfferDate,126) THEN MIN(EffectiveDate)
		                                                  WHEN MIN(EffectiveDate) < CONVERT(VARCHAR(10),@OfferDate,126) THEN CONVERT(VARCHAR(10),@OfferDate,126) 
														  WHEN MIN(EffectiveDate) = CONVERT(VARCHAR(10),@OfferDate,126) THEN CONVERT(VARCHAR(10),@OfferDate,126)  
		                                             END) 
				WHEN @MultipleDatesAZFlag = 2  THEN CONVERT(VARCHAR(10),@OfferDate,126) 
				WHEN @MultipleDatesAZFlag = 3  THEN  MAX(EffectiveDate) 
			END 
        END EffectiveDate
	FROM #ODD
	WHERE CountryCode NOT IN (SELECT CountryCode FROM #SharedCountryCode)
	GROUP BY CountryCode
	
	INSERT INTO #MstrCountryDate
	SELECT CountryCode
		, CASE WHEN @EnhanceVOExpirationLogic = 0 THEN EffectiveDate
			   WHEN @EnhanceVOExpirationLogic = -1 THEN CASE WHEN @ExpirationDate > EffectiveDate THEN @ExpirationDate ELSE EffectiveDate END 
			   ELSE @ExpirationDate END
	FROM #CountryDate
	
	-- retire digits
	UPDATE DD
	SET EndDate = O.EffectiveDate - 1
		, ModifiedDate = GETDATE()
		, ModifiedByID = @UserID
		, Updated = 1
	FROM #tbDialedDigits DD 
	JOIN iXCore_Main.dbo.tbDestination D ON DD.DestinationID = D.DestinationID
    JOIN iXTrade_Port.dbo.tsCountry C ON D.CountryID = C.CountryID
	JOIN #MstrCountryDate O ON C.CountryCode = O.CountryCode
	LEFT JOIN #ODD T ON DD.DestinationID = T.DestinationID
	WHERE D.NumberPlanID = @NumberPlanID AND DD.Deleted = 0
	AND (DD.EndDate IS NULL OR DD.EndDate >= (O.EffectiveDate))
	AND T.DestinationID IS NULL
	AND DD.DialedDigits NOT IN (SELECT DD FROM #ODD) -- retire digits for FullCountry/A-Z for destinations not in the offer only if the digit is not in the offer. If the digit is in the offer it would have been retired already as part of the regular retiring

	IF (@@ERROR <> 0) GOTO ERROR_HANDLER
END

 
IF @OfferContent = 'A-Z'
BEGIN
	-- Retire all destinations that are not there in the offer for a country
	-- Retire shared countrycode(1,7) 

	-- EffectiveDate in #countrydate is the date used to enddate the retired destinations
	INSERT 	#CountryDate(CountryCode,EffectiveDate)
	SELECT DISTINCT CountryCode
		, CASE WHEN MAX(EffectiveDate)= MIN(EffectiveDate) THEN MIN(EffectiveDate)
			   WHEN MAX(EffectiveDate) <> MIN(EffectiveDate) THEN CASE
				WHEN @MultipleDatesAZFlag = 1 THEN (CASE WHEN MIN(EffectiveDate) > CONVERT(VARCHAR(10),@OfferDate,126) THEN MIN(EffectiveDate)
														 WHEN MIN(EffectiveDate) < CONVERT(VARCHAR(10),@OfferDate,126) THEN CONVERT(VARCHAR(10),@OfferDate,126) 
														 WHEN MIN(EffectiveDate) = CONVERT(VARCHAR(10),@OfferDate,126) THEN CONVERT(VARCHAR(10),@OfferDate,126)  
													END) 
				WHEN @MultipleDatesAZFlag = 2  THEN CONVERT(VARCHAR(10),@OfferDate,126) 
				WHEN @MultipleDatesAZFlag = 3  THEN  MAX(EffectiveDate) 
				END
		END EffectiveDate
	FROM #ODD
	GROUP BY CountryCode
	
	INSERT INTO #MstrCountryDate
	SELECT CountryCode
		, CASE WHEN @EnhanceVOExpirationLogic = 0 THEN EffectiveDate
			   WHEN @EnhanceVOExpirationLogic = -1 THEN CASE WHEN @ExpirationDate > EffectiveDate THEN @ExpirationDate ELSE EffectiveDate END 
			   ELSE @ExpirationDate END
	FROM #CountryDate
	
	-- retire  digits for destinations for countries that are in the offer. Have to retire products based ON the effdate for the country
	UPDATE DD
	SET EndDate = O.EffectiveDate - 1
		, ModifiedDate = GETDATE()
		, ModifiedByID = @UserID
		, Updated = 1
	FROM #tbDialedDigits DD 
	JOIN iXCore_Main.dbo.tbDestination D ON DD.DestinationID = D.DestinationID
	JOIN iXTrade_Port.dbo.tsCountry C ON D.CountryID = C.CountryID
	JOIN #MstrCountryDate O ON C.CountryCode = O.CountryCode
	LEFT JOIN #ODD T ON DD.DestinationID = T.DestinationID
	WHERE D.NumberPlanID = @NumberPlanID AND DD.Deleted = 0
	AND (DD.EndDate IS NULL OR DD.EndDate >= (O.EffectiveDate))
	AND T.DestinationID IS NULL
	AND DD.DialedDigits NOT IN (SELECT DD FROM #ODD) -- retire digits for FullCountry/A-Z for destinations not in the offer only if the digit is not in the offer. If the digit is in the offer it would have been retired already as part of the regular retiring

	IF (@@ERROR <> 0) GOTO ERROR_HANDLER
	
	SELECT @EDate = CASE WHEN @EnhanceVOExpirationLogic = 0 THEN CONVERT(VARCHAR(10),@OfferDate, 126) 
						 WHEN @EnhanceVOExpirationLogic = -1 THEN CASE WHEN @ExpirationDate > @MinVOEffDate THEN @ExpirationDate ELSE @MinVOEffDate END
						 ELSE @ExpirationDate END
						 
	-- retire digits for destinations that are not in the offer as of OfferDate
	UPDATE DD
	SET EndDate = @EDate - 1
		, ModifiedDate = GETDATE()
		, ModifiedByID = @UserID
		, Updated = 1
	FROM #tbDialedDigits DD 
	JOIN iXCore_Main.dbo.tbDestination D ON DD.DestinationID = D.DestinationID
	JOIN iXTrade_Port.dbo.tsCountry C ON D.CountryID = C.CountryID
	LEFT JOIN #ODD T ON DD.DestinationID = T.DestinationID
	WHERE D.NumberPlanID = @NumberPlanID AND DD.Deleted = 0
	AND (DD.EndDate IS NULL OR DD.EndDate >= (CONVERT(VARCHAR(10),@EDate,126)))
	AND T.DestinationID IS NULL
	AND DD.DialedDigits NOT IN (SELECT DD FROM #ODD) -- retire digits for FullCountry/A-Z for destinations not in the offer only if the digit is not in the offer. If the digit is in the offer it would have been retired already as part of the regular retiring
	AND D.CountryID NOT IN (SELECT CountryID FROM #tbCountryAsFullCountry)
	AND C.CountryCode NOT IN (SELECT CountryCode FROM #CountryDate)

	IF (@@ERROR <> 0) GOTO ERROR_HANDLER	
END

-- 2008.04.17 rs #18444 retire rates as per digit end date when there are multiple dates in a country
IF @OfferContent = 'Full Country' OR @OfferContent = 'A-Z'
BEGIN
	IF EXISTS (SELECT OfferID FROM wtbRateRetiring WHERE OfferID = @OfferID)
	BEGIN
		DECLARE @DDDates TABLE(DestinationID INT, MaxEndDate DATETIME)

		INSERT @DDDates (DestinationID, MaxEndDate)
		SELECT DD.DestinationID, MAX(DD.EndDate)
		FROM wtbRateRetiring W 
		JOIN #tbDialedDigits DD ON W.DestinationID = DD.DestinationID
		WHERE W.OfferID = @OfferID AND DD.Deleted = 0
		GROUP BY DD.DestinationID

		UPDATE R
		SET R.EndDate = CASE WHEN @EnhanceVOExpirationLogic = 0 THEN ISNULL(DD.MaxEndDate, R.EndDate)
								WHEN @EnhanceVOExpirationLogic = -1 THEN CASE WHEN @ExpirationDate > ISNULL(DD.MaxEndDate, R.EndDate) THEN @ExpirationDate - 1 ELSE ISNULL(DD.MaxEndDate, R.EndDate) END
								ELSE @ExpirationDate - 1 END
			, R.ModifiedDate = GETDATE()
			, R.ModifiedByID = @UserID
		FROM iXCore_Main.dbo.tbRate R 
		JOIN wtbRateRetiring W ON R.RateID = W.RateID	-- only if the rate record has remained the same and not been overwritten by later offers (incase this offer came out of sequence)
			AND	R.EndDate = W.EndDate	-- Also if the end date hasn't changed
		JOIN @DDDates DD ON  W.DestinationID = DD.DestinationID	
		WHERE W.OfferID = @OfferID

		IF (@@ERROR <> 0) GOTO ERROR_HANDLER	
		
		IF EXISTS(SELECT ProductID FROM wtbRateRetiring WHERE OfferID = @OfferID GROUP BY OfferID, RatePlanID, CountryID, ProductID, DestinationID HAVING COUNT(*) > 1)
		BEGIN
			SELECT RatePlanID, ProductID 
			INTO #PossibleOverlapProduct
			FROM wtbRateRetiring
			WHERE OFferID = @OfferID
			GROUP BY OfferID, RatePlanID, ProductID
			HAVING COUNT(*) > 1
			
			CREATE TABLE #tmpRate(
				ID INT IDENTITY(1,1)
				, RateID INT
				, RatePlanID INT
				, ProductID INT
				, BeginDate DATETIME
				, EndDate DATETIME
			)
			
			INSERT INTO #tmpRate
			SELECT R.RateID
				, R.RatePlanID
				, R.ProductID
				, R.BeginDate
				, R.EndDate
			FROM iXCore_Main.dbo.tbRate R
			JOIN #PossibleOverlapProduct P ON P.RatePlanID = R.RatePlanID AND P.ProductID = R.ProductID
			ORDER BY R.ProductID, R.BeginDate, R.EndDate
			
			UPDATE R2
			SET R2.EndDate = R.BeginDate - 1
			FROM #tmpRate R
			JOIN #tmpRate R1 ON R.ID = R1.ID + 1 AND R.ProductID = R1.ProductID
			JOIN #tmpRate R2 ON R1.RateID = R2.RateID
			WHERE R.BeginDate BETWEEN R1.BeginDate AND ISNULL(R1.EndDate, R.BeginDate)
			AND (R.BeginDate < R.EndDate OR R.EndDate IS NULL)
			
			IF OBJECT_ID('tempdb..#tmpRate') IS NOT NULL
				DROP TABLE #tmpRate
			IF OBJECT_ID('tempdb..#PossibleOverlapProduct') IS NOT NULL
				DROP TABLE #PossibleOverlapProduct
		END
		
		DELETE FROM wtbRateRetiring WHERE OfferID = @OfferID	
	END
END

IF OBJECT_ID('tempdb..#CountryDate') IS NOT NULL
	DROP TABLE #CountryDate
IF OBJECT_ID('tempdb..#MstrCountryDate') IS NOT NULL
	DROP TABLE #MstrCountryDate
IF OBJECT_ID('tempdb..#IncomingDD') IS NOT NULL
	DROP TABLE #IncomingDD
IF OBJECT_ID('tempdb..#IncomingOffer') IS NOT NULL
	DROP TABLE #IncomingOffer
IF OBJECT_ID('tempdb..#IncomingDest') IS NOT NULL
	DROP TABLE #IncomingDest
IF OBJECT_ID('tempdb..#PrevOffer') IS NOT NULL
	DROP TABLE #PrevOffer
IF OBJECT_ID('tempdb..#PrevDD') IS NOT NULL
	DROP TABLE #PrevDD
IF OBJECT_ID('tempdb..#tmpImportDDDelta') IS NOT NULL
	DROP TABLE #tmpImportDDDelta

RETURN 0

ERROR_HANDLER:
EXEC gspLogInsert @LogInfo, 'Upload', 'Upload Failed', @UserID


RETURN -1
END -- Procedure End

GO

Select allOpen in new window

it seems it really touch a lot of tempdb space:

IF OBJECT_ID('tempdb..#CountryDate') IS NOT NULL
	DROP TABLE #CountryDate
IF OBJECT_ID('tempdb..#MstrCountryDate') IS NOT NULL
	DROP TABLE #MstrCountryDate
IF OBJECT_ID('tempdb..#IncomingDD') IS NOT NULL
	DROP TABLE #IncomingDD
IF OBJECT_ID('tempdb..#IncomingOffer') IS NOT NULL
	DROP TABLE #IncomingOffer
IF OBJECT_ID('tempdb..#IncomingDest') IS NOT NULL
	DROP TABLE #IncomingDest
IF OBJECT_ID('tempdb..#PrevOffer') IS NOT NULL
	DROP TABLE #PrevOffer
IF OBJECT_ID('tempdb..#PrevDD') IS NOT NULL
	DROP TABLE #PrevDD
IF OBJECT_ID('tempdb..#tmpImportDDDelta') IS NOT NULL
	DROP TABLE #tmpImportDDDelta

Select allOpen in new window

Yes, absolutely, it does everything in temp tables.

I can't make much sense of it as I don't know anything about the purpose or the data being processed. Sorry.

I would discuss it with the developers of that code if that is possible.

"I would discuss it with the developers of that code if that is possible. "

yeah, so discuss with them about what? it take so much tempdb space because of this ?

What can be the suggestion ?  how to make it use RAM instead of tempdB ? we have RAM left.

As I said I have really not much idea what this code is doing functionally, or the data, or the volumes, or the frequency.

I would just say, Hey, I got a problem - can you help? This is consuming really scarce tempdb and I'm hoping you can suggest ways to avoid using that resource.

Ask them to explain it, why they are doing what they are doing, and if they could use alternatives (such as CTE's or normal tables)

Others here may have suggestions, so hold on for a while.

ok, so the direction of not using tempdb table, #dsxxxx , is not good anyway and use normal table is the direction ?

Using normal tables has overheads that you may not want either - particularly if the volumes on inserts and deletions is large - which will affect your logs - I keep telling you I really cannot provide much insight into this as I know nothing about the required functionality. Please don't rely on my conversational comments as factual of as best practice - I claim neither.

"I keep telling you I really cannot provide much insight into this as I know nothing about the required functionality. Please don't rely on my conversational comments as factual of as best practice - I claim neither. "

I just asking for advice, I don't think anyone is responsbile for anything.

then what is good thing to do then ?

Well, the procedure use a cursor.. imho this is the main problem.

Especially as you're creating and destroying temporary objects in the cursor loop. E.g. pure nonsense as long as you don't have measured it: You're creating the temporary table #IncomingDD before the cursor loop, but you're destroying and recreating indices on it. Also calling the procedure aspUploadDDSetChanged in the loop is - at least semantically - weird. Looks like it's loop invariant.

Another problem: You're using many conditions based on date ranges without having appropriate indices.

"Well, the procedure use a cursor.. imho this is the main problem.
"
that one only cause high CPU?

"You're creating the temporary table #IncomingDD before the cursor loop, but you're destroying and recreating indices on it. "

what is the point of it ?

"
 Another problem: You're using many conditions based on date ranges without having appropriate indices. "

so date range as the where cause but the date range do not have indexes?

tks.