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

Откуда:
Сообщений: 157
Доброго дня.

Есть таблица заданий (100.000) и к заданиям комментарии (300.000). В среднем на задание до 10 комментариев.

Запрос с фильтрацией по статусу отрабатывает за 0.5 секунды и выбирает 2.500 записей:
DECLARE @ent_issue_status_id INT = 77;

SELECT ei.ent_issue_id
  FROM dbo.ent_issue ei
  WHERE ei.ent_issue_status_id = @ent_issue_status_id


Запрос с фильтрацией по статусу с поиском по комментариям отрабатывает за 5 секунды и выбирает 230 записей:
DECLARE @ent_issue_status_id INT = 77;

SELECT ei.ent_issue_id
  FROM dbo.ent_issue ei
  WHERE ei.ent_issue_status_id = @ent_issue_status_id
    AND (EXISTS (SELECT 1
      FROM dbo.ent_issue_comment eic
      WHERE eic.ent_issue_id = ei.ent_issue_id
        AND eic.record_status = 'A'
        AND eic.comment LIKE '%firma%')
    )


План по последнему запросу:
StmtText	StmtId	NodeId	Parent	PhysicalOp	LogicalOp	Argument	DefinedValues	EstimateRows	EstimateIO	EstimateCPU	AvgRowSize	TotalSubtreeCost	OutputList	Warnings	Type	Parallel	EstimateExecutions
SELECT ei.ent_issue_id FROM dbo.ent_issue ei WHERE ei.ent_issue_status_id = 77 AND (EXISTS (SELECT 1 FROM dbo.ent_issue_comment eic WHERE eic.ent_issue_id = ei.ent_issue_id AND eic.record_status = 'A' AND eic.comment LIKE '%firma%') ) 1 1 0 NULL NULL 1 NULL 287.1874 NULL NULL NULL 8.816943 NULL NULL SELECT 0 NULL
|--Hash Match(Left Semi Join, HASH:([ei].[ent_issue_id])=([eic].[ent_issue_id])) 1 2 1 Hash Match Left Semi Join HASH:([ei].[ent_issue_id])=([eic].[ent_issue_id]) NULL 287.1874 0 0.09768445 11 8.816943 [ei].[ent_issue_id] NULL PLAN_ROW 0 1
|--Index Seek(OBJECT:([GASTRODEV].[dbo].[ent_issue].[idx_ent_issue_ent_issue_status_id] AS [ei]), SEEK:([ei].[ent_issue_status_id]=(77)) ORDERED FORWARD) 1 3 2 Index Seek Index Seek OBJECT:([GASTRODEV].[dbo].[ent_issue].[idx_ent_issue_ent_issue_status_id] AS [ei]), SEEK:([ei].[ent_issue_status_id]=(77)) ORDERED FORWARD [ei].[ent_issue_id] 2356 0.005487481 0.0027486 11 0.008236081 [ei].[ent_issue_id] NULL PLAN_ROW 0 1
|--Clustered Index Scan(OBJECT:([GASTRODEV].[dbo].[ent_issue_comment].[pk_ent_issue_comment] AS [eic]), WHERE:([GASTRODEV].[dbo].[ent_issue_comment].[record_status] as [eic].[record_status]=N'A' AND [GASTRODEV].[dbo].[ent_issue_comment].[comment] as [eic].[comment] like N'%firma%')) 1 4 2 Clustered Index Scan Clustered Index Scan OBJECT:([GASTRODEV].[dbo].[ent_issue_comment].[pk_ent_issue_comment] AS [eic]), WHERE:([GASTRODEV].[dbo].[ent_issue_comment].[record_status] as [eic].[record_status]=N'A' AND [GASTRODEV].[dbo].[ent_issue_comment].[comment] as [eic].[comment] like N'%firma%') [eic].[ent_issue_id] 12402.94 7.932755 0.3597866 4019 8.292542 [eic].[ent_issue_id] NULL PLAN_ROW 0 1

Понимаю что like еще тот подарок, но может кто сталкивался с подобной проблемой.
14 апр 16, 14:15    [19056586]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией запроса  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
DDL таблиц и индексов вы не показали.
Поэтому пальцем в небо (может поможет, может нет) - добавьте к dbo.ent_issue_comment индекс (ent_issue_id, record_status) include (comment)
14 апр 16, 14:25    [19056682]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией запроса  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
В теории такие индексы должны давать профит:

CREATE UNIQUE NONCLUSTERED INDEX ix ON dbo.ent_issue (ent_issue_status_id, ent_issue_id)
GO
CREATE UNIQUE NONCLUSTERED INDEX ix ON dbo.ent_issue_comment (ent_issue_id/*, record_status*/)
    INCLUDE(comment) WHERE record_status = 'A'
GO
14 апр 16, 14:36    [19056745]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией запроса  [new]
mezzanine
Member

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

CREATE TABLE GASTRODEV.dbo.ent_issue (
  ent_issue_id INT IDENTITY, 
  ent_issue_status_id INT NOT NULL,
  ...
  CONSTRAINT pk_ent_issue PRIMARY KEY CLUSTERED (ent_issue_id),
  CONSTRAINT fk_ent_issue_ent_issue_status FOREIGN KEY (ent_issue_status_id) REFERENCES dbo.ent_issue_status (ent_issue_status_id)
) ON [PRIMARY]
GO

CREATE INDEX idx_ent_issue_ent_issue_status_id
ON GASTRODEV.dbo.ent_issue (ent_issue_status_id)
ON [PRIMARY]
GO


CREATE TABLE GASTRODEV.dbo.ent_issue_comment (
  ent_issue_comment_id INT IDENTITY,
  ent_issue_id INT NOT NULL,
  comment NVARCHAR(4000) NOT NULL,
  comment_user_id INT NOT NULL,
  created_date DATETIME NOT NULL DEFAULT (SYSDATETIME()),
  modified_date DATETIME NOT NULL DEFAULT (SYSDATETIME()),
  modified_user NVARCHAR(100) NOT NULL,
  record_status NVARCHAR(1) NOT NULL DEFAULT ('A'),
  CONSTRAINT pk_ent_issue_comment PRIMARY KEY CLUSTERED (ent_issue_comment_id),
  CONSTRAINT fk_ent_issue_comment_comment_user FOREIGN KEY (comment_user_id) REFERENCES dbo.usr_user (usr_user_id),
  CONSTRAINT fk_ent_issue_comment_ent_issue FOREIGN KEY (ent_issue_id) REFERENCES dbo.ent_issue (ent_issue_id)
) ON [PRIMARY]
GO

CREATE INDEX idx_ent_issue_comment_ent_issue_id
ON GASTRODEV.dbo.ent_issue_comment (ent_issue_id)
ON [PRIMARY]
GO


Поле record_status в 99% будет в значение "А". Наверно нет смысла добавлять к индексу.
14 апр 16, 14:39    [19056763]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией запроса  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
План выполнения в sqlplan можно? В теории у Вас все просто - лечится правильными индексами.
14 апр 16, 14:41    [19056777]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией запроса  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
mezzanine
Поле record_status в 99% будет в значение "А". Наверно нет смысла добавлять к индексу.
Смысл есть. Только в include добавьте.
14 апр 16, 14:42    [19056783]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией запроса  [new]
mezzanine
Member

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

Изменил индекс, теперь выглядит так:
CREATE INDEX idx_ent_issue_comment_ent_issue_id
ON GASTRODEV.dbo.ent_issue_comment (ent_issue_id)
INCLUDE (comment, record_status)
ON [PRIMARY]
GO


Ситуация не поменялась.
StmtText
|--Merge Join(Left Semi Join, MERGE:([ei].[ent_issue_id])=([eic].[ent_issue_id]), RESIDUAL:([GASTRODEV].[dbo].[ent_issue_comment].[ent_issue_id] as [eic].[ent_issue_id]=[GASTRODEV].[dbo].[ent_issue].[ent_issue_id] as [ei].[ent_issue_id]))
|--Index Seek(OBJECT:([GASTRODEV].[dbo].[ent_issue].[idx_ent_issue_ent_issue_status_id] AS [ei]), SEEK:([ei].[ent_issue_status_id]=[@ent_issue_status_id]) ORDERED FORWARD)
|--Index Scan(OBJECT:([GASTRODEV].[dbo].[ent_issue_comment].[idx_ent_issue_comment_ent_issue_id] AS [eic]), WHERE:([GASTRODEV].[dbo].[ent_issue_comment].[record_status] as [eic].[record_status]=N'A' AND [GASTRODEV].[dbo].[ent_issue_comment].[comment] as [eic].[comment] like N'%firma%') ORDERED FORWARD)
14 апр 16, 14:55    [19056859]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией запроса  [new]
iljy
Member

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

много записей выбирается, поэтому сервер решает, что скан будет быстрее. поменяйте EXISTS на CROSS APPLY ( SELECT TOP 1 ...), чтобы точно был цикл, и посмотрите, будет ли эффект.
14 апр 16, 14:59    [19056881]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией запроса  [new]
invm
Member

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

Добавьте к запросу option(recompile).

ЗЫ: И планы выкладывайте актуальные в формате sqlplan.
14 апр 16, 15:02    [19056900]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией запроса  [new]
Владислав Колосов
Member

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

с чего бы ситуация поменялась. если Вы заставляете сервер перебирать все записи LIKE '%firma%'.
14 апр 16, 15:51    [19057186]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией запроса  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
К слову о фильтрации по %....% может имеет смысл бинарный коллейшен на столбце использовать. Быстрее будет на порядок
14 апр 16, 15:56    [19057218]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией запроса  [new]
Crimean
Member

Откуда:
Сообщений: 13147
AlanDenton
К слову о фильтрации по %....% может имеет смысл бинарный коллейшен на столбце использовать. Быстрее будет на порядок


а можно подробностей про это?
14 апр 16, 16:05    [19057294]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией запроса  [new]
iljy
Member

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

а какие тут больно подробности? бинарное сравнение проще, поэтому и работает быстрее. Хотя насчет порядка, это вы, гражданин, соврамши
14 апр 16, 16:09    [19057339]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией запроса  [new]
Glory
Member

Откуда:
Сообщений: 104751
Используйте full-text search для вашего поля комментариев. Вы же ищите все таки по началу слов
14 апр 16, 16:12    [19057368]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией запроса  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Crimean
а можно подробностей про это?


http://aboutsqlserver.com/2015/01/20/optimizing-substring-search-performance-in-sql-server/

iljy
Хотя насчет порядка, это вы, гражданин, соврамши


Позвольте узнать в чем не прав? :) ведь сколько раз пользовался бинарными коллейшенами и проблем с перфомансом не было.
14 апр 16, 16:18    [19057446]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией запроса  [new]
Guf
Member

Откуда: Новосибирск
Сообщений: 659
AlanDenton
К слову о фильтрации по %....% может имеет смысл бинарный коллейшен на столбце использовать. Быстрее будет на порядок

+ Можно подробнее, пожалуйста?

У нас в базе колейшн = Cyrillic_General_BIN
И кругом "регистронезависимые" поиски типа:
SELECT * FROM [MyTable] WHERE UPPER([MyField]) LIKE UPPER('%pattern%')

Знаю, знаю, жесть... Коробочный продукт.
Пробовали
SELECT * FROM [MyTable] WHERE [MyField] COLLATE Cyrillic_General_100_CI_AI LIKE '%pattern%'

и
SELECT * FROM [MyTable] WHERE [MyField] COLLATE Cyrillic_General_100_CI_AI LIKE '%pattern%' COLLATE Cyrillic_General_100_CI_AI

Результат

SQL Server Execution Times:
CPU time = 1669 ms, elapsed time = 1979 ms.

SQL Server Execution Times:
CPU time = 4867 ms, elapsed time = 4953 ms.

SQL Server Execution Times:
CPU time = 4820 ms, elapsed time = 4921 ms.
Что как бы подтверждает ваши слова. Порядок, не порядок, но в 2 раза быстрее, стабильно
Но, блин, почему?
14 апр 16, 16:23    [19057497]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией запроса  [new]
Guf
Member

Откуда: Новосибирск
Сообщений: 659
AlanDenton,

:) Спасибо
14 апр 16, 16:24    [19057504]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией запроса  [new]
iljy
Member

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

Позвольте узнать в чем не прав? :) ведь сколько раз пользовался бинарными коллейшенами и проблем с перфомансом не было.



В оценке неправы :) Разница будет примерно вдвое, может втрое, но никак не в 10 раз.


Guf
Что как бы подтверждает ваши слова. Порядок, не порядок, но в 2 раза быстрее, стабильно
Но, блин, почему?


Потому что бинарный колейшн проще, это тупое сравнение байтов. Остальные делают дополнительную обработку.
14 апр 16, 16:27    [19057529]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией запроса  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
iljy
В оценке неправы :) Разница будет примерно вдвое, может втрое, но никак не в 10 раз.


Возможно :)
14 апр 16, 16:33    [19057568]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией запроса  [new]
mezzanine
Member

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

Попробовал на CROSS APPLY, просто супер, выполняется за 0.3 секунды:
SELECT ei.ent_issue_id
  FROM dbo.ent_issue ei
  CROSS APPLY (SELECT TOP 1 *
    FROM dbo.ent_issue_comment eic
    WHERE eic.ent_issue_id = ei.ent_issue_id
      AND eic.record_status = 'A'
      AND eic.comment LIKE '%firma%') AS o
  WHERE ei.ent_issue_status_id = @ent_issue_status_id


Еще попробую с бинари побаловаться.
Насчет планов. В дальнейшем просто прикреплять файл с расширением sqlplan?
14 апр 16, 16:52    [19057711]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией запроса  [new]
mezzanine
Member

Откуда:
Сообщений: 157
По поводу Full Text Search. Уже пробовал, штука шустро работает, но к сожалению не может искать по шаблону "%...%". Ищет только слово целиком, или по формату "wor*", т.е. аналог LIKE в формате "wor%" который в таком случае работает с индексом.
14 апр 16, 16:56    [19057748]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией запроса  [new]
Crimean
Member

Откуда:
Сообщений: 13147
mezzanine
По поводу Full Text Search. Уже пробовал, штука шустро работает, но к сожалению не может искать по шаблону "%...%". Ищет только слово целиком, или по формату "wor*", т.е. аналог LIKE в формате "wor%" который в таком случае работает с индексом.


я в таких случаях отправляю в гугл поискать. часто помогает - наступает "просветление". ибо результаты поисков по "мама" и "ама" слегка разные, ага? после чего к FTS отношение меняется. а со временем начинают использовать его преимущества.
14 апр 16, 17:15    [19057861]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией запроса  [new]
Glory
Member

Откуда:
Сообщений: 104751
mezzanine
Ищет только слово целиком, или по формату "wor*", т.е. аналог LIKE в формате "wor%" который в таком случае работает с индексом

Вы сильно заблуждаетесь. FTS ищет каждую лексему строки, начинающуюся на wor. А не строку, начинающуюся на wor
14 апр 16, 19:53    [19058497]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с оптимизацией запроса  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
Что делать, когда Full-Text бессилен
15 апр 16, 09:55    [19060109]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить