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

Откуда: не знакомлюсь!
Сообщений: 32397
Здравствуйте

есть запрос, генерирующийся ORMом, следующего вида

select * from Document d 
inner join "file" f on d.id = f.documentId
inner join callSolr('%D0%BF%D1%80%D0%BE%D0%B2%D0%B5%D1%80%D0%BA%D0%B0') sc on 
sc.fileId=f.path  or 
SUBSTRING(sc.fileId, 5, LEN(sc.fileId))=f.path


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

если закомментировать любой из последних условий OR, например,

select * from Document d 
inner join "file" f on d.id = f.documentId
inner join callSolr('%D0%BF%D1%80%D0%BE%D0%B2%D0%B5%D1%80%D0%BA%D0%B0') sc on 
--sc.fileId=f.path  or 
SUBSTRING(sc.fileId, 5, LEN(sc.fileId))=f.path


получается уже правильный план, без Index Scanов - поле file.path проиндексировано и этот индекс используется.

Если переписать запрос следующим образом,

select * from callSolr('%D0%BF%D1%80%D0%BE%D0%B2%D0%B5%D1%80%D0%BA%D0%B0') sc 
inner join "file" f on 
sc.fileId=f.path  or 
SUBSTRING(sc.fileId, 5, LEN(sc.fileId))=f.path
inner join Document d on d.id = f.documentId
option (force order)


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

Я жертва ORMа и не могу менять в запросе порядок джоинов (или пока не придумал как).

Не подскажете ли какие-нибудь еще волшебные слова, чтобы подсказать оптимизатору, что функция выведет малое количество строк и все нужно джоинить к ее результату всегда?

спасибо
13 июн 13, 12:14    [14427464]     Ответить | Цитировать Сообщить модератору
 Re: Помогите подсказать оптимизатору  [new]
аувв
Member

Откуда: не знакомлюсь!
Сообщений: 32397
сервер 10,50,1617
13 июн 13, 12:16    [14427476]     Ответить | Цитировать Сообщить модератору
 Re: Помогите подсказать оптимизатору  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22550
аувв
Я жертва ORMа и не могу менять в запросе порядок джоинов (или пока не придумал как)
большинство современных ORM позволяют обратиться за данными к хранимой процедуре
а уж в ней Вы можете написать запрос как Вам угодно
13 июн 13, 12:20    [14427512]     Ответить | Цитировать Сообщить модератору
 Re: Помогите подсказать оптимизатору  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
аувв,

А что там внутри функции? Я так понимаю это multystatemnet table function, на inline никак не переделать?
Давайте действительный план, может кто-то что-то подскажет. Пока, предварительно, если совсем ничего менять нельзя, можно посмотреть в сторону Plan Guides. Выполнить запрос с хинтом и прибить получившийся план к проблемному запросу.
13 июн 13, 12:27    [14427546]     Ответить | Цитировать Сообщить модератору
 Re: Помогите подсказать оптимизатору  [new]
аувв
Member

Откуда: не знакомлюсь!
Сообщений: 32397
USE [PetroCopy3]
GO
/****** Object:  UserDefinedFunction [dbo].[callSolr]    Script Date: 06/13/2013 12:38:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[callSolr] (@ask nvarchar(526))
RETURNS @foundFileIds TABLE 
(
    fileId nvarchar(400) primary key NOT NULL
)
--Returns a result set that lists all the employees who report to the 
--specific employee directly or indirectly.*/
AS
BEGIN

Declare @Object as Int;
declare @objDomDoc as int;
declare @objNodeList as int;
declare @objNode as int;
declare @listSize as int;
declare @i as int
declare @text as nvarchar(400);

select @i=0;

Exec sp_OACreate 'Msxml2.ServerXMLHTTP', @Object OUT;

select @ask = 'http://host/solr/select?rows=65535&q=' + @ask;

Exec sp_OAMethod @Object, 'open', NULL, 'get',
    @ask, 'false'
Exec sp_OAMethod @Object, 'send'
Exec sp_OAGetProperty @Object, 'responseXML', @objDomDoc OUTPUT
Exec sp_OAMethod @objDomDoc, 'selectNodes', @objNodeList OUTPUT, '/response/result/doc/str[@name="id"]'
Exec sp_OAGetProperty @objNodeList, 'length', @listSize OUTPUT

while @i < @listSize
begin

	Exec sp_OAMethod @objNodeList, 'nextNode', @objNode OUTPUT
	Exec sp_OAGetProperty @objNode, 'text', @text OUTPUT
	
	insert @foundFileIds values (@text);
	
	select @i=@i+1;
	

	Exec sp_OADestroy @objNode
end


Exec sp_OADestroy @Object	
Exec sp_OADestroy @objDomDoc
Exec sp_OADestroy @objNodeList

return


END;
13 июн 13, 12:38    [14427631]     Ответить | Цитировать Сообщить модератору
 Re: Помогите подсказать оптимизатору  [new]
аувв
Member

Откуда: не знакомлюсь!
Сообщений: 32397
это функция, сам родил, с помощью гугла...
13 июн 13, 12:39    [14427635]     Ответить | Цитировать Сообщить модератору
 Re: Помогите подсказать оптимизатору  [new]
аувв
Member

Откуда: не знакомлюсь!
Сообщений: 32397
Паганель
аувв
Я жертва ORMа и не могу менять в запросе порядок джоинов (или пока не придумал как)
большинство современных ORM позволяют обратиться за данными к хранимой процедуре
а уж в ней Вы можете написать запрос как Вам угодно
запрос формируется динамически в зависимости от заполненных полей формы поиска... и Документ должен первым стоять, или слишком трудно будет его формировать...
13 июн 13, 12:41    [14427648]     Ответить | Цитировать Сообщить модератору
 Re: Помогите подсказать оптимизатору  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22550
аувв
запрос формируется динамически в зависимости от заполненных полей формы поиска...
https://www.sql.ru/faq/faq_topic.aspx?fid=114
аувв
Документ должен первым стоять, или слишком трудно будет его формировать...
не пишите звездочку
13 июн 13, 12:47    [14427689]     Ответить | Цитировать Сообщить модератору
 Re: Помогите подсказать оптимизатору  [new]
аувв
Member

Откуда: не знакомлюсь!
Сообщений: 32397
SomewhereSomehow
аувв,

А что там внутри функции? Я так понимаю это multystatemnet table function, на inline никак не переделать?
Давайте действительный план, может кто-то что-то подскажет. Пока, предварительно, если совсем ничего менять нельзя, можно посмотреть в сторону Plan Guides. Выполнить запрос с хинтом и прибить получившийся план к проблемному запросу.


плохой план (как я думаю)

К сообщению приложен файл. Размер - 28Kb
13 июн 13, 12:59    [14427763]     Ответить | Цитировать Сообщить модератору
 Re: Помогите подсказать оптимизатору  [new]
аувв
Member

Откуда: не знакомлюсь!
Сообщений: 32397
аувв
SomewhereSomehow
аувв,

А что там внутри функции? Я так понимаю это multystatemnet table function, на inline никак не переделать?
Давайте действительный план, может кто-то что-то подскажет. Пока, предварительно, если совсем ничего менять нельзя, можно посмотреть в сторону Plan Guides. Выполнить запрос с хинтом и прибить получившийся план к проблемному запросу.


плохой план (как я думаю)
хороший, вроде, план - если закомментировать один OR

К сообщению приложен файл. Размер - 36Kb
13 июн 13, 13:02    [14427775]     Ответить | Цитировать Сообщить модератору
 Re: Помогите подсказать оптимизатору  [new]
аувв
Member

Откуда: не знакомлюсь!
Сообщений: 32397
а план переделанного запроса с хинтом какой-то длинный, в экран не помещается =)

но ни одного нет index scan
13 июн 13, 13:04    [14427793]     Ответить | Цитировать Сообщить модератору
 Re: Помогите подсказать оптимизатору  [new]
Glory
Member

Откуда:
Сообщений: 104751
аувв
а план переделанного запроса с хинтом какой-то длинный, в экран не помещается =)

Потому что план надо записывать в файл, который и прикреплять к сообщению
13 июн 13, 13:07    [14427811]     Ответить | Цитировать Сообщить модератору
 Re: Помогите подсказать оптимизатору  [new]
аувв
Member

Откуда: не знакомлюсь!
Сообщений: 32397
Паганель
аувв
запрос формируется динамически в зависимости от заполненных полей формы поиска...
https://www.sql.ru/faq/faq_topic.aspx?fid=114
аувв
Документ должен первым стоять, или слишком трудно будет его формировать...
не пишите звездочку
спасибо! в следующий раз обязательно!

в моем же случае в зависимости от выбранных полей делаются или не делаются джоины. например, мы можем искать по наименованию валюты, которая в справочнике - тогда помимо where еще джоинится таблица с названиями валют
13 июн 13, 13:07    [14427812]     Ответить | Цитировать Сообщить модератору
 Re: Помогите подсказать оптимизатору  [new]
аувв
Member

Откуда: не знакомлюсь!
Сообщений: 32397
Glory
аувв
а план переделанного запроса с хинтом какой-то длинный, в экран не помещается =)

Потому что план надо записывать в файл, который и прикреплять к сообщению
спасибо, вот

К сообщению приложен файл (goodPlan.sqlplan - 60Kb) cкачать
13 июн 13, 13:08    [14427819]     Ответить | Цитировать Сообщить модератору
 Re: Помогите подсказать оптимизатору  [new]
Glory
Member

Откуда:
Сообщений: 104751
аувв
в моем же случае в зависимости от выбранных полей делаются или не делаются джоины. например, мы можем искать по наименованию валюты, которая в справочнике - тогда помимо where еще джоинится таблица с названиями валют

И как select * влияет на выражение в where или join ?
13 июн 13, 13:08    [14427821]     Ответить | Цитировать Сообщить модератору
 Re: Помогите подсказать оптимизатору  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
аувв,

Если участники форума не могут запустить какой-то пример, чтобы получить у себя план локально, то планы лучше прикладывать в формате .sqlplan/xml, как в соседней теме, например. Тогда не нужно будет умещать в экран =) пока только видно, что форма плана другая, т.к. вы закомментили одно условие, и видимо, поменялись оценки.
13 июн 13, 13:10    [14427830]     Ответить | Цитировать Сообщить модератору
 Re: Помогите подсказать оптимизатору  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
аувв,

А "плохость" плана в чем проявляется? Время выполнения сильно отличается? Кол-во строк-то в таблицах небольшое...
Или вопрос чисто из любопытства, т.к. просто не нравится скан?
13 июн 13, 13:19    [14427871]     Ответить | Цитировать Сообщить модератору
 Re: Помогите подсказать оптимизатору  [new]
аувв
Member

Откуда: не знакомлюсь!
Сообщений: 32397
вот план первого запроса

К сообщению приложен файл (badPlan.sqlplan - 39Kb) cкачать
13 июн 13, 13:20    [14427875]     Ответить | Цитировать Сообщить модератору
 Re: Помогите подсказать оптимизатору  [new]
аувв
Member

Откуда: не знакомлюсь!
Сообщений: 32397
вот с закомментированным условием

тоже good назвал

К сообщению приложен файл (goodPlan.sqlplan - 38Kb) cкачать
13 июн 13, 13:21    [14427883]     Ответить | Цитировать Сообщить модератору
 Re: Помогите подсказать оптимизатору  [new]
аувв
Member

Откуда: не знакомлюсь!
Сообщений: 32397
SomewhereSomehow
аувв,

А "плохость" плана в чем проявляется? Время выполнения сильно отличается? Кол-во строк-то в таблицах небольшое...
Или вопрос чисто из любопытства, т.к. просто не нравится скан?
у клиента большое. да, очень тормозит, пропорционально кличеству записей, пока думаю, что из-за этого
13 июн 13, 13:21    [14427890]     Ответить | Цитировать Сообщить модератору
 Re: Помогите подсказать оптимизатору  [new]
аувв
Member

Откуда: не знакомлюсь!
Сообщений: 32397
Glory
аувв
в моем же случае в зависимости от выбранных полей делаются или не делаются джоины. например, мы можем искать по наименованию валюты, которая в справочнике - тогда помимо where еще джоинится таблица с названиями валют

И как select * влияет на выражение в where или join ?
никак.

с указанием только d.id в select план остался такой же, как в badPlan.sqlplan
13 июн 13, 13:23    [14427896]     Ответить | Цитировать Сообщить модератору
 Re: Помогите подсказать оптимизатору  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
аувв,

Т.е. у клиентов другие данные, другая статистика? Лучше бы, конечно, взять реальные планы клиентов, поскольку причина может быть не в том, в чем вам кажется.
Но, допустим, причина именно в этом.
+ чтобы удобно было смотреть
use tempdb;
go
create table [file] (id int not null, documentId int, path nvarchar(400), mimeType int, isDeleted int, fileName nvarchar(400) );
alter table [file] add constraint  [PK_file_1] primary key (id);
create nonclustered index [IX_file] on [file]([path]);
create table [document]
(id int  not null,siteId int,stageId int,domainId int,year int,number int,archiveNumber int,title int,
fullTitle  int, typeId int,comment int,originationDetails int,originationDate int,approvalDate int,
registrationDate int,placementDate int,placerId int,updateDate int,pageCount int,geometryTypeId int,
scale int,resolution int,projectionCode int,periodicityId int,classificationId int,limitationDetails int,
typeOfWorkId int,workProcessId int,condensed int);
alter table [document] add constraint  [PK_document] primary key (id);
go
update statistics [file] with rowcount = 111, pagecount = 5;
update statistics [document] with rowcount = 89, pagecount = 3;
go
create function [dbo].[callsolr] (@ask nvarchar(526))
returns @foundfileids table 
(
    fileid nvarchar(400) primary key not null
)
as
begin
	insert into @foundfileids(fileid) values (N'something');
	return;
end;
go

Смотрим оценочный план запроса.
dbcc freeproccache;
go
select * from Document d 
inner join "file" f on d.id = f.documentId
inner join callSolr('%D0%BF%D1%80%D0%BE%D0%B2%D0%B5%D1%80%D0%BA%D0%B0') sc on 
sc.fileId=f.path  or 
SUBSTRING(sc.fileId, 5, LEN(sc.fileId))=f.path
option (loop join)
go

Картинка с другого сайта.

1. Не очень хорошо - выражение, зачем сбивать с толку оптимизатор, его можно спокойно вычислять в функции.
2. Сама табличная функция, по сути табличная переменная, нет статистики, для оптимизатора - черный ящик, по-этому, он использует догадки.
3. OR в предикате. Часто, это не очень хорошо для поиска по индексу.

Попробуем форсировать индекс ix_file
dbcc freeproccache;
go
select * from Document d 
inner join "file" f with(index(ix_file)) on d.id = f.documentId
inner join callSolr('%D0%BF%D1%80%D0%BE%D0%B2%D0%B5%D1%80%D0%BA%D0%B0') sc on 
sc.fileId=f.path  or 
SUBSTRING(sc.fileId, 5, LEN(sc.fileId))=f.path
option (loop join)
go

Картинка с другого сайта.

По-сути ничего не изменилось, только теперь сканируем некластерный+лукап за остальными данными в кластерный.
А вот дальше, становится интересней. Форсируем еще и поиск по этому индексу:
dbcc freeproccache;
go
select * from Document d 
inner join "file" f with(index(ix_file),forceseek) on d.id = f.documentId
inner join callSolr('%D0%BF%D1%80%D0%BE%D0%B2%D0%B5%D1%80%D0%BA%D0%B0') sc on 
sc.fileId=f.path  or 
SUBSTRING(sc.fileId, 5, LEN(sc.fileId))=f.path
option (loop join)
go

Картинка с другого сайта.
Становится похож на ваш план с force order (только у force order на порядок лучше оценки).

Почему так, и что значит эта ветка с merge interval.
Для объяснения, посмотрим планы запросов:
declare @1 int = 1, @2 int = 2;
select * from [file] where id in(1,2);
select * from [file] where id in(@1,@2);

Картинка с другого сайта.
Когда у нас в запросе константы оптимизатор четко знает условия поиска, знает что строка(-ки), которую он заберет из индекса по условию id = 1, и строка, которую он заберет из индекса по условию id = 2 - это разные строки.
Теперь, представим, что у нас в переменной @1 значение 1 и в переменной @2 значение 1. Если бы оптимизатор поступил как в первом случае, он одну и ту же строку нашел бы два раза. Поэтому, он строит план, в котором есть операторы, которые мерджат т.е. сливают одинаковые значения, если такие попадаются. После этого, уже слитые значения отправляются в индекс для поиска.

Возвращаясь к примеру.
Тут происходит то же самое. Когда мы форсируем поиск, либо форсируем порядок, то у нас на внешней стороне цикла соединения функция (суть табличная переменная), и мы обязаны найти в индексе ix_file строки по условию и sc.fileId=f.path, и SUBSTRING(sc.fileId, 5, LEN(sc.fileId))=f.path. При этом, одна строка может удовлетворять и тому и другому условию. По этому, оптимизатор делает тот же самый прием со слиянием выражений, которые могут привести к выборке одной и той же строки дважды.

Это может быть не очень очевидно, поскольку кажется, что оптимизатор может пойти один раз в индекс и проверить сперва условие 1, если оно не подходит проверить условие 2. Но смысл в том, что "проверить" как раз и заключается в том, чтобы выполнить поиск. Если он проверил условие 1, то точно также должен выполнить поиск и по второму. И т.к. результаты поиска могут привести к прочтению строки дважды - то он принимает меры, чтобы этого избежать.

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

Что можно сделать.
1.
Можно попробовать убрать лукап, сделав тем самым поиск по индексу ix_file более дешевым следовательно, более предпочтительным вариантом.
drop index [file].[IX_file];
create index [IX_file] on [file]([path]) include (documentId, mimeType, isDeleted, fileName);
dbcc freeproccache;
go
select * from Document d 
inner join "file" f on d.id = f.documentId
inner join callSolr('%D0%BF%D1%80%D0%BE%D0%B2%D0%B5%D1%80%D0%BA%D0%B0') sc on 
sc.fileId=f.path  or 
SUBSTRING(sc.fileId, 5, LEN(sc.fileId))=f.path

Картинка с другого сайта.

2.
Можно избавиться совсем от второго условия. Вместо этого, в функции, в таблице сохранять и выражения с подстрокой.
alter function [dbo].[callsolr] (@ask nvarchar(526))
returns @foundfileids table 
(
    fileid nvarchar(400) not null, primary key(fileid)
)
as
begin
	declare @text nvarchar(400) = N'something';	
	insert into @foundfileids(fileid) values (@text);
	insert into @foundfileids(fileid) values (SUBSTRING(@text, 5, LEN(@text)));
	return;
end;

Обратите внимание, первичный ключ я оставил. Если вы точно знаете что после обрезания повторений не будет, нужно его оставить, если будут - то в запрос придется добавлять группировку.
dbcc freeproccache;
go
select *
from 
	[file] f
	join Document d on d.id = f.documentId
	join callSolr('%D0%BF%D1%80%D0%BE%D0%B2%D0%B5%D1%80%D0%BA%D0%B0') sc on sc.fileId = f.path;

Картинка с другого сайта.


Ну и если ничего этого нельзя делать, то остаются Plan Guides, как я уже писал. Придется прибить план.
А вообще, если б не было ORM, нормальным способом является материализовать во временную таблицу результаты выполнения непонятной для оптимизатора функции, там и статистика есть и оценки лучше.

А совсем вообще, нужно смотреть что там у клиентов, может дело совсем не в этом =)
13 июн 13, 17:09    [14429800]     Ответить | Цитировать Сообщить модератору
 Re: Помогите подсказать оптимизатору  [new]
аувв
Member

Откуда: не знакомлюсь!
Сообщений: 32397
SomewhereSomehow,

огромное спасибо! буду изучать.
13 июн 13, 17:21    [14429870]     Ответить | Цитировать Сообщить модератору
 Re: Помогите подсказать оптимизатору  [new]
Maxx
Member [скрыт]

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

подскажите какой тулзой вы смотрите планы, если не жалко
13 июн 13, 18:36    [14430230]     Ответить | Цитировать Сообщить модератору
 Re: Помогите подсказать оптимизатору  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Maxx,

Не жалко. SQL Sentry Plan Explorer - бесплатная. Есть еще версия pro - платная. Также рекомендую для удобства скачать плагинчик к студии, он позволяет открывать планы прямо из SSMS.
13 июн 13, 18:57    [14430300]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить