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

Откуда:
Сообщений: 241
Исходные данные:

Есть три таблицы, кластеризованные, индексы и ссылочные ограничения (кол-во данных в каждой ~1000 строк):

CREATE TABLE [objects].[Streams](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [nvarchar](100) NOT NULL,
	[isDeleted] [int] NOT NULL,
	[cDate] [date] NOT NULL,
	[cTime] [time](0) NOT NULL,
 CONSTRAINT [PK_Streams] PRIMARY KEY CLUSTERED 
(
	[id] 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
ALTER TABLE [objects].[Streams] ADD  CONSTRAINT [DF_Streams_isDeleted]  DEFAULT ((0)) FOR [isDeleted]
GO
ALTER TABLE [objects].[Streams] ADD  CONSTRAINT [DF_Streams_cDate]  DEFAULT (getdate()) FOR [cDate]
GO
ALTER TABLE [objects].[Streams] ADD  CONSTRAINT [DF_Streams_cTime]  DEFAULT (getdate()) FOR [cTime]
GO
CREATE TABLE [objects].[Brands](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[streamId] [int] NOT NULL,
	[name] [nvarchar](100) NOT NULL,
	[isDeleted] [int] NOT NULL,
	[cDate] [date] NOT NULL,
	[cTime] [time](0) NOT NULL,
 CONSTRAINT [PK_Brands] PRIMARY KEY CLUSTERED 
(
	[id] 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

ALTER TABLE [objects].[Brands]  WITH CHECK ADD  CONSTRAINT [FK_Brands_Streams] FOREIGN KEY([streamId])
REFERENCES [objects].[Streams] ([id])
GO
ALTER TABLE [objects].[Brands] CHECK CONSTRAINT [FK_Brands_Streams]
GO
ALTER TABLE [objects].[Brands] ADD  CONSTRAINT [DF_Brands_isDeleted]  DEFAULT ((0)) FOR [isDeleted]
GO
ALTER TABLE [objects].[Brands] ADD  CONSTRAINT [DF_Brands_cDate]  DEFAULT (getdate()) FOR [cDate]
GO
ALTER TABLE [objects].[Brands] ADD  CONSTRAINT [DF_Brands_cTime]  DEFAULT (getdate()) FOR [cTime]
GO

CREATE TABLE [objects].[Chains](
	[__$isa_attribute_value_id] [int] NOT NULL,
	[id] [int] IDENTITY(1,1) NOT NULL,
	[brandId] [int] NOT NULL,
	[name] [nvarchar](100) NOT NULL,
	[isDirect] [bit] NOT NULL,
	[isDeleted] [int] NOT NULL,
	[cDate] [date] NOT NULL,
	[cTime] [time](0) NOT NULL,
 CONSTRAINT [PK_Chains] PRIMARY KEY CLUSTERED 
(
	[id] 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

ALTER TABLE [objects].[Chains]  WITH CHECK ADD  CONSTRAINT [FK_Chains_Brands] FOREIGN KEY([brandId])
REFERENCES [objects].[Brands] ([id])
GO

ALTER TABLE [objects].[Chains] CHECK CONSTRAINT [FK_Chains_Brands]
GO

ALTER TABLE [objects].[Chains] ADD  CONSTRAINT [DF_Chains_isDirect]  DEFAULT (N'False') FOR [isDirect]
GO

ALTER TABLE [objects].[Chains] ADD  CONSTRAINT [DF_Chains_isDeleted]  DEFAULT ((0)) FOR [isDeleted]
GO

ALTER TABLE [objects].[Chains] ADD  CONSTRAINT [DF_Chains_cDate]  DEFAULT (getdate()) FOR [cDate]
GO

ALTER TABLE [objects].[Chains] ADD  CONSTRAINT [DF_Chains_cTime]  DEFAULT (getdate()) FOR [cTime]
GO


Так же есть два некластеризованных, неуникальных индекса:

CREATE NONCLUSTERED INDEX [IX_Brands_streamId] ON [objects].[Brands] 
(
	[streamId] ASC
)
INCLUDE ( [name]) 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

CREATE NONCLUSTERED INDEX [IX_Chains_brandId] ON [objects].[Chains] 
(
	[brandId] ASC
)
INCLUDE ( [name]) 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

Есть два запроса и планы их выполнения (во вложении), обновлена статистика и сброшен кэш:

SELECT
		s.*,
		b.name, 
		c.name
	FROM
		objects.Streams AS s
INNER merge JOIN
		objects.Brands AS b
	ON
		s.id = b.streamId
INNER JOIN
		objects.Chains AS c
	ON
		b.id = c.brandId;

SELECT
		s.*,
		b.name, 
		c.name
	FROM
		objects.Streams AS s
INNER JOIN
		objects.Brands AS b
	ON
		s.id = b.streamId
INNER  JOIN
		objects.Chains AS c
	ON
		b.id = c.brandId;

собственно сам вопрос: почему оптимизатор выбирает хэш соединение?


К сообщению приложен файл (plans.xlsx - 10Kb) cкачать
4 июн 12, 12:12    [12660819]     Ответить | Цитировать Сообщить модератору
 Re: чем обусловлен выбор hash соединения?  [new]
skorpk
Member

Откуда: Волгоград
Сообщений: 276
Понимание hash join
Join hints
4 июн 12, 20:50    [12664213]     Ответить | Цитировать Сообщить модератору
 Re: чем обусловлен выбор hash соединения?  [new]
dmitry stakanov
Member

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

прошу прощения, я видимо, не так задал вопрос.

при отсутствии хинта оптимизатор выбирает хэш, косты по хэшу в три раза больше, чем по мержу.
вопрос: почему при явном выигрыше по костам оптимизатор строит план с хэшем, вместо меджа?
пс планы запроса во вложении.
5 июн 12, 10:41    [12666054]     Ответить | Цитировать Сообщить модератору
 Re: чем обусловлен выбор hash соединения?  [new]
skorpk
Member

Откуда: Волгоград
Сообщений: 276
Обновите статистику.
5 июн 12, 10:54    [12666198]     Ответить | Цитировать Сообщить модератору
 Re: чем обусловлен выбор hash соединения?  [new]
skorpk
Member

Откуда: Волгоград
Сообщений: 276
И у подумайте над индексами таблицы [ChainsRepository].[objects].[Streams].
5 июн 12, 10:58    [12666239]     Ответить | Цитировать Сообщить модератору
 Re: чем обусловлен выбор hash соединения?  [new]
dmitry stakanov
Member

Откуда:
Сообщений: 241
dmitry stakanov
Есть два запроса и планы их выполнения (во вложении), обновлена статистика и сброшен кэш:


а причем здесь индексы? есть три таблицы, есть какие-то индексы, есть запрос. вопрос почему оптимизатор выбирает менее оптимальный (с моей точки зрения) план. я пытабсь понять, что я упускаю.

пс
в общем-то задача выбрать все из streams и имена из остальных таблиц.
и честно сказать при такой задаче не могу представить какой индекс будет более оптимальным, чем кластеризованнный.
5 июн 12, 11:09    [12666366]     Ответить | Цитировать Сообщить модератору
 Re: чем обусловлен выбор hash соединения?  [new]
denis2710
Member

Откуда: Москва
Сообщений: 3384
dmitry stakanov,
а что показывает
set statistics time on;
set statistics io on;
5 июн 12, 11:18    [12666472]     Ответить | Цитировать Сообщить модератору
 Re: чем обусловлен выбор hash соединения?  [new]
skorpk
Member

Откуда: Волгоград
Сообщений: 276
dmitry stakanov
в общем-то задача выбрать все из streams

Не все а только те записи которые совпадают с join.
Оптимизатор решил что у вас тяжелый запрос и оптимальный выбор это использовать именно хеш-соединение, а не каое то другое.
5 июн 12, 11:24    [12666515]     Ответить | Цитировать Сообщить модератору
 Re: чем обусловлен выбор hash соединения?  [new]
dmitry stakanov
Member

Откуда:
Сообщений: 241
denis2710
dmitry stakanov,
а что показывает
set statistics time on;
set statistics io on;



SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

(1220 row(s) affected)
Table 'Chains'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Brands'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Streams'. Scan count 1, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 1030 ms.

(1220 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Chains'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Brands'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Streams'. Scan count 1, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 1376 ms.


причем затраченное время меняется, то один то другой запрос выигрывает по времени.
5 июн 12, 11:30    [12666586]     Ответить | Цитировать Сообщить модератору
 Re: чем обусловлен выбор hash соединения?  [new]
dmitry stakanov
Member

Откуда:
Сообщений: 241
skorpk
dmitry stakanov
в общем-то задача выбрать все из streams

Не все а только те записи которые совпадают с join.
Оптимизатор решил что у вас тяжелый запрос и оптимальный выбор это использовать именно хеш-соединение, а не каое то другое.


все имелось в виду - "все поля", а не все строки.

дело не том, что совпадет, а что нет. и там и там сканирование, с однаковыми костами.
почему при равных условиях выбирается не самая оптимальная стратегия, вто в чем вопрос.
5 июн 12, 11:35    [12666652]     Ответить | Цитировать Сообщить модератору
 Re: чем обусловлен выбор hash соединения?  [new]
skorpk
Member

Откуда: Волгоград
Сообщений: 276
Посмотрите ваш план. В первом случае используется сортировка данных, а во втором нет. Сделайте сортировку.
5 июн 12, 11:36    [12666663]     Ответить | Цитировать Сообщить модератору
 Re: чем обусловлен выбор hash соединения?  [new]
dmitry stakanov
Member

Откуда:
Сообщений: 241
skorpk
Посмотрите ваш план. В первом случае используется сортировка данных, а во втором нет. Сделайте сортировку.


не совсем понял предоложение. в первом случае sort вызван оператором мерж джойн, который свою очередь вызывает хинт.
что вы хотите сортировать, где, и зачем?
5 июн 12, 11:46    [12666771]     Ответить | Цитировать Сообщить модератору
 Re: чем обусловлен выбор hash соединения?  [new]
denis2710
Member

Откуда: Москва
Сообщений: 3384
dmitry stakanov,
спрошу на всякий может оптимизатор по time out прекращает работать?
5 июн 12, 11:51    [12666830]     Ответить | Цитировать Сообщить модератору
 Re: чем обусловлен выбор hash соединения?  [new]
denis2710
Member

Откуда: Москва
Сообщений: 3384
dmitry stakanov,
может планы в более "человеческом" виде выложите?
5 июн 12, 11:52    [12666838]     Ответить | Цитировать Сообщить модератору
 Re: чем обусловлен выбор hash соединения?  [new]
skorpk
Member

Откуда: Волгоград
Сообщений: 276
dmitry stakanov,
Сделайте сортировку и тогда оптимизатор будит использовать merge вместо hash.
5 июн 12, 12:00    [12666921]     Ответить | Цитировать Сообщить модератору
 Re: чем обусловлен выбор hash соединения?  [new]
dmitry stakanov
Member

Откуда:
Сообщений: 241
Rows	PhysicalOp	Argument																					EstimateRows	EstimateIO	EstimateCPU	TotalSubtreeCost
1220	NULL	NULL																							1206.367	NULL	NULL	0.07634721
1220	Merge Join	MERGE:([b].[id])=([c].[brandId]), RESIDUAL:([ChainsRepository].[objects].[Chains].[brandId] as [c].[brandId]=[ChainsRepository].[objects].[Brands].[id] as [b].[id])		1206.367	0		0.01069471	0.07634721
1196	Sort		ORDER BY:([b].[id] ASC)																				1148.606	0.01126126	0.01831533	0.0573218
1196	Merge Join	MERGE:([s].[id])=([b].[streamId]), RESIDUAL:([ChainsRepository].[objects].[Brands].[streamId] as [b].[streamId]=[ChainsRepository].[objects].[Streams].[id] as [s].[id])	1148.606	0		0.01051546	0.02774521
1090	Clustered Index Scan	OBJECT:([ChainsRepository].[objects].[Streams].[PK_Streams] AS [s]), ORDERED FORWARD											1090		0.007569444	0.001356	0.008925444
1196	Index Scan	OBJECT:([ChainsRepository].[objects].[Brands].[IX_Brands_streamId] AS [b]), ORDERED FORWARD											1196		0.006828704	0.0014726	0.008301304
1220	Index Scan	OBJECT:([ChainsRepository].[objects].[Chains].[IX_Chains_brandId] AS [c]), ORDERED FORWARD											1220		0.006828704	0.001499	0.008327704
						
1220	NULL	NULL																							1162.285	NULL		NULL		0.2121193
1220	Hash Match	HASH:([s].[id])=([b].[streamId])																		1162.285	0		0.09217934	0.2121193
1090	Clustered Index Scan	OBJECT:([ChainsRepository].[objects].[Streams].[PK_Streams] AS [s])													1090		0.007569444	0.001356	0.008925444
1220	Hash Match	HASH:([b].[id])=([c].[brandId])																			1209.604	0		0.09437954	0.1110115
1196	Index Scan	OBJECT:([ChainsRepository].[objects].[Brands].[IX_Brands_streamId] AS [b])													1196		0.006828704	0.0014726	0.008301304
1220	Index Scan	OBJECT:([ChainsRepository].[objects].[Chains].[IX_Chains_brandId] AS [c])													1220		0.006828704	0.001499	0.008327704
5 июн 12, 12:12    [12667016]     Ответить | Цитировать Сообщить модератору
 Re: чем обусловлен выбор hash соединения?  [new]
dmitry stakanov
Member

Откуда:
Сообщений: 241
skorpk, приведите пример, где вы хотите сделать сортировку.
5 июн 12, 12:13    [12667026]     Ответить | Цитировать Сообщить модератору
 Re: чем обусловлен выбор hash соединения?  [new]
dmitry stakanov
Member

Откуда:
Сообщений: 241
denis2710
dmitry stakanov,
спрошу на всякий может оптимизатор по time out прекращает работать?


нет.
5 июн 12, 12:14    [12667033]     Ответить | Цитировать Сообщить модератору
 Re: чем обусловлен выбор hash соединения?  [new]
skorpk
Member

Откуда: Волгоград
Сообщений: 276
merge join
В данной статье все описано про соединение слиянием.
5 июн 12, 12:16    [12667049]     Ответить | Цитировать Сообщить модератору
 Re: чем обусловлен выбор hash соединения?  [new]
dmitry stakanov
Member

Откуда:
Сообщений: 241
skorpk, это не пример конретной задачи, которая више.
5 июн 12, 12:19    [12667061]     Ответить | Цитировать Сообщить модератору
 Re: чем обусловлен выбор hash соединения?  [new]
denis2710
Member

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

dmitry stakanov
Rows	PhysicalOp	Argument																					EstimateRows	EstimateIO	EstimateCPU	TotalSubtreeCost
1220	NULL	NULL																							1206.367	NULL	NULL	0.07634721
1220	Merge Join	MERGE:([b].[id])=([c].[brandId]), RESIDUAL:([ChainsRepository].[objects].[Chains].[brandId] as [c].[brandId]=[ChainsRepository].[objects].[Brands].[id] as [b].[id])		1206.367	0		0.01069471	0.07634721
1196	Sort		ORDER BY:([b].[id] ASC)																				1148.606	0.01126126	0.01831533	0.0573218
1196	Merge Join	MERGE:([s].[id])=([b].[streamId]), RESIDUAL:([ChainsRepository].[objects].[Brands].[streamId] as [b].[streamId]=[ChainsRepository].[objects].[Streams].[id] as [s].[id])	1148.606	0		0.01051546	0.02774521
1090	Clustered Index Scan	OBJECT:([ChainsRepository].[objects].[Streams].[PK_Streams] AS [s]), ORDERED FORWARD											1090		0.007569444	0.001356	0.008925444
1196	Index Scan	OBJECT:([ChainsRepository].[objects].[Brands].[IX_Brands_streamId] AS [b]), ORDERED FORWARD											1196		0.006828704	0.0014726	0.008301304
1220	Index Scan	OBJECT:([ChainsRepository].[objects].[Chains].[IX_Chains_brandId] AS [c]), ORDERED FORWARD											1220		0.006828704	0.001499	0.008327704
						
1220	NULL	NULL																							1162.285	NULL		NULL		0.2121193
1220	Hash Match	HASH:([s].[id])=([b].[streamId])																		1162.285	0		0.09217934	0.2121193
1090	Clustered Index Scan	OBJECT:([ChainsRepository].[objects].[Streams].[PK_Streams] AS [s])													1090		0.007569444	0.001356	0.008925444
1220	Hash Match	HASH:([b].[id])=([c].[brandId])																			1209.604	0		0.09437954	0.1110115
1196	Index Scan	OBJECT:([ChainsRepository].[objects].[Brands].[IX_Brands_streamId] AS [b])													1196		0.006828704	0.0014726	0.008301304
1220	Index Scan	OBJECT:([ChainsRepository].[objects].[Chains].[IX_Chains_brandId] AS [c])													1220		0.006828704	0.001499	0.008327704


у нас походу расходятся понятия о "человеческом" виде
5 июн 12, 12:20    [12667076]     Ответить | Цитировать Сообщить модератору
 Re: чем обусловлен выбор hash соединения?  [new]
dmitry stakanov
Member

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

в человеческом это как?)
5 июн 12, 12:23    [12667106]     Ответить | Цитировать Сообщить модератору
 Re: чем обусловлен выбор hash соединения?  [new]
denis2710
Member

Откуда: Москва
Сообщений: 3384
dmitry stakanov,
ну типа *.sqlplan
5 июн 12, 12:25    [12667124]     Ответить | Цитировать Сообщить модератору
 Re: чем обусловлен выбор hash соединения?  [new]
dmitry stakanov
Member

Откуда:
Сообщений: 241
denis2710
dmitry stakanov,
ну типа *.sqlplan


во вложени.

К сообщению приложен файл (plan.sqlplan - 27Kb) cкачать
5 июн 12, 12:37    [12667220]     Ответить | Цитировать Сообщить модератору
 Re: чем обусловлен выбор hash соединения?  [new]
denis2710
Member

Откуда: Москва
Сообщений: 3384
dmitry stakanov,
ну вот, а говорите не сваливает по time out.
5 июн 12, 12:40    [12667243]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить