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

Откуда: Ростов-на-Дону
Сообщений: 315
 		Уже два дня как стали происходить странные вещи.
Имею функцию

CREATE FUNCTION StringCheckSum
(@s varchar(20))  
RETURNS int AS  
BEGIN 

DECLARE @i int, @Result int

SET @Result = 0
SET @i = 1

IF LEN(@s) <> 0 AND @s IS NOT NULL BEGIN

  WHILE @i <= LEN(@s)
  BEGIN
    SET @Result = @Result + ASCII(SUBSTRING(@s, @i, 1)) * @i
    SET @i = @i + 1
  END

END

RETURN @Result


END


И запрос, который использует эту функцию

SELECT [Location Code], [Posting Date], SUM([Invoiced Quantity]) AS InvoicedQuantity, [Document No_] 
, ( SELECT SUM(CheckSumValue) FROM
     (SELECT [Posting Date], [Location Code], [Document No_], [Item No_], [Variant Code], (SUM([Invoiced Quantity]) * (dbo.StringCheckSum([Item No_]) + dbo.StringCheckSum([Variant Code]))) AS CheckSumValue FROM [База1].[dbo].[Таблица1] AS TInt 
      WHERE TInt.[Posting Date] = TExt.[Posting Date] AND
            TInt.[Location Code] = TExt.[Location Code] AND
            TInt.[Document No_] = TExt.[Document No_]          
      GROUP BY [Posting Date], [Location Code], [Document No_], [Item No_], [Variant Code]) AS Select1
  ) AS CheckSumQuantity
FROM [База1].[dbo].[Таблица1] AS TExt
GROUP BY [Posting Date], [Location Code], [Document No_]


Ранее этот селект выполнялся стабильно 3 минуты. Последние два дня он выполняется 30. Ничего не менялось абсолютно. Все проверил. Восстанавливаю базу на сервере-дублере - выполняется за те же 3 минуты.
Самое интересное в том, что если из этого куска
(SUM([Invoiced Quantity]) * (dbo.StringCheckSum([Item No_]) + dbo.StringCheckSum([Variant Code]))
сделать
(SUM([Invoiced Quantity]))
то запрос будет выполняться 3 минуты
Поэтому я пришел к выводу что тормозит функция. Беру в функции убираю все тело и пишу просто

SET @Result = 0
RETURN @Result

и восстанавливаю кусок
(SUM([Invoiced Quantity]) * (dbo.StringCheckSum([Item No_]) + dbo.StringCheckSum([Variant Code]))

Получаю опять 30 минут.
Куда рыть? Как пустая функция может выполняться так долго?

Прошу пока не предлагать переделать код чтобы CheckSum проходил отдельным вторым этапом и без функции - это я сделаю. Просто хочу разобраться с текущим положением вещей. Год запрос выполнялся 3 раза в день стабильно 3 минуты и вот уже два дня как выполняется стабильно 30 минут.

Сообщение было отредактировано: 5 сен 12, 12:25
5 сен 12, 11:04    [13115946]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит даже пустая функция  [new]
Kaktus_
Member

Откуда: Ростов-на-Дону
Сообщений: 315
 		Забыл указать.

Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
5 сен 12, 11:08    [13115970]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит даже пустая функция  [new]
Glory
Member

Откуда:
Сообщений: 104751
Kaktus_
Как пустая функция может выполняться так долго?

Ничего себе пустая !
Для каждой записи выполнить 2 цикла по 20 итераций.
5 сен 12, 11:10    [13116004]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит даже пустая функция  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
 		типовое предложение: сравните быстрый и медленный планы

ой, навижн...
5 сен 12, 11:14    [13116030]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит даже пустая функция  [new]
Kaktus_
Member

Откуда: Ростов-на-Дону
Сообщений: 315
Glory
Kaktus_
Как пустая функция может выполняться так долго?

Ничего себе пустая !
Для каждой записи выполнить 2 цикла по 20 итераций.


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

Т.е. если грубо получается что раньше запрос выполнялся 3 минуты из которых 10 секунд шло на запрос, почти три минуты шло на CheckSumы. Сейчас получается запрос как выполнялся 10 секунд так и выполняется, только CheckSumы стали выполняться не почти три минуты, а полчаса (даже если из тела функции удалить всё).
5 сен 12, 11:18    [13116063]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит даже пустая функция  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 32167
Glory
Kaktus_
Как пустая функция может выполняться так долго?

Ничего себе пустая !
Для каждой записи выполнить 2 цикла по 20 итераций.
ТС написал, что заменил тело фенкции на return


Kaktus_
Куда рыть?
Рыть в план.

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

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

Конкретно вашу функцию можно заменить на встроенные хэши, либо реализовать ваш алгоритм джойном с таблицей с числами (если нужно оставить неизменным алгоритм вычисления чек-сумммы).
5 сен 12, 11:19    [13116069]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит даже пустая функция  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 32167
alexeyvg
либо реализовать ваш алгоритм джойном с таблицей с числами (если нужно оставить неизменным алгоритм вычисления чек-сумммы).
Например:
--	Таблица с числами (нужно сделать зараннее такую постоянную таблицу - пригодится)
declare @n table (n int)
insert @n select ROW_NUMBER() over(order by id) from sysobjects

--	Вычисление
select	t.name, 
		SUM(ASCII(SUBSTRING(t.name, n.n, 1)) * n.n),
		dbo.StringCheckSum(t.name) -- для проверки алгоритма
from sys.tables t
	join @n n on n.n <= LEN(t.name)
group by t.name
order by t.name
5 сен 12, 11:38    [13116256]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит даже пустая функция  [new]
Kaktus_
Member

Откуда: Ростов-на-Дону
Сообщений: 315
alexeyvg
alexeyvg
либо реализовать ваш алгоритм джойном с таблицей с числами (если нужно оставить неизменным алгоритм вычисления чек-сумммы).
Например:
--	Таблица с числами (нужно сделать зараннее такую постоянную таблицу - пригодится)
declare @n table (n int)
insert @n select ROW_NUMBER() over(order by id) from sysobjects

--	Вычисление
select	t.name, 
		SUM(ASCII(SUBSTRING(t.name, n.n, 1)) * n.n),
		dbo.StringCheckSum(t.name) -- для проверки алгоритма
from sys.tables t
	join @n n on n.n <= LEN(t.name)
group by t.name
order by t.name


Да, что-нибудь такое сделаю. Ну или выгружу результат запроса в таблицу и пробегусь курсором.



Пробовал сравнивать планы выполнения. Получились одинаковые. Что на основном сервере, что на дублере, а время выполнения отличается порядка 20 раз
5 сен 12, 11:48    [13116358]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит даже пустая функция  [new]
iap
Member

Откуда: Москва
Сообщений: 47194
 		Kaktus_,

какой-такой курсор-мурсор? Забудьте!
Что, курсор типа быстрее всех что ли?
Сделайте таблицу с числами - и счастье наступит!
5 сен 12, 12:02    [13116534]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит даже пустая функция  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
Kaktus_
Пробовал сравнивать планы выполнения. Получились одинаковые. Что на основном сервере, что на дублере, а время выполнения отличается порядка 20 раз

версию дублёра - в студию
туда же - планы с основного и с дублёра
а также план с основного - без функции
5 сен 12, 12:52    [13117065]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит даже пустая функция  [new]
MyNiGoo
Member

Откуда:
Сообщений: 233
 		а попробуйте сделать dbcc freeproccache на сервере с медленным запросом и сразу его выполнить? лучше станет?
5 сен 12, 12:56    [13117107]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит даже пустая функция  [new]
Glory
Member

Откуда:
Сообщений: 104751
Kaktus_
Пробовал сравнивать планы выполнения. Получились одинаковые. Что на основном сервере, что на дублере, а время выполнения отличается порядка 20 раз

Потому что на дублере в 20 раз меньше записей ?
5 сен 12, 13:22    [13117357]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит даже пустая функция  [new]
Kaktus_
Member

Откуда: Ростов-на-Дону
Сообщений: 315
 		К сожалению dbcc freeprocache не помог.

На дублере свежайшая утренняя база восстановленная из sql-бэкапа.

Версия сервера-дублера

Microsoft SQL Server 2000 - 8.00.2055 (Intel X86) Dec 16 2008 19:46:53 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

В первый раз приходится сравнивать планы. Графические я сравнил - идентичны. Кидаю текстовые. Не знаю как их сравнивать - у меня глаза разбегаются.

План основного сервера с функцией

  |--Compute Scalar(DEFINE:([Expr1005]=[Expr1005]))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([TExt].[Posting Date], [TExt].[Location Code], [TExt].[Document No_]))
            |--Hash Match(Aggregate, HASH:([TExt].[Posting Date], [TExt].[Document No_]), RESIDUAL:([TExt].[Posting Date]=[TExt].[Posting Date] AND [TExt].[Document No_]=[TExt].[Document No_]) DEFINE:([Expr1001]=SUM([TExt].[Invoiced Quantity]), [TExt].[Loc
            |    |--Clustered Index Scan(OBJECT:([База1].[dbo].[Таблица1$Item Ledger Entry].[Таблица1$Item Ledger Entry$0] AS [TExt]), WHERE:([TExt].[Location Code]='МАГАЗИН7'))
            |--Compute Scalar(DEFINE:([Expr1005]=If ([Expr1014]=0) then NULL else [Expr1015]))
                 |--Stream Aggregate(DEFINE:([Expr1014]=COUNT_BIG([Expr1004]), [Expr1015]=SUM([Expr1004])))
                      |--Compute Scalar(DEFINE:([Expr1004]=[Expr1003]*Convert(([dbo].[StringCheckSum](Convert([TInt].[Item No_]))+[dbo].[StringCheckSum](Convert([TInt].[Variant Code]))))))
                           |--Stream Aggregate(GROUP BY:([TInt].[Variant Code], [TInt].[Item No_]) DEFINE:([Expr1003]=SUM([TInt].[Invoiced Quantity])))
                                |--Sort(ORDER BY:([TInt].[Variant Code] ASC, [TInt].[Item No_] ASC))
                                     |--Filter(WHERE:([TInt].[Location Code]=[TExt].[Location Code]))
                                          |--Bookmark Lookup(BOOKMARK:([Bmk1002]), OBJECT:([База1].[dbo].[Таблица1$Item Ledger Entry] AS [TInt]))
                                               |--Index Seek(OBJECT:([База1].[dbo].[Таблица1$Item Ledger Entry].[$6] AS [TInt]), SEEK:([TInt].[Document No_]=[TExt].[Document No_] AND [TInt].[Posting Date]=[TExt].[Posting D


План с дублера с функцией


  |--Compute Scalar(DEFINE:([Expr1005]=[Expr1005]))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([TExt].[Posting Date], [TExt].[Location Code], [TExt].[Document No_]))
            |--Hash Match(Aggregate, HASH:([TExt].[Posting Date], [TExt].[Document No_]), RESIDUAL:([TExt].[Posting Date]=[TExt].[Posting Date] AND [TExt].[Document No_]=[TExt].[Document No_]) DEFINE:([Expr1001]=SUM([TExt].[Invoiced Quantity]), [TExt].[Loc
            |    |--Clustered Index Scan(OBJECT:([База1].[dbo].[Таблица1$Item Ledger Entry].[Таблица1$Item Ledger Entry$0] AS [TExt]), WHERE:([TExt].[Location Code]='МАГАЗИН7'))
            |--Compute Scalar(DEFINE:([Expr1005]=If ([Expr1014]=0) then NULL else [Expr1015]))
                 |--Stream Aggregate(DEFINE:([Expr1014]=COUNT_BIG([Expr1004]), [Expr1015]=SUM([Expr1004])))
                      |--Compute Scalar(DEFINE:([Expr1004]=[Expr1003]*Convert(([dbo].[StringCheckSum](Convert([TInt].[Item No_]))+[dbo].[StringCheckSum](Convert([TInt].[Variant Code]))))))
                           |--Stream Aggregate(GROUP BY:([TInt].[Variant Code], [TInt].[Item No_]) DEFINE:([Expr1003]=SUM([TInt].[Invoiced Quantity])))
                                |--Sort(ORDER BY:([TInt].[Variant Code] ASC, [TInt].[Item No_] ASC))
                                     |--Filter(WHERE:([TInt].[Location Code]=[TExt].[Location Code]))
                                          |--Bookmark Lookup(BOOKMARK:([Bmk1002]), OBJECT:([База1].[dbo].[Таблица1$Item Ledger Entry] AS [TInt]) WITH PREFETCH)
                                               |--Index Seek(OBJECT:([База1].[dbo].[Таблица1$Item Ledger Entry].[$6] AS [TInt]), SEEK:([TInt].[Document No_]=[TExt].[Document No_] AND [TInt].[Posting Date]=[TExt].[Posting D

План основного сервера без функции

  |--Compute Scalar(DEFINE:([Expr1004]=[Expr1004]))
       |--Parallelism(Gather Streams)
            |--Nested Loops(Inner Join, OUTER REFERENCES:([TExt].[Posting Date], [TExt].[Location Code], [TExt].[Document No_]))
                 |--Hash Match(Aggregate, HASH:([TExt].[Posting Date], [TExt].[Document No_]), RESIDUAL:([TExt].[Posting Date]=[TExt].[Posting Date] AND [TExt].[Document No_]=[TExt].[Document No_]) DEFINE:([Expr1001]=SUM([TExt].[Invoiced Quantity]), [TExt]
                 |    |--Parallelism(Repartition Streams, PARTITION COLUMNS:([TExt].[Posting Date], [TExt].[Document No_]))
                 |         |--Clustered Index Scan(OBJECT:([База1].[dbo].[Таблица1$Item Ledger Entry].[Таблица1$Item Ledger Entry$0] AS [TExt]), WHERE:([TExt].[Location Code]='МАГАЗИН7'))
                 |--Compute Scalar(DEFINE:([Expr1004]=If ([Expr1013]=0) then NULL else [Expr1014]))
                      |--Stream Aggregate(DEFINE:([Expr1013]=COUNT_BIG([Expr1003]), [Expr1014]=SUM([Expr1003])))
                           |--Stream Aggregate(GROUP BY:([TInt].[Variant Code], [TInt].[Item No_]) DEFINE:([Expr1003]=SUM([TInt].[Invoiced Quantity])))
                                |--Sort(ORDER BY:([TInt].[Variant Code] ASC, [TInt].[Item No_] ASC))
                                     |--Filter(WHERE:([TInt].[Location Code]=[TExt].[Location Code]))
                                          |--Bookmark Lookup(BOOKMARK:([Bmk1002]), OBJECT:([База1].[dbo].[Таблица1$Item Ledger Entry] AS [TInt]))
                                               |--Index Seek(OBJECT:([База1].[dbo].[Таблица1$Item Ledger Entry].[$6] AS [TInt]), SEEK:([TInt].[Document No_]=[TExt].[Document No_] AND [TInt].[Posting Date]=[TExt].[Posting D


План дублера без функции

  |--Compute Scalar(DEFINE:([Expr1004]=[Expr1004]))
       |--Parallelism(Gather Streams)
            |--Nested Loops(Inner Join, OUTER REFERENCES:([TExt].[Posting Date], [TExt].[Location Code], [TExt].[Document No_]))
                 |--Hash Match(Aggregate, HASH:([TExt].[Posting Date], [TExt].[Document No_]), RESIDUAL:([TExt].[Posting Date]=[TExt].[Posting Date] AND [TExt].[Document No_]=[TExt].[Document No_]) DEFINE:([Expr1001]=SUM([TExt].[Invoiced Quantity]), [TExt]
                 |    |--Parallelism(Repartition Streams, PARTITION COLUMNS:([TExt].[Posting Date], [TExt].[Document No_]))
                 |         |--Clustered Index Scan(OBJECT:([База1].[dbo].[Таблица1$Item Ledger Entry].[Таблица1$Item Ledger Entry$0] AS [TExt]), WHERE:([TExt].[Location Code]='МАГАЗИН7'))
                 |--Compute Scalar(DEFINE:([Expr1004]=If ([Expr1013]=0) then NULL else [Expr1014]))
                      |--Stream Aggregate(DEFINE:([Expr1013]=COUNT_BIG([Expr1003]), [Expr1014]=SUM([Expr1003])))
                           |--Stream Aggregate(GROUP BY:([TInt].[Variant Code], [TInt].[Item No_]) DEFINE:([Expr1003]=SUM([TInt].[Invoiced Quantity])))
                                |--Sort(ORDER BY:([TInt].[Variant Code] ASC, [TInt].[Item No_] ASC))
                                     |--Filter(WHERE:([TInt].[Location Code]=[TExt].[Location Code]))
                                          |--Bookmark Lookup(BOOKMARK:([Bmk1002]), OBJECT:([База1].[dbo].[Таблица1$Item Ledger Entry] AS [TInt]) WITH PREFETCH)
                                               |--Index Seek(OBJECT:([База1].[dbo].[Таблица1$Item Ledger Entry].[$6] AS [TInt]), SEEK:([TInt].[Document No_]=[TExt].[Document No_] AND [TInt].[Posting Date]=[TExt].[Posting D


Сообщение было отредактировано: 5 сен 12, 13:35
5 сен 12, 13:34    [13117470]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит даже пустая функция  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 32167
Kaktus_
В первый раз приходится сравнивать планы. Графические я сравнил - идентичны. Кидаю текстовые. Не знаю как их сравнивать - у меня глаза разбегаются.
Да, планы одинаковые. А версии сервера тоже одинаковые?

Может, дублёр загружен меньше или сам сервер быстрее.

Ещё можете посмотреть в профайлере затраты ресурсов в запросе - если CPU и Reads одинаковые, то значит всёодинаково, просто дублёр мощнее или не так загружен.

Выкидывайте функцию и всё будет быстро.
Самое идеальное - просто хранить эту чексумму в таблице и вычислять при изменении данных (на триггере или в процедуре/на клиенте).
Или, если таблицы менять нельзя (сторонний продукт), то вычисляйте, как я выше писал.
5 сен 12, 14:17    [13117832]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит даже пустая функция  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 32167
alexeyvg
А версии сервера тоже одинаковые?
А, прочитал - версия дублёра новее. Может, там пофиксили немного скорость, но всё равно это несильно - 30 минут больше чем 3 минуты, но без функции будет 30 секунд...
5 сен 12, 14:20    [13117854]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит даже пустая функция  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
 		планы на продакшене и на дублёре - разные, пусть немного, но всё таки
на дублёре есть WITH PREFETCH
5 сен 12, 14:58    [13118133]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит даже пустая функция  [new]
Kaktus_
Member

Откуда: Ростов-на-Дону
Сообщений: 315
locky
планы на продакшене и на дублёре - разные, пусть немного, но всё таки
на дублёре есть WITH PREFETCH


Да, действительно есть отличие в этом Prefetche. Откуда оно взялось и что толком делает я чесно-говоря так и не смог понять. Для себя пока сделал промежуточный вывод что это какой-то косяк и завтра попробую пляски с бубном типа пересохранения функции с другим именем, рестарты sql-сервера и т.п. Ну и если ничего не выйдет - переделаю чтобы работало без функции. :)
Если вдруг найду какую-то зависимость - отпишусь.
5 сен 12, 19:24    [13120214]     Ответить | Цитировать Сообщить модератору
 Re: Тормозит даже пустая функция  [new]
StringCheckSum
Guest 		Kaktus_,

оба использования StringCheckSum можно вынести на один подзапрос выше. за group by.

+ опции создания функции посмотри на обоих серверах
6 сен 12, 11:37    [13122636]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить