Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Оптимизация ХП содержащей в себе LIKE  [new]
Paco
Member

Откуда: Анапа
Сообщений: 109
Прошу помощи. Знаю что, при запросе в котором используются LIKE-ки индексы не используются. В связи с чем запрос выполняется несколько минут и мой хостинг-провайдер из-за этого очень зол. Зол вплоть до отключения моей БД :(

Можно ли как-то оптимизировать этот запрос внутри ХП?

/* Выборка списка населенных пунктов, для которых в БД есть объекты недвижимости */
ALTER PROCEDURE [dbo].[realty_GetExistSettlement]
(
	@ParentCode nvarchar(13),
	@ID_ObjectType int
)
AS

-- процедура принимает код уровня КЛАДР и выбирает из таблицы объектов всех потомков переданного уровня, объекта заданной категории 
-- выборка делается из таблицы КЛАДР
-- Сначала определяем уровень переданного кода

-- зная уровень выберим из первых четырех уровней все н/п у которых код LIKE переданный_код_без_конечных_нулей + '%'
	DECLARE @Code nvarchar(13)
	DECLARE @Code1 nvarchar(13)
	SET @Code = @ParentCode
	SET @Code1 = @Code
	-- убрать в конце нули
	IF SUBSTRING(@Code1, 9, 3) = '000'
	BEGIN
		SET @Code1 = LEFT(@Code1, 8)

		IF SUBSTRING(@Code1, 6, 3) = '000'
		BEGIN
			SET @Code1 = LEFT(@Code1, 5)

				IF SUBSTRING(@Code1, 3, 3) = '000'
				BEGIN
					SET @Code1 = LEFT(@Code1, 2)
				END
		END
	END

	
	DECLARE @RankTable TABLE
	(
		Rank int identity not null,
		Code nvarchar(13),
		[Name] nvarchar(40),
		ID_Object int
	);

	IF @ID_ObjectType = 1
	BEGIN
	INSERT INTO @RankTable	
	(
		Code,
		[Name],
		ID_Object
	) 
	SELECT AddressLevel4Code, AddressLevel4, ID_Building FROM realty_Building WHERE 
		AddressLevel4Code LIKE @Code1 + '%' 
		AND AddressLevel4Code <> @Code

	INSERT INTO @RankTable	
	(
		Code,
		[Name],
		ID_Object
	) 
	SELECT AddressLevel3Code, AddressLevel3, ID_Building FROM realty_Building WHERE 
		AddressLevel3Code LIKE @Code1 + '%' 
		AND AddressLevel3Code <> @Code
		AND ID_Building NOT IN (SELECT ID_Object FROM @RankTable)
	
	INSERT INTO @RankTable	
	(
		Code,
		[Name],
		ID_Object
	) 
	SELECT AddressLevel2Code, AddressLevel2, ID_Building FROM realty_Building WHERE 
		AddressLevel2Code LIKE @Code1 + '%' 
		AND AddressLevel2Code <> @Code
		AND ID_Building NOT IN (SELECT ID_Object FROM @RankTable)
		
	INSERT INTO @RankTable	
	(
		Code,
		[Name],
		ID_Object
	) 
	SELECT AddressLevel4Code, AddressLevel4, ID_Building FROM realty_Building WHERE 
		(AddressLevel2Code LIKE @Code1 + '%' OR AddressLevel3Code LIKE @Code1 + '%')
		AND AddressLevel4Code IS NULL
		AND AddressLevel4 IS NOT NULL
		AND ID_Building NOT IN (SELECT ID_Object FROM @RankTable)
		AND AbsentAddress = 1
	END
	ELSE
	BEGIN
	INSERT INTO @RankTable	
	(
		Code,
		[Name],
		ID_Object
	) 
	SELECT AddressLevel4Code, AddressLevel4, ID_Object FROM realty_Object WHERE 
		AddressLevel4Code LIKE @Code1 + '%' 
		AND AddressLevel4Code <> @Code
		AND ID_ObjectType = @ID_ObjectType

	INSERT INTO @RankTable	
	(
		Code,
		[Name],
		ID_Object
	) 
	SELECT AddressLevel3Code, AddressLevel3, ID_Object FROM realty_Object WHERE 
		AddressLevel3Code LIKE @Code1 + '%' 
		AND AddressLevel3Code <> @Code
		AND ID_Object NOT IN (SELECT ID_Object FROM @RankTable)
		AND ID_ObjectType = @ID_ObjectType
	
	INSERT INTO @RankTable	
	(
		Code,
		[Name],
		ID_Object
	) 
	SELECT AddressLevel2Code, AddressLevel2, ID_Object FROM realty_Object WHERE 
		AddressLevel2Code LIKE @Code1 + '%' 
		AND AddressLevel2Code <> @Code
		AND ID_Object NOT IN (SELECT ID_Object FROM @RankTable)
		AND ID_ObjectType = @ID_ObjectType
		
	INSERT INTO @RankTable	
	(
		Code,
		[Name],
		ID_Object
	) 
	SELECT AddressLevel4Code, AddressLevel4, ID_Object FROM realty_Object WHERE 
		(AddressLevel2Code LIKE @Code1 + '%' OR AddressLevel3Code LIKE @Code1 + '%')
		AND AddressLevel4Code IS NULL
		AND AddressLevel4 IS NOT NULL
		AND ID_Object NOT IN (SELECT ID_Object FROM @RankTable)
		AND ID_ObjectType = @ID_ObjectType
		AND AbsentAddress = 1
	END

	SELECT DISTINCT
		r.Code,
		r.Name + ' (' + CAST((SELECT Count([Name]) FROM @RankTable WHERE [Name] = r.[Name]) as nvarchar(30)) + ')' as [Name],
		r.Name as CleanName
	FROM @RankTable as r
	ORDER BY [Name]
7 апр 15, 23:38    [17485944]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация ХП содержащей в себе LIKE  [new]
X-Cite
Member

Откуда: Минск
Сообщений: 1814
Paco
Прошу помощи. Знаю что, при запросе в котором используются LIKE-ки индексы не используются.

Не знаете.
При LIKE 'AAA%' используются
7 апр 15, 23:48    [17485967]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация ХП содержащей в себе LIKE  [new]
X-Cite
Member

Откуда: Минск
Сообщений: 1814
Paco,

Замените DECLARE @RankTable TABLE на временную таблицу.
Положите на нее соответствующие индексы перед выборкой.

И вообще... вы проверяли на каком этапе идет просадка?
План и все такое?
8 апр 15, 00:01    [17485996]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация ХП содержащей в себе LIKE  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31785
Paco
Знаю что, при запросе в котором используются LIKE-ки индексы не используются. В связи с чем запрос выполняется несколько минут и мой хостинг-провайдер из-за этого очень зол. Зол вплоть до отключения моей БД :(

Можно ли как-то оптимизировать этот запрос внутри ХП?
1. Используются. Если они есть, конечно.
2. для начала найдите этот запрос(ы).
Выполните эту процедуру из SSMS, предварительно настроив трейс на SPID коннекта, и сделав в нём заход внутрь процедуры, и показ реальных планов выполнения.
Дальше находите проблемные запросы, оптимизируйте их.
Возможно, достаточно будет, как уже писали, заменить таблицу-переменную @RankTable на временную таблицу, и сделав индекс по ID_Object. Или, как вариант, сделав кластерный ПК по нему в таблице-переменной, если это поле уникально.
8 апр 15, 10:45    [17487003]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация ХП содержащей в себе LIKE  [new]
X-Cite
Member

Откуда: Минск
Сообщений: 1814
alexeyvg
Paco
Знаю что, при запросе в котором используются LIKE-ки индексы не используются. В связи с чем запрос выполняется несколько минут и мой хостинг-провайдер из-за этого очень зол. Зол вплоть до отключения моей БД :(

Можно ли как-то оптимизировать этот запрос внутри ХП?
1. Используются. Если они есть, конечно.
2. для начала найдите этот запрос(ы).
Выполните эту процедуру из SSMS, предварительно настроив трейс на SPID коннекта, и сделав в нём заход внутрь процедуры, и показ реальных планов выполнения.
Дальше находите проблемные запросы, оптимизируйте их.
Возможно, достаточно будет, как уже писали, заменить таблицу-переменную @RankTable на временную таблицу, и сделав индекс по ID_Object. Или, как вариант, сделав кластерный ПК по нему в таблице-переменной, если это поле уникально.

Как раз таки судя по последнему запросу индекс нужен не по ID_Object, а по Name.
Поэтому там надо будут два индекса и по ID_Object для подзапросов и по Name для последнего при том Code в Included д.б.
И имеет смысл по Id_Object добавить сразу после создания таблицы, а по Name перед последним запросом.
8 апр 15, 10:51    [17487054]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация ХП содержащей в себе LIKE  [new]
Paco
Member

Откуда: Анапа
Сообщений: 109
X-Cite,

спасибо за наводку на план. Он рекомендовал следующее:
/*
Missing Index Details from SQLQuery1.sql
The Query Processor estimates that implementing the following index could improve the query cost by 76.0531%.
*/


USE [YourDBName]
GO
CREATE NONCLUSTERED INDEX [AddrLevel4Index]
ON [dbo].[realty_Object] ([ID_ObjectType],[AddressLevel4Code])
INCLUDE ([ID_Object],[AddressLevel4])
GO


После создания этого индекса самой дорогой операцией стал Insert в @RankTable.

P.S. А разве временные таблицы эффективнее, чем созданные с помощью table?
8 апр 15, 11:09    [17487166]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация ХП содержащей в себе LIKE  [new]
Slava_Nik
Member

Откуда: из России
Сообщений: 892
Paco,
эффективны или не эффективны, но разница есть.
почитайте теорию по табличным переменным и временным таблицам, где и что нужно использовать и почему.
8 апр 15, 11:19    [17487254]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация ХП содержащей в себе LIKE  [new]
Glory
Member

Откуда:
Сообщений: 104751
Создаете один FTS индекс на все ваши "бесконечные" поля AddressLevelNNNCode
И вместо повторяющихся запросов пишите один
8 апр 15, 11:24    [17487284]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация ХП содержащей в себе LIKE  [new]
Paco
Member

Откуда: Анапа
Сообщений: 109
Всем спасибо! Советы помогли.
Проставил рекомендуемые в актуальном плане выполнения индексы, сделал временные таблицы вместо таблиц-переменных и запрос стал отрабатываться меньше чем за секунду.
8 апр 15, 12:05    [17487613]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить