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

Откуда: г. Калуга
Сообщений: 1213
есть запрос такой
select F1, F2, F3, dbo.payment_name(F3) as PaymentName
from t1
where .....

Запрос выполняется 7 секунд, возвращает 800 записей. Если функцию убрать, то меньше секунды.
Сама функция такая:

ALTER function [dbo].[payment_name](@pt integer)
returns varchar(64)
as
begin
  declare @pname varchar(64)
  --select @pname=(select "name" from paymentTypes where id=@pt)
  set @pname = null
  if @pname is null
  begin
    set @pname='???' 
    if @pt=-1 set @pname='Перелив'
    if @pt=0 set @pname='Наличные'
    if @pt=-2 set @pname='ЛНР'
    if @pt=-3 set @pname='Нал со скидкой'
    if(@pt>=1 and @pt<=65535)
              begin
                --select @pname=(select CouponName from Coupons where CouponCode=@pt)
                if @pname is null set @pname='Талон '+cast(@pt as varchar(32))
              end
    if @pt=65536 set @pname='Мерник'
    if @pt=65537 set @pname='Б/Н Перелив'
    if @pt=536870912 set @pname='Ведомость'
    if @pt=1073741824 set @pname='С/К'
  end                  
  return(@pname)
end


специально закоментил запросы, чтоб убрать возможные замедления
Разъясните, что может являться причиной тормоза???
27 фев 15, 12:21    [17320316]     Ответить | Цитировать Сообщить модератору
 Re: Скалярная функция тормозит запрос  [new]
aleks2
Guest
Дык, программирование не твое.
Иди в управдомы.
27 фев 15, 12:23    [17320326]     Ответить | Цитировать Сообщить модератору
 Re: Скалярная функция тормозит запрос  [new]
Ken@t
Member

Откуда: 大地
Сообщений: 3265
minva,

А чо реляцией на справочник не по пацански?
27 фев 15, 12:24    [17320332]     Ответить | Цитировать Сообщить модератору
 Re: Скалярная функция тормозит запрос  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
прочитайте про case + Талоны свои вы никогда не вычислите :)
А вообще для таких справочников делаеться отдельная таблица
27 фев 15, 12:26    [17320350]     Ответить | Цитировать Сообщить модератору
 Re: Скалярная функция тормозит запрос  [new]
aleks2
Guest
Чо ты там понаписал херни?
ALTER function [dbo].[payment_name](@pt integer)
returns varchar(64)
as
begin
    if @pt=-1 return 'Перелив'
    if @pt=0 return 'Наличные'
    if @pt=-2 return 'ЛНР'
    if @pt=-3 return 'Нал со скидкой'
    if(@pt>=1 and @pt<=65535) return isnull( (select CouponName from Coupons where CouponCode=@pt), 'Талон '+cast(@pt as varchar(32)) )
    if @pt=65536 return 'Мерник'
    if @pt=65537  return 'Б/Н Перелив'
    if @pt=536870912  return 'Ведомость'
    if @pt=1073741824  return 'С/К'
  return '???' 
end

А настоящие джигиты пишут join с таблицей-справочником.
27 фев 15, 12:27    [17320357]     Ответить | Цитировать Сообщить модератору
 Re: Скалярная функция тормозит запрос  [new]
minva
Member

Откуда: г. Калуга
Сообщений: 1213
Хотя я немного обманываю. вот реальный запрос, который тормозит с функцией
select W.Code as WareCode, W.Name as WareName, 
S.was_at, S.quantity as Qty, 
       S.price * S.quantity as Cost, 
       S.orig_price as Price, 
       S.self_price as SelfPrice,
       dbo.payment_name(payment_id) as PaymentName,
       S.quantity
from single_ware_sells S	
      inner join Wares W on W.Code = S.ware_code		
      inner join Relays R on R.AfsID = S.object_id and R.OpenDateTime <= S.was_at and R.CloseDateTime >= S.was_at
     
where RID = 10472591


если избавится от соединения с Relays и подставить сразу время, то наличие или отсутсвие функции на скорость не влияет

select W.Code as WareCode, W.Name as WareName, 
S.was_at, S.quantity as Qty, 
       S.price * S.quantity as Cost, 
       S.orig_price as Price, 
       S.self_price as SelfPrice,
       dbo.payment_name(payment_id) as PaymentName,
       S.quantity
from single_ware_sells S	
      inner join Wares W on W.Code = S.ware_code		
where was_at >= '20111231 08:30:31.827' and was_at <= '20111231 23:41:49.843' 


но от этого понятней не становится
27 фев 15, 12:28    [17320360]     Ответить | Цитировать Сообщить модератору
 Re: Скалярная функция тормозит запрос  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
Сервер бедняга!
Вот эту лабуду 800 раз за один запрос выполнять приходится!
27 фев 15, 12:28    [17320364]     Ответить | Цитировать Сообщить модератору
 Re: Скалярная функция тормозит запрос  [new]
minva
Member

Откуда: г. Калуга
Сообщений: 1213
aleks2,
А настоящие джигиты может и пишут, но во-первых не все мною написано, а уже так есть,
во-вторых мой вопрос не в этом. Функция то простейшая и летать должна...
27 фев 15, 12:30    [17320381]     Ответить | Цитировать Сообщить модератору
 Re: Скалярная функция тормозит запрос  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
minva
aleks2,
А настоящие джигиты может и пишут, но во-первых не все мною написано, а уже так есть,
во-вторых мой вопрос не в этом. Функция то простейшая и летать должна...
Это почему ещё??
Она только оптимизатору мешает
27 фев 15, 12:31    [17320389]     Ответить | Цитировать Сообщить модератору
 Re: Скалярная функция тормозит запрос  [new]
aleks2
Guest
minva
aleks2,
А настоящие джигиты может и пишут, но во-первых не все мною написано, а уже так есть,
во-вторых мой вопрос не в этом. Функция то простейшая и летать должна...

Всех, кому должен, - прощаю.
27 фев 15, 12:32    [17320392]     Ответить | Цитировать Сообщить модератору
 Re: Скалярная функция тормозит запрос  [new]
minva
Member

Откуда: г. Калуга
Сообщений: 1213
iap, да, я ему тоже сочувствую :( Но посмотрите уточнение, непонятно, почему одном случае норм, во втором тормоз.
27 фев 15, 12:32    [17320396]     Ответить | Цитировать Сообщить модератору
 Re: Скалярная функция тормозит запрос  [new]
minva
Member

Откуда: г. Калуга
Сообщений: 1213
Чтоб конкретно до функции не докапывались, упрощу её. Суть от этого не поменялась
ALTER function [dbo].[payment_name](@pt integer)
returns varchar(64)
as
begin
     if @pt=0 return 'Наличные'
		  return 'Тут должно быть какое-то сложное вычисление, в справочник не запихнуть ну никак'
end
27 фев 15, 12:43    [17320457]     Ответить | Цитировать Сообщить модератору
 Re: Скалярная функция тормозит запрос  [new]
Gviber
Member

Откуда:
Сообщений: 124
Может в таблицу t1 добавить вычисляемую колонку и туда индекс навесить? Намного быстрее будет работать.
27 фев 15, 13:03    [17320610]     Ответить | Цитировать Сообщить модератору
 Re: Скалярная функция тормозит запрос  [new]
aleks2
Guest
minva
Чтоб конкретно до функции не докапывались, упрощу её. Суть от этого не поменялась
ALTER function [dbo].[payment_name](@pt integer)
returns varchar(64)
as
begin
     if @pt=0 return 'Наличные'
		  return 'Тут должно быть какое-то сложное вычисление, в справочник не запихнуть ну никак'
end


Страдалец, оптимизатор - глуп. Как всякая программа.
Он может выбрать стратегию:
1. Сделать выборку по фильтрам, потом применить к результату функцию. Будет быстро.
а может
2. Сначала применить ко ВСЕМ строкам таблицы функцию, а потом сделать выборку по фильтрам. Будет медленно.

А как на самом деле - написано в плане выполнения.
27 фев 15, 13:06    [17320625]     Ответить | Цитировать Сообщить модератору
 Re: Скалярная функция тормозит запрос  [new]
Gviber
Member

Откуда:
Сообщений: 124
По поводу индекса я погорячился Но по-моему вычисляемая колонка тут самое норм решение (в плане производительности).

Тут еще могут быть вариации с индексированным представлением.
27 фев 15, 13:10    [17320660]     Ответить | Цитировать Сообщить модератору
 Re: Скалярная функция тормозит запрос  [new]
minva
Member

Откуда: г. Калуга
Сообщений: 1213
aleks2, хорошо, тогда как заставить оптимизатор накидывать функцию только на результат?
27 фев 15, 13:17    [17320708]     Ответить | Цитировать Сообщить модератору
 Re: Скалярная функция тормозит запрос  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 6202
minva
Чтоб конкретно до функции не докапывались, упрощу её. Суть от этого не поменялась

Конкретно не докопаться до функции не выйдет, потому что функция - скалярка, а пользовательские скалярки в запросах - зло и тормоза сами по себе. Как советовали выше, переписывайте с кейсами или джойнами, делайте инлайновую функцию - что угодно, только не скалярку.
27 фев 15, 13:37    [17320856]     Ответить | Цитировать Сообщить модератору
 Re: Скалярная функция тормозит запрос  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1887
CREATE VIEW [dbo].[payment_name]
as
	SELECT -1 as pt,'Перелив' as Name
	UNION ALL SELECT -2,'ЛНР'
	UNION ALL SELECT -3,'Нал со скидкой'
	UNION ALL SELECT  0,'Наличные'
	UNION ALL SELECT 65536,'Мерник'
    UNION ALL SELECT 65537,'Б/Н Перелив'
    UNION ALL SELECT 536870912,'Ведомость'
    UNION ALL SELECT 1073741824,'С/К'

GO
select W.Code as WareCode, W.Name as WareName, 
S.was_at, S.quantity as Qty, 
       S.price * S.quantity as Cost, 
       S.orig_price as Price, 
       S.self_price as SelfPrice,
       ISNULL(pn.Name,'Талон '+cast(s.payment_id as varchar(32)))  as PaymentName,
       S.quantity
from single_ware_sells S	
inner join Wares W on W.Code = S.ware_code		
inner join Relays R on R.AfsID = S.object_id and R.OpenDateTime <= S.was_at and R.CloseDateTime >= S.was_at
left join [dbo].[payment_name] pn on pn.pt = s.payment_id
where RID = 10472591
27 фев 15, 13:54    [17320998]     Ответить | Цитировать Сообщить модератору
 Re: Скалярная функция тормозит запрос  [new]
есть запрос такой
Guest
minva,

интересно, что будет, если талонов таки наберется 65535 штук
черт, да их же можно будут пустить в минус! да. тема. начать можно с минус девяти. на всякий случай.
27 фев 15, 14:11    [17321142]     Ответить | Цитировать Сообщить модератору
 Re: Скалярная функция тормозит запрос  [new]
minva
Member

Откуда: г. Калуга
Сообщений: 1213
есть запрос такой, если талонов наберется хотя бы 10000, то клиент, который с ними работает сдохнет от помешательства. Это как у строителя дачи спрашивать: "А если к тебе на второй этаж танк заедет, выдержит? Нет? хреново строишь."

LexusR спасибо, но задачи не решает. Строки ISNULL(pn.Name,'Талон '+cast(s.payment_id as varchar(32)))
в запросе быть не должно, ибо функция должна возвращать готовый результат. Кроме того в исходно сообщении в теле функции закоментированы два запроса, а они в реале должны быть
if(@pt>=1 and @pt<=65535)
              begin
                select @pname=(select CouponName from Coupons where CouponCode=@pt)
                if @pname is null set @pname='Талон '+cast(@pt as varchar(32))
              end


Если функция/view будет возвращать таблицу-справочник, которая будет джойнится с запросом, то не найденный талон(NULL) придется все равно обрабатывать в вызывающем запросе
Как ни крути получается, что нужна функция возвращая одно значение. Попробовал вернуть не значение, а таблицу из одного столбца и одной записи, подцепить к запросу при помощи Cross Apply - легче стало на 1 сек, 6 вместо 7
27 фев 15, 14:52    [17321392]     Ответить | Цитировать Сообщить модератору
 Re: Скалярная функция тормозит запрос  [new]
invm
Member

Откуда: Москва
Сообщений: 9842
Функцию переделать а инлайновую:
create function dbo.payment_name(@pt integer)
returns table
as
return (
 select
  cast(case
   when @pt = -1 then 'Перелив'
   when @pt = 0 then 'Наличные'
   when @pt=-2 then 'ЛНР'
   when @pt=-3 then 'Нал со скидкой'
   when @pt >= 1 and @pt <= 65535 then isnull( (select CouponName from Coupons where CouponCode=@pt), 'Талон '+cast(@pt as varchar(32)) )
   when @pt=65536 then 'Мерник'
   when @pt=65537 then 'Б/Н Перелив'
   when @pt=536870912 then 'Ведомость'
   when @pt=1073741824 then 'С/К'
   else '???' 
  end as varchar(64)) as PaymentName
);

Запрос переписать:
select
 t.*, p.PaymentName
from
 (
  select top (2147483647)
   W.Code as WareCode, W.Name as WareName, 
   S.was_at, S.quantity as Qty, 
   S.price * S.quantity as Cost, 
   S.orig_price as Price, 
   S.self_price as SelfPrice,
   S.quantity,
   payment_id
  from single_ware_sells S	
       inner join Wares W on W.Code = S.ware_code		
       inner join Relays R on R.AfsID = S.object_id and R.OpenDateTime <= S.was_at and R.CloseDateTime >= S.was_at
  where RID = 10472591
 ) t cross apply
 dbo.payment_name(t.payment_id) p;
27 фев 15, 15:37    [17321768]     Ответить | Цитировать Сообщить модератору
 Re: Скалярная функция тормозит запрос  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8832
Скалярные функции отключают параллелизм, как минимум.
27 фев 15, 15:46    [17321832]     Ответить | Цитировать Сообщить модератору
 Re: Скалярная функция тормозит запрос  [new]
minva
Member

Откуда: г. Калуга
Сообщений: 1213
invm, спасибо, так получилось.
Я пробовал писать так, тоже были тормоза
ALTER FUNCTION [dbo].[Payment_Name](@pt integer)
RETURNS @tblPaymentName TABLE
  (PaymentName VARCHAR(90) NOT NULL
  )
AS
begin
	insert into @tblPaymentName
	select -3, 'Нал со скидкой'
	union ALL
	.....
                
  return
end


Последний вопрос, какая принципиальная разница между этим вариантом и
create function dbo.payment_name(@pt integer)
returns table
as
return (
 select


Можно ткнуть в БОЛ, я что-то не нахожу :(
27 фев 15, 15:58    [17321900]     Ответить | Цитировать Сообщить модератору
 Re: Скалярная функция тормозит запрос  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
minva
Можно ткнуть в БОЛ, я что-то не нахожу :(

Читать снимательно что написанно НАД примерами
27 фев 15, 16:24    [17322089]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить