Если большая таблица, содержит xml-поля и nvarchar(max), имеет ли смысл,
выделить эти "большие поля" в отдельную дочернюю таблицу и связать с родительской констрейнтом?

CREATE TABLE dbo.Person
(
PersonId int IDENTITY(1,1) PRIMARY KEY,
PersonName nvarchar(256)
)
GO

CREATE TABLE dbo.PersonDetail
(
PersonId int PRIMARY KEY,
PersonData xml NOT NULL
)
GO
ALTER TABLE dbo.PersonDetail WITH CHECK ADD CONSTRAINT FK_PersonDetail_Person FOREIGN KEY(PersonId)
REFERENCES dbo.Person (PersonId)
GO

ALTER TABLE dbo.PersonDetail CHECK CONSTRAINT FK_PersonDetail_Person
GO

Какое при этом получается преимущество перед тем чтобы хранить все данные в одной таблице.

CREATE TABLE dbo.Person2
(
PersonId int IDENTITY(1,1) PRIMARY KEY,
PersonName nvarchar(256),
PersonData xml
)
GO

Поле PersonData довольно часто (в 50% случаев) будет содержать NULL.
В таблицу данные будут добавляться и никогда не изменяться, только читаться.

Как посоветуете поступить?
Какая ещё информация нужна для принятия решения?
Спасибо.

Здравствуйте, Pek2014, Вы писали:

P>Если большая таблица, содержит xml-поля и nvarchar(max), имеет ли смысл,
P>выделить эти "большие поля" в отдельную дочернюю таблицу и связать с родительской констрейнтом?

Не нужно, вы ничего на этом не выиграете, если конечно не будете везде бездумно писать Select * from table. А вот на джойнах можно и потерять.

Здравствуйте, BlackEric, Вы писали:

BE>Здравствуйте, Pek2014, Вы писали:

P>>Если большая таблица, содержит xml-поля и nvarchar(max), имеет ли смысл,
P>>выделить эти "большие поля" в отдельную дочернюю таблицу и связать с родительской констрейнтом?

BE>Не нужно, вы ничего на этом не выиграете, если конечно не будете везде бездумно писать Select * from table. А вот на джойнах можно и потерять.

А если мы в 80% процентах случаев будем читать все поля за исключением "больших" полей.
Выиграют ли от вертикального разрезания таблицы такие запросы?
Если выиграют, это будет существенно или "теоретически"?

> Выиграют ли от вертикального разрезания таблицы такие запросы?
> Если выиграют, это будет существенно или "теоретически"?

Не нужно "оптимизировать" то что не тормозит.
Тем более ЗАРАНЕЕ.

Здравствуйте, Pek2014, Вы писали:

P>А если мы в 80% процентах случаев будем читать все поля за исключением "больших" полей.
P>Выиграют ли от вертикального разрезания таблицы такие запросы?
P>Если выиграют, это будет существенно или "теоретически"?

Поднять тестовую БД. "Разрезать" таблицу. Сделать тесты. Результатами не забыть поделиться. Но я бы не стал ничего резать. Как справедливо заметил BlackEric, вряд ли выигрыш в скорости покроет затраты на джойны. Да и INSERT/UPDATE, буде таковые случаться станут, тоже не добавят скорости. IMHO, конечно. Без реальных данных и тестов судить об этом невозможно.

Здравствуйте, Pek2014, Вы писали:

P>Если большая таблица, содержит xml-поля и nvarchar(max), имеет ли смысл,
P>выделить эти "большие поля" в отдельную дочернюю таблицу и связать с родительской констрейнтом?

У нас в похожем кэйсе это имело смысл. кэйс — хранение почтовых сообщений, тело письма (текст без атачей) обычно довольно жирное. статистика примерно такова что более 90% места занимало именно тело письма. общий размер таблицы уходил за десятки Гигов, при кол-ве записей к сотне тысяч. и при запросе типа subject like '%что то там%' СУБД приходилось поднимать все эти гиги, время выполнения запросов могло легко уходить за минуты. После выноса тела письма в отдельную таблицу, практически любое условие отбора с фул-сканом работало миллисекунды и не сжиралось куча памяти.

Здравствуйте, MadHuman, Вы писали:

MH>Здравствуйте, Pek2014, Вы писали:

P>>Если большая таблица, содержит xml-поля и nvarchar(max), имеет ли смысл,
P>>выделить эти "большие поля" в отдельную дочернюю таблицу и связать с родительской констрейнтом?

MH>У нас в похожем кэйсе это имело смысл. кэйс — хранение почтовых сообщений, тело письма (текст без атачей) обычно довольно жирное. статистика примерно такова что более 90% места занимало именно тело письма. общий размер таблицы уходил за десятки Гигов, при кол-ве записей к сотне тысяч. и при запросе типа subject like '%что то там%' СУБД приходилось поднимать все эти гиги, время выполнения запросов могло легко уходить за минуты. После выноса тела письма в отдельную таблицу, практически любое условие отбора с фул-сканом работало миллисекунды и не сжиралось куча памяти.

Какая СУБД, если не секрет?

Здравствуйте, swimmers, Вы писали:

S>Какая СУБД, если не секрет?

Да любая РСУБД при таких условиях сляжет с гриппом. Постановка задачи изначально неверной была, вот и все. Нормализация — не слышал? Это во-первых. А во-вторых, LIKE автоматом валит производительность, потому что напрочь игнорирует индексы. Нет, можно, конечно, построить индекс по функции, используя наиболее распространенные LIKE. Но это совсем не выход, совсем...

Здравствуйте, LuciferNovoros, Вы писали:

LN>Здравствуйте, swimmers, Вы писали:

S>>Какая СУБД, если не секрет?

LN>Да любая РСУБД при таких условиях сляжет с гриппом. Постановка задачи изначально неверной была, вот и все. Нормализация — не слышал? Это во-первых. А во-вторых, LIKE автоматом валит производительность, потому что напрочь игнорирует индексы. Нет, можно, конечно, построить индекс по функции, используя наиболее распространенные LIKE. Но это совсем не выход, совсем...

При чем здесь нормализация?

Вроде нормальные СУБД и так хранят VARCHAR(MAX) отдельно от основной таблицы.
Потому и спрашиваю — что за СУБД

Здравствуйте, swimmers, Вы писали:

S>Вроде нормальные СУБД и так хранят VARCHAR(MAX) отдельно от основной таблицы.

Нормальные СУБД всегдя хранят BLOB отдельно от основной таблицы, это ты прав. Но на любой СУБД поиск с LIKE по BLOB'у положит эту СУБД на лопатки.

S>Потому и спрашиваю — что за СУБД

Без разницы.

Здравствуйте, LuciferNovoros, Вы писали:

LN>Здравствуйте, swimmers, Вы писали:

S>>Вроде нормальные СУБД и так хранят VARCHAR(MAX) отдельно от основной таблицы.

LN>Нормальные СУБД всегдя хранят BLOB отдельно от основной таблицы, это ты прав. Но на любой СУБД поиск с LIKE по BLOB'у положит эту СУБД на лопатки.

S>>Потому и спрашиваю — что за СУБД

LN>Без разницы.

Ходим по кругу.
Уважаемому MadHuman помог вынос блобов в отдельную таблицу.
Мы с вами сошлись, что нормальные СУБД и так хранят блоб отдельно.
Вопрос — какая у него (MadHuman)СУБД и почему все стало летать.

Здравствуйте, swimmers, Вы писали:

S>Ходим по кругу.
S>Уважаемому MadHuman помог вынос блобов в отдельную таблицу.
S>Мы с вами сошлись, что нормальные СУБД и так хранят блоб отдельно.
S>Вопрос — какая у него (MadHuman)СУБД и почему все стало летать.

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

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

хочу обратить внимание, уважаемых собеседников — что речь идет об участии в условии отбора всех полей, кроме текста сообщения. понятно что messageBody like '%аааа%' положит базу и ничо не сделать (ну кроме конечно задействования полнотекстовых поисков), но речь не о них.

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

> СУБД SQLite, но дело в ней.

:-D

Здравствуйте, Pek2014, Вы писали:

P>А если мы в 80% процентах случаев будем читать все поля за исключением "больших" полей.
P>Выиграют ли от вертикального разрезания таблицы такие запросы?
P>Если выиграют, это будет существенно или "теоретически"?
Если в dbo.Person много полей, то "большое" поле лучше вынести. Если мало — достаточно проиндексировать.
Я про Оракл. В вашей системе возможно будет по-другому.

Здравствуйте, MadHuman, Вы писали:

MH>Всё стало летать, так как при фул-скане таблицы для отработки условий выборки по другим полям, с диска перестали подниматься и прогоняться через кэш десятки Гигов данных, размер таблицы с письмами после выноса поля с текстом письма в отдельную, стал сильно меньше и даже её фул-скан стал работать быстро.
Мелкое замечание: несмотря на то, что index seek при like c % в начале образца выполнить не удастся, индекс поможет оптимизировать и такие запросы.
Если бы у вас был индекс по subject, то subject like '%что то там%' не стал бы поднимать в память гигабайты — вместо full scan был бы index scan, а он значительно эффективнее по i/o.

Здравствуйте, Pek2014, Вы писали:

P>Если большая таблица, содержит xml-поля и nvarchar(max), имеет ли смысл,
P>выделить эти "большие поля" в отдельную дочернюю таблицу и связать с родительской констрейнтом?

P>...

P>Как посоветуете поступить?
P>Какая ещё информация нужна для принятия решения?
P>Спасибо.

Как я понимаю, вы используете SQL Server, хотя это справедливо и для других СУБД. Пути у вас два, на мой взгляд:

1. Оставить все в одной таблице и создать покрывающий индекс, в который не входят поля, содержащие LOB данные. Используя индекс, вам удастся избежать ненужного чтения данных с типами varchar(max), xml и прочее.
2. Реализовать подход, который предложил коллега MadHuman здесь

Автор: MadHuman
Дата: 05.04.17

. Секрет успеха кроется не в том, что используется SQLite, а в том, что базовая таблица за счет переноса части данных была уменьшена в объемах. Поэтому для получения того же количества данных теперь требуется меньше ресурсов в виде памяти и процессора. Причем в приведенном примере хранились не LOB данные, да и к тому же использовалось сканирование таблицы.

Существует мнение, которое здесь и было высказано, что LOB данные типа varchar(max), xml и прочие хранятся в отдельной единице размещения называемой LOB_DATA и это правильно. Но есть нюанс, который заключается в том что, если ваша вставляемая запись умещается на страницу 8000 байт, то используется обычная единица размещения IN_ROW_DATA. Например в случае предполагаемых колонок С1 — varchar(max) и С2 — xml, если С1 содержит NULL, то размер xml документа должен быть больше 8КБ, чтобы данные разместились в единице размещения LOB_DATA, в противном случае они будут размещены рядом со всеми остальными данными в IN_ROW_DATA. Это оказывается важным при сканировании данных, будь то индекс или куча, даже не смотря на то, что вы не используете *. Но все не так страшно, поведение по умолчанию можно переопределить через функцию sp_tableoption и тогда LOB данные в независимости от размера всегда будут располагаться в единице размещения LOB_DATA. Об этом нужно помнить и при необходимости использовать.

Здравствуйте, MadHuman, Вы писали:


MH>Вижу дисскусия набрала обороты СУБД SQLite, но дело в ней.

Да нет, дело именно в ней.

MH>я не говорил что текст письма хранится в блобе, он хранится в обычном текстовом поле, иногда же письма бывают короткие.
MH>Думаю что остальные СУБД также хранят текстовые поля в основных данных записи, ведь сама СУБД же не знает что там будет — много или мало данных.

Не хранят, именно потому, что знают, много или мало данных.

Здравствуйте, swimmers, Вы писали:

S>Вроде нормальные СУБД и так хранят VARCHAR(MAX) отдельно от основной таблицы.

А можно парочку примеров таких СУБД? Те, что я знаю, не хранят VARCHAR отдельно, по крайней мере без дополнительных телодвижений. И, надеюсь, ты VARCHAR с BLOB/CLOB не путаешь.

Здравствуйте, wildwind, Вы писали:

S>>Вроде нормальные СУБД и так хранят VARCHAR(MAX) отдельно от основной таблицы.

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

Возможно, имеется в виду хранение LOB данных в отдельной от остальных данных единице размещения в пределах одной таблицы, как например у SQL Server — LOB_DATA против IN_ROW_DATA allocation unit (Организация таблиц и индексов)

Здравствуйте, Olaf, Вы писали:

O>Возможно, имеется в виду хранение LOB данных в отдельной от остальных данных единице размещения в пределах одной таблицы

Сомневаюсь, что человек, написавший "VARCHAR(MAX)", имел в виду LOB, к которого нет никакого "(MAX)".