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

Откуда:
Сообщений: 24
Здравствуйте.

Подскажите, пожалуйста, где рыть в следующей ситуации. Имеется база с двумя таблицами. В обе таблицы каждые 5 минут выполняется INSERT нескольких сотен/тысяч позиций. На данный момент в таблицах несколько миллионов позиций. Созданы индексы по ключевым полям. SELECT из обеих таблиц с JOIN по ключевым полям выполняется около 20 минут. При выполнении SELECT возникают блокировки, вероятнее всего из-за INSERT. Подскажите, пожалуйста, как можно добиться быстрого выполненич SELECT. Возможно вопрос детский, т.к. не сталкивался с подобным ситуациям. Заранее благодарен.
5 дек 18, 20:48    [21754968]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение SELECT при частых INSERT  [new]
PizzaPizza
Member

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

Структуру таблиц и запрос покажите.
5 дек 18, 20:54    [21754980]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение SELECT при частых INSERT  [new]
UncleFedor32
Member

Откуда:
Сообщений: 24
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]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение SELECT при частых INSERT  [new]
Yasha123
Member

Откуда:
Сообщений: 1114
впервые вижу, чтобы таблицу проектировал человек с подобным раздвоением личности.
вроде знает, что есть varchar(max),
и его использует.
а вроде как и не знает, и использует еще и text.
----
но вот человека, который еще и группирует по полю типа текст(даже отконвертировав его),
к серверу не надо подпускать вообще
5 дек 18, 21:35    [21755029]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение SELECT при частых INSERT  [new]
Владислав Колосов
Member

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

да, выражение группировки - просто жесткач даже не для миллионов.
5 дек 18, 21:40    [21755033]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение SELECT при частых INSERT  [new]
Yasha123
Member

Откуда:
Сообщений: 1114
Владислав Колосов
да, выражение группировки - просто жесткач даже не для миллионов.

на самом деле, это у него такой DISTINCT
5 дек 18, 21:44    [21755036]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение SELECT при частых INSERT  [new]
PizzaPizza
Member

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

ИМХО тут проблема не в соединении. Даже для нескольких миллионов 20 минут было бы слишком.
Выполните свой запрос без... группировки и посмотрите, сколько времени займет.
5 дек 18, 21:57    [21755048]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение SELECT при частых INSERT  [new]
Владислав Колосов
Member

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

на мой взгляд, так может как-то помочь покрывающий индекс

parameter_per.id include (parameter_per.NAME, parameter_per.value)

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

У Вас сильно нагружена tempdb, попробуйте добавить количество файлов по рекомендациям.
5 дек 18, 21:58    [21755049]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение SELECT при частых INSERT  [new]
invm
Member

Откуда: Москва
Сообщений: 8203
UncleFedor32
Подскажите, пожалуйста, как можно добиться быстрого выполненич SELECT
Никак. Такой запрос быстро выполняться не сможет.

Избавиться от влияния конкурентных insert'ов можно:
- хинтом nolock на таблицы, если допустимы грязные данные.
- включением у БД RCSI, если грязные данные недопустимы.
5 дек 18, 22:05    [21755058]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение SELECT при частых INSERT  [new]
PizzaPizza
Member

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

ЗЫ. А вы действительно поднимаете все "несколько миллионов позиций" с CONVERT(varchar(512), description) ? Это у вас просто обрезается description или у вас не может там быть больше varchar(512) ?
Просто ради интереса, а зачем вам (периодически) выбирать все описания для миллионов позиций ?
5 дек 18, 22:12    [21755065]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение SELECT при частых INSERT  [new]
Владислав Колосов
Member

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

попытка сделать витрину для сайта?
5 дек 18, 22:15    [21755070]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение SELECT при частых INSERT  [new]
PizzaPizza
Member

Откуда:
Сообщений: 146
Владислав Колосов
PizzaPizza,

попытка сделать витрину для сайта?


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

Можно подумать, что это какой то отчет. Но опять же - зачем в отчете текстовые описания позиций. Загадка прям.
5 дек 18, 22:30    [21755085]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение SELECT при частых INSERT  [new]
Ennor Tiegael
Member

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

Например, это может быть самопальный кэш в приложении, который он обновляет таким образом. В порядке бреда, такскать.
6 дек 18, 01:47    [21755188]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение SELECT при частых INSERT  [new]
PizzaPizza
Member

Откуда:
Сообщений: 146
Ennor Tiegael,

все варианты какие то архитектурно сомнительные.

Я б посоветовал UncleFedor32 задать вопрос в разделе Проектирование БД для начала.
Я б сказал даже, что это надо делать asap т.к. "каждые 5 минут выполняется INSERT нескольких сотен/тысяч позиций"
6 дек 18, 02:18    [21755206]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение SELECT при частых INSERT  [new]
UncleFedor32
Member

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

Попробую отказаться от типа text. Спасибо.

Yasha123,PizzaPizza

Разумеется сначала использовался distinct. Но с ним запрос выполнялся ещё дольше. Group by применился как искусственный вариант при поиске решения.
6 дек 18, 05:55    [21755245]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение SELECT при частых INSERT  [new]
PizzaPizza
Member

Откуда:
Сообщений: 146
UncleFedor32
Yasha123,

Попробую отказаться от типа text. Спасибо.

Yasha123,PizzaPizza

Разумеется сначала использовался distinct. Но с ним запрос выполнялся ещё дольше. Group by применился как искусственный вариант при поиске решения.


Distinct применяется для фильтрации уникальных значений. Задайте себе вопрос: откуда у вас дубли по многим полям в таблице и зачем вы их там храните и заставляете сервер проверять каждое! varchar! поле на уникальность и потом ещё по сочетанию полей.

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

Хранение в поле metro VARCHAR(64) значений типа "Алексеевская" означает перерасход памяти, как ЖД так и оперативной и при выборках повышает нагрузку на процессор. В вашей таблице такие поля... все. Начните с азов, почитайте про нормальные формы.
6 дек 18, 06:28    [21755254]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение SELECT при частых INSERT  [new]
UncleFedor32
Member

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

В таблицах есть другие поля, которых нет в селект и которые не повторяются
6 дек 18, 15:01    [21755889]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение SELECT при частых INSERT  [new]
Владислав Колосов
Member

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

значит у Вас данные не по канону и они требуют нормализации.
6 дек 18, 15:10    [21755907]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение SELECT при частых INSERT  [new]
UncleFedor32
Member

Откуда:
Сообщений: 24
Заменил тип text на varchar, пробовал nolock, rcsi, на всякий случай опять попробовал нормальный distinct вместо ненормального group by. Результат тот же.

Ради интереса выполнил select * from board. Запрос выполнялся 6 минут. Результат 2.7 миллионов строк. Такая низкая скорость выполнения запроса без join и where ещё больше настораживает.
6 дек 18, 19:37    [21756302]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение SELECT при частых INSERT  [new]
Yasha123
Member

Откуда:
Сообщений: 1114
UncleFedor32,
"заменил" это как, данные перелил в новую таблицу или просто alter table alter column сделал?
второе вообще ничего не поменяло для имеющихся данных, как лежали в блоб-страницах, так и лежат.

тормознее чтения блобов с диска может быть только их сортировка (хоть gruop by, хоть distinct)
6 дек 18, 19:44    [21756310]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение SELECT при частых INSERT  [new]
PizzaPizza
Member

Откуда:
Сообщений: 146
UncleFedor32
Ради интереса выполнил select * from board. Запрос выполнялся 6 минут. Результат 2.7 миллионов строк. Такая низкая скорость выполнения запроса без join и where ещё больше настораживает.


Какой у вас размер таблицы в гигабайтах? Если грубо прикинуть у вас минимум 4,3 кб памяти на строку * 2700000 это, если я проснулся уже, = 11 Гб. Вы хотите, что бы база перечитала вам 11 гигов данных и выдала их меньше, чем за 6 минут?

Почитайте уже про нормализацию данных.
6 дек 18, 20:18    [21756351]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение SELECT при частых INSERT  [new]
UncleFedor32
Member

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

Изначально не нормализовали структуру, т.к. данные абсолютно неоднородны и неклассифицированы. Характер вводимых данных хаотичен, названия полей очень слабо отражают суть данных, которые в них содержатся. Попробую нормализовать, но ожидаю негативный результат. Как сделаю напишу. Спасибо большое.
6 дек 18, 21:31    [21756430]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение SELECT при частых INSERT  [new]
PizzaPizza
Member

Откуда:
Сообщений: 146
UncleFedor32
PizzaPizza,

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


Аксиома "данные абсолютно неоднородны и неклассифицированы" означает, что РБД (в частности ms sql) не подходит для ваших задач вообще.

Реляционная модель данных - это всё только про однородность и классификацию, читай нормализацию. Использовать SQL как строковую помойку для миллионов записей - просто глупо, т.к. оно не заточено для работу с такими данными.
6 дек 18, 21:55    [21756449]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение SELECT при частых INSERT  [new]
Glebanski
Member

Откуда: Msk ->NL
Сообщений: 265
UncleFedor32,

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

Непонятно, кстати, кто у вас данные в эту кучу валит. Чья-то чужая аппликуха или ее тоже можно пофиксить?
7 дек 18, 01:10    [21756520]     Ответить | Цитировать Сообщить модератору
 Re: Долгое выполнение SELECT при частых INSERT  [new]
Владислав Колосов
Member

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

для хранения несистематизированных данных используются нереляционные БД, например, текстовые. Там все намного быстрее работает на больших объемах. Обычно в таких базах хранят журналы подключений, web запросов пользователей и другие подобные данные.
7 дек 18, 12:37    [21756902]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить