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

Откуда:
Сообщений: 34
update 	dbo.poks_value	
set value=#pok_new.value
from #pok_new
where 	dbo.poks_value.guidDoc	= 	#pok_new.guidDoc
		and dbo.poks_value.code	= 	#pok_new.code	
		and	dbo.poks_value.groupId	= 	#pok_new.groupId	

В таблице poks_value 8,000,000+ строк, в #pok_new 5000. Этот запрос выполняется 10+ минут. Можно ли как-то оптимизировать этот запрос?
20 авг 13, 21:06    [14732784]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
iap
Member

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

индексы есть?
20 авг 13, 21:34    [14732879]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Xape4ka
Member

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

есть
20 авг 13, 22:05    [14732981]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Xape4ka
Member

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

По всем столбцам которые тут задействованы в таблице poks_value есть индексы
20 авг 13, 22:14    [14733012]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Xape4ka
iap
индексы есть?
есть
Из анекдота
- Петька, приборы!?
- 300!
- Что 300?
- А что "приборы"?
Какие индексы? План запроса. Скрипт таблицы.
Xape4ka
5000
Эскалация локировок?

Если индексов много, если параллельных процессов много то не удивительно (точнее удивительно что нет дедлоков).
А если строк меньше, ну скажем 1000?

И пишите понятнее:
UPDATE	V
SET	value	= N.value
FROM	#pok_new	N
JOIN	dbo.poks_value	V ON V.guidDoc	= N.guidDoc
			 AND V.code	= N.code
			 AND V.groupId	= N.groupId
20 авг 13, 22:25    [14733054]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Xape4ka
По всем столбцам которые тут задействованы в таблице poks_value есть индексы
Так же из анекдота
Дайте пилюли от жадности, и побольше, побольше

Необдуманное нафигачивание индексов и приводит к полной ЗДЦ. Совершайте обдуманные поступки, не ищите на неё приключения.
В этом разделе можно получить профилированную помощь.
20 авг 13, 22:30    [14733083]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Xape4ka
Member

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

Скрипт таблицы

USE [BaseName]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[poks_value](
	[guid] [uniqueidentifier] NOT NULL,
	[guidDoc] [uniqueidentifier] NULL,
	[code] [varchar](50) NOT NULL,
	[value] [varchar](255) NULL,
	[valueFloat] [decimal](15, 3) NULL,
	[baseName] [varchar](10) NOT NULL,
	[changedUser] [varchar](250) NULL,
	[groupId] [uniqueidentifier] NULL,
 CONSTRAINT [PK_poks_value] PRIMARY KEY CLUSTERED 
(
	[guid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[poks_value] ADD  CONSTRAINT [DF_poks_value_guid]  DEFAULT (newid()) FOR [guid]
GO


Индексы:
ALTER TABLE [dbo].[poks_value] ADD  CONSTRAINT [PK_poks_value] PRIMARY KEY CLUSTERED 
(
	[guid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [poks_value_BaseName] ON [dbo].[poks_value] 
(
	[baseName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [poks_value_code] ON [dbo].[poks_value] 
(
	[code] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [poks_value_guidDoc] ON [dbo].[poks_value] 
(
	[guidDoc] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [pols_value_group] ON [dbo].[poks_value] 
(
	[groupId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Помоему тут ничего криминального нет.
20 авг 13, 22:43    [14733130]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31993
Xape4ka
Помоему тут ничего криминального нет.
В итоге сервер выбирает один из этих индексов, селективность (возможно) низкая у любого из них, в итоге медленный апдэйт.

Нужно сделать составной индекс на 3 поля: guidDoc, code, groupId

Конечно, скорость вставки в таблицу уменьшится, но зтот запрос ускортся. Возможно, получится отказаться от других, ненужных индексов.
Впрочем, скорость вставки вас не волнует, судя по кластерному PRIMARY KEY :-)

Ещё нужно убедиться, что долгое время выполнения не результат блокировок.
20 авг 13, 22:58    [14733189]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Xape4ka
Member

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

А разве он не выбирает каждый раз тот индекс, по какому полю сейчас идёт проверка?

автор
Впрочем, скорость вставки вас не волнует, судя по кластерному PRIMARY KEY :-)

И вот, если честно, то не понял юмора(
20 авг 13, 23:06    [14733222]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31993
Xape4ka
alexeyvg,

А разве он не выбирает каждый раз тот индекс, по какому полю сейчас идёт проверка?
Выбирает. Но у вас то делается проверка по трём полям, а не по одному, а он может выбрать только индекс по одному из полей.

То есть по трём полям выбирается 5000 записей, но по каждому из полей критериям поиска удовлетворяет милион.
Вот он берёт милион ключей из индекса, потом делает милион запросов к таблице для выборки этого милиона записей, и фильтрует оттуда 5000 записей.
Ну или, как вариант, по каждому из трёх полей по трём инедксам берёт по милиону записей, и потом делает пересечение этих множеств.

Собственно, это так медленно, что сервер может выбрать просто скан таблицы.

Xape4ka
автор
Впрочем, скорость вставки вас не волнует, судя по кластерному PRIMARY KEY :-)

И вот, если честно, то не понял юмора(
Ну как, индекс кластерный, по GUID, то есть записи физически упорядочены по случайному числу.

Т.е. когда вы вставляете тыщу записей (допустим, суммарно 16КБ данных, или 2 страницы на диске), то сервер их вставит в 1000 разных страниц, а так как страницы могут быть уже заполнены, то он каждую из них разделит на 2 страницы.
То есть получаем запись уже 2000 страниц вместо 2-х, да ещё и случайных, то есть нужно выполнить 2000 рандом-записи на диск. Урцтые серверные диски могут выполнять 20 записей в секунду.
21 авг 13, 00:53    [14733595]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
sqlwriter
Member

Откуда: Stavropolsky kray -> Nashville, TN
Сообщений: 133
Temp table тоже индексирована хотя бы по полю guidDoc? А вообще не мешало бы выложить план запроса, все вопросы бы сразу отпали.
21 авг 13, 00:59    [14733610]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31993
sqlwriter
Temp table тоже индексирована хотя бы по полю guidDoc?
Зачем, из неё нужны все данные, так что скан самое оптимальное.
sqlwriter
А вообще не мешало бы выложить план запроса, все вопросы бы сразу отпали.
Это точно.
21 авг 13, 01:01    [14733613]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
sqlwriter
Member

Откуда: Stavropolsky kray -> Nashville, TN
Сообщений: 133
alexeyvg
sqlwriter
Temp table тоже индексирована хотя бы по полю guidDoc?
Зачем, из неё нужны все данные, так что скан самое оптимальное.

Дело в том что в зависимости от какой таблицы sql пляшет может случиться что для каждого guidDoc из dbo.poks_value ищется строка из #pok_new, т.е. скан каждый раз. Да и даже наоборот, тоже по крайней мере 5000 сканов, к тому же еще нужно найти code и groupId из таблицы.
21 авг 13, 01:06    [14733624]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31993
sqlwriter
Дело в том что в зависимости от какой таблицы sql пляшет может случиться что для каждого guidDoc из dbo.poks_value ищется строка из #pok_new, т.е. скан каждый раз. Да и даже наоборот, тоже по крайней мере 5000 сканов, к тому же еще нужно найти code и groupId из таблицы.
Ну вообще да, такой план тоже может быть.

Хотя самое эффективное всё таки составной индекс guidDoc, code, groupId для основной таблицы.
21 авг 13, 01:12    [14733631]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
sqlwriter
Member

Откуда: Stavropolsky kray -> Nashville, TN
Сообщений: 133
[/quot]Ну вообще да, такой план тоже может быть.

Хотя самое эффективное всё таки составной индекс guidDoc, code, groupId для основной таблицы.[/quot]
Это верно, я бы ещё value добавил чтобы исключить дополнительный lookup, плюс такой же индекс на temp table
21 авг 13, 01:16    [14733635]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
aleks2
Guest
sqlwriter

Это верно, я бы ещё value добавил чтобы исключить дополнительный lookup[/quot]
Хе-хе? Куда ж ты денешься, когда разденешься (таблицу то обновлять надо)?

И ведь ни одна скотина не сказала, что на #pok_new тоже надо бы иметь индексы...
Это несложно, а эффект, кстате, может быть нехилый.
21 авг 13, 06:37    [14733703]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31993
aleks2
И ведь ни одна скотина не сказала, что на #pok_new тоже надо бы иметь индексы...
Это несложно, а эффект, кстате, может быть нехилый.
Двумя постами выше это уже обсудили.
ИМХО это лишнее. Если будет покрывающий индекс, то что ещё нужно? Скан временной таблицы, поиск по индексу и обновление записей по ПК, куда уж проще.
sqlwriter
я бы ещё value добавил чтобы исключить дополнительный lookup
Незачем, тут же value используется только при обновлении.
21 авг 13, 08:47    [14733860]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
aleks2
Guest
alexeyvg
Скан временной таблицы, поиск по индексу и обновление записей по ПК, куда уж проще.


Чего ты меня то лечишь?
Ты оптимизатору это скажи...
21 авг 13, 08:50    [14733873]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31993
aleks2
alexeyvg
Скан временной таблицы, поиск по индексу и обновление записей по ПК, куда уж проще.


Чего ты меня то лечишь?
Ты оптимизатору это скажи...
Что, я ещё оптимизатор уговаривать буду??? Просто нужно создать индекс, и оптимизатор именно такой план и сделает.
21 авг 13, 09:21    [14734008]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Xape4ka
Member

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

А можешь пояснить за составные индексы и как его сделать?
21 авг 13, 10:33    [14734448]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3757
оптимизатор не имеет статистики по временной таблице и легко может луп джоин выбрать.
Так что либо на нее индекс (по трем полям + инклюд вэлъю), либо захинтовать хэш джоин.
Плюс как сказали - составной индекс на три поля соединения вместо трех.
21 авг 13, 10:43    [14734484]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Гость333
Member

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

Вы план-то запроса выложите сегодня?

Ivan Durak
оптимизатор не имеет статистики по временной таблице

Почему не имеет?!
21 авг 13, 10:50    [14734529]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31993
Xape4ka
alexeyvg,

А можешь пояснить за составные индексы и как его сделать?

CREATE NONCLUSTERED INDEX [poks_value_guidDoc_groupId_code] ON [dbo].[poks_value] 
(
	guidDoc, groupId, code
)
21 авг 13, 10:57    [14734564]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Xape4ka
Member

Откуда:
Сообщений: 34
Гость333
Вы план-то запроса выложите сегодня?


Вам нужен весь запрос до UPDATE? если что, то та часть запроса выполняется за несколько секунд. Проблема именно в этом куске. Или что вы подразумеваете по планом запроса?
21 авг 13, 11:07    [14734619]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Гость333
Member

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

Нужен план проблемного запроса (update из первого поста, выполняющийся 10+ минут).
21 авг 13, 11:12    [14734663]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить