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

Откуда:
Сообщений: 457
Доброе утро!

Скажите пожалуйста такое было у кого нибудь?

Запрос из однострочной табличной функции с параметрами выполнялся медленно, у меня 27 секунд,
а внутренний запрос этой функции с этими же параметрами заранее объявленными выполнялся 0 секунд.

SELECT * FROM FLISTFREE(2,26) AS C 

27 СЕКУНД
а отдельно вне функции

DECLARE
@P1 INT,
@P2 INT

SELECT T1.ID ....
FROM ...
WHERE ...=@P1
AND ....=@P2

0 СЕКУНД

Заранее спасибо.
19 фев 14, 10:05    [15587364]     Ответить | Цитировать Сообщить модератору
 Re: табличная функция  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
функция какая? инлайн или мультистатмент? думаю второе
19 фев 14, 10:07    [15587381]     Ответить | Цитировать Сообщить модератору
 Re: табличная функция  [new]
Draginsv
Member

Откуда:
Сообщений: 457
функция инлайн
19 фев 14, 10:12    [15587419]     Ответить | Цитировать Сообщить модератору
 Re: табличная функция  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
https://www.sql.ru/blogs/somewheresomehow/999
19 фев 14, 10:15    [15587444]     Ответить | Цитировать Сообщить модератору
 Re: табличная функция  [new]
aleks2
Guest
Draginsv
функция инлайн

Функция тоже запрос.
С запросами такое случается.
Сначала надо посмотреть планы...
19 фев 14, 10:16    [15587451]     Ответить | Цитировать Сообщить модератору
 Re: табличная функция  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
Draginsv
функция инлайн
Дайте глянуть! :)
Потому что сомнительно
19 фев 14, 10:17    [15587464]     Ответить | Цитировать Сообщить модератору
 Re: табличная функция  [new]
Draginsv
Member

Откуда:
Сообщений: 457
iap,
Что глянуть - запрос функции?
19 фев 14, 10:33    [15587607]     Ответить | Цитировать Сообщить модератору
 Re: табличная функция  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
Draginsv
iap,
Что глянуть - запрос функции?
Хотя бы определение функции
CREATE FUNCTION ... - а дальше?
19 фев 14, 10:36    [15587626]     Ответить | Цитировать Сообщить модератору
 Re: табличная функция  [new]
Draginsv
Member

Откуда:
Сообщений: 457
Ну вот.
Только не зная структуры можно разобраться ?
ALTER FUNCTION [dbo].[fListFreeCell2] 
(	
	@SIZE				BIT,
	@IdsKagentStore		INT
)
RETURNS TABLE 
AS
RETURN 
(
SELECT [key_id], Number_cell, Busy FROM Cell C
left outer join
(
SELECT DISTINCT Cell_ID 
FROM Major M
INNER JOIN Doc_in DI ON M.DocIN_ID = DI.DocID 
LEFT OUTER JOIN Doc_out DO ON M.DocOUT_ID = DO.DocOID 
WHERE (NOT (DI.Date_input IS NOT NULL)) OR
      (NOT (DI.ReadytoPark = 0)) OR
      (NOT (DO.Date_output IS NOT NULL)) OR
      (NOT (DO.ReadytoPrint = 1))
UNION
SELECT M1.Cell_ID
FROM Major M1 INNER JOIN 
VinPDI VP ON M1.RVIN = VP.VIN AND M1.Date_output = VP.MDO
UNION
SELECT Cell_id FROM MajorTmp WHERE Cell_id IS NOT NULL
) as cz on c.key_id = cz.Cell_ID 
WHERE c.Enable <> 1 
AND Size = @SIZE
AND IdsKagentStore = @IdsKagentStore
AND cz.Cell_ID is null
19 фев 14, 10:44    [15587695]     Ответить | Цитировать Сообщить модератору
 Re: табличная функция  [new]
aleks2
Guest
ЭТО не может не тормозить.
19 фев 14, 10:46    [15587706]     Ответить | Цитировать Сообщить модератору
 Re: табличная функция  [new]
aleks2
Guest
1. DISTINCT c UNION - феерично!
19 фев 14, 10:49    [15587724]     Ответить | Цитировать Сообщить модератору
 Re: табличная функция  [new]
Draginsv
Member

Откуда:
Сообщений: 457
Вот план при запросе
SELECT * from fListFreeCell2(0, 8) C


К сообщению приложен файл (plan.rar - 8Kb) cкачать
19 фев 14, 10:51    [15587737]     Ответить | Цитировать Сообщить модератору
 Re: табличная функция  [new]
Draginsv
Member

Откуда:
Сообщений: 457
aleks2,
сам запрос отдельно выполняется мгновенно
19 фев 14, 10:52    [15587742]     Ответить | Цитировать Сообщить модератору
 Re: табличная функция  [new]
aleks2
Guest
2. И ваще переписать на на полдюжины not exists( ) или на except.
3. Это тот случай, когда multistatement-function является полезной.
19 фев 14, 10:52    [15587743]     Ответить | Цитировать Сообщить модератору
 Re: табличная функция  [new]
Draginsv
Member

Откуда:
Сообщений: 457
aleks2,
я так думаю мультистатман пишет в темпдб, тем самым еще процесс замедляя , ранее эта функция и была мультистатманом
сам внутренний скрипт ее выполнялся долго. Вот и удалось вставить все в один запрос для инлайн функции.
Что еще удивительно в т Cell очень мало записей с IdsKagentStore = 26 а большая часть = 9
Так еще, функция с параметром 9 возвращает быстро, а с 26 думает.
19 фев 14, 11:02    [15587800]     Ответить | Цитировать Сообщить модератору
 Re: табличная функция  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3422
Жесть. В первую очередь, ваш запрос вида:
select c.*
from Cell c
	left join (
		select id from table1
		union
		select id from table2
		union
		...
	) cz on ...
where cz.Id is null
функционально эквивалентен такому:
select c.*
from Cell c
where not exists (select 0 from table1 t where t.id = c.id)
	and not exists (select 0 from table2 t where t.id = c.id)
	and not exists (...)
Но второй будет выполняться сильно лучше, т.к. там нет UNION + DISTINCT. Вы, видимо, не знаете, как UNION работает.

Далее, изврат:
WHERE (NOT (DI.Date_input IS NOT NULL)) OR
      (NOT (DI.ReadytoPark = 0)) OR
      (NOT (DO.Date_output IS NOT NULL)) OR
      (NOT (DO.ReadytoPrint = 1))
можно переписать, избавившись от OR:
where not (
	DI.Date_input is not null
	and DI.ReadytoPark = 0
	and DO.Date_output is not null
	and DO.ReadytoPrint = 1
)
Попробуйте начать хотя бы с этого.
19 фев 14, 11:14    [15587926]     Ответить | Цитировать Сообщить модератору
 Re: табличная функция  [new]
Draginsv
Member

Откуда:
Сообщений: 457
Ennor Tiegael,
"изврат" мне создал построитель, я выражался так как вы мне предлагаете, но это на скорость не влияет.
вот not exist попробую
19 фев 14, 11:32    [15588106]     Ответить | Цитировать Сообщить модератору
 Re: табличная функция  [new]
Draginsv
Member

Откуда:
Сообщений: 457
Ennor Tiegael,
NOT EXISTS на скорость не влияет
19 фев 14, 11:53    [15588324]     Ответить | Цитировать Сообщить модератору
 Re: табличная функция  [new]
~
Guest
Draginsv,
Это ваше мнение или ваш результат?
А сравнить планы выполнения и затраченные ресурсы через профайлер...
19 фев 14, 12:10    [15588529]     Ответить | Цитировать Сообщить модератору
 Re: табличная функция  [new]
Draginsv
Member

Откуда:
Сообщений: 457
~,
результат.
профайлер доступен только на сервере, меня туда не пускают на рабочий стол мыкаюсь в ms
19 фев 14, 12:36    [15588820]     Ответить | Цитировать Сообщить модератору
 Re: табличная функция  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
Draginsv
~,
результат.
профайлер доступен только на сервере, меня туда не пускают на рабочий стол мыкаюсь в ms
профайлер - это клиентское приложение
19 фев 14, 12:39    [15588851]     Ответить | Цитировать Сообщить модератору
 Re: табличная функция  [new]
SomewhereSomehow
Member

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

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

Сравните планы:
+
use tempdb;
go
create function dbo.uf (@b datetime)
returns table
--with schemabinding
as
return 
(
	select o.name, o.type_desc from master.sys.objects o where o.create_date < @b
)
go
set statistics xml on;
-- 1. Literal
select o.name, o.type_desc from master.sys.objects o where o.create_date < '2010-04-02T16:59:21.910';

-- 2. Inline (Literal is INLINED into query text, and query optimized with it, as whole)
select * from dbo.uf ('2010-04-02T16:59:21.910');

-- 3. @d is variable, it's value is Unknown in optimization time, so estimates are guessed
declare @d datetime = '2010-04-02T16:59:21.910';
select o.name, o.type_desc from master.sys.objects o where o.create_date < @d;
set statistics xml off;

В двух первых Nested Loops и они одинаковы, во втором Sort Merge.

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

Ну и в смысл запроса я не вникал, может имеет смысл как то переписать, тут вам много уже советов дали. (Но статистику в любом случае обновите).
19 фев 14, 13:01    [15589118]     Ответить | Цитировать Сообщить модератору
 Re: табличная функция  [new]
Draginsv
Member

Откуда:
Сообщений: 457
Переписал функцию и ускорилось до 1 секунды
ALTER FUNCTION [dbo].[fListFreeCell1]
(	
	@SIZE				BIT,
	@IdsKagentStore		INT
)
RETURNS @Rezult TABLE (
[key_id]	INT,
Number_cell	VARCHAR(50),
Busy		BIT
)
AS
BEGIN
DECLARE @TC TABLE( CI INT, ID INT IDENTITY(1,1) UNIQUE (CI, ID))

INSERT INTO @TC (CI)
SELECT DISTINCT Cell_ID 
FROM Major M
INNER JOIN Doc_in DI ON M.DocIN_ID = DI.DocID 
LEFT OUTER JOIN Doc_out DO ON M.DocOUT_ID = DO.DocOID 
WHERE (NOT (DI.Date_input IS NOT NULL)) OR
      (NOT (DI.ReadytoPark = 0)) OR
      (NOT (DO.Date_output IS NOT NULL)) OR
      (NOT (DO.ReadytoPrint = 1))

INSERT INTO @TC (CI)
SELECT Cell_id FROM MajorTmp WHERE Cell_id IS NOT NULL

INSERT INTO @TC (CI)
SELECT M1.Cell_ID
FROM Major M1 INNER JOIN 
VinPDI VP ON M1.RVIN = VP.VIN AND M1.Date_output = VP.MDO

INSERT INTO @Rezult ([key_id], Number_cell, Busy )
SELECT [key_id], Number_cell, Busy FROM Cell C
LEFT OUTER JOIN  @TC TC ON C.[key_id] = TC.CI 
WHERE Enable <> 1 
AND Size = @SIZE
AND IdsKagentStore = @IdsKagentStore
AND TC.CI IS NULL

RETURN 
END


вот этот компонент SELECT M1.Cell_ID
FROM Major M1 INNER JOIN
VinPDI VP ON M1.RVIN = VP.VIN AND M1.Date_output = VP.MDO
в котором VinPDI - сложная въюха
сам он выполняется быстро,
но при его наличии функция тормозит без него летает.
я его засунул во времянку и он ??? стал вычисляться один раз ???
19 фев 14, 13:22    [15589411]     Ответить | Цитировать Сообщить модератору
 Re: табличная функция  [new]
SomewhereSomehow
Member

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

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

Разбили запрос - упростили жизнь оптимизатору, т.к в силу естественных причин оптимизатор лучше справляется с бoльшим числом мелких запросов, чем с одним крупным. Особенно если что-то не так было с оценками, а у вас явно что-то было не так (либо статистика, либо модель).

Есть даже статья SQL CAT такая, которая рекомендует разбивать сложный запрос на несколько более мелких, чтобы уменьшить влияние плохих оценок - When To Break Down Complex Queries.
19 фев 14, 13:41    [15589646]     Ответить | Цитировать Сообщить модератору
 Re: табличная функция  [new]
Draginsv
Member

Откуда:
Сообщений: 457
SomewhereSomehow,
Спасибо!
Статья полезная, как раз раскрывает мою проблему.
19 фев 14, 14:01    [15589938]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить