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

Откуда:
Сообщений: 9
Всем здравствуйте.
В таблице 2 млн записей. У каждой записи может быть от 1-го до 8-ми каких-либо свойств. Например, цвет. Самая главная задача - это сделать быструю выборку по определенному цвету. Планирую создать для этого поле с типом байт и установить каждой записи свои цвета, типа 00010011. Потом как то быстро выбрать с примерно таким условием:

Where color[3] = 1. Что означает выбрать все оранжевые, потому что четвертый бит - это оранжевый цвет.

Сейчас все реализовано через соединение таблиц. Вторая таблица содержит данные о цветах элементов. Скорость теряется при соединении таблиц. Хотелось бы решить задачу без соединения таблиц. Главное чтобы ооочень быстро делалась выборка. Как можно реализовать такое условие запроса? И возможно ли вообще это в MS SQL 2012. Спасибо.
7 окт 16, 19:41    [19757281]     Ответить | Цитировать Сообщить модератору
 Re: Отбор по "биту в байте"  [new]
o-o
Guest
2млн записей это вообще не объем,
сколько это у вас в мегабайтах?
7 окт 16, 19:58    [19757339]     Ответить | Цитировать Сообщить модератору
 Re: Отбор по "биту в байте"  [new]
aleks2
Guest
remitradar
Всем здравствуйте.
В таблице 2 млн записей. У каждой записи может быть от 1-го до 8-ми каких-либо свойств. Например, цвет. Самая главная задача - это сделать быструю выборку по определенному цвету. Планирую создать для этого поле с типом байт и установить каждой записи свои цвета, типа 00010011. Потом как то быстро выбрать с примерно таким условием:

Where color[3] = 1. Что означает выбрать все оранжевые, потому что четвертый бит - это оранжевый цвет.

Сейчас все реализовано через соединение таблиц. Вторая таблица содержит данные о цветах элементов. Скорость теряется при соединении таблиц. Хотелось бы решить задачу без соединения таблиц. Главное чтобы ооочень быстро делалась выборка. Как можно реализовать такое условие запроса? И возможно ли вообще это в MS SQL 2012. Спасибо.


2^8 = 256 различных значений. Чем париться с битами - проще во второй таблице нагенерить все 256 вариантов.
Будет оооочень быстро.
7 окт 16, 20:08    [19757378]     Ответить | Цитировать Сообщить модератору
 Re: Отбор по "биту в байте"  [new]
o-o
Guest
Еще никак не соображу, во второй таблице всего 8 строк(у вас максимум 8 цветов?)
И вот эти 8 строк "медленно соединяются" с 2млн строк? Фантастиш...
Хотя конечно, если в каждой строке есть поле типа ntext,
а в нем под 2 Гб реального текста,
и на каждый цвет вы выгребаете 1/8 этих 2млн *2Гб,
то да, это медленно.
Только дело не в соединении таблиц
7 окт 16, 20:13    [19757391]     Ответить | Цитировать Сообщить модератору
 Re: Отбор по "биту в байте"  [new]
remitradar
Member

Откуда:
Сообщений: 9
Нееее... во второй таблице где-то 8 млн строк. В среднем по четыре цвета у каждого элемента. Где-то один, где-то все восемь. И соединяются 2 млн с 8 млн, а только потом делается условие на отбор цвета.
7 окт 16, 20:34    [19757458]     Ответить | Цитировать Сообщить модератору
 Re: Отбор по "биту в байте"  [new]
remitradar
Member

Откуда:
Сообщений: 9
Я понимаю, что можно сначала сделать отбор во второй таблице нужного цвета, а потом соединить с первой, но в первой еще много разных условий отбора есть. В общем, я пока склоняюсь к варианту без использования соединения. Может даже добавить 8 колонок с типом int в основную таблицу, по такому полю оооочень быстро делается отбор. Но все же восемь колонок это немного изврат))) хотелось бы покрасивее решить
7 окт 16, 20:41    [19757476]     Ответить | Цитировать Сообщить модератору
 Re: Отбор по "биту в байте"  [new]
remitradar
Member

Откуда:
Сообщений: 9
o-o
2млн записей это вообще не объем,
сколько это у вас в мегабайтах?


да хз. согласен что это совсем немного. Но когда соединяем 2 млн и 8 млн, то все-таки заметно тормозит. Сейчас выборка при помощи соединения отрабатывает где-то за 1,6 сек. Мне бы до 0,3 довести. Если без отбора по цвету, т.е. без соединения, то я такой результат получаю.
7 окт 16, 20:45    [19757489]     Ответить | Цитировать Сообщить модератору
 Re: Отбор по "биту в байте"  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
remitradar,

чтобы найти запись по цвету, надо будет просканировать миллионы строк что ли? Всю таблицу?
Или я что-то неправильно понял?
7 окт 16, 20:56    [19757519]     Ответить | Цитировать Сообщить модератору
 Re: Отбор по "биту в байте"  [new]
o-o
Guest
никак не пойму, откуда 8млн цветов, RGB что ли?
так RGB в байт не полезет, хоть тресни, это ж 3 байта.
и если в таблице в 2 млн отражены не 8, а 8млн цветов,
то можно и индекс навесить.
это ж что, цвет или есть или его нет, а не уйма строк одного цвета
7 окт 16, 21:06    [19757538]     Ответить | Цитировать Сообщить модератору
 Re: Отбор по "биту в байте"  [new]
remitradar
Member

Откуда:
Сообщений: 9
Господа, похоже я не так объясняюсь) Я изначально не стал морочить голову подробностями, потому что вопрос совершенно не в этом, но раз уж задели эту тему, то пояснюсь.

Есть 2 таблицы. Основная с элементами, в ней 2 млн записей. И таблица с цветами, в ней всего 8 цветов.
Каждый элемент может содержать от 1 до 8ми цветов.
Связь многие-ко-многим: один цвет может быть назначен нескольким элементам. Так же один элемент может содержать несколько цветов.
Для хранения этой информации используются дополнительная таблица. См.скриншот.

На самом деле соединений получается 2 - главная таблица соединяется со вспомогательной. Вспомогательная соединяется с таблицей цветов.

Смотрел профайлером - 96% времени занимают именно эти соединения. Индексы настроены верно. Поэтому я бы хотел использовать только одну таблицу, чтобы не делать трудозатратных соединений. Хочу попробовать в главную таблицу добавить колонку с типом байт. Тогда каждой записи я добавлю такие значения: 00010011. Это значит, что у этого элемента есть 3 определенных цвета. Нужно сделать запрос в выбором, где, например, третий байт = 1. Вот как его сделать?

К сообщению приложен файл. Размер - 22Kb
7 окт 16, 21:40    [19757659]     Ответить | Цитировать Сообщить модератору
 Re: Отбор по "биту в байте"  [new]
o-o
Guest
какой-то бред.
таблица из двух колонок типа int,
8млн строк, это от силы 300Мб.
выбрать оттуда все строки такого-то цвета это 1 скан,
не надо никаких индексов.
просканить 300Мб это 1 секунда на ноуте
+
Table 'nums_10'. Scan count 1, logical reads 16109, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 1014 ms, elapsed time = 1033 ms.

какое же тут торможение,
какая еще оптимизация?
вывод на клиенте миллиона строк, вот все, куда уходит время...
7 окт 16, 22:21    [19757788]     Ответить | Цитировать Сообщить модератору
 Re: Отбор по "биту в байте"  [new]
iljy
Member

Откуда:
Сообщений: 8711
remitradar,

сделайте 8 полей типа бит и не мучьте животное. В принципе, можно на каждое повесить индекс, а лучше даже фильтрованные индексы по ИД насоздавать, но полезность этого мне кажется довольно сомнительной. Хотя все зависит от селективности и используемых выборок, запрос на пересечение нескольких редких цветов может очень быстро сработать.
7 окт 16, 22:22    [19757790]     Ответить | Цитировать Сообщить модератору
 Re: Отбор по "биту в байте"  [new]
remitradar
Member

Откуда:
Сообщений: 9
o-o
таблица из двух колонок типа int,
8млн строк, это от силы 300Мб.
выбрать оттуда все строки такого-то цвета это 1 скан,
не надо никаких индексов.
просканить 300Мб это 1 секунда на ноуте
+
Table 'nums_10'. Scan count 1, logical reads 16109, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 1014 ms, elapsed time = 1033 ms.

какое же тут торможение,
какая еще оптимизация?
вывод на клиенте миллиона строк, вот все, куда уходит время...


Спасибо, конечно, за тесты))) только там совсем не две колонки и условий отбора гораздо больше по основной таблице. Зачем мне вам голову морочить лишней информацией, вопрос совершенно не в этом. Как я уже говорил, что если не делать соединений, то отрабатывает все за 0,3 секунды примерно. Как только я добавляю соединения, то 1,6 сек. Профайлер говорит тоже, что основное время ест соединения таблиц. Клиенту уходит всего около 5К строк.
7 окт 16, 22:36    [19757839]     Ответить | Цитировать Сообщить модератору
 Re: Отбор по "биту в байте"  [new]
remitradar
Member

Откуда:
Сообщений: 9
iljy
remitradar,

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


эх... неужели в sql нет хорошей функции по работе "с битами в байте"...
было бы красиво одним полем обойтись и как-нибудь так:
Where color[3] = 1
Хотя, конечно, сомневаюсь, что это будет быстрее чем 8 колонок с типом бит, но мало ли, попробовать хотелось бы.
Не могу правильно запрос в гугле сформулировать, всяко уже кто-то делал что-то подобное, вот и решил на форуме спросить.
7 окт 16, 22:44    [19757867]     Ответить | Цитировать Сообщить модератору
 Re: Отбор по "биту в байте"  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
remitradar
iljy
remitradar,

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


эх... неужели в sql нет хорошей функции по работе "с битами в байте"...
было бы красиво одним полем обойтись и как-нибудь так:
Where color[3] = 1
Хотя, конечно, сомневаюсь, что это будет быстрее чем 8 колонок с типом бит, но мало ли, попробовать хотелось бы.
Не могу правильно запрос в гугле сформулировать, всяко уже кто-то делал что-то подобное, вот и решил на форуме спросить.
Вы уж там решите сначала, феншуй вам надо или нормально и быстро чтобы было.
7 окт 16, 22:57    [19757934]     Ответить | Цитировать Сообщить модератору
 Re: Отбор по "биту в байте"  [new]
remitradar
Member

Откуда:
Сообщений: 9
Гавриленко Сергей Алексеевич
Вы уж там решите сначала, феншуй вам надо или нормально и быстро чтобы было.


Так хотелось бы все варианты попробовать и уж потом выбрать. А пока выбирать кроме как из 8-ми полей не из чего. Вот и спрашиваю.
7 окт 16, 23:08    [19757986]     Ответить | Цитировать Сообщить модератору
 Re: Отбор по "биту в байте"  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
remitradar,

В скл Есть битовый операции, но таки вы подучите скан всей таблицы.
8 окт 16, 00:05    [19758185]     Ответить | Цитировать Сообщить модератору
 Re: Отбор по "биту в байте"  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
o-o
какой-то бред.
таблица из двух колонок типа int,
8млн строк, это от силы 300Мб.
выбрать оттуда все строки такого-то цвета это 1 скан,
не надо никаких индексов.
просканить 300Мб это 1 секунда на ноуте
+
Table 'nums_10'. Scan count 1, logical reads 16109, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 1014 ms, elapsed time = 1033 ms.

какое же тут торможение,
какая еще оптимизация?
вывод на клиенте миллиона строк, вот все, куда уходит время...


Вы про скорость оперативки ноута пишите?
8 окт 16, 00:06    [19758192]     Ответить | Цитировать Сообщить модератору
 Re: Отбор по "биту в байте"  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
remitradar,

И кстати, вы в курсе что 8 полей типа бит занимают 1 байт? На чем вы хотите сэкономить? Я уж молчу про нарушение уже первой нормальной формы, кладя разномастные данный в один столбец
8 окт 16, 00:11    [19758203]     Ответить | Цитировать Сообщить модератору
 Re: Отбор по "биту в байте"  [new]
remitradar
Member

Откуда:
Сообщений: 9
Mike_za
И кстати, вы в курсе что 8 полей типа бит занимают 1 байт?

Да

Mike_za
На чем вы хотите сэкономить?

На том что избавлюсь от ресурсоемких соединений и сделаю все в одной таблице

Mike_za
Я уж молчу про нарушение уже первой нормальной формы, кладя разномастные данный в один столбец

Почему же разномастные? Каждый бит это будет один цвет. Мне не сложно будет где-то запомнить, что первый бит - это зеленый, второй - красный и т.д. И потом чтобы выбрать все красные писать Where color[1] = 1.
8 окт 16, 00:25    [19758239]     Ответить | Цитировать Сообщить модератору
 Re: Отбор по "биту в байте"  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
remitradar
На том что избавлюсь от ресурсоемких соединений и сделаю все в одной таблице
Пихайте все в один блоб на 2 Гб: получится всего одна таблица с одним полем поле с одной записью! Скорость должна быть просто космической же.

Сообщение было отредактировано: 8 окт 16, 00:32
8 окт 16, 00:28    [19758248]     Ответить | Цитировать Сообщить модератору
 Re: Отбор по "биту в байте"  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
remitradar
И потом чтобы выбрать все красные писать Where color[1] = 1.
Возьмите словарь побольше и посчитайте все слова, которые начинаются на букву "й", т.е. буква[1] = "й". А потом посчитайте все слова, у которых третья буква в слове -- "й", т.е. буква[3] = "й". И расскажите, сколько времени вы затратили на первый поиск, и сколько на второй. Может после у вас поубавится желания отбирать по биту в байте.

Сообщение было отредактировано: 8 окт 16, 00:37
8 окт 16, 00:31    [19758258]     Ответить | Цитировать Сообщить модератору
 Re: Отбор по "биту в байте"  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
remitradar
было бы красиво одним полем обойтись и как-нибудь так:
Where color[3] = 1
Вам шашечки или ехать?
declare @colors_mask int = 1 | 2 | 8;
...
where
 ... colors & @colors_mask = @colors_mask ...
8 окт 16, 01:31    [19758334]     Ответить | Цитировать Сообщить модератору
 Re: Отбор по "биту в байте"  [new]
Andrey Sribnyak
Member

Откуда: Киев
Сообщений: 599
invm,

8 окт 16, 17:07    [19759007]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить