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

Откуда: Москва
Сообщений: 39
Вопрос наверное более теоритический, но хотелось бы понимать возможные последствия действий. В общем, есть таблица с большим числом строк (порядка 500 миллионов), пополняется раз в сутки примерно на 2-3 миллиона записей. Структура таблицы - поле smalldatetime, 5 полей типа int и поле tinyint - 4 из них являются внешними ключами и вместе составляют первичный ключ. Записей реально много, вставка происходит все медленнее и я задумал разбить эту большую таблицу на несколько меньших. Один из внешних ключей (поле tinyint) имеет ограниченное число значений - 8. Я хочу вместо одной таблицы сделать 8 с одинаковой структурой и в каждой из них хранить записи только с одним значением этого поля tinyint. При создании отчетов буду использовать union. Самое большое опасение - если нужно будет сделать какую-то сложную выборку по данным всех таблиц, я буду вынужден сначала их склеить union-ом.
Вообще, какими подводными камнями это чревато (помимо умножения на 8 числа запросов)? Вставка будет идти быстрее, это одноначно.
6 сен 13, 17:59    [14806118]     Ответить | Цитировать Сообщить модератору
 Re: Разбиение таблицы на несколько  [new]
Oblom
Member

Откуда: Moscow Square
Сообщений: 637
andy_111,

Партицирование?
7 сен 13, 00:02    [14807483]     Ответить | Цитировать Сообщить модератору
 Re: Разбиение таблицы на несколько  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34709
andy_111,

Как бы разбивать не имеет смысла.
Вставка должна быть O(1) от размера таблицы, если таблица с индексами -- O(log N) от размера.
Вставка не может сильно тормозить.

Если ты уменьшишь таблицу так, как хочешь, то размер каждого куска уменьшится на 8, это чуть меньше,
чем на порядок, ну, пусть будет на порядок. Это с 5*10^8 до 5*10^7 !
Несущественная разница. Учитывая, что она под логарифмом, то её считай что нет вообще.
7 сен 13, 00:32    [14807580]     Ответить | Цитировать Сообщить модератору
 Re: Разбиение таблицы на несколько  [new]
Pich
Member

Откуда: Minsk
Сообщений: 173
Разбиение таблиц на части и даже разбиение баз данных на части, по моему называется, секционирование. Это рекомендуемый прием для повышения быстродействия информационной системы в целом. Мы им пользуемся постоянно.
7 сен 13, 01:50    [14807789]     Ответить | Цитировать Сообщить модератору
 Re: Разбиение таблицы на несколько  [new]
andy_111
Member

Откуда: Москва
Сообщений: 39
Спасибо за ответы, забыл написать самое главное :
select @@version
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   
Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) 

То есть ни партиционирования, ни компрессии не предусмотрено в моей версии. Я вообще перед вставкой сношу все индексы на таблице и создаю их потом - мне кажется, что они станут жутко фрагментированными при вставке таких масштабов. Вставляю запросом типа :

INSERT INTO TABLE1 T1 VALUES(...) WHERE NOT EXISTS(SELECT DT FROM TABLE1 T2 WHERE T1.КЛЮЧЕВЫЕ ПОЛЯ = T2.КЛЮЧЕВЫЕ ПОЛЯ)
Вынужден проверять наличие данных, так как источник ненадежен и вполне может выложить дважды одно и то же.

А можно попродробнее про
MasterZiv
Вставка должна быть O(1) от размера таблицы, если таблица с индексами -- O(log N) от размера.

Если ты уменьшишь таблицу так, как хочешь, то размер каждого куска уменьшится на 8, это чуть меньше,
чем на порядок, ну, пусть будет на порядок. Это с 5*10^8 до 5*10^7 !
Несущественная разница. Учитывая, что она под логарифмом, то её считай что нет вообще.

Хотелось бы изучить эту теорию, я пока не наталкивался на эти расчеты. Можете дать ссылку?
7 сен 13, 10:34    [14807966]     Ответить | Цитировать Сообщить модератору
 Re: Разбиение таблицы на несколько  [new]
zxc1257
Member

Откуда:
Сообщений: 71
andy_111

Я вообще перед вставкой сношу все индексы на таблице и создаю их потом - мне кажется, что они станут жутко фрагментированными при вставке таких масштабов. Вставляю запросом типа :

INSERT INTO TABLE1 T1 VALUES(...) WHERE NOT EXISTS(SELECT DT FROM TABLE1 T2 WHERE T1.КЛЮЧЕВЫЕ ПОЛЯ = T2.КЛЮЧЕВЫЕ ПОЛЯ)


Вынужден проверять наличие данных, так как источник ненадежен и вполне может выложить дважды одно и то же.


:)
7 сен 13, 10:49    [14807981]     Ответить | Цитировать Сообщить модератору
 Re: Разбиение таблицы на несколько  [new]
zxc1257
Member

Откуда:
Сообщений: 71
не надо вам партиции строгать. мало данных 500 млн.

как вам такой вариант?

1) дропнули индексы
2) залили с помощью bcp
3) создали индексы
4) а потом таким запросом определили дубликаты

select t2.id
from
(
    select t1.id, row_number() over(partition by id order by (select 0)) rn
    from tbl t1
) t2
where rn > 1
7 сен 13, 10:54    [14807987]     Ответить | Цитировать Сообщить модератору
 Re: Разбиение таблицы на несколько  [new]
zxc1257
Member

Откуда:
Сообщений: 71
на шаге 3 создали не уникальный индекс по ключу. определили запросом дубли. разобрались с ними и потом построили constraint pk.
7 сен 13, 11:00    [14807996]     Ответить | Цитировать Сообщить модератору
 Re: Разбиение таблицы на несколько  [new]
aleks2
Guest
andy_111
INSERT INTO TABLE1 T1 VALUES(...) WHERE NOT EXISTS(SELECT DT FROM TABLE1 T2 WHERE T1.КЛЮЧЕВЫЕ ПОЛЯ = T2.КЛЮЧЕВЫЕ ПОЛЯ)

Хотелось бы изучить эту теорию, я пока не наталкивался на эти расчеты. Можете дать ссылку?


Может обучить тредстартера создать кластерный индекс и познакомить с опцией IGNORE_DUPLICATE?
7 сен 13, 11:20    [14808033]     Ответить | Цитировать Сообщить модератору
 Re: Разбиение таблицы на несколько  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4538
Pich
Разбиение таблиц на части и даже разбиение баз данных на части, по моему называется, секционирование. Это рекомендуемый прием для повышения быстродействия информационной системы в целом. Мы им пользуемся постоянно.

У ТС версия стандарт.
7 сен 13, 11:40    [14808060]     Ответить | Цитировать Сообщить модератору
 Re: Разбиение таблицы на несколько  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34709
andy_111
Хотелось бы изучить эту теорию, я пока не наталкивался на эти расчеты. Можете дать ссылку?


Какую теорию? Тебе нужна ссылка на мое предыдущее сообщение?
Поиск по индексу — это бинарный поиск, найди в википедии, посмотри его стоимость.
7 сен 13, 12:54    [14808190]     Ответить | Цитировать Сообщить модератору
 Re: Разбиение таблицы на несколько  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34709
andy_111
Я вообще перед вставкой сношу все индексы на таблице и создаю их потом - мне кажется, что они станут жутко фрагментированными при вставке таких масштабов.


Не парься на эту тему вообще никогда.
Индексы стоит удалять перед вставкой только если так будет быстрее. Только не забудь считать время удаления индексов, вставки и создания индексов в одном случае и чисто вставки в другом.
Кроме того, раз у тебя не чисто вставка, тебе хотя бы один индекс удалять НЕЛЬЗЯ !

andy_111
Вставляю запросом типа :

INSERT INTO TABLE1 T1 VALUES(...) WHERE NOT EXISTS(SELECT DT FROM TABLE1 T2 WHERE T1.КЛЮЧЕВЫЕ ПОЛЯ = T2.КЛЮЧЕВЫЕ ПОЛЯ)
Вынужден проверять наличие данных, так как источник ненадежен и вполне может выложить дважды одно и то же.


Вот на эти T2.КЛЮЧЕВЫЕ ПОЛЯ у тебя обязательно должен быть индекс при вставке, вот отсюда и тормоза, если у тебя его нет.

Есть другой сценарий — вставляйте все в другую таблицу с такой же структурой, потом удаляй оттуда записи, которые уже есть, потом дропай индекс и усталый скопом все записи. Если конечно, ещё раз, индексы вообще нужно дропать.
В чем я сомневаюсь.
7 сен 13, 13:42    [14808308]     Ответить | Цитировать Сообщить модератору
 Re: Разбиение таблицы на несколько  [new]
andy_111
Member

Откуда: Москва
Сообщений: 39
aleks2
andy_111
INSERT INTO TABLE1 T1 VALUES(...) WHERE NOT EXISTS(SELECT DT FROM TABLE1 T2 WHERE T1.КЛЮЧЕВЫЕ ПОЛЯ = T2.КЛЮЧЕВЫЕ ПОЛЯ)

Хотелось бы изучить эту теорию, я пока не наталкивался на эти расчеты. Можете дать ссылку?


Может обучить тредстартера создать кластерный индекс и познакомить с опцией IGNORE_DUPLICATE?


Учить не надо, я после изучения книги SQL Server Internals от Kalen Delaney и сам могу поучить :) Спасибо, я примерно понял - единого решения не существует, надо пробовать. Замкнутый круг получается - чтобы быстро найти вставляемые данные, нужен кластеризованный индекс по указанным ключевым полям (я его сношу перед вставкой), но при самой вставке этот индекс реально замедляет процесс - это проверено на практике, с ним вставка получается медленнее в несколько раз.

В общем, всем спасибо за внимание, буду заниматься экспериментами.
7 сен 13, 14:57    [14808475]     Ответить | Цитировать Сообщить модератору
 Re: Разбиение таблицы на несколько  [new]
Exproment
Member

Откуда:
Сообщений: 418
andy_111
Учить не надо, я после изучения книги SQL Server Internals от Kalen Delaney и сам могу поучить :) Спасибо, я примерно понял - единого решения не существует, надо пробовать. Замкнутый круг получается - чтобы быстро найти вставляемые данные, нужен кластеризованный индекс по указанным ключевым полям (я его сношу перед вставкой), но при самой вставке этот индекс реально замедляет процесс - это проверено на практике, с ним вставка получается медленнее в несколько раз.

Можно увидеть данную стратегию индексирования ? 0_о Кроме кластерного еще индексы есть ? вы и их удаляете ? Кто вообще сказал удалять кластерный индекс и создавать его снова ? Это дало вам производительность ? 0_о

1) Я так понимаю, что ваша таблица лишена суррогатного ключа... ?
2) вы уверены, что на таблице нет триггеров ?
3) Как обстоят дела с констреинтами ?
4) покажите наконец план выполнения вашего инсерта.
7 сен 13, 15:16    [14808513]     Ответить | Цитировать Сообщить модератору
 Re: Разбиение таблицы на несколько  [new]
Exproment
Member

Откуда:
Сообщений: 418
andy_111
чтобы быстро найти вставляемые данные, нужен кластеризованный индекс по указанным ключевым полям

откуда данное утверждение ? чем оно обосновано ?
7 сен 13, 15:18    [14808515]     Ответить | Цитировать Сообщить модератору
 Re: Разбиение таблицы на несколько  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 32169
andy_111
Учить не надо, я после изучения книги SQL Server Internals от Kalen Delaney и сам могу поучить :) Спасибо, я примерно понял - единого решения не существует, надо пробовать. Замкнутый круг получается - чтобы быстро найти вставляемые данные, нужен кластеризованный индекс по указанным ключевым полям (я его сношу перед вставкой), но при самой вставке этот индекс реально замедляет процесс - это проверено на практике, с ним вставка получается медленнее в несколько раз.
Как то вы невнимательно читали :-)

Тут же всё очевидно, из за неправильного дизайна таблицы вставка замедляется. Кто выбрал такой кластерный индекс, что для вставки 1000 записей нужно расщепить 1000 страниц?

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

Решение типа удаления индексов принципиально неправильны, если речь идёт о периодической вставке ничтожной доли записей (меньше процента).
7 сен 13, 16:59    [14808731]     Ответить | Цитировать Сообщить модератору
 Re: Разбиение таблицы на несколько  [new]
aleks2
Guest
Exproment
andy_111
нужен кластеризованный индекс по указанным ключевым полям

откуда данное утверждение ? чем оно обосновано ?


Пониманием высшего предназначения кластерного индекса. Учись, студент.

ЗЫ. Тока нафег не надо эти данные "искать".
7 сен 13, 17:26    [14808779]     Ответить | Цитировать Сообщить модератору
 Re: Разбиение таблицы на несколько  [new]
Exproment
Member

Откуда:
Сообщений: 418
aleks2
Exproment
пропущено...

откуда данное утверждение ? чем оно обосновано ?


Пониманием высшего предназначения кластерного индекса. Учись, студент.

ЗЫ. Тока нафег не надо эти данные "искать".

нафег именно кластеризованный индекс для так называемого "быстрого" поиска, студент ?
7 сен 13, 18:01    [14808843]     Ответить | Цитировать Сообщить модератору
 Re: Разбиение таблицы на несколько  [new]
aleks2
Guest
Exproment
aleks2
пропущено...


Пониманием высшего предназначения кластерного индекса. Учись, студент.

ЗЫ. Тока нафег не надо эти данные "искать".

нафег именно кластеризованный индекс для так называемого "быстрого" поиска, студент ?


Читать и то не научился?
7 сен 13, 18:14    [14808869]     Ответить | Цитировать Сообщить модератору
 Re: Разбиение таблицы на несколько  [new]
Exproment
Member

Откуда:
Сообщений: 418
aleks2
Читать и то не научился?

Я как погляжу ваши сообщения не обременены смысловой нагрузкой и больше напоминают флуд... Если вы видите то, что не вижу я, а именно где топикстартер расписывает стратегию индексирования, в частности как выглядит кластеризованный индекс на таблице и почему он выглядит именно так - то милости просим цитаты... Иначе у меня более нет желания вести с вами диалог.

А вообще я еще ни разу не видел необходимости создавать кластеризованный индекс более чем на одном поле и в 99,9999% случаев это суррогатный ключ. Все остальные данные не является одновременно уникальными и последовательно увеличиваемыми(с точки зрения сортировки конечно).
7 сен 13, 19:30    [14808992]     Ответить | Цитировать Сообщить модератору
 Re: Разбиение таблицы на несколько  [new]
Exproment
Member

Откуда:
Сообщений: 418
aleks2
Читать и то не научился?

Я как погляжу ваши сообщения не обременены смысловой нагрузкой и больше напоминают флуд... Если вы видите то, что не вижу я, а именно где топикстартер расписывает стратегию индексирования, в частности как выглядит кластеризованный индекс на таблице и почему он выглядит именно так - то милости просим цитаты... Иначе у меня более нет желания вести с вами диалог.

А вообще я еще ни разу не видел необходимости создавать кластеризованный индекс более чем на одном поле и в 99,9999% случаев это суррогатный ключ. Все остальные данные не является одновременно уникальными и последовательно увеличиваемыми(с точки зрения сортировки конечно).
7 сен 13, 19:32    [14808997]     Ответить | Цитировать Сообщить модератору
 Re: Разбиение таблицы на несколько  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 32169
Exproment
А вообще я еще ни разу не видел необходимости создавать кластеризованный индекс более чем на одном поле и в 99,9999% случаев это суррогатный ключ. Все остальные данные не является одновременно уникальными и последовательно увеличиваемыми(с точки зрения сортировки конечно).
Я вот такое вижу уже несколько лет :-)

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

Насчёт проблемы ТС - тут мы только можем указать на некоторые моменты, которые кажутся подозрительными, но совета дать не можем, потому что детали задачи нам неизвестны.
7 сен 13, 20:53    [14809101]     Ответить | Цитировать Сообщить модератору
 Re: Разбиение таблицы на несколько  [new]
zxc1257
Member

Откуда:
Сообщений: 71
alexeyvg
Exproment
А вообще я еще ни разу не видел необходимости создавать кластеризованный индекс более чем на одном поле и в 99,9999% случаев это суррогатный ключ. Все остальные данные не является одновременно уникальными и последовательно увеличиваемыми(с точки зрения сортировки конечно).
Я вот такое вижу уже несколько лет :-)

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

Насчёт проблемы ТС - тут мы только можем указать на некоторые моменты, которые кажутся подозрительными, но совета дать не можем, потому что детали задачи нам неизвестны.
ся

по поводу замечания об уникальности. вероятно, Exproment намекал/имел_ввиду, что если создать не уникальный кластерный индекс, то db engine сделает его уникальным добавив невидимый унификатор к дублирующимся ключам. кроме того, в строках листовых блоков всех некластерных уникальных/не_уникальных индексов добавится этот унификатор, т. е. у некластерных индексов в листовых блоках строка будет выглядеть уже так: не_кластерный_ключ | дублирующийся_кластерный_ключ | унификатор_кластерного_ключа). унификатор - 4 байта. кроме того, если ключ кластерного индекса делать не суррогатным, при обновлении полей ключа кластерного индекса, будут затронуты и листья не кластерных индексов.
7 сен 13, 21:13    [14809133]     Ответить | Цитировать Сообщить модератору
 Re: Разбиение таблицы на несколько  [new]
Exproment
Member

Откуда:
Сообщений: 418
zxc1257
по поводу замечания об уникальности. вероятно, Exproment намекал/имел_ввиду, что если создать не уникальный кластерный индекс, то db engine сделает его уникальным добавив невидимый унификатор к дублирующимся ключам. кроме того, в строках листовых блоков всех некластерных уникальных/не_уникальных индексов добавится этот унификатор, т. е. у некластерных индексов в листовых блоках строка будет выглядеть уже так: не_кластерный_ключ | дублирующийся_кластерный_ключ | унификатор_кластерного_ключа). унификатор - 4 байта. кроме того, если ключ кластерного индекса делать не суррогатным, при обновлении полей ключа кластерного индекса, будут затронуты и листья не кластерных индексов.

+1 а самое прикольное начинается, когда приходится перестраивать (например после архивирования контента) огромный кластеризованный неуникальный индекс. Т.к. данный процесс начинает изменять вообще все индексы таблицы.

alexeyvg
Я вот такое вижу уже несколько лет :-)

Вам не будет сложно привести чисто абстрактный пример ? Мне действительно интересно наконец понять полезность данного действия :)
7 сен 13, 22:15    [14809229]     Ответить | Цитировать Сообщить модератору
 Re: Разбиение таблицы на несколько  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 32169
Exproment
alexeyvg
Я вот такое вижу уже несколько лет :-)

Вам не будет сложно привести чисто абстрактный пример ? Мне действительно интересно наконец понять полезность данного действия :)
Индекс (уникальный кластерный) состоит из трёх полей целого типа:
ИД_ДатаВремя (данные заливаются по часам, то есть это ссылка на некую таблицу-календарь)
ИД_Счётчика
ИД_Объекта

Данные всегда вставляются для одного значения ИД_ДатаВремя, и для некоего диапазона ИД_Счётчика, при вставке других данных с такими же ИД_ДатаВремя+ИД_Счётчика в таблице нет.

Данные всегда выбираются либо для диапазона ИД_ДатаВремя (агрегация, расчёты), или для диапазона ИД_ДатаВремя + набор счётчиков.

Это даёт максимально крупные блоки работы с диском как для записи, так и для чтения. Ну и вообще, все остальные варианты не просто хуже, а вообще неработоспособны.

И в общем это типично для хранилищь, именно так и рекомендуется делать, это просто как шаблон.
7 сен 13, 23:18    [14809397]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить