Gautam Sing Ответов: 1

У меня есть проблема с производительностью с нижеприведенным запросом SQL server, для его выполнения требуется более 90 минут. Пожалуйста, помогите, этот запрос должен быть выполнен на версии 2012.

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.

Gautam Sing

Уместно ли использование курсора в приведенном выше запросе, или есть необходимость использовать альтернативу для курсора. Пожалуйста, помогите с альтернативным методом записи этого запроса в SQL Server.

OriginalGriff

Просто разместить длинный SQL-запрос и сказать "ускорить это" совсем не полезно: мы не знаем, что он должен делать, и у нас нет никакого доступа к вашим данным, чтобы проверить его и выяснить (даже если бы мы захотели, и лично это "нет").

Поэтому вместо этого подумайте о своих данных и о том, что вы пытаетесь из них извлечь, и посмотрите на план выполнения в SQL, чтобы попытаться определить горлышки бутылок.

Gerry Schmitz

Создайте "диаграмму потока данных". Если вы не можете, то вы по уши в дерьме.

1 Ответов

Рейтинг:
2

CHill60

Вы правы в своем подходе - избавьтесь от этого курсора. Это очень редкий день, когда курсор вообще требуется … В моей статье приводится несколько отработанных примеров альтернатив в различных сценариях. Циклы обработки в SQL Server[^]

Здесь действительно слишком много кода, чтобы я мог пробираться, чтобы найти вам конкретное решение (мы делаем это в свободное время), и, как отметил @OriginalGriff, мы обычно не слишком склонны помогать с большими дампами кода, без образцов данных, ожидаемых результатов и без представления о том, чего вы пытаетесь достичь.

Я скажу, что вы, по-видимому, пытаетесь вычислить некоторые значения, основанные на значениях, возвращаемых курсором. Вы определенно можете сделать это как целый набор, не проходя через каждую запись. Вы можете обновить значения во временной таблице с помощью серии инструкций UPDATE, а не курсора

У вас есть несколько строк, где вы используете SET @rowId = @rowId + 1 ... yuk. Подумайте о том, чтобы либо поместить столбец идентификаторов во временную таблицу, либо использовать ROW_NUMBER (Transact-SQL) - SQL Server | Microsoft Docs[^]

Maciej Los

5ed!