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

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

Именно такой запрос и выполнял. 5 раз выполнил, каждый раз 10-11 секунд. Вот даже план

К сообщению приложен файл (recompile.zip - 17Kb) cкачать
26 апр 16, 11:30    [19106966]     Ответить | Цитировать Сообщить модератору
 Re: Почему такое время выполнения запроса при передаче параметров в функцию?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Max_11111,

а my_function() inline?
26 апр 16, 11:50    [19107137]     Ответить | Цитировать Сообщить модератору
 Re: Почему такое время выполнения запроса при передаче параметров в функцию?  [new]
Max_11111
Member

Откуда:
Сообщений: 201
TaPaK
а my_function() inline?

да
26 апр 16, 11:53    [19107176]     Ответить | Цитировать Сообщить модератору
 Re: Почему такое время выполнения запроса при передаче параметров в функцию?  [new]
iljy
Member

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

слушайте, а что у вас с типами параметров и полей? У вас одна функция вроде как возвращает дату, а вторая на вход ожидает инты, так? сама по себе ситуация так себе, но попробуйте сделать явное приведение типов при возврате дат.
26 апр 16, 11:55    [19107188]     Ответить | Цитировать Сообщить модератору
 Re: Почему такое время выполнения запроса при передаче параметров в функцию?  [new]
Max_11111
Member

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

нет, у меня дат в явном виде тут нет. Как я писал в самом начале, я использую int идентификатор даты в виде ггггммдд
26 апр 16, 11:57    [19107202]     Ответить | Цитировать Сообщить модератору
 Re: Почему такое время выполнения запроса при передаче параметров в функцию?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Max_11111,

ну показывайте тогда функцию, почему она у вас гоняет столько записей....
26 апр 16, 12:07    [19107296]     Ответить | Цитировать Сообщить модератору
 Re: Почему такое время выполнения запроса при передаче параметров в функцию?  [new]
iljy
Member

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

у вас проблема явно видна в плане - сервер не использует переданные в функцию параметры для поиска по индексу, если они не переданы явно. Весьма и весьма странное поведение. Версия сервера у вас какая?
26 апр 16, 12:08    [19107299]     Ответить | Цитировать Сообщить модератору
 Re: Почему такое время выполнения запроса при передаче параметров в функцию?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
я так подозреваю что у вас
SELECT * FROM t
UNION ALL
SELECT * FROM t
WHERE
data BETWEEN @d1 AND @d2
26 апр 16, 12:09    [19107312]     Ответить | Цитировать Сообщить модератору
 Re: Почему такое время выполнения запроса при передаче параметров в функцию?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Max_11111,

и кстати, у вас сортировки(зачем то?) сливаеются в tempdb.. посмотрите Never Ignore a Sort Warning in SQL Server
26 апр 16, 12:44    [19107611]     Ответить | Цитировать Сообщить модератору
 Re: Почему такое время выполнения запроса при передаче параметров в функцию?  [new]
Max_11111
Member

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

sql server 2012 sp2 (build 11.0.5343)

TaPaK,

Сортировок у меня нет. Не знаю откуда они в плане.
В исходной таблице почти миллиард строк, в результате запроса получаю около 300 тысяч.
	
	SELECT
		data.DateID,
		littleTable.ID AS StaffID,
		CAST((CASE WHEN Item2CtType = 4608 THEN Item2CtID ELSE NULL END) AS TINYINT) AS TypeID,
		[Sum]
	FROM
		bigTbl AS data
		INNER JOIN littleTable
			on Item1CtType = 10
			and Item1CtID = littleTable.ItemID
	WHERE
		DateID between @DateStartID and @DateStopID
		AND ValDtID = 394 -- 44
		AND ValCtID = 395 -- 70.1

	UNION ALL

	SELECT
		data.DateID,
		littleTable.ID AS StaffID,
		CAST(3 AS TINYINT) AS TypeID,
		-[Sum] AS [Sum]
	FROM
		bigTbl AS data
		INNER JOIN littleTable
			on Item1CtType = 10
			and Item1CtID = littleTable.ItemID
	WHERE
		DateID between @DateStartID and @DateStopID
		AND ValDtID = 394 -- 44
		AND ValCtID = 490 -- 70.2
		AND Item1DtType = 7
		AND Item1DtID = 20
26 апр 16, 13:10    [19107862]     Ответить | Цитировать Сообщить модератору
 Re: Почему такое время выполнения запроса при передаче параметров в функцию?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Max_11111,

честно говоря хрень какая-то :) попробуйте дропнуть и поднять свою функцию, план останется?
26 апр 16, 13:22    [19107964]     Ответить | Цитировать Сообщить модератору
 Re: Почему такое время выполнения запроса при передаче параметров в функцию?  [new]
Max_11111
Member

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

Без изменений =(
26 апр 16, 13:34    [19108046]     Ответить | Цитировать Сообщить модератору
 Re: Почему такое время выполнения запроса при передаче параметров в функцию?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Max_11111,

а какой у вас кластерный индекс на FACT_Transactions?
26 апр 16, 13:50    [19108150]     Ответить | Цитировать Сообщить модератору
 Re: Почему такое время выполнения запроса при передаче параметров в функцию?  [new]
Max_11111
Member

Откуда:
Сообщений: 201
TaPaK
а какой у вас кластерный индекс на FACT_Transactions?

По DateID
26 апр 16, 14:08    [19108274]     Ответить | Цитировать Сообщить модератору
 Re: Почему такое время выполнения запроса при передаче параметров в функцию?  [new]
SomewhereSomehow
Member

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

Добрый день.

Разница в первых планах потому, что один параллельный, другой нет. Параллельным медленный план быть не может из-за использования скалярных функций, которые запрещают параллелизм в плане. Более того сам вызов скалярной функции очень медленный. Так что правильно сделали, что отказались от них в пользу Inline.

Сортировка в плане возникает из-за внутренней оптимизации сервера. Он сортирует данные по ключу поиска для того, чтобы случайный доступ в Key Lookup был более последовательным. Из-за неверных оценок она сливает данные на диск.

Два хэш соединения с битмап фильтрами возникают потому, что у вас применяется оптимизация Index Join to hash, т.е. объединение индексов в Hash. Объединение индексов возникает потому, что вы запрашиваете данные, которые дешевле всего взять из двух разных индексов поиском, после чего соединить результаты. Правило, которое реализует эту оптимизацию, работает со сложными предикатами, включающими столбцы других таблиц, чуть лучше, чем никак. Поэтому у вас условие по дате не проталкивается на уровень доступа к данным и читается больше данных, чем могло бы.

Подытоживая все вышесказанное, я бы рекомендовал сделать индекс:
create index AccountDtCtDate on [dbo].[FACT_Transactions] (AccountDtID, AccountCtID, DateID) include (DocType, DocID, Line)

Либо еще лучше, сделать покрывающий индекс, чтобы избежать сортировок и лукапа (если такой индекс вам позволительно создавать, ну судя по префиксу FACT это что-то не под OLTP нагрузкой):
create index AccountDtCtDate on [dbo].[FACT_Transactions] (AccountDtID, AccountCtID, DateID) include (DocType, DocID, Line, Item1CtID, Item2CtType, Item2CtID, Sum)

Или хотя бы добавить колонку AccountDtID или AccountCtID в один или другой индекс соответственно.

Также желательно обновить статистику с опцией fullscan, по таблицам участвующим в запросе, чтобы минимизиовать ошибки из-за неточной или неактуальной статистики.
26 апр 16, 14:15    [19108318]     Ответить | Цитировать Сообщить модератору
 Re: Почему такое время выполнения запроса при передаче параметров в функцию?  [new]
TaPaK
Member

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

зачем покрывать поля которые в предикате? план и так предлагает создать вполне адекватный индекс
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[FACT_Transactions] ([AccountDtID],[AccountCtID],[Item1DtType],[Item1DtID],[Item1CtType])
INCLUDE ([DateID],[Item1CtID],[Sum])
26 апр 16, 14:22    [19108361]     Ответить | Цитировать Сообщить модератору
 Re: Почему такое время выполнения запроса при передаче параметров в функцию?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
хотя DateId вынес бы в индекс, но судя по всему сервер не планирует поднимать предикат :)
26 апр 16, 14:40    [19108515]     Ответить | Цитировать Сообщить модератору
 Re: Почему такое время выполнения запроса при передаче параметров в функцию?  [new]
Max_11111
Member

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

Увы, не могу сейчас создать индекс. Жду хотя-бы апгрейда дисков на SSD. Если создам ещё один индекс, уверен, обмен встанет. Да, это не OLTP система, загрузка данных у меня раз в сутки проходит.

С текущими индексами можно как-либо решить данную проблему?
26 апр 16, 14:42    [19108523]     Ответить | Цитировать Сообщить модератору
 Re: Почему такое время выполнения запроса при передаче параметров в функцию?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Max_11111,

обновите статистику,как пишет SomewhereSomehow, но лукап и сортировка не пропадут
26 апр 16, 14:45    [19108541]     Ответить | Цитировать Сообщить модератору
 Re: Почему такое время выполнения запроса при передаче параметров в функцию?  [new]
SomewhereSomehow
Member

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

В рамках текущих индексов добавить в один из индексов поля AccountDtID или AccountCtID, в зависимости от индекса, это уже снимет проблему пересечения индексов.

Если совсем ничего нельзя трогать, то прежде всего обновите статистику с полным сканированием. Хотя это довольно ресурсоемкая операция, и, если вы боитесь положить сервер, представьте, что будет столько полных сканирований вашей большой таблицы, сколько статистик. Либо если вы выясните какая конкретно статистика(и) используется – все равно бужет полное сканирование, что тоже затратно. После обновления статистики, посмотрите, будет ли оптимизатор по-прежнему настаивать на пересечении индексов? Если да, то может быть его модель ошибается, можно попробовать поэкспериментировать с подсказками, указав конкретный индекс.

Что касается сортировки для лукапа – есть документированный TF 2340 отключающий такую оптимизацию: https://support.microsoft.com/en-us/kb/2009160 можете попробовать использовать его. Но не факт, что станет быстрее, ведь сортировку перед случайным доступом придумали именно для ускорения, хотя иногда это работает в обратную сторону. В вашем случае, сортировка даже сливает данные в tempdb.

В любом случае, наиболее оптимальный вариант, это обойтись без всяких подсказок и флагов трассировки, а просто создать подходящий индекс. Вариант индекса основанный на анализе проблем плана я вам предложил. Можете также рассмотреть индекс, который предлагает сам сервер, он также озвучен выше, или как-то обобщить два варианта. На мой взгляд подходящий индекс – самое оптимальное решение.
26 апр 16, 18:45    [19109744]     Ответить | Цитировать Сообщить модератору
 Re: Почему такое время выполнения запроса при передаче параметров в функцию?  [new]
Max_11111
Member

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

Ок, спасибо. Вечером попробую добавить поля AccountDtID, AccountCtID в include соответствующих индексов и посмотрю что будет.

Попробовал поэкспериментировать с другой функцией - результат аналогичный:
SELECT   *
FROM  dbo.OLAP_FACT_Credits(dbo.OLAP_GetPartitionIntervals(N'2016y_1hy', N'MIN'), dbo.OLAP_GetPartitionIntervals(N'2016y_1hy', N'MAX')) AS FACT_Credits
-- 16 сек

declare
	@d1 int = dbo.OLAP_GetPartitionIntervals(N'2016y_1hy', N'MIN'),
	@d2 int = dbo.OLAP_GetPartitionIntervals(N'2016y_1hy', N'MAX')
	
SELECT * FROM dbo.OLAP_FACT_Credits(@d1, @d2)
-- 6 сек


SELECT * FROM dbo.OLAP_GetPartitionIntervals_Inline2(N'2016y_1hy') a
   CROSS APPLY dbo.OLAP_FACT_Credits(a.DateMinID, a.DateMaxID)
-- 17 сек

Почему inline не ускоряет выполнение функции, хотя по вашим словам должен?
Текст функции:
	SELECT
		FACT_CashPayment.DocType,
		FACT_CashPayment.DocID,
		FACT_CashPayment.PayLine,
		MAX(FACT_CashPayment.BankID) AS BankID,
		MAX(FACT_CashPayment.BankCardID) AS BankCardID,
		MAX(FACT_CashPayment.DateID) AS DateID,
		MAX(FACT_CashPayment.BranchID) AS BranchID,
		MAX(FACT_CashPayment.PayTypeID) AS PayTypeID,
		MAX(FACT_CashPayment.CreditBarCodeTypeID) AS CreditBarCodeTypeID,
		MAX(PD.WithRegister) AS WithRegister,
		MAX(PD.WithStatement) AS WithStatement,
		MAX(CASE WHEN PD.CountDaysWaitPay > 11 THEN 11 ELSE PD.CountDaysWaitPay END) AS CountDaysWaitPay,
		MAX(CASE WHEN PD.CountDaysWithoutPay > 11 THEN 11 ELSE PD.CountDaysWithoutPay END) AS CountDaysWithoutPay,
		MAX(CASE WHEN FACT_CashPayment.CountDaysWaitRegister > 11 THEN 11 ELSE FACT_CashPayment.CountDaysWaitRegister END) AS CountDaysWaitRegister,
		MAX(CASE WHEN FACT_CashPayment.CountDaysWithoutRegister > 11 THEN 11 ELSE FACT_CashPayment.CountDaysWithoutRegister END) AS CountDaysWithoutRegister,
		SUM(FACT_CashPayment.PaySum) AS [Sum]
	FROM
		FACT_CashPayment
		INNER JOIN (
			SELECT
				CP.DocType,
				CP.DocID,
				CP.PayLine,
				MAX(CASE WHEN FACT_BankRegister.DocID IS NULL THEN 0 ELSE 1 END) AS WithRegister,
				MAX(CASE WHEN FACT_BankRegister.CountPayDays IS NULL THEN 0 ELSE 1 END) AS WithStatement,
				MAX(FACT_BankRegister.CountPayDays) AS CountDaysWaitPay,
				MAX(FACT_BankRegister.CountWithoutPayDays) AS CountDaysWithoutPay
			FROM
				FACT_CashPayment AS CP
				LEFT JOIN FACT_BankRegister
					ON FACT_BankRegister.PayDocType = CP.DocType
					AND FACT_BankRegister.PayDocID = CP.DocID
					AND FACT_BankRegister.PayDocLine = CP.PayLine
			WHERE
				CP.PayTypeID = 3
				AND CP.DateID BETWEEN @DateStartID AND @DateStopID
			GROUP BY
				CP.DocType,
				CP.DocID,
				CP.PayLine
		) AS PD
			ON FACT_CashPayment.DocType = PD.DocType
			AND FACT_CashPayment.DocID = PD.DocID
			AND FACT_CashPayment.PayLine = PD.PayLine 
	GROUP BY
		FACT_CashPayment.DocType,
		FACT_CashPayment.DocID,
		FACT_CashPayment.PayLine


Планы во вложении

К сообщению приложен файл (plan.zip - 33Kb) cкачать
27 апр 16, 06:58    [19110941]     Ответить | Цитировать Сообщить модератору
 Re: Почему такое время выполнения запроса при передаче параметров в функцию?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Max_11111,

Вообщем для вашего случая профита он инлайна с CROSS APPLY нет ввиду того что у вам в принципе нет необходимости объединять в один запрос. Условия соединяются для каждой строки результата вашей функции, а не попадают на вход в ваших селекта.
наверное оптимально будет нечто вроде

DECLARE @D1 INT, @D2 INT

SELECT @D1 = DateMinID, @D2 = DateMaxID FROM dbo.OLAP_GetPartitionIntervals_Inline2(N'2016y_1hy')

SELECT * FROM dbo.OLAP_FACT_Credits(@d1, @d2)
27 апр 16, 09:30    [19111220]     Ответить | Цитировать Сообщить модератору
 Re: Почему такое время выполнения запроса при передаче параметров в функцию?  [new]
Max_11111
Member

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

Теперь понял. Т.е. инлайн с CROSS APPLY даёт профит в виде распараллеливания запросов в случае если запросы объединятся через UNION. Если UNION нет, то запрос будет выполняться в одном потоке.

Скорее всего придётся передавать в запрос конкретные значения параметров: my_function(20160101, 20160630). Профит в скорости выполнения (разница примерно в 2.5-3 раза для разных функций), на мой взгляд, намного важнее удобства и универсальности. Тем более границы секций я не так часто меняю.
27 апр 16, 09:48    [19111292]     Ответить | Цитировать Сообщить модератору
 Re: Почему такое время выполнения запроса при передаче параметров в функцию?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Max_11111
TaPaK,

Теперь понял. Т.е. инлайн с CROSS APPLY даёт профит в виде распараллеливания запросов в случае если запросы объединятся через UNION. Если UNION нет, то запрос будет выполняться в одном потоке.

Скорее всего придётся передавать в запрос конкретные значения параметров: my_function(20160101, 20160630). Профит в скорости выполнения (разница примерно в 2.5-3 раза для разных функций), на мой взгляд, намного важнее удобства и универсальности. Тем более границы секций я не так часто меняю.

я даже не представляю откуда такой вывод :) Может тогда вообще не передавать, а в запросе ставить, будет ещё быстрее :)
27 апр 16, 09:51    [19111305]     Ответить | Цитировать Сообщить модератору
 Re: Почему такое время выполнения запроса при передаче параметров в функцию?  [new]
Max_11111
Member

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

Ну это уже слишком, в случае изменений придётся 10 одинаковых запросов переписывать =).
Только что попробовал - секция обработалась в 2 раза быстрее. А аналогичных функций у меня несколько. И выполняются они не секунды, а минуты. И секций (соответственно и запросов) у меня штук 10. В итоге получается сокращение времени обработки OLAP-куба на 20-30%, а это уже существенно
27 апр 16, 10:10    [19111374]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить