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

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

Завис над такой проблемой. Допустим есть запрос типа:

SELECT
id,
a = dbo.f(id),
b = dbo.f(id) * 2,
c = dbo.f(id) / 2
FROM
table1

где f - скалярная функция, которая перемалывает большую таблицу и выдает целое число.
В этом запросе функция вызывается 3 раза, т.е. 3 раза приходится производить длительную обработку.
Я хотел бы сократить лишние вычисления но такой вариант не срабатывает:

SELECT
id,
a = dbo.f(id),
b = a * 2,
c = a / 2
FROM
table1

Где выход?
18 май 17, 11:52    [20492402]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать запрос?  [new]
TaPaK
Member

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

CROSS APPLY

автор
скалярная функция, которая перемалывает большую таблицу и выдает целое число.

как же это грустно
18 май 17, 11:53    [20492410]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать запрос?  [new]
Rankatan
Member

Откуда:
Сообщений: 217
Вполне возможно, что оптимизатор не вызывает dbo.f(id) три раза, а только один.
18 май 17, 11:54    [20492416]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать запрос?  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 767
glaz99,
SELECT 
id,
a,
b = a * 2,
c = a / 2
from (select id, a = dbo.f(id), from table1) as subtable
18 май 17, 11:54    [20492417]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать запрос?  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 387
glaz99,
-- Через времянку
SELECT 
  [id],
  [t] = dbo.f([id])
FROM
  table1
INTO
  #temp
-- Через CROSS APPLY
SELECT 
  [id],
  ss.[t]
FROM
  table1
  CROSS APPLY (
    SELECT
       [t] = dbo.f([id])
  ) ss
INTO
  #temp
18 май 17, 11:55    [20492422]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать запрос?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 3315
Rankatan
Вполне возможно, что оптимизатор не вызывает dbo.f(id) три раза, а только один.

вполне невозможно
18 май 17, 11:56    [20492426]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать запрос?  [new]
Добрый Э - Эх
Guest
glaz99,

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

Select id, a, a*2, a/2
from (select id, f(id) a from t) v
18 май 17, 11:56    [20492428]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать запрос?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 3315
автор
Вполне возможно, что оптимизатор не вызывает dbo.f(id) три раза, а только один.

автор
но нет никаких гарантий, что сервер не сольет встроенное выполнение и не выполнит функцию три раза.

ветку переименовать: Мифы и сказания DATABASE ENGINE
18 май 17, 11:58    [20492440]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать запрос?  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 387
Код
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON 
SET STATISTICS TIME, XML, IO OFF
;
IF OBJECT_ID( 'tempdb..#dates' ) IS NOT NULL
  DROP TABLE #dates
;
WITH
dd AS (
  SELECT
    [date] = CONVERT( DATE, '20000101' )
  UNION ALL
  SELECT
    [date] = DATEADD( DAY, 1, dd.[date] )
  FROM
    dd
  WHERE
    dd.[date] < CONVERT( DATE, '20200101' )
)
SELECT
  [date]
INTO
  #dates
FROM
  dd
OPTION (
  MAXRECURSION 0 )
;
SET STATISTICS TIME ON
;
SELECT
  dd.[date],
  [t1] = DATEADD( HOUR  , 1, sb.dbo.l_month( dd.[date] ) ),
  [t2] = DATEADD( MINUTE, 1, sb.dbo.l_month( dd.[date] ) ),
  [t3] = DATEADD( SECOND, 1, sb.dbo.l_month( dd.[date] ) )
FROM
  #dates dd
;
SELECT
  dd.[date],
  [t1] = DATEADD( HOUR  , 1, ss.[ld] ),
  [t2] = DATEADD( MINUTE, 1, ss.[ld] ),
  [t3] = DATEADD( SECOND, 1, ss.[ld] )
FROM
  #dates dd
  CROSS APPLY (
    SELECT
      [ld] = sb.dbo.l_month( dd.[date] )
  ) ss
;
SELECT 
  t.[date],
  [t1] = DATEADD( HOUR  , 1, t.[ld] ),
  [t2] = DATEADD( MINUTE, 1, t.[ld] ),
  [t3] = DATEADD( SECOND, 1, t.[ld] )
FROM (
  SELECT 
    [date],
    [ld] = sb.dbo.l_month( dd.[date] )
  FROM
    #dates dd
 ) t
;

Результат
 SQL Server Execution Times:
   CPU time = 63 ms,  elapsed time = 156 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 107 ms.

 SQL Server Execution Times:
   CPU time = 31 ms,  elapsed time = 56 ms.
18 май 17, 12:16    [20492484]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать запрос?  [new]
glaz99
Member

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

CROSS APPLY


Интересно. Надо попробовать, спасибо!
18 май 17, 12:49    [20492571]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать запрос?  [new]
glaz99
Member

Откуда:
Сообщений: 71
Руслан Дамирович
Код

Результат


Спасибо!
Понятно, что первый способ самый неоптимальный.
А CROSS APPLY наверно будет лучшим выбором.
18 май 17, 12:55    [20492601]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать запрос?  [new]
iap
Member

Откуда: Москва
Сообщений: 44249
Лучше всего было бы переписать скалярную функцию в online табличную.
18 май 17, 13:08    [20492672]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать запрос?  [new]
TaPaK
Member

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

inline :)
18 май 17, 13:09    [20492675]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать запрос?  [new]
iap
Member

Откуда: Москва
Сообщений: 44249
TaPaK
iap,

inline :)
18 май 17, 13:12    [20492699]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать запрос?  [new]
glaz99
Member

Откуда:
Сообщений: 71
Minamoto
glaz99,
SELECT 
id,
a,
b = a * 2,
c = a / 2
from (select id, a = dbo.f(id), from table1) as subtable


Такой вариант вряд ли подойдет, т.к. реально задача посложнее, например:
SELECT
id,
a = dbo.f1(id),
b = dbo.f1(id) * 2,
c = dbo.f1(id) / 2,
d = dbo.f2(id),
e = dbo.f2(id) * dbo.f1(id),
f = dbo.f2(id) / dbo.f1(id)
FROM
table1

Там на самом деле несколько функций.
18 май 17, 13:24    [20492753]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать запрос?  [new]
iap
Member

Откуда: Москва
Сообщений: 44249
glaz99
Minamoto
glaz99,
SELECT 
id,
a,
b = a * 2,
c = a / 2
from (select id, a = dbo.f(id), from table1) as subtable



Такой вариант вряд ли подойдет, т.к. реально задача посложнее, например:
SELECT
id,
a = dbo.f1(id),
b = dbo.f1(id) * 2,
c = dbo.f1(id) / 2,
d = dbo.f2(id),
e = dbo.f2(id) * dbo.f1(id),
f = dbo.f2(id) / dbo.f1(id)
FROM
table1

Там на самом деле несколько функций.
Кто мешает их все вычислить в подзапросе, а снаружи использовать результ в виде полей подзапроса?
18 май 17, 13:34    [20492791]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать запрос?  [new]
aleks2
Guest
Руслан Дамирович
Код


А ничо, что все функции datetime - недетерминистические?
18 май 17, 13:47    [20492879]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать запрос?  [new]
glaz99
Member

Откуда:
Сообщений: 71
iap
Кто мешает их все вычислить в подзапросе, а снаружи использовать результ в виде полей подзапроса?


А кстати да.
Теперь осталось выбрать между таким подзапросом и CROSS APPLY:
CPU time = 31 ms, elapsed time = 56 ms.
CPU time = 0 ms, elapsed time = 107 ms.
Наверно CROSS APPLY победит.
18 май 17, 13:54    [20492920]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать запрос?  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 387
aleks2
Руслан Дамирович
Код

А ничо, что все функции datetime - недетерминистические?

1. Давайте ваш код с детерминистическими функциями - чтобы ТС понимал, что к чему.
2. Кто сказал, что у ТСа функции (не)детерминистические?
18 май 17, 14:13    [20492993]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать запрос?  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 387
glaz99
Понятно, что первый способ самый не оптимальный.
А CROSS APPLY наверно будет лучшим выбором.

Нет. Самым лучшим будет временная таблица.
Но в условиях быстрой разработки пойдет и CROSS APPLY...
А вообще, подумайте о том, чтобы:
1. переписать функции из scalar в inline table valued, так как первые лишают ваши запросы возможности построения (и выполнения) параллельных планов.
2. постараться все эти функции объединить в одну, если они используют одни и те же наборы внутри.
3. вообще отказаться от функций
18 май 17, 14:26    [20493048]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать запрос?  [new]
aleks2
Guest
Руслан Дамирович
aleks2
пропущено...

А ничо, что все функции datetime - недетерминистические?

1. Давайте ваш код с детерминистическими функциями - чтобы ТС понимал, что к чему.
2. Кто сказал, что у ТСа функции (не)детерминистические?


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

Детерминистическая функция будет исчислена 1 (один раз) для одного уникального аргумента.
Недетерминистическая - столько раз, сколько вызовов присутствует.
18 май 17, 14:46    [20493100]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать запрос?  [new]
человек_ниоткуда
Guest
Скалярная функция всегда NOT INLINE. Чтоб сократить расходы сервера на выполнение функции, нужно сделать её inline.
Чтоб сделать inline функцию, надо сделать её TABLE VALUED.
Простейший пример:
CREATE function [dbo].[NameById_ft]
(	@Para int)
returns table
as return
	(	SELECT
			[Value] = CASE @Para 
				WHEN 1 THEN 'one'
				WHEN 2 THEN 'two'
				WHEN 3 THEN 'three'
			END
	)
;
GO

Далее использовать APPLY или подзапрос в колонке. Тогда SQL не будет её лишний раз вызывать даже если ты в запросе её несколько раз прописал.

Если в твоей скалярной функции нет обращения к таблицам, то самым лучшим вариантом будет сделать в таблице CALCULATED COLUMN.
18 май 17, 14:52    [20493121]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать запрос?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 3315
человек_ниоткуда,

оригинальненько "Далее использовать APPLY или подзапрос в колонке. "

автор
Если в твоей скалярной функции нет обращения к таблицам, то самым лучшим вариантом будет сделать в таблице CALCULATED COLUMN.

спорненько
18 май 17, 14:55    [20493131]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать запрос?  [new]
человек_ниоткуда
Guest
TaPaK
человек_ниоткуда,
оригинальненько "Далее использовать APPLY или подзапрос в колонке. "

Ну а как ещё TVF вызвать? Вариант проверенный - увы... работает быстрее.

TaPaK
человек_ниоткуда,
автор
Если в твоей скалярной функции нет обращения к таблицам, то самым лучшим вариантом будет сделать в таблице CALCULATED COLUMN.

спорненько

* Есть шанс оптимизировать LEGACY код. В котором есть такое же выражение.
* Можно сделать PERSISTED и навесить индекс.
* И уверен ты мне приведёшь кучу плохих "кейсов". И я могу несколько припомнить. Но, я думаю, бесспорно, для любого архитектурноего решения можно найти плохие USE CASE.
18 май 17, 15:40    [20493275]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать запрос?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 3315
человек_ниоткуда,
автор
Ну а как ещё TVF вызвать? Вариант проверенный - увы... работает быстрее.

JOIN мать его... какой нафиг APPLY??????
дальше вообще чушь какая-то
18 май 17, 16:18    [20493416]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить