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

Откуда: Сибирь
Сообщений: 23
есть вот такой вот скрипт:
SET NOCOUNT ON
GO

BEGIN tran;

CREATE TABLE #AG_T
(
 AG_NUM INT, 
 AG_STR NVARCHAR(1), 
 AG_ORG_FK INT, 
 AG_ORG_SIMPLE_INT INT, 
 AG_ORG_INDEX INT,
 AG_DATE datetime
);

declare @begin datetime = '2000-01-01T00:00:00', @end datetime = '2013-12-31T23:59:59';

INSERT INTO #Ag_t
	(
		Ag_num,
		Ag_str,
		Ag_org_fk,
		Ag_org_simple_int,
		Ag_org_index,
		Ag_date
	)
	SELECT TOP 5000000
		CONVERT(int, CONVERT(binary(4), NEWID())),
		CONVERT(nvarchar(1), CONVERT(binary(2), NEWID())),
		CONVERT(int, CONVERT(binary(4), NEWID())),
		CONVERT(int, CONVERT(binary(4), NEWID())),
		CONVERT(int, CONVERT(binary(4), NEWID())),
		CAST(CAST(@Begin AS float) + (CAST(@End AS float) - CAST(@Begin AS float)) * RAND(CHECKSUM(NEWID())) AS datetime)
	FROM Sysobjects O1, Sysobjects O2, Sysobjects O3, Sysobjects O4;

SELECT
	@@Rowcount;

/*CREATE NONCLUSTERED INDEX [NonClusteredIndex-XXX] ON #AG_T
(
 AG_DATE ASC
);*/

DELETE FROM #Ag_t
WHERE Ag_date > '2007-01-01T00:00:00';

SELECT
	@@Rowcount;

--DROP INDEX [Nonclusteredindex-xxx] ON #Ag_t;

DROP TABLE #Ag_t;
 
ROLLBACK tran;

данный скрипт выполняется быстрее при отсутствии индекса по полю AG_DATE (индекс я закомментировал). однако сам оптимизатор советует создать индекс по AG_DATE. в чем подвох?
7 окт 13, 17:44    [14935611]     Ответить | Цитировать Сообщить модератору
 Re: еще один вопрос по использованию индекса на временной таблице  [new]
create index
Guest
создание индекса то же не бесплатная операция
7 окт 13, 17:45    [14935619]     Ответить | Цитировать Сообщить модератору
 Re: еще один вопрос по использованию индекса на временной таблице  [new]
Серега MS SQL
Member

Откуда: Сибирь
Сообщений: 23
create index
создание индекса то же не бесплатная операция

оптимизатор столь плох, что не может просчитать, что без индекса лучше?
7 окт 13, 17:47    [14935631]     Ответить | Цитировать Сообщить модератору
 Re: еще один вопрос по использованию индекса на временной таблице  [new]
create index
Guest
Серега MS SQL
create index
создание индекса то же не бесплатная операция

оптимизатор столь плох, что не может просчитать, что без индекса лучше?

оптимизатор говорит вам о потенциальной проблеме конкретного запроса
select ...
7 окт 13, 17:49    [14935646]     Ответить | Цитировать Сообщить модератору
 Re: еще один вопрос по использованию индекса на временной таблице  [new]
create index
Guest
create index
Серега MS SQL
пропущено...

оптимизатор столь плох, что не может просчитать, что без индекса лучше?

оптимизатор говорит вам о потенциальной проблеме конкретного запроса
delete ...
7 окт 13, 17:50    [14935649]     Ответить | Цитировать Сообщить модератору
 Re: еще один вопрос по использованию индекса на временной таблице  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Серега MS SQL,

Поддерживаю коллегу высказавшегося ранее по всем пунктам.

1. Во-первых давайте все-таки мерить время самого запроса (иначе оно может быть нивелировано куда более ресурсоемкими операциями вставик, создания индекса и т.д.):
+
SET NOCOUNT ON
GO

BEGIN tran;

CREATE TABLE #AG_T
(
 AG_NUM INT, 
 AG_STR NVARCHAR(1), 
 AG_ORG_FK INT, 
 AG_ORG_SIMPLE_INT INT, 
 AG_ORG_INDEX INT,
 AG_DATE datetime
);

declare @begin datetime = '2000-01-01T00:00:00', @end datetime = '2013-12-31T23:59:59';

INSERT INTO #Ag_t
	(
		Ag_num,
		Ag_str,
		Ag_org_fk,
		Ag_org_simple_int,
		Ag_org_index,
		Ag_date
	)
	SELECT TOP 5000000
		CONVERT(int, CONVERT(binary(4), NEWID())),
		CONVERT(nvarchar(1), CONVERT(binary(2), NEWID())),
		CONVERT(int, CONVERT(binary(4), NEWID())),
		CONVERT(int, CONVERT(binary(4), NEWID())),
		CONVERT(int, CONVERT(binary(4), NEWID())),
		CAST(CAST(@Begin AS float) + (CAST(@End AS float) - CAST(@Begin AS float)) * RAND(CHECKSUM(NEWID())) AS datetime)
	FROM Sysobjects O1, Sysobjects O2, Sysobjects O3, Sysobjects O4;

SELECT
	@@Rowcount;

CREATE NONCLUSTERED INDEX [NonClusteredIndex-XXX] ON #AG_T
(
 AG_DATE ASC
);

set statistics time, xml on;
DELETE FROM #Ag_t
WHERE Ag_date > '2007-01-01T00:00:00';
set statistics time, xml off;

SELECT
	@@Rowcount;

DROP INDEX [Nonclusteredindex-xxx] ON #Ag_t;

DROP TABLE #Ag_t;
 
ROLLBACK tran;

(Я сюда включил еще и сбор реального плана, он замедляет, но сохраняет тенденцию)

Время:
автор
без индекса:
SQL Server Execution Times: CPU time = 5055 ms, elapsed time = 5059 ms.
с индексом:
SQL Server Execution Times: CPU time = 15242 ms, elapsed time = 16281 ms.


2. Во-вторых. Почему такая разница? Сравните планы:
Картинка с другого сайта.

Чтобы понять что происходит, нужно знать как поддерживаются индексы.
План по изменению включает две части, та что читает и та что выполняет изменения, иногда называют - read курсор и write курсор (не те курсоры, о которых подумалось.) Кроме того есть per-row (narrow) и per-index (wide) планы. У вас тут план то что называется wide или index план. Он выполняет поиск по индексу, потом выполняет удаления из базовой структуры (кучи), пото удаленные закладки применяет для удаления значений в индексе, при этом, чтобы доступ не был случайным он их предварительно сортирует по ключу индекса и по закладке. Вот тут основной тормоз.

3. В-третьих, оптимизатор отвечает за построение плана одного конкретного запроса. По этим оптимизациям собирается статистика, и сервер вам может давать подсказки, в виде missing indexes. Эти подсказки несовершенны. Например, дофига предлагает создать индекс такой же как уже есть, или включить в индекс все колонки. Не следует их воспринимать как руководство к действию и тем более, как к руководству изменения схемы. Оптимизатор не может давать руководства к изменению схемы (пока, во всяком случае).
7 окт 13, 20:27    [14936145]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить