I have performance issue with below SQL server query, it is taking more than 90 mints to execute. Please help, this query is to execute on version 2012.

Question

0.00/5 (No votes)

See more:

IF (
		@SiteCategoryId IN (
			SELECT lookupid
			FROM system_lookup
			WHERE lookuptype = 'ctsitecategory'
				AND lookupvalue IN (0)
			)
		)
BEGIN
	INSERT INTO #temp (
		RowNumber
		,SiteCTId
		,CTAppointmentDate
		,CTType
		,CTRemovalDate
		,OM_SiteCT.SiteId
		,ExtraCT
		)
	SELECT ROW_NUMBER() OVER (
			PARTITION BY OM_SiteCT.SiteId
			,ExtraCT ORDER BY SiteCTId
			) AS 'RowNumber'
		,SiteCTId
		,CTAppointmentDate
		,CTType
		,CTRemovalDate
		,OM_SiteCT.SiteId
		,ExtraCT
	FROM OM_SiteCT
		,Site
		,(
			SELECT DISTINCT Siteid
			FROM Share sh
			WHERE (
					(
						(sh.ShareNo = 0)
						AND (
							(
								SELECT SiteStatusId
								FROM SIte
								WHERE Siteid = sh.SiteId
								) = @AnchorOnAirStatusId
							AND (
								SELECT AnchorOnAirDate
								FROM SIte
								WHERE Siteid = sh.SiteId
								) <= GetDate()
							)
						)
					OR (
						(sh.ShareNo <> 0)
						AND (
							sh.ShareStatusId = @ShareOnAirStatusId
							AND ShareOnAirDate IS NOT NULL
							AND ShareOnAirDate <= GetDate()
							)
						)
					)
			) a
	WHERE Site.SiteId = OM_SiteCT.SiteId
		AND (
			Site.CircleId IN (
				SELECT value
				FROM dbo.fn_split(@CircleId, ',')
				)
			OR @CircleId = '0'
			)
		--AND Site.CircleID = CASE WHEN @CircleId = 0 THEN Site.CircleId ELSE @CircleId END
		AND Site.CircleId IN (
			SELECT CircleId
			FROM System_ContactCircles
			WHERE ContactId = @ContactId
			)
		AND (
			Site.EntityID IN (
				SELECT value
				FROM dbo.fn_split(@EntityId, ',')
				)
			OR @EntityId = '0'
			)
		--AND Site.EntityID = CASE WHEN @EntityId = 0 THEN Site.EntityId ELSE @EntityId END
		AND (
			(
				(
					OM_SiteCT.CTAppointmentDate BETWEEN @StartDT
						AND @EndDT
					)
				OR (
					ISNULL(OM_SiteCT.CTRemovalDate, GetDate()) BETWEEN @StartDT
						AND @EndDT
					)
				)
			OR (
				(
					@StartDT BETWEEN OM_SiteCT.CTAppointmentDate
						AND ISNULL(OM_SiteCT.CTRemovalDate, GetDate())
					)
				OR (
					@EndDT BETWEEN OM_SiteCT.CTAppointmentDate
						AND ISNULL(OM_SiteCT.CTRemovalDate, GetDate())
					)
				)
			)
		AND OM_SiteCT.CTRemovalDate IS NOT NULL
		AND site.siteid = a.siteid
END
	END

CREATE NONCLUSTERED INDEX #IDX_Temp ON #temp (
	SiteId
	,SiteCTId
	,ExtraCT
	) INCLUDE (
	CTAppointmentDate
	,CTType
	,CTRemovalDate
	)

DECLARE @RowNumber INT
DECLARE @SiteCTId INT
DECLARE @CTAppointmentDate DATETIME
DECLARE @CTType VARCHAR(50)
DECLARE @CTRemovalDate DATETIME
DECLARE @SiteId INT
DECLARE @ExtraCT BIT
DECLARE @baseValue INT
DECLARE @CTCount INT
DECLARE @ExtraCTCount INT
DECLARE @MaxCount INT
DECLARE @SitesCount INT
DECLARE @previousSiteId INT

SET @previousSiteId = 0

DECLARE @OddRow INT
DECLARE @rowId INT

SET @OddRow = 0
SET @rowId = 0

CREATE NONCLUSTERED INDEX #IDX_Temp_CT ON #temp_CT (
	SiteID
	,SId
	)

DECLARE Cur_SiteCT CURSOR
FOR
SELECT RowNumber
	,SiteCTId
	,CTAppointmentDate
	,CTType
	,CTRemovalDate
	,SiteId
	,ExtraCT
FROM #temp
ORDER BY SiteId
	,RowNumber

OPEN Cur_SiteCT

FETCH Cur_SiteCT
INTO @RowNumber
	,@SiteCTId
	,@CTAppointmentDate
	,@CTType
	,@CTRemovalDate
	,@SiteId
	,@ExtraCT

WHILE @@FETCH_STATUS = 0
BEGIN
	IF (@previousSiteId <> @SiteId)
	BEGIN
		SET @CTCount = CASE 
				WHEN (
						SELECT Count(*)
						FROM #temp
						WHERE ExtraCT = 0
							AND SiteId = @SiteId
						) = 0
					THEN 1
				ELSE (
						SELECT Count(*)
						FROM #temp
						WHERE ExtraCT = 0
							AND SiteId = @SiteId
						)
				END
		SET @ExtraCTCount = CASE 
				WHEN (
						SELECT Count(*)
						FROM #temp
						WHERE ExtraCT = 1
							AND SiteId = @SiteId
						) = 0
					THEN 1
				ELSE (
						SELECT Count(*)
						FROM #temp
						WHERE ExtraCT = 1
							AND SiteId = @SiteId
						)
				END
		SET @MaxCount = (
				SELECT CASE 
						WHEN (
								@CTCount = 1
								AND @ExtraCTCount = 1
								)
							THEN 1
						ELSE CASE 
								WHEN @CTCount > @ExtraCTCount
									THEN (
											CASE 
												WHEN @CTCount % 2 = 0
													THEN @CTCount / 2
												ELSE (@CTCount / 2) + 1
												END
											)
								ELSE (
										CASE 
											WHEN @ExtraCTCount % 2 = 0
												THEN @ExtraCTCount / 2
											ELSE (@ExtraCTCount) + 1
											END
										)
								END
						END
				)
		SET @baseValue = 0
		SET @OddRow = 0
		SET @rowId = 0

		WHILE (@baseValue < @MaxCount)
		BEGIN
			INSERT INTO #temp_CT (
				SId
				,SiteId
				)
			SELECT @baseValue + 1
				,SiteId
			FROM #temp
			WHERE #temp.SiteId = @SiteId
			GROUP BY #temp.SiteId

			SET @baseValue = @baseValue + 1
		END
	END

	SET @previousSiteId = @SiteId

	IF (@RowNumber % 2 <> 0)
	BEGIN
		IF (@RowNumber <> @OddRow)
		BEGIN
			SET @OddRow = @RowNumber
			SET @rowId = @rowId + 1
		END
	END
	ELSE
		SET @rowId = @rowId

	UPDATE #temp
	SET sId = @rowId
	WHERE SiteCTId = @SiteCTId
		AND SiteId = @SiteId

	UPDATE #temp_CT
	SET [Site ID] = Site.SiteRef
		,[Site Name] = Site.SiteName
		,[Circle] = System_Circle.CircleName
		,[Entity] = System_Entity.EntityName
		,[Site Type (GBT/RTT)] = (
			SELECT lookuptext
			FROM system_lookup
			WHERE lookupId = Site.TowerTypeId
				AND lookupType = 'TowerType'
			) --dbo.GetLookupText(Site.TowerTypeId)       
	FROM Site
	INNER JOIN #temp_CT ON #temp_CT.SiteID = Site.SiteID
	LEFT OUTER JOIN System_Circle ON System_Circle.CircleId = Site.CircleId
	LEFT OUTER JOIN System_Entity ON System_Entity.EntityId = Site.EntityId
	WHERE Site.SiteId = @SiteId

	IF (@RowNumber % 2 <> 0)
	BEGIN
		UPDATE #temp_CT
		SET [CT Appointment Date] = Convert(DATETIME, CTAppointmentDate, 103)
			,[CT Type (Vendor/Owner/Relative/Patrolling)] = ISNULL((
					SELECT lookuptext
					FROM system_lookup
					WHERE lookupId = CTType
						AND lookupType = 'CTType'
					), '')
			,--ISNULL(dbo.GetLookupText(CTType),''),
			[CT Removal Date] = Convert(DATETIME, CTRemovalDate, 103)
		FROM #temp
		WHERE #temp.SiteId = @SiteId
			AND #temp.ExtraCT = 0
			AND #temp.RowNumber = @RowNumber
			AND #temp.sId = #temp_CT.SId
			AND #temp_CT.SiteID = #temp.SiteID

		UPDATE #temp_CT
		SET [Extra CT Appointment Date] = Convert(DATETIME, CTAppointmentDate, 103)
			,[Extra CT Type (Vendor/Owner/Relative/Patrolling)] = ISNULL((
					SELECT lookuptext
					FROM system_lookup
					WHERE lookupId = CTType
						AND lookupType = 'CTType'
					), '')
			,--ISNULL(dbo.GetLookupText(CTType),''),
			[Extra CT Removal Date] = Convert(DATETIME, CTRemovalDate, 103)
		FROM #temp
		WHERE #temp.SiteId = @SiteId
			AND #temp.ExtraCT = 1
			AND #temp.RowNumber = @RowNumber
			AND #temp.sId = #temp_CT.SId
			AND #temp_CT.SiteID = #temp.SiteID
	END
	ELSE
		IF (@RowNumber % 2 = 0)
		BEGIN
			UPDATE #temp_CT
			SET [CT Reappointment Date] = Convert(DATETIME, CTAppointmentDate, 103)
				,[CT Reappointment  Type (Vendor/Owner/Relative/Patrolling)] = ISNULL((
						SELECT lookuptext
						FROM system_lookup
						WHERE lookupId = CTType
							AND lookupType = 'CTType'
						), '')
				,--ISNULL(dbo.GetLookupText(CTType),''),
				[CT Reappointment Removal Date] = Convert(DATETIME, CTRemovalDate, 103)
			FROM #temp
			WHERE #temp.SiteId = @SiteId
				AND #temp.ExtraCT = 0
				AND #temp.RowNumber = @RowNumber
				AND #temp.sId = #temp_CT.SId
				AND #temp_CT.SiteID = #temp.SiteID

			UPDATE #temp_CT
			SET [Extra CT Reappointment Date] = Convert(DATETIME, CTAppointmentDate, 103)
				,[Extra CT Reappointment  Type(Vendor/Owner/Relative/Patrolling)] = ISNULL((
						SELECT lookuptext
						FROM system_lookup
						WHERE lookupId = CTType
							AND lookupType = 'CTType'
						), '')
				,--ISNULL(dbo.GetLookupText(CTType),''),
				[Extra CT Reappointment Removal Date] = Convert(DATETIME, CTRemovalDate, 103)
			FROM #temp
			WHERE #temp.SiteId = @SiteId
				AND #temp.ExtraCT = 1
				AND #temp.RowNumber = @RowNumber
				AND #temp.sId = #temp_CT.SId
				AND #temp_CT.SiteID = #temp.SiteID
		END

	UPDATE #temp_CT
	SET [CT Status (Yes/No)] = dbo.GetCTStatus((
				SELECT SiteCTId
				FROM #temp
				WHERE #temp.SiteId = #temp_CT.SiteId
					AND #temp.CTAppointmentDate = #temp_CT.[CT Appointment Date]
					AND #temp.ExtraCT = 0
					AND #temp.sId = #temp_CT.SId
					AND #temp.RowNumber = @RowNumber
				), (
				SELECT SiteCTId
				FROM #temp
				WHERE #temp.SiteId = #temp_CT.SiteId
					AND #temp.CTAppointmentDate = #temp_CT.[Extra CT Appointment Date]
					AND #temp.ExtraCT = 1
					AND #temp.sId = #temp_CT.SId
					AND #temp.RowNumber = @RowNumber
				))
		,[Total No Of CT(Including Extra CT)] = dbo.GetTotalCTCount((
				SELECT SiteCTId
				FROM #temp
				WHERE #temp.SiteId = #temp_CT.SiteId
					AND #temp.CTAppointmentDate = #temp_CT.[CT Appointment Date]
					AND #temp.ExtraCT = 0
					AND #temp.sId = #temp_CT.SId
					AND #temp.RowNumber = @RowNumber
				), (
				SELECT SiteCTId
				FROM #temp
				WHERE #temp.SiteId = #temp_CT.SiteId
					AND #temp.CTAppointmentDate = #temp_CT.[Extra CT Appointment Date]
					AND #temp.ExtraCT = 1
					AND #temp.sId = #temp_CT.SId
					AND #temp.RowNumber = @RowNumber
				))
		,[Site Category] = (
			SELECT lookuptext
			FROM system_lookup
			WHERE lookuptype = 'CTsitecategory'
				AND lookupvalue = (
					dbo.GetTotalCTCount((
							SELECT SiteCTId
							FROM #temp
							WHERE #temp.SiteId = #temp_CT.SiteId
								AND #temp.CTAppointmentDate = #temp_CT.[CT Appointment Date]
								AND #temp.ExtraCT = 0
								AND #temp.sId = #temp_CT.SId
								AND #temp.RowNumber = @RowNumber
							), (
							SELECT SiteCTId
							FROM #temp
							WHERE #temp.SiteId = #temp_CT.SiteId
								AND #temp.CTAppointmentDate = #temp_CT.[Extra CT Appointment Date]
								AND #temp.ExtraCT = 1
								AND #temp.sId = #temp_CT.SId
								AND #temp.RowNumber = @RowNumber
							))
					)
			)

	FETCH Cur_SiteCT
	INTO @RowNumber
		,@SiteCTId
		,@CTAppointmentDate
		,@CTType
		,@CTRemovalDate
		,@SiteId
		,@ExtraCT
END

CLOSE Cur_SiteCT

DEALLOCATE Cur_SiteCT

IF (
		@SiteCategoryId IN (
			SELECT lookupid
			FROM system_lookup
			WHERE lookuptype = 'ctsitecategory'
				AND lookupvalue IN (0)
			)
		)
BEGIN
	--Select * from #temp Order By SiteId,RowNumber 
	SELECT [Site ID]
		,[Site Name]
		,[Circle]
		,[Entity]
		,[Site Type (GBT/RTT)]
		,[CT Status (Yes/No)]
		,[CT Appointment Date]
		,[CT Type (Vendor/Owner/Relative/Patrolling)]
		,[CT Removal Date]
		,[Extra CT Appointment Date]
		,[Extra CT Type (Vendor/Owner/Relative/Patrolling)]
		,[Extra CT Removal Date]
		,[CT Reappointment Date]
		,[CT Reappointment  Type (Vendor/Owner/Relative/Patrolling)]
		,[CT Reappointment Removal Date]
		,[Extra CT Reappointment Date]
		,[Extra CT Reappointment  Type(Vendor/Owner/Relative/Patrolling)]
		,[Extra CT Reappointment Removal Date]
		,[Total No Of CT(Including Extra CT)]
		,[Site Category]
	FROM #temp_CT
	WHERE [Site Category] = 'CAT-C'
	ORDER BY [Site ID]
   END
END

What I have tried:

I tried to find out an alternative for cursor to optimize it.

Posted 8-Apr-19 20:45pm

Gautam Sing

Updated 8-Apr-19 22:07pm

v3

Add a Solution

Comments

Gautam Sing 9-Apr-19 2:47am

Is the use of Cursor appropriate in the above query, or is there a need to use an alternative for cursor. Please help with alternative method to write this query in SQL Server.

OriginalGriff 9-Apr-19 3:35am

Just posting a long SQL query and saying "speed this up" isn't at all helpful: we don't know what it is supposed to do, and we don't have any access to your data to test it and find out (even if we wanted to, and personally that's a "no").

So instead, think about your data and what you are trying to get out of it, and look at the execution plan in SQL to try and spot the bottle necks.

[no name] 9-Apr-19 11:40am

Create a "data flow diagram". If you can't, you're in over your head.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2019-04-08T22:08:00

You are right in your attempted approach - get rid of that cursor. It's a very rare day when a cursor is required at all … My article gives several worked examples of alternatives in different scenarios. Processing Loops in SQL Server[^]

There really is too much code here for me to wade through to find you a specific solution (we do this in our spare time) and as @OriginalGriff has pointed out we are not usually too inclined to help with big code dumps, no sample data nor expected results and no idea of what you are trying to achieve.

I will say that you appear to be attempting to calculate some values based on values returned in the cursor. You can definitely do that as an entire set without having to step through each record. You could update the values in a temporary table in a series of UPDATE statements rather than a CURSOR

You have some lines where you are using SET @rowId = @rowId + 1 ... yuk. Consider either putting an Identity column on your temporary table or using ROW_NUMBER (Transact-SQL) - SQL Server | Microsoft Docs[^]