Добро пожаловать в форум, Guest >> Войти | Регистрация | Поиск | Правила | | В избранное | Подписаться | ||
Все форумы / Microsoft SQL Server |
![]() ![]() |
UncleFedor32 Member Откуда: Сообщений: 30 |
Здравствуйте. Подскажите, пожалуйста, где рыть в следующей ситуации. Имеется база с двумя таблицами. В обе таблицы каждые 5 минут выполняется INSERT нескольких сотен/тысяч позиций. На данный момент в таблицах несколько миллионов позиций. Созданы индексы по ключевым полям. SELECT из обеих таблиц с JOIN по ключевым полям выполняется около 20 минут. При выполнении SELECT возникают блокировки, вероятнее всего из-за INSERT. Подскажите, пожалуйста, как можно добиться быстрого выполненич SELECT. Возможно вопрос детский, т.к. не сталкивался с подобным ситуациям. Заранее благодарен. |
5 дек 18, 20:48 [21754968] Ответить | Цитировать Сообщить модератору |
PizzaPizza Member Откуда: Сообщений: 422 |
UncleFedor32, Структуру таблиц и запрос покажите. |
5 дек 18, 20:54 [21754980] Ответить | Цитировать Сообщить модератору |
UncleFedor32 Member Откуда: Сообщений: 30 |
PizzaPizza,CREATE TABLE board.dbo.board ( id_board BIGINT IDENTITY ,id VARCHAR(64) NULL ,url VARCHAR(255) NULL ,title VARCHAR(512) NULL ,price MONEY NULL ,time DATETIME NULL ,phone VARCHAR(64) NULL ,person VARCHAR(128) NULL ,person_type VARCHAR(64) NULL ,city VARCHAR(64) NULL ,metro VARCHAR(64) NULL ,address VARCHAR(255) NULL ,description TEXT NULL ,nedvigimost_type VARCHAR(64) NULL ,source VARCHAR(128) NULL ,shape GEOMETRY NULL ,parameters VARCHAR(MAX) NULL ,vid VARCHAR(255) NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO CREATE CLUSTERED INDEX IDX_board ON board.dbo.board (id_board) ON [PRIMARY] GO CREATE TABLE board.dbo.parameter ( id_parameter BIGINT IDENTITY ,id VARCHAR(64) NULL ,name VARCHAR(255) NULL ,value VARCHAR(255) NULL ) ON [PRIMARY] GO CREATE INDEX IDX_parameter ON board.dbo.parameter (id) ON [PRIMARY] GO SELECT board.id_board,phone ,'№ '+board.id +'<br>' ,url ,person_type ,nedvigimost_type,person ,phone ,nedvijimost_type ,parameter_per.value ,city +'<br>'+ metro +'<br>'+ address ,price,CONVERT(varchar,time,104) ,person+'<br>'+phone ,' '+title+' <br> ' + CONVERT(varchar(512), description) ,source FROM board.dbo.board board LEFT JOIN board.dbo.parameter parameter_per ON (parameter_per.id=board.id and parameter_per.NAME='Вид' and parameter_per.value='В') WHERE board.id<>0 AND board.id_board<>0 GROUP BY board.id_board,phone ,'№ '+board.id +'<br>' ,url ,person_type ,nedvigimost_type ,person ,phone ,nedvigimost_type ,parameter_per.value ,city +'<br>'+ metro +'<br>'+ address ,price ,CONVERT(varchar,time,104) ,person+'<br>'+phone ,' '+title+' <br> ' + CONVERT(varchar(512), description) ,source |
5 дек 18, 21:06 [21754998] Ответить | Цитировать Сообщить модератору |
Yasha123 Member Откуда: Сообщений: 1968 |
впервые вижу, чтобы таблицу проектировал человек с подобным раздвоением личности. вроде знает, что есть varchar(max), и его использует. а вроде как и не знает, и использует еще и text. ---- но вот человека, который еще и группирует по полю типа текст(даже отконвертировав его), к серверу не надо подпускать вообще |
5 дек 18, 21:35 [21755029] Ответить | Цитировать Сообщить модератору |
Владислав Колосов Member Откуда: Сообщений: 8484 |
Yasha123, да, выражение группировки - просто жесткач даже не для миллионов. |
5 дек 18, 21:40 [21755033] Ответить | Цитировать Сообщить модератору |
Yasha123 Member Откуда: Сообщений: 1968 |
на самом деле, это у него такой DISTINCT |
||
5 дек 18, 21:44 [21755036] Ответить | Цитировать Сообщить модератору |
PizzaPizza Member Откуда: Сообщений: 422 |
UncleFedor32, ИМХО тут проблема не в соединении. Даже для нескольких миллионов 20 минут было бы слишком. Выполните свой запрос без... группировки и посмотрите, сколько времени займет. |
5 дек 18, 21:57 [21755048] Ответить | Цитировать Сообщить модератору |
Владислав Колосов Member Откуда: Сообщений: 8484 |
UncleFedor32, на мой взгляд, так может как-то помочь покрывающий индекс parameter_per.id include (parameter_per.NAME, parameter_per.value) но из-за интенсивной вставки обслуживание индекса может вызвать еще большие проблемы. Чтобы развязать запись и чтение можете попробовать перевести таблицу в in memory. Однако, это будет иметь свои последствия. У Вас сильно нагружена tempdb, попробуйте добавить количество файлов по рекомендациям. |
5 дек 18, 21:58 [21755049] Ответить | Цитировать Сообщить модератору |
invm Member Откуда: Москва Сообщений: 9683 |
Избавиться от влияния конкурентных insert'ов можно: - хинтом nolock на таблицы, если допустимы грязные данные. - включением у БД RCSI, если грязные данные недопустимы. |
||
5 дек 18, 22:05 [21755058] Ответить | Цитировать Сообщить модератору |
PizzaPizza Member Откуда: Сообщений: 422 |
UncleFedor32, ЗЫ. А вы действительно поднимаете все "несколько миллионов позиций" с CONVERT(varchar(512), description) ? Это у вас просто обрезается description или у вас не может там быть больше varchar(512) ? Просто ради интереса, а зачем вам (периодически) выбирать все описания для миллионов позиций ? |
5 дек 18, 22:12 [21755065] Ответить | Цитировать Сообщить модератору |
Владислав Колосов Member Откуда: Сообщений: 8484 |
PizzaPizza, попытка сделать витрину для сайта? |
5 дек 18, 22:15 [21755070] Ответить | Цитировать Сообщить модератору |
PizzaPizza Member Откуда: Сообщений: 422 |
Для витрины все миллионы не нужны сразу. Возможно этот запрос есть выгрузка в другую бд которая уже и обслуживает сайт, но судя по примененной архитектуре varchar, этот вариант сомнителен. Можно подумать, что это какой то отчет. Но опять же - зачем в отчете текстовые описания позиций. Загадка прям. |
||
5 дек 18, 22:30 [21755085] Ответить | Цитировать Сообщить модератору |
Ennor Tiegael Member Откуда: Сообщений: 3376 |
PizzaPizza, Например, это может быть самопальный кэш в приложении, который он обновляет таким образом. В порядке бреда, такскать. |
6 дек 18, 01:47 [21755188] Ответить | Цитировать Сообщить модератору |
PizzaPizza Member Откуда: Сообщений: 422 |
Ennor Tiegael, все варианты какие то архитектурно сомнительные. Я б посоветовал UncleFedor32 задать вопрос в разделе Проектирование БД для начала. Я б сказал даже, что это надо делать asap т.к. "каждые 5 минут выполняется INSERT нескольких сотен/тысяч позиций" |
6 дек 18, 02:18 [21755206] Ответить | Цитировать Сообщить модератору |
UncleFedor32 Member Откуда: Сообщений: 30 |
Yasha123, Попробую отказаться от типа text. Спасибо. Yasha123,PizzaPizza Разумеется сначала использовался distinct. Но с ним запрос выполнялся ещё дольше. Group by применился как искусственный вариант при поиске решения. |
6 дек 18, 05:55 [21755245] Ответить | Цитировать Сообщить модератору |
PizzaPizza Member Откуда: Сообщений: 422 |
Distinct применяется для фильтрации уникальных значений. Задайте себе вопрос: откуда у вас дубли по многим полям в таблице и зачем вы их там храните и заставляете сервер проверять каждое! varchar! поле на уникальность и потом ещё по сочетанию полей. Ваша архитектура при миллионах записей и с такой динамикой наполнения скоро приведет к тому, что элементарные запросы по часу будут выполняться. Хранение в поле metro VARCHAR(64) значений типа "Алексеевская" означает перерасход памяти, как ЖД так и оперативной и при выборках повышает нагрузку на процессор. В вашей таблице такие поля... все. Начните с азов, почитайте про нормальные формы. |
||
6 дек 18, 06:28 [21755254] Ответить | Цитировать Сообщить модератору |
UncleFedor32 Member Откуда: Сообщений: 30 |
PizzaPizza, В таблицах есть другие поля, которых нет в селект и которые не повторяются |
6 дек 18, 15:01 [21755889] Ответить | Цитировать Сообщить модератору |
Владислав Колосов Member Откуда: Сообщений: 8484 |
UncleFedor32, значит у Вас данные не по канону и они требуют нормализации. |
6 дек 18, 15:10 [21755907] Ответить | Цитировать Сообщить модератору |
UncleFedor32 Member Откуда: Сообщений: 30 |
Заменил тип text на varchar, пробовал nolock, rcsi, на всякий случай опять попробовал нормальный distinct вместо ненормального group by. Результат тот же. Ради интереса выполнил select * from board. Запрос выполнялся 6 минут. Результат 2.7 миллионов строк. Такая низкая скорость выполнения запроса без join и where ещё больше настораживает. |
6 дек 18, 19:37 [21756302] Ответить | Цитировать Сообщить модератору |
Yasha123 Member Откуда: Сообщений: 1968 |
UncleFedor32, "заменил" это как, данные перелил в новую таблицу или просто alter table alter column сделал? второе вообще ничего не поменяло для имеющихся данных, как лежали в блоб-страницах, так и лежат. тормознее чтения блобов с диска может быть только их сортировка (хоть gruop by, хоть distinct) |
6 дек 18, 19:44 [21756310] Ответить | Цитировать Сообщить модератору |
PizzaPizza Member Откуда: Сообщений: 422 |
Какой у вас размер таблицы в гигабайтах? Если грубо прикинуть у вас минимум 4,3 кб памяти на строку * 2700000 это, если я проснулся уже, = 11 Гб. Вы хотите, что бы база перечитала вам 11 гигов данных и выдала их меньше, чем за 6 минут? Почитайте уже про нормализацию данных. |
||
6 дек 18, 20:18 [21756351] Ответить | Цитировать Сообщить модератору |
UncleFedor32 Member Откуда: Сообщений: 30 |
PizzaPizza, Изначально не нормализовали структуру, т.к. данные абсолютно неоднородны и неклассифицированы. Характер вводимых данных хаотичен, названия полей очень слабо отражают суть данных, которые в них содержатся. Попробую нормализовать, но ожидаю негативный результат. Как сделаю напишу. Спасибо большое. |
6 дек 18, 21:31 [21756430] Ответить | Цитировать Сообщить модератору |
PizzaPizza Member Откуда: Сообщений: 422 |
Аксиома "данные абсолютно неоднородны и неклассифицированы" означает, что РБД (в частности ms sql) не подходит для ваших задач вообще. Реляционная модель данных - это всё только про однородность и классификацию, читай нормализацию. Использовать SQL как строковую помойку для миллионов записей - просто глупо, т.к. оно не заточено для работу с такими данными. |
||
6 дек 18, 21:55 [21756449] Ответить | Цитировать Сообщить модератору |
Glebanski Member Откуда: Msk ->NL Сообщений: 319 |
UncleFedor32, Выгрузите для начала результат этого ужасного селекта в отдельную таблицу, с которой вы можете начать профилирование данных. Серверу полегчает без экспериментальных запросов. Непонятно, кстати, кто у вас данные в эту кучу валит. Чья-то чужая аппликуха или ее тоже можно пофиксить? |
7 дек 18, 01:10 [21756520] Ответить | Цитировать Сообщить модератору |
Владислав Колосов Member Откуда: Сообщений: 8484 |
UncleFedor32, для хранения несистематизированных данных используются нереляционные БД, например, текстовые. Там все намного быстрее работает на больших объемах. Обычно в таких базах хранят журналы подключений, web запросов пользователей и другие подобные данные. |
7 дек 18, 12:37 [21756902] Ответить | Цитировать Сообщить модератору |
Все форумы / Microsoft SQL Server | ![]() |