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

Откуда: москва
Сообщений: 291
alexeyvg
eny
это вы плохо читали ;) я говорю про БОЛЬШИЕ ОБЪЕМЫ данных, пока таблицы маленькие MS SQL может искать по простому индексу, получать ссылку на кластерный индекс и искать но нему повторно, при этом сервер молчит как партизан, что ищет по кластеру во второй проход.
Сервер всегда после поиска по индексу будет делать второй поиск на страничный уровень, либо по кластерному индексу, либо по ИД страницы, это одно и то же (исключая случай, когда все данные есть в индексе).

Как же иначе, без второго прохода???


не знаю что такое страничный уровень?

Есть промежуточные страницы уровней b-дерева на котором указываются ссылки на следующие страницы и есть листовые страницы b-дерева на которых хранятся либо физические ссылки на строку таблицы (строку кластерного индекса) либо данные кластеризованного ключа и подгруженные в индекс поля данных.

При создании кластерного индексов любой поиск по индексу превращается в последовательность поиска сначала по индексу, а потом по кластерному индексу, те двойная работа
6 май 13, 15:37    [14263523]     Ответить | Цитировать Сообщить модератору
 Re: Господа, прошу помощи с оптимизацией индексной структуры  [new]
eny
Member

Откуда: москва
Сообщений: 291
Гавриленко Сергей Алексеевич
Так же будем считать, что таблица под определение "большие данные" подходит.


Забейте таблицу, как в начале обсуждения, реальными данными с той же структурой и тогда рассуждайте.
А демонстрировать планы запросов с одной строкой в таблице -это ни о чем
6 май 13, 15:39    [14263538]     Ответить | Цитировать Сообщить модератору
 Re: Господа, прошу помощи с оптимизацией индексной структуры  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37050
eny
При создании кластерного индексов любой поиск по индексу превращается в последовательность поиска сначала по индексу, а потом по кластерному индексу, те двойная работа
В кластерный индекс будет поиск только в том случае, если в обычном индексе недостает каких-либо данных. И это отдельная операция Bookmark Lookup, которую можно наблюдать в плане. Вы уверены, что вам не надо еще раз перечитать книги, прежде чем так безаппеляционно нести фингю и сбивать людей с толку?
6 май 13, 15:41    [14263552]     Ответить | Цитировать Сообщить модератору
 Re: Господа, прошу помощи с оптимизацией индексной структуры  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37050
eny
Гавриленко Сергей Алексеевич
Так же будем считать, что таблица под определение "большие данные" подходит.


Забейте таблицу, как в начале обсуждения, реальными данными с той же структурой и тогда рассуждайте.
А демонстрировать планы запросов с одной строкой в таблице -это ни о чем
Может еще вам за пивком сгонять? Я привел два примера, опровергающие ваши утверждения. Потрудитесь теперь вы хоть что-нибудь привести. Иначе все ваше поведение - это обычный троллинг.
6 май 13, 15:42    [14263560]     Ответить | Цитировать Сообщить модератору
 Re: Господа, прошу помощи с оптимизацией индексной структуры  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37050
eny
А демонстрировать планы запросов с одной строкой в таблице -это ни о чем
Идите перечитывайте тему. Вы, оказывается, даже не в состоянии прочитать, что вам тут постят.

Сообщение было отредактировано: 6 май 13, 15:44
6 май 13, 15:44    [14263567]     Ответить | Цитировать Сообщить модератору
 Re: Господа, прошу помощи с оптимизацией индексной структуры  [new]
eny
Member

Откуда: москва
Сообщений: 291
Гавриленко Сергей Алексеевич
eny
Гавриленко Сергей Алексеевич,

ага без проблем:
Хендерсон - Профессиональное руководство по MS SQL server
Дэн Тоу - Настройка SQL для профессионалов

можно стянуть с торрентов
Там этой чуши нет. Вас же не затруднит дать цитату из книги?


ну-ну, а что там есть?
найдите сами следующие утверждение в книгах:

При создании кластерного индекса на таблице все остальные индексы начинают указывать не на физическую запись в таблице, а на запись в кластерном индексе. Таким образом СУБД, оказываются не способной получать ссылки на данные напрямую и неизбежно переходит к кластерному индексу.

Дэн Тоу вообще рекомендует обходиться без кластеров.
6 май 13, 15:46    [14263581]     Ответить | Цитировать Сообщить модератору
 Re: Господа, прошу помощи с оптимизацией индексной структуры  [new]
eny
Member

Откуда: москва
Сообщений: 291
Гавриленко Сергей Алексеевич
eny
пропущено...


Забейте таблицу, как в начале обсуждения, реальными данными с той же структурой и тогда рассуждайте.
А демонстрировать планы запросов с одной строкой в таблице -это ни о чем
Может еще вам за пивком сгонять? Я привел два примера, опровергающие ваши утверждения. Потрудитесь теперь вы хоть что-нибудь привести. Иначе все ваше поведение - это обычный троллинг.


Это у Вас троллинг, в самом начале человек описал реальную ситуацию с проблемами в большой таблице, которые реально существуют, а Вы рассазываете как у Вас все работает на простой табличке с одной записью.

То есть Ваша рекомендация удалить строки, оставить одну и наслаждаться быстодействием - так?
6 май 13, 15:50    [14263626]     Ответить | Цитировать Сообщить модератору
 Re: Господа, прошу помощи с оптимизацией индексной структуры  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31429
eny
найдите сами следующие утверждение в книгах:

При создании кластерного индекса на таблице все остальные индексы начинают указывать не на физическую запись в таблице, а на запись в кластерном индексе.
Да с этим никто не спорит, это азы, у вас просто выводы неправильные, я вам уже писал.

eny
Таким образом СУБД, оказываются не способной получать ссылки на данные напрямую и неизбежно переходит к кластерному индексу.
Надо же, неужели так дословно и написано?

Это конечно верно, не поспоришь, но звучит как "Таким образом СУБД, оказываются не способной получать данные напрямую и неизбежно переходит к чтению с диска или из памяти" :-)
6 май 13, 15:51    [14263629]     Ответить | Цитировать Сообщить модератору
 Re: Господа, прошу помощи с оптимизацией индексной структуры  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37050
eny
Гавриленко Сергей Алексеевич
пропущено...
Может еще вам за пивком сгонять? Я привел два примера, опровергающие ваши утверждения. Потрудитесь теперь вы хоть что-нибудь привести. Иначе все ваше поведение - это обычный троллинг.


Это у Вас троллинг, в самом начале человек описал реальную ситуацию с проблемами в большой таблице, которые реально существуют, а Вы рассазываете как у Вас все работает на простой табличке с одной записью.

То есть Ваша рекомендация удалить строки, оставить одну и наслаждаться быстодействием - так?

Специально для слепых:
name                                                                                                                             rows        reserved           data               index_size         unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
SomeBigTable                                                                                                                     1668990048  225184512 KB       59688416 KB        164629040 KB       867056 KB
6 май 13, 15:52    [14263637]     Ответить | Цитировать Сообщить модератору
 Re: Господа, прошу помощи с оптимизацией индексной структуры  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37050
eny
При создании кластерного индекса на таблице все остальные индексы начинают указывать не на физическую запись в таблице, а на запись в кластерном индексе. Таким образом СУБД, оказываются не способной получать ссылки на данные напрямую и неизбежно переходит к кластерному индексу.
Когда все необходимые данные есть в некластерном индексе, сервер никуда не переходит. Пишу второй раз, опять же, для слепых.

eny
Дэн Тоу вообще рекомендует обходиться без кластеров.
Оставим эти рекомендации на его совести.
6 май 13, 15:55    [14263660]     Ответить | Цитировать Сообщить модератору
 Re: Господа, прошу помощи с оптимизацией индексной структуры  [new]
eny
Member

Откуда: москва
Сообщений: 291
Гавриленко Сергей Алексеевич

Кластерный по (bigint_key, binary_key), некластерный по binary_key.

Так же будем считать, что таблица под определение "большие данные" подходит.


Извиняюсь не увидел, значит в ms sql не все так плохо как пишут ;) видимо обычный индекс тоже дает возможность прямого доступа.

Но проблема со сканированием возникает, когда нужно не одну запись получить, а диапазон, вот тогда оказывается, что проще перебирать кластерный индекс, в этом случае считывается минимальное количество страниц данных, то есть все.
6 май 13, 15:57    [14263679]     Ответить | Цитировать Сообщить модератору
 Re: Господа, прошу помощи с оптимизацией индексной структуры  [new]
Кот Матроскин
Member

Откуда: Москва
Сообщений: 8933
alexeyvg
Сергей Викт.
alexeyvg, т.е если создать такой индекс, как вы описали и в запросе указать WITH INDEX, должно проскакивать быстрее?
Без всякого WITH INDEX он должен использоваться и должно быть быстрее.


Ну это если считать, что PLType более селективен, чем PLdate. Учитывая, что по словам ТС, отдельный индекс по PLtype есть, но оптимизатор тем не менее его не берет - это не факт.
6 май 13, 16:07    [14263759]     Ответить | Цитировать Сообщить модератору
 Re: Господа, прошу помощи с оптимизацией индексной структуры  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37050
eny
Но проблема со сканированием возникает, когда нужно не одну запись получить, а диапазон, вот тогда оказывается, что проще перебирать кластерный индекс, в этом случае считывается минимальное количество страниц данных, то есть все.
Да, это возможно.
6 май 13, 16:10    [14263778]     Ответить | Цитировать Сообщить модератору
 Re: Господа, прошу помощи с оптимизацией индексной структуры  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37050
Кот Матроскин
alexeyvg
пропущено...
Без всякого WITH INDEX он должен использоваться и должно быть быстрее.


Ну это если считать, что PLType более селективен, чем PLdate. Учитывая, что по словам ТС, отдельный индекс по PLtype есть, но оптимизатор тем не менее его не берет - это не факт.
Не берет, поди, не из-за селективности, а из-за того, что надо с этого некластерного индекса лукапить другие поля. Надо сначала его сделать покрывающим, а потом сравнивать.
6 май 13, 16:12    [14263792]     Ответить | Цитировать Сообщить модератору
 Re: Господа, прошу помощи с оптимизацией индексной структуры  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31429
Кот Матроскин
alexeyvg
пропущено...
Без всякого WITH INDEX он должен использоваться и должно быть быстрее.


Ну это если считать, что PLType более селективен, чем PLdate. Учитывая, что по словам ТС, отдельный индекс по PLtype есть, но оптимизатор тем не менее его не берет - это не факт.
В данном случае сравниваем 2 варианта
PLType, PLdate
PLdate, PLType

При этом в условиях для PLType указывается одно значение, для PLdate - диапазон

Если совокупность условий "одно значение PLType, диапазон PLdate" (а не условие только по значению PLType!) оказывается достаточно селективным, то сиквел выберет этот индекс.

А вот индекс PLdate, PLType при таком же условии будет по любому хуже.

Если же значения будут неселективными, то собственон ни тот, ни другой индекс использоваться не будут, будет скан по индексу, таблице или поиск по какому то другому индексу.
6 май 13, 16:17    [14263827]     Ответить | Цитировать Сообщить модератору
 Re: Господа, прошу помощи с оптимизацией индексной структуры  [new]
Алексей Куренков
Member [заблокирован]

Откуда: Москва
Сообщений: 567
Сергей Викт.,

Вообще достаточно глубоко разбирают оптимизацию по индексам на текдее в майкрасофте - посмотрите сами )))
http://www.techdays.ru/videos/4303.html

там же найдете и 2 и 3ю часть при желании.
6 май 13, 16:17    [14263831]     Ответить | Цитировать Сообщить модератору
 Re: Господа, прошу помощи с оптимизацией индексной структуры  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31429
Гавриленко Сергей Алексеевич
Надо сначала его сделать покрывающим, а потом сравнивать.
Да, я же ещё там говорил про INCLUDE...

Но вообще, как уже говорили, нужна статистика конкретных запросов от прирложения, мало ли, может там по этому условию полтаблицы подтягивается, тогда только сканить.
6 май 13, 16:19    [14263849]     Ответить | Цитировать Сообщить модератору
 Re: Господа, прошу помощи с оптимизацией индексной структуры  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37050
eny
Гавриленко Сергей Алексеевич
Кластерный по (bigint_key, binary_key), некластерный по binary_key.

Так же будем считать, что таблица под определение "большие данные" подходит.


Извиняюсь не увидел, значит в ms sql не все так плохо как пишут ;) видимо обычный индекс тоже дает возможность прямого доступа.
Да нет никакого прямого или косого доступа.

Когда сервер использует некластерный индекс для доступа к данным, то варианты развития событий следующие:
1. все нужные есть непосредственно в некластерном индексе:
-- в его ключе;
-- в кластерном ключе (который располагается на листовом уровне некластерного);
-- они были "вкрячены" в некластерный индекс кляюзой include.
2. каких-то полей не хватает, тогда они достаются:
-- при наличии на таблице кластерного индекса - операцией Bookmark Lookup / Clustered Index Seek, т.е. поиском по кластерному индексу используя значение ключа кластерного индекса (который располагается на листовом уровне некластерного же)
-- при отсутствии на таблице кластерного индекса - операцией RID Lookup, используя физические координаты страницы, которые в этом случае хранятся вместо ключей кластерного индекса на листовом уровне некластерного.

Все. Проще пареной репы.
6 май 13, 16:21    [14263865]     Ответить | Цитировать Сообщить модератору
 Re: Господа, прошу помощи с оптимизацией индексной структуры  [new]
Кот Матроскин
Member

Откуда: Москва
Сообщений: 8933
Гавриленко Сергей Алексеевич,

Ну я поэтому и говорю "Не факт, что более селективен". Если бы не брал даже покрывающий - можно было бы смело говорить "Факт что не более селективен" :)
6 май 13, 16:22    [14263872]     Ответить | Цитировать Сообщить модератору
 Re: Господа, прошу помощи с оптимизацией индексной структуры  [new]
Сергей Викт.
Member

Откуда: Москва
Сообщений: 888
Алексей Куренков
Сергей Викт.,

Вообще достаточно глубоко разбирают оптимизацию по индексам на текдее в майкрасофте - посмотрите сами )))
http://www.techdays.ru/videos/4303.html

там же найдете и 2 и 3ю часть при желании.


Алексей, спасибо, обязательно посмотрю из дома) На работе низя:)
6 май 13, 16:27    [14263903]     Ответить | Цитировать Сообщить модератору
 Re: Господа, прошу помощи с оптимизацией индексной структуры  [new]
Кот Матроскин
Member

Откуда: Москва
Сообщений: 8933
alexeyvg
Кот Матроскин
пропущено...

Ну это если считать, что PLType более селективен, чем PLdate. Учитывая, что по словам ТС, отдельный индекс по PLtype есть, но оптимизатор тем не менее его не берет - это не факт.
В данном случае сравниваем 2 варианта
PLType, PLdate
PLdate, PLType

При этом в условиях для PLType указывается одно значение, для PLdate - диапазон

Если совокупность условий "одно значение PLType, диапазон PLdate" (а не условие только по значению PLType!) оказывается достаточно селективным, то сиквел выберет этот индекс.

А вот индекс PLdate, PLType при таком же условии будет по любому хуже.

В смысле "по любому"? Если данное значение PLType e 2/3 записей в таблице - (PlType, Pldate) использовать явно не стоит, а вот (PLdate, PlType) - почему бы и нет.
6 май 13, 16:31    [14263924]     Ответить | Цитировать Сообщить модератору
 Re: Господа, прошу помощи с оптимизацией индексной структуры  [new]
Сергей Викт.
Member

Откуда: Москва
Сообщений: 888
В общем сейчас буду менять тип с char на varchar и засекать результаты:)
скрипт сгенерился таким образом:

BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_t_Pricelists
(
PLDate datetime NOT NULL,
PLType varchar(18) NOT NULL,
Article varchar(25) NOT NULL,
BPrice money NOT NULL
) ON [PRIMARY]
GO
GRANT DELETE ON dbo.Tmp_t_Pricelists TO public AS dbo
GO
GRANT INSERT ON dbo.Tmp_t_Pricelists TO public AS dbo
GO
GRANT SELECT ON dbo.Tmp_t_Pricelists TO public AS dbo
GO
GRANT UPDATE ON dbo.Tmp_t_Pricelists TO public AS dbo
GO
GRANT VIEW DEFINITION ON dbo.Tmp_t_Pricelists TO public AS dbo
GO
IF EXISTS(SELECT * FROM dbo.t_Pricelists)
EXEC('INSERT INTO dbo.Tmp_t_Pricelists (PLDate, PLType, Article, BPrice)
SELECT PLDate, CONVERT(varchar(18), PLType), CONVERT(varchar(25), Article), BPrice FROM dbo.t_Pricelists WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.t_Pricelists
GO
EXECUTE sp_rename N'dbo.Tmp_t_Pricelists', N't_Pricelists', 'OBJECT'
GO
ALTER TABLE dbo.t_Pricelists ADD CONSTRAINT
PK_Pricelists PRIMARY KEY CLUSTERED
(
PLDate,
PLType,
Article
) WITH( PAD_INDEX = OFF, FILLFACTOR = 90, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
CREATE NONCLUSTERED INDEX Relation_7_FK ON dbo.t_Pricelists
(
Article
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX Relation_9_FK ON dbo.t_Pricelists
(
PLType
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IX_Pricelists_PLDate ON dbo.t_Pricelists
(
PLDate
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.t_Pricelists ADD CONSTRAINT
CH_Pricelists_Archive CHECK (([PLDate]<'05/14/2012' OR [PLDate]>'07/09/2012'))
GO
CREATE trigger tid_Pricelists_UpdatePLDates on dbo.t_Pricelists
for insert,delete
as

if @@rowcount = 0
return


if (select count(*) from inserted) > 0
begin

if not update(PLDate)
return

if exists(
select top 1 1
from inserted
where PLDate not in (select PLDate from t_PLDates)
)
insert t_PLDates(
PLDate,
Items
)
select
PLDate,
count(*)
from
inserted
group by
PLDate
having
PLDate not in (select PLDate from t_PLDates)
else
update t_PLDates
set Items = t.Items + i.Items
from
t_PLDates t,
(
select
PLDate,
count(*)
from
inserted
group by
PLDate
)i(
PLDate,
Items
)
where
t.PLDate = i.PLDate
end

if (select count(*) from deleted) > 0
begin
update t_PLDates
set Items = t.Items - d.Items
from
t_PLDates t,
(
select
PLDate,
count(*)
from
deleted
group by
PLDate
)d(
PLDate,
Items
)
where
t.PLDate = d.PLDate

if @@rowcount > 0
delete t_PLDates
where Items = 0
end
GO
COMMIT

Вот сижу думаю, сколько времени он будет выполняться на 350 млн. записей:(
6 май 13, 17:06    [14264136]     Ответить | Цитировать Сообщить модератору
 Re: Господа, прошу помощи с оптимизацией индексной структуры  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31429
Кот Матроскин
В смысле "по любому"? Если данное значение PLType e 2/3 записей в таблице - (PlType, Pldate) использовать явно не стоит, а вот (PLdate, PlType) - почему бы и нет.
"По любому" - там же написано - "при таком же условии", то есть при условии, что выборка по PlType, Pldate высокоселективная.
6 май 13, 17:34    [14264248]     Ответить | Цитировать Сообщить модератору
 Re: Господа, прошу помощи с оптимизацией индексной структуры  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31429
Сергей Викт.
В общем сейчас буду менять тип с char на varchar и засекать результаты:)
скрипт сгенерился таким образом:
Странно, а почему не просто alter table???
6 май 13, 17:36    [14264250]     Ответить | Цитировать Сообщить модератору
 Re: Господа, прошу помощи с оптимизацией индексной структуры  [new]
Сергей Викт.
Member

Откуда: Москва
Сообщений: 888
alexeyvg
Странно, а почему не просто alter table???


А вот хз почему так SSMS решил) Ещё не запускал, попробую просто через Alter Table
6 май 13, 17:45    [14264293]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 2 [3] 4 5   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить