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

Откуда: Ставрополь
Сообщений: 55
Всем добрый день!
Прошу помочь убрать из плана выполнения запроса операцию KEY LOOKUP.
Вот собственно сам текст запроса:
SELECT
			ra_328.sp331
			,ra_328.sp330
			,ra_328.sp4061
			,ra_328.sp340
			,ra_328.sp341
			,ra_328.sp1554
			,ra_328.sp7404
			,CASE WHEN ra_328.debkred = 0 THEN -ra_328.sp342 ELSE ra_328.sp342 END
			,CASE WHEN ra_328.debkred = 0 THEN -ra_328.sp421 ELSE ra_328.sp421 END
			,CASE WHEN ra_328.debkred = 0 THEN -ra_328.sp343 ELSE ra_328.sp343 END
			,CASE WHEN ra_328.debkred = 0 THEN -ra_328.sp344 ELSE ra_328.sp344 END
			
		FROM
			RA328 AS ra_328 
		WHERE (ra_328.DATE_TIME_IDDOC >= '2010082681XO68  7HJA   ')
		AND (ra_328.DATE_TIME_IDDOC < '201008268815EO  7HMA  0')
План выполнения запроса:
  |--Compute Scalar(DEFINE:([Expr1002]=CASE WHEN CONVERT_IMPLICIT(tinyint,[scat].[dbo].[RA328].[DEBKRED] as [ra_328].[DEBKRED],0)=(0) THEN  -[scat].[dbo].[RA328].[SP342] as [ra_328].[SP342] ELSE [scat].[dbo].[RA328].[SP342] as [ra_328].[SP342] END, [Expr1003]=CASE WHEN CONVERT_IMPLICIT(tinyint,[scat].[dbo].[RA328].[DEBKRED] as [ra_328].[DEBKRED],0)=(0) THEN  -[scat].[dbo].[RA328].[SP421] as [ra_328].[SP421] ELSE [scat].[dbo].[RA328].[SP421] as [ra_328].[SP421] END, [Expr1004]=CASE WHEN CONVERT_IMPLICIT(tinyint,[scat].[dbo].[RA328].[DEBKRED] as [ra_328].[DEBKRED],0)=(0) THEN  -[scat].[dbo].[RA328].[SP343] as [ra_328].[SP343] ELSE [scat].[dbo].[RA328].[SP343] as [ra_328].[SP343] END, [Expr1005]=CASE WHEN CONVERT_IMPLICIT(tinyint,[scat].[dbo].[RA328].[DEBKRED] as [ra_328].[DEBKRED],0)=(0) THEN  -[scat].[dbo].[RA328].[SP344] as [ra_328].[SP344] ELSE [scat].[dbo].[RA328].[SP344] as [ra_328].[SP344] END))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([ra_328].[IDDOC], [ra_328].[LINENO_], [ra_328].[ACTNO]) OPTIMIZED)
            |--Index Seek(OBJECT:([scat].[dbo].[RA328].[DATETIME] AS [ra_328]), SEEK:([ra_328].[DATE_TIME_IDDOC] >= '2010082681XO68  7HJA   ' AND [ra_328].[DATE_TIME_IDDOC] < '201008268815EO  7HMA  0') ORDERED FORWARD)
            |--Clustered Index Seek(OBJECT:([scat].[dbo].[RA328].[PK_RA328] AS [ra_328]), SEEK:([ra_328].[IDDOC]=[scat].[dbo].[RA328].[IDDOC] as [ra_328].[IDDOC] AND [ra_328].[LINENO_]=[scat].[dbo].[RA328].[LINENO_] as [ra_328].[LINENO_] AND [ra_328].[ACTNO]=[scat].[dbo].[RA328].[ACTNO] as [ra_328].[ACTNO]) LOOKUP ORDERED FORWARD)
План запроса в графическом виде и скрипт создания таблицы прикладываю.
26 авг 10, 14:57    [9330726]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса KEY LOOKUP  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
Сделать кластерный индекс DATE_TIME_IDDOC, или индекс по DATE_TIME_IDDOC с инклудом всех полей в запросе, или покрывающий по всем полям в запросе.
26 авг 10, 14:59    [9330751]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса KEY LOOKUP  [new]
leshikkam
Member

Откуда: Ставрополь
Сообщений: 55
А файл не приложился :-(
/****** Object:  Table [dbo].[RA328]    Script Date: 08/26/2010 14:55:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[RA328](
	[IDDOC] [char](9) NOT NULL,
	[LINENO_] [smallint] NOT NULL,
	[ACTNO] [int] NOT NULL,
	[DEBKRED] [bit] NOT NULL,
	[IDDOCDEF] [int] NOT NULL,
	[DATE_TIME_IDDOC] [char](23) NOT NULL,
	[SP331] [char](9) NOT NULL,
	[SP330] [char](9) NOT NULL,
	[SP4061] [char](9) NOT NULL,
	[SP340] [char](9) NOT NULL,
	[SP341] [char](9) NOT NULL,
	[SP1554] [datetime] NOT NULL,
	[SP7404] [numeric](14, 2) NOT NULL,
	[SP342] [numeric](14, 5) NOT NULL,
	[SP421] [numeric](14, 2) NOT NULL,
	[SP343] [numeric](14, 2) NOT NULL,
	[SP344] [numeric](14, 2) NOT NULL,
	[SP347] [char](9) NOT NULL,
	[SP6818] [numeric](17, 2) NOT NULL,
	[SP7685] [numeric](14, 2) NOT NULL,
	[SP13048] [char](9) NOT NULL,
 CONSTRAINT [PK_RA328] PRIMARY KEY CLUSTERED 
(
	[IDDOC] ASC,
	[LINENO_] ASC,
	[ACTNO] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
Вот скрипт создания таблицы
26 авг 10, 14:59    [9330752]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса KEY LOOKUP  [new]
leshikkam
Member

Откуда: Ставрополь
Сообщений: 55
Гавриленко Сергей Алексеевич
Сделать кластерный индекс DATE_TIME_IDDOC, или индекс по DATE_TIME_IDDOC с инклудом всех полей в запросе, или покрывающий по всем полям в запросе.

Ок. сейчас попробуем.
26 авг 10, 15:02    [9330788]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса KEY LOOKUP  [new]
leshikkam
Member

Откуда: Ставрополь
Сообщений: 55
В общем добавление в INCLUDE полей запроса только изменило соотношение - 10% осталось на Index seek и 90% стал KEY LOOKUP.
Добавление Date_Time_Iddoc в PK решило проблему на 100% но пока это не самый удобный вариант так как надо освоить технологию подмены описания индексов при изменении конфигурации.
Осталось попробовать сделать покрывающий индекс.
26 авг 10, 15:47    [9331310]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса KEY LOOKUP  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
leshikkam
В общем добавление в INCLUDE полей запроса только изменило соотношение - 10% осталось на Index seek и 90% стал KEY LOOKUP.
Если вы добавили все поля, которые у вас упоминаются в запросе, то какие же поля достает lookup?

leshikkam
Добавление Date_Time_Iddoc в PK решило проблему на 100% но пока это не самый удобный вариант так как надо освоить технологию подмены описания индексов при изменении конфигурации.
Primary key не обязательно должен быть кластерным. Кластерный индекс не обязательно должен быть уникальным и соответствовать ключу.
26 авг 10, 15:50    [9331336]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса KEY LOOKUP  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381

> В общем добавление в INCLUDE полей запроса только изменило соотношение -
> 10% осталось на Index seek и 90% стал KEY LOOKUP.

а скрипт создания этого индекса можно увидеть?

Posted via ActualForum NNTP Server 1.4

26 авг 10, 15:51    [9331342]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса KEY LOOKUP  [new]
leshikkam
Member

Откуда: Ставрополь
Сообщений: 55
daw

> В общем добавление в INCLUDE полей запроса только изменило соотношение -
> 10% осталось на Index seek и 90% стал KEY LOOKUP.

а скрипт создания этого индекса можно увидеть?

Посыпаю голову пеплом - действительно я пропустил одно из полей.
Добавление в INCLUDE перечня полей запроса полностью исключило KEY LOOKUP.
Спасибо большое!!!
26 авг 10, 15:59    [9331430]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Оптимизация запроса KEY LOOKUP  [new]
munster
Member

Откуда:
Сообщений: 36
День добрый.
есть у меня запрос, который занимает ресурсов вроде бы мало, но выплолняется настолько часто, что приводит к блокировкам

что самое странное не пойму зачем в нем KEY LOOKUP, на который 99% - просмотрел все поля вроде бы есть в индексе, статистика обновляется ежедневно..
прошу подсказки

К сообщению приложен файл (query_plan21.sqlplan - 92Kb) cкачать
23 авг 12, 11:06    [13053695]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса KEY LOOKUP  [new]
denis2710
Member

Откуда: Москва
Сообщений: 3384
munster, не правда в индексе не все поля,которые выбираются.
23 авг 12, 11:15    [13053790]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса KEY LOOKUP  [new]
munster
Member

Откуда:
Сообщений: 36
denis2710,

укажите мне, пожалуйста, чего я не вижу?
Period, Tref, Rref, LineNo - ничего более нет.
23 авг 12, 11:26    [13053902]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса KEY LOOKUP  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
munster
Period, Tref, Rref, LineNo - ничего более нет.
А в запросе разве только эти столбцы выбираются?
23 авг 12, 11:32    [13053961]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса KEY LOOKUP  [new]
denis2710
Member

Откуда: Москва
Сообщений: 3384
munster,

+ запрос
SELECT TOP 20001
T1._Period,
T1._RecorderTRef,
T1._RecorderRRef,
T1._LineNo,
T1._Active,
T1._Fld17376,
T1._Fld17377RRef,
T1._Fld17378,
T1._Fld17379RRef,
T1._Fld17380RRef,
T1._Fld17381_TYPE,
T1._Fld17381_RTRef,
T1._Fld17381_RRRef,
T1._Fld17382_TYPE,
T1._Fld17382_RTRef,
T1._Fld17382_RRRef,
T1._Fld17383_TYPE,
T1._Fld17383_RTRef,
T1._Fld17383_RRRef,
T1._Fld17384RRef,
T1._Fld17385RRef,
T1._Fld17386RRef,
T1._Fld17387RRef,
T1._Fld17388_TYPE,
T1._Fld17388_RTRef,
T1._Fld17388_RRRef,
T1._Fld17389_TYPE,
T1._Fld17389_RTRef,
T1._Fld17389_RRRef,
T1._Fld17390,
T1._Fld17391,
T1._Fld17392,
T1._Fld17393RRef,
T1._Fld17394RRef,
T1._Fld17395RRef,
T1._Fld17396,
T1._Fld17397,
T1._Fld17398RRef,
T1._Fld17399,
T1._Fld17400,
T1._Fld17401_TYPE,
T1._Fld17401_RTRef,
T1._Fld17401_RRRef,
T1._Fld17402_TYPE,
T1._Fld17402_RTRef,
T1._Fld17402_RRRef,
T1._Fld17403_TYPE,
T1._Fld17403_RTRef,
T1._Fld17403_RRRef,
T1._Fld17404_TYPE,
T1._Fld17404_RTRef,
T1._Fld17404_RRRef,
T1._Fld17405_TYPE,
T1._Fld17405_RTRef,
T1._Fld17405_RRRef,
T1._Fld17406_TYPE,
T1._Fld17406_RTRef,
T1._Fld17406_RRRef,
T1._Fld17407_TYPE,
T1._Fld17407_RTRef,
T1._Fld17407_RRRef,
T1._Fld17408_TYPE,
T1._Fld17408_RTRef,
T1._Fld17408_RRRef,
T1._Fld17409_TYPE,
T1._Fld17409_RTRef,
T1._Fld17409_RRRef,
T1._Fld17410RRef,
T1._Fld17411RRef,
T1._Fld17412_TYPE,
T1._Fld17412_RTRef,
T1._Fld17412_RRRef,
T1._Fld17413,
T1._Fld17414,
T1._Fld17415RRef,
T1._Fld17416,
T1._Fld17417RRef,
T1._Fld17418RRef,
T1._Fld17419RRef,
T1._Fld17420RRef,
T1._Fld17421RRef,
T1._Fld17422RRef,
T1._Fld17423RRef,
T1._Fld17424_TYPE,
T1._Fld17424_RTRef,
T1._Fld17424_RRRef,
T1._Fld17425,
T1._Fld17426,
T1._Fld17427,
T1._Fld17428,
T1._Fld17429RRef,
T1._Fld17430RRef,
T1._Fld17431RRef,
T1._Fld17432RRef,
T1._Fld17433_TYPE,
T1._Fld17433_RTRef,
T1._Fld17433_RRRef,
T1._Fld17434RRef,
T1._Fld17435_TYPE,
T1._Fld17435_RTRef,
T1._Fld17435_RRRef,
T1._Fld17436RRef,
T1._Fld17437_TYPE,
T1._Fld17437_RTRef,
T1._Fld17437_RRRef,
T1._Fld17438RRef,
T1._Fld17439RRef,
T1._Fld17440RRef,
T1._Fld17441RRef,
T1._Fld17442,
T1._Fld17443RRef,
T1._Fld17444RRef,
T1._Fld17445RRef,
T1._Fld17446RRef,
T1._Fld17447RRef,
T1._Fld17448RRef,
T1._Fld17449,
T1._Fld17450,
T1._Fld17451,
T1._Fld17452,
T1._Fld17453,
T1._Fld17454RRef,
T1._Fld17455RRef,
T1._Fld17456RRef,
T1._Fld17457RRef,
T1._Fld17458RRef,
T1._Fld17459RRef,
T1._Fld17460_TYPE,
T1._Fld17460_RTRef,
T1._Fld17460_RRRef,
T1._Fld17461RRef,
T1._Fld17462RRef,
T1._Fld17463RRef,
T1._Fld17464,
T1._Fld17465RRef
FROM _InfoRg17375 T1
WHERE T1._RecorderTRef = @P1 AND T1._RecorderRRef = @P2
ORDER BY T1._LineNo

--поля индекса
	/*_Period,_RecorderTRef,_RecorderRRef,_LineNo	*/
--Найдите минимум 10 отличий

И почему вам этот key lookup не нравиться?
23 авг 12, 11:39    [13054038]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса KEY LOOKUP  [new]
munster
Member

Откуда:
Сообщений: 36
invm,

Понял свою ошибку - смотрел на графический план на Output List, вместо того чтобы внутрь посмотреть.
я так понял sql что может по максимуму берет из некластерного или любого где больше информации сможет взять, а всеми остальными полями дополняет уже из кластерного..
осталось только понять нужен ли индекс со всем этими полями в included columns
23 авг 12, 12:04    [13054314]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса KEY LOOKUP  [new]
munster
Member

Откуда:
Сообщений: 36
denis2710,

потому что этот key lookup отнимает 99% от стоимости общего запроса. и пусть общая стоимость запроса мала, но он лидер среди тяжелых запросов по total_elapsed_time
23 авг 12, 12:19    [13054459]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса KEY LOOKUP  [new]
denis2710
Member

Откуда: Москва
Сообщений: 3384
munster
denis2710,

потому что этот key lookup отнимает 99% от стоимости общего запроса. и пусть общая стоимость запроса мала, но он лидер среди тяжелых запросов по total_elapsed_time

Ну, а когда index seek будет занимать 100% от стоимости общего запроса вы тоже будете с этим бороться?Вам точно нужны все эти поля,которые перечислены в select?
У вас в 1ц какой уровень изоляции для базы стоит?
23 авг 12, 12:24    [13054510]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса KEY LOOKUP  [new]
munster
Member

Откуда:
Сообщений: 36
denis2710,

Я ищу слабые места, на что можно уменьшить время для более быстрой работы.
Вы хотите сказать что, поскольку Nested Loops не отнимает нисколько, и малая стоимость Key Lookup делаю это зря?
уровень изоляции для базы - если вы про режим блокировок, то "автоматический и управляемый", но обьект делающий записи да и сами регистры в управляемом режиме
23 авг 12, 12:42    [13054655]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса KEY LOOKUP  [new]
denis2710
Member

Откуда: Москва
Сообщений: 3384
munster,
Я хочу сказать вы ни чего не выиграете,когда избавитесь от Key Lookup имхо
автор
уровень изоляции для базы - если вы про режим блокировок, то "автоматический и управляемый",
В 1ц я не селен.Я про уровни изоляции транзакций в базе данных.В 1ц можно выставлять в настройках(аля readcommited,repeatableread).
автор
... но выплолняется настолько часто, что приводит к блокировкам

Как определили что именно этот select приводит к блокировкам?
Индексы давно перестраивались?
23 авг 12, 12:56    [13054775]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса KEY LOOKUP  [new]
munster
Member

Откуда:
Сообщений: 36
denis2710
munster,
Я хочу сказать вы ни чего не выиграете,когда избавитесь от Key Lookup имхо
автор
уровень изоляции для базы - если вы про режим блокировок, то "автоматический и управляемый",
В 1ц я не селен.Я про уровни изоляции транзакций в базе данных.В 1ц можно выставлять в настройках(аля readcommited,repeatableread).
автор
... но выплолняется настолько часто, что приводит к блокировкам

Как определили что именно этот select приводит к блокировкам?
Индексы давно перестраивались?


Вот за этот ответ спасибо. он мне и нужен был. я из виду упустил тот факт что бороться с Key Lookup имеет смысл при больших затратах на него или на Nested Loops. а по 1с - управляемый подразумевает readcommited, индексы 2 раза в неделю перестраиваются, статистика по 2 раза на дню
23 авг 12, 14:02    [13055412]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить