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

Откуда:
Сообщений: 3947
Есть большая база данных ФИО - около 50 млн
очень уж тяжелая - поэтому подумываю создать справочники Фамилий, Имен и Отчеств и связать их с основной таблицей

Встает вопрос: что практичней и лучше (меньше по объему, быстрее по доступу, удобней для доступа)?

Спасибо за мнения
2 фев 09, 04:21    [6764708]     Ответить | Цитировать Сообщить модератору
 Re: Что лучше: индекс по столбцу или создание справочника?  [new]
miksoft
Member

Откуда:
Сообщений: 38301
Зависит от того, что вы делаете с этой табличкой.
Имхо, если для задачи достаточно индекса, то лучше остановиться на индексе.
2 фев 09, 10:14    [6765116]     Ответить | Цитировать Сообщить модератору
 Re: Что лучше: индекс по столбцу или создание справочника?  [new]
sp
Member

Откуда:
Сообщений: 3947
miksoft
Зависит от того, что вы делаете с этой табличкой.
Имхо, если для задачи достаточно индекса, то лучше остановиться на индексе.


я так понимаю индекс по полям раздует сильно базу?
2 фев 09, 10:49    [6765359]     Ответить | Цитировать Сообщить модератору
 Re: Что лучше: индекс по столбцу или создание справочника?  [new]
miksoft
Member

Откуда:
Сообщений: 38301
sp
miksoft
Зависит от того, что вы делаете с этой табличкой.
Имхо, если для задачи достаточно индекса, то лучше остановиться на индексе.
я так понимаю индекс по полям раздует сильно базу?
что для вас "сильно"? у вас база хранится на дисках прошлого века? Или вам регулярно нужно ее по диал-апу передавать?
2 фев 09, 10:52    [6765378]     Ответить | Цитировать Сообщить модератору
 Re: Что лучше: индекс по столбцу или создание справочника?  [new]
Bely
Member

Откуда: Москва
Сообщений: 1903
sp
я так понимаю индекс по полям раздует сильно базу?
А вы думаете, что поиск в справочнике имени текстом, получение ID имени и потом поиск этого ID в большой таблице (без индекса) будет быстрым?
Наврядли... Так что индекс вам полюбому строить, скорее всего.

Но опять же - смотря какие запросы будут гоняться.

PS: если справочники имен и отчеств будут довольно компактными, то справочник фамилий будет большим. Надо еще и это учитывать.
2 фев 09, 11:13    [6765533]     Ответить | Цитировать Сообщить модератору
 Re: Что лучше: индекс по столбцу или создание справочника?  [new]
ASCRUS
Member

Откуда: МО Электросталь
Сообщений: 5994
Я бы сделал в таблице на поля Имя и Отчество просто индексы, а вот на поле Фамилия вычисляемое поле c_ФИО COMPUTE(Left(ФИО, 3)) и на него уже индекс. Тогда вне зависимости от кол-ва фамилий в таблице, вот такой вот запрос всегда бы летал:
SELECT *
FROM Таблица
WHERE c_ФИО = Left(@ПоисковоеЗначение, 3) AND ФИО = @ПоисковоеЗначение

P.S. Все конечно будет зависеть от того, поддерживает ли СУБД вычисляемые поля и индексы на них. В принципе, если не поддерживает, никто не мешает сделать обычное NULL поле и аналогично вычислять его своей логикой - в триггере, ХП или же на клиенте.
2 фев 09, 11:47    [6765808]     Ответить | Цитировать Сообщить модератору
 Re: Что лучше: индекс по столбцу или создание справочника?  [new]
sp
Member

Откуда:
Сообщений: 3947
Bely
sp
я так понимаю индекс по полям раздует сильно базу?
А вы думаете, что поиск в справочнике имени текстом, получение ID имени и потом поиск этого ID в большой таблице (без индекса) будет быстрым?
Наврядли... Так что индекс вам полюбому строить, скорее всего.

Но опять же - смотря какие запросы будут гоняться.

PS: если справочники имен и отчеств будут довольно компактными, то справочник фамилий будет большим. Надо еще и это учитывать.


Ну индекс по числовому столбцу компактнее чем по столбцу фамилий в основной таблице?

Дело в том что я проиндексировал сейчас столбец фамилий и наблюдаю следующую какртину - в соединении при первом запросе по фамилии база долго тарахтит около минуты и затем выдает результат, последующие запросы проходят быстро - насколько я понимаю сервер подготовился типерь к запросу такого типа
Стоит поработать с другими таблицами и опять сделать запрос к таблице по фамилии опять при первом запросе тормоза - вот это меня сильно смущает
2 фев 09, 11:48    [6765809]     Ответить | Цитировать Сообщить модератору
 Re: Что лучше: индекс по столбцу или создание справочника?  [new]
sp
Member

Откуда:
Сообщений: 3947
ASCRUS
Я бы сделал в таблице на поля Имя и Отчество просто индексы, а вот на поле Фамилия вычисляемое поле c_ФИО COMPUTE(Left(ФИО, 3)) и на него уже индекс. Тогда вне зависимости от кол-ва фамилий в таблице, вот такой вот запрос всегда бы летал:
SELECT *
FROM Таблица
WHERE c_ФИО = Left(@ПоисковоеЗначение, 3) AND ФИО = @ПоисковоеЗначение

P.S. Все конечно будет зависеть от того, поддерживает ли СУБД вычисляемые поля и индексы на них. В принципе, если не поддерживает, никто не мешает сделать обычное NULL поле и аналогично вычислять его своей логикой - в триггере, ХП или же на клиенте.


Интересная идея!
А почему индекс только на 3 символа?
Можно мысль поразвернутей?

Спасибо
2 фев 09, 11:54    [6765860]     Ответить | Цитировать Сообщить модератору
 Re: Что лучше: индекс по столбцу или создание справочника?  [new]
miksoft
Member

Откуда:
Сообщений: 38301
sp
Дело в том что я проиндексировал сейчас столбец фамилий и наблюдаю следующую какртину - в соединении при первом запросе по фамилии база долго тарахтит около минуты и затем выдает результат, последующие запросы проходят быстро - насколько я понимаю сервер подготовился типерь к запросу такого типа
Стоит поработать с другими таблицами и опять сделать запрос к таблице по фамилии опять при первом запросе тормоза - вот это меня сильно смущает
Что для вас "запрос по фамилии"?
2 фев 09, 11:58    [6765896]     Ответить | Цитировать Сообщить модератору
 Re: Что лучше: индекс по столбцу или создание справочника?  [new]
grey_rat
Member

Откуда:
Сообщений: 62
sp
ASCRUS
Я бы сделал в таблице на поля Имя и Отчество просто индексы, а вот на поле Фамилия вычисляемое поле c_ФИО COMPUTE(Left(ФИО, 3)) и на него уже индекс.


Интересная идея!
А почему индекс только на 3 символа?
Можно мысль поразвернутей?
Спасибо


Это выработанный практикой компромисс между скоростью и объемом индекса.
Есть вариант - индекс по первым трем буквам фамилии и инициалам. Скорость почти не уступает полностью проиндексированным ФИО, а размер - детский.
2 фев 09, 12:59    [6766341]     Ответить | Цитировать Сообщить модератору
 Re: Что лучше: индекс по столбцу или создание справочника?  [new]
sp
Member

Откуда:
Сообщений: 3947
grey_rat
sp
ASCRUS
Я бы сделал в таблице на поля Имя и Отчество просто индексы, а вот на поле Фамилия вычисляемое поле c_ФИО COMPUTE(Left(ФИО, 3)) и на него уже индекс.


Интересная идея!
А почему индекс только на 3 символа?
Можно мысль поразвернутей?
Спасибо


Это выработанный практикой компромисс между скоростью и объемом индекса.
Есть вариант - индекс по первым трем буквам фамилии и инициалам. Скорость почти не уступает полностью проиндексированным ФИО, а размер - детский.


Большое спасибо за то что поделились таким нетривиальным трюком - (о таком вообще нигде не видел и намека) такое бы в фак :)
2 фев 09, 14:37    [6767218]     Ответить | Цитировать Сообщить модератору
 Re: Что лучше: индекс по столбцу или создание справочника?  [new]
MasterZiv
Member

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

Bely пишет:

> А вы думаете, что поиск в справочнике имени текстом, получение ID имени
> и потом поиск этого ID в большой таблице (без индекса) будет быстрым?
> Наврядли... Так что индекс вам полюбому строить, скорее всего.
>

Наоборот, очень даже возможно что будет быстрее.
Не говоря уже о том, что размер таблици с данными сократится --
вместро трёх полей символьных будет одно поле int.

Или вместо трёх полей символьных будет три поля int (постоянной длины).

> Но опять же - смотря какие запросы будут гоняться.
Это - всегда понятно. Запросы разные по-разному и работают.

Posted via ActualForum NNTP Server 1.4

2 фев 09, 18:26    [6768933]     Ответить | Цитировать Сообщить модератору
 Re: Что лучше: индекс по столбцу или создание справочника?  [new]
MasterZiv
Member

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

sp пишет:

> Ну индекс по числовому столбцу компактнее чем по столбцу фамилий в
> основной таблице?

Компактность иднексов никого не волнует. Считайте, что они одинаково
компактны.

> Дело в том что я проиндексировал сейчас столбец фамилий и наблюдаю
> следующую какртину - в соединении при первом запросе по фамилии база
> долго тарахтит около минуты и затем выдает результат, последующие
> запросы проходят быстро - насколько я понимаю сервер подготовился типерь
> к запросу такого типа

Кэш данных заполнился вашей здоровой таблицей. Или этим индексом.

> Стоит поработать с другими таблицами и опять сделать запрос к таблице по
> фамилии опять при первом запросе тормоза - вот это меня сильно смущает

Стоит смотреть на запросы и их планы. и думать. За вас это вряд ли кто-то
сделает.

Posted via ActualForum NNTP Server 1.4

2 фев 09, 18:28    [6768946]     Ответить | Цитировать Сообщить модератору
 Re: Что лучше: индекс по столбцу или создание справочника?  [new]
MasterZiv
Member

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

sp пишет:

> Интересная идея!
Да не такая уж и интересная.

Posted via ActualForum NNTP Server 1.4

2 фев 09, 18:29    [6768950]     Ответить | Цитировать Сообщить модератору
 Re: Что лучше: индекс по столбцу или создание справочника?  [new]
MasterZiv
Member

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

sp пишет:

> Большое спасибо за то что поделились таким нетривиальным трюком - (о
> таком вообще нигде не видел и намека) такое бы в фак :)

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

Какая СУБД у вас ?

Posted via ActualForum NNTP Server 1.4

2 фев 09, 18:31    [6768958]     Ответить | Цитировать Сообщить модератору
 Re: Что лучше: индекс по столбцу или создание справочника?  [new]
sp
Member

Откуда:
Сообщений: 3947
MasterZiv

sp пишет:

> Большое спасибо за то что поделились таким нетривиальным трюком - (о
> таком вообще нигде не видел и намека) такое бы в фак :)

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

Какая СУБД у вас ?


ms sql
но скажу вам что индексы не так уж и компактны - после индексации база выросла на 6 гиг
2 фев 09, 18:35    [6768972]     Ответить | Цитировать Сообщить модератору
 Re: Что лучше: индекс по столбцу или создание справочника?  [new]
sp
Member

Откуда:
Сообщений: 3947
MasterZiv

sp пишет:

> Ну индекс по числовому столбцу компактнее чем по столбцу фамилий в
> основной таблице?

Компактность иднексов никого не волнует. Считайте, что они одинаково
компактны.

> Дело в том что я проиндексировал сейчас столбец фамилий и наблюдаю
> следующую какртину - в соединении при первом запросе по фамилии база
> долго тарахтит около минуты и затем выдает результат, последующие
> запросы проходят быстро - насколько я понимаю сервер подготовился типерь
> к запросу такого типа

Кэш данных заполнился вашей здоровой таблицей. Или этим индексом.

> Стоит поработать с другими таблицами и опять сделать запрос к таблице по
> фамилии опять при первом запросе тормоза - вот это меня сильно смущает

Стоит смотреть на запросы и их планы. и думать. За вас это вряд ли кто-то
сделает.


А что тут думать и какие планы строить ???

select *
from Table1
where F='Иванов' and I='Иван' and 'Иванович'

проиндексировано по F
2 фев 09, 18:53    [6769019]     Ответить | Цитировать Сообщить модератору
 Re: Что лучше: индекс по столбцу или создание справочника?  [new]
Bely
Member

Откуда: Москва
Сообщений: 1903
MasterZiv
Наоборот, очень даже возможно что будет быстрее.
Не говоря уже о том, что размер таблици с данными сократится --
вместро трёх полей символьных будет одно поле int.

Или вместо трёх полей символьных будет три поля int (постоянной длины).
Не забыли посчитать размер справочника фамилий + индекс по числовому полю фамилия_id + индекс по справочнику по текстовому полю? Фулсканы по таблице в 50 млн - даже по числовым полям нехорошо будут выглядеть.
так что выигрыш размера не так очевиден как кажется.
2 фев 09, 19:30    [6769101]     Ответить | Цитировать Сообщить модератору
 Re: Что лучше: индекс по столбцу или создание справочника?  [new]
MasterZiv
Member

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

sp пишет:

> ms sql
> но скажу вам что индексы не так уж и компактны - после индексации база
> выросла на 6 гиг

Так у вас и таблица немаленькая.

Posted via ActualForum NNTP Server 1.4

2 фев 09, 19:56    [6769152]     Ответить | Цитировать Сообщить модератору
 Re: Что лучше: индекс по столбцу или создание справочника?  [new]
MasterZiv
Member

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

Bely пишет:

> Не забыли посчитать размер справочника фамилий + индекс по числовому

Тут нужно знать частоту повторения фамилий или ФИО, чтобы говорить
о выигрыше. Думаю, уж что-то он выиграет.

> полю фамилия_id + индекс по справочнику по текстовому полю? Фулсканы по
> таблице в 50 млн - даже по числовым полям нехорошо будут выглядеть.
> так что выигрыш размера не так очевиден как кажется.

При чём тут сканы ? Да проигрыш тоже не очевиден. Согласны ?
Смотреть надо. Данные знать.

Posted via ActualForum NNTP Server 1.4

2 фев 09, 19:59    [6769158]     Ответить | Цитировать Сообщить модератору
 Re: Что лучше: индекс по столбцу или создание справочника?  [new]
Bely
Member

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

Bely пишет:

> Не забыли посчитать размер справочника фамилий + индекс по числовому

Тут нужно знать частоту повторения фамилий или ФИО, чтобы говорить
о выигрыше. Думаю, уж что-то он выиграет.
У нас таблицы по проверке ФИО примерно такого размера:
Имена: 28 тыс.
Отчетсва: 22,4 тыс.
Фамилии: 248 тыс.
причем фамилии не находятся в 5-10% проверяемых данных.

вот такая статистика.
Поэтому у меня есть сомнения, что на справочнике фамилий можно выиграть в объеме.
2 фев 09, 20:37    [6769220]     Ответить | Цитировать Сообщить модератору
 Re: Что лучше: индекс по столбцу или создание справочника?  [new]
MasterZiv
Member

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

Bely пишет:

> У нас таблицы по проверке ФИО примерно такого размера:
> Имена: 28 тыс.
> Отчетсва: 22,4 тыс.
> Фамилии: 248 тыс.
> причем фамилии не находятся в 5-10% проверяемых данных.

> Поэтому у меня есть сомнения, что на справочнике фамилий можно выиграть
> в объеме.

Как же сомнения-то ?

Если в 5-10% случаях только фамилии НЕ находятся, т.е. 5-10% фамилий
уникальны, а остальные повторяются, да если
248e3 / 50e6 = 0.496 % большой таблицы на каждую фамилию из справочника,
да ещё и на имя и отчество перемножить (ещё меньше будет), так это
ж просто здорово будет.

Вот написал и только теперь дошло, что вы - не автор изначального
поста.

Posted via ActualForum NNTP Server 1.4

2 фев 09, 22:39    [6769548]     Ответить | Цитировать Сообщить модератору
 Re: Что лучше: индекс по столбцу или создание справочника?  [new]
Bely
Member

Откуда: Москва
Сообщений: 1903
MasterZiv
Если в 5-10% случаях только фамилии НЕ находятся, т.е. 5-10% фамилий
уникальны, а остальные повторяются, да если
248e3 / 50e6 = 0.496 % большой таблицы на каждую фамилию из справочника,
да ещё и на имя и отчество перемножить (ещё меньше будет), так это
ж просто здорово будет.
еще раз повторю...

По именам и отчествам справочник уменьшит размер БД и, возможно, улучшит скорость запросов.
По фамилиям - наврядли. Просто за счет того, что кроме фамилии и индекса по ней надо будет хранить - еще два числа (идентификатор в )

Автору - если запросы по таблице вида:
last_name like 'Абдул%'
то можно задуматься о партиционировании таблицы по первым (первой) букве фамилии.
3 фев 09, 10:50    [6770855]     Ответить | Цитировать Сообщить модератору
 Re: Что лучше: индекс по столбцу или создание справочника?  [new]
sp
Member

Откуда:
Сообщений: 3947
Bely
MasterZiv
Наоборот, очень даже возможно что будет быстрее.
Не говоря уже о том, что размер таблици с данными сократится --
вместро трёх полей символьных будет одно поле int.

Или вместо трёх полей символьных будет три поля int (постоянной длины).
Не забыли посчитать размер справочника фамилий + индекс по числовому полю фамилия_id + индекс по справочнику по текстовому полю? Фулсканы по таблице в 50 млн - даже по числовым полям нехорошо будут выглядеть.
так что выигрыш размера не так очевиден как кажется.


Так а что кричать и куда бежать???? :)
получаецца что выделение фамилий имен и отчеств в отдельные справочники даже может усугубить ситуацию?
3 фев 09, 11:44    [6771371]     Ответить | Цитировать Сообщить модератору
 Re: Что лучше: индекс по столбцу или создание справочника?  [new]
sp
Member

Откуда:
Сообщений: 3947
Bely
MasterZiv

Bely пишет:

> Не забыли посчитать размер справочника фамилий + индекс по числовому

Тут нужно знать частоту повторения фамилий или ФИО, чтобы говорить
о выигрыше. Думаю, уж что-то он выиграет.
У нас таблицы по проверке ФИО примерно такого размера:
Имена: 28 тыс.
Отчетсва: 22,4 тыс.
Фамилии: 248 тыс.
причем фамилии не находятся в 5-10% проверяемых данных.

вот такая статистика.
Поэтому у меня есть сомнения, что на справочнике фамилий можно выиграть в объеме.


а если на справочник фамилий сделать как предлагалось вычисляемое поле с left(3), к примеру, и по нему создать индекс??? не усеорит ли это выборку по фамилии??
3 фев 09, 11:46    [6771387]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3 4 5   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить