Добро пожаловать в форум, Guest >> Войти | Регистрация | Поиск | Правила | | В избранное | Подписаться | ||
Все форумы / Microsoft SQL Server |
![]() ![]() |
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] Ответить | Цитировать Сообщить модератору |
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] Ответить | Цитировать Сообщить модератору |
Glory Member Откуда: Сообщений: 104751 |
Ничего себе пустая ! Для каждой записи выполнить 2 цикла по 20 итераций. |
||
5 сен 12, 11:10 [13116004] Ответить | Цитировать Сообщить модератору |
locky Member Откуда: Харьков, Украина Сообщений: 62034 |
типовое предложение: сравните быстрый и медленный планы ой, навижн... |
5 сен 12, 11:14 [13116030] Ответить | Цитировать Сообщить модератору |
Kaktus_ Member Откуда: Ростов-на-Дону Сообщений: 315 |
Я имею ввиду что я пробую из этой функции удалить все тело где идут итерации - и всеравно выполнение идет 30 минут. А если вообще из запроса убираю упоминание о функциях - он выполняется за 10 секунд (выше я ошибочно указал 3 минуты). Т.е. если грубо получается что раньше запрос выполнялся 3 минуты из которых 10 секунд шло на запрос, почти три минуты шло на CheckSumы. Сейчас получается запрос как выполнялся 10 секунд так и выполняется, только CheckSumы стали выполняться не почти три минуты, а полчаса (даже если из тела функции удалить всё). |
||||
5 сен 12, 11:18 [13116063] Ответить | Цитировать Сообщить модератору |
alexeyvg Member Откуда: Moscow Сообщений: 31783 |
Функции имеют большие накладные расходы на вызов (к сожалению, такая уж реализация, ничего не сделать). Нужно добиваться, что бы в плане был минимум её вызоыов, а ещё лучше заменить пользовательскую функцию на выражение (можно с встроенными функциями - они вызываются быстро). Конкретно вашу функцию можно заменить на встроенные хэши, либо реализовать ваш алгоритм джойном с таблицей с числами (если нужно оставить неизменным алгоритм вычисления чек-сумммы). |
||||||
5 сен 12, 11:19 [13116069] Ответить | Цитировать Сообщить модератору |
alexeyvg Member Откуда: Moscow Сообщений: 31783 |
-- Таблица с числами (нужно сделать зараннее такую постоянную таблицу - пригодится) 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] Ответить | Цитировать Сообщить модератору |
Kaktus_ Member Откуда: Ростов-на-Дону Сообщений: 315 |
Да, что-нибудь такое сделаю. Ну или выгружу результат запроса в таблицу и пробегусь курсором. Пробовал сравнивать планы выполнения. Получились одинаковые. Что на основном сервере, что на дублере, а время выполнения отличается порядка 20 раз |
||||
5 сен 12, 11:48 [13116358] Ответить | Цитировать Сообщить модератору |
iap Member Откуда: Москва Сообщений: 47052 |
Kaktus_, какой-такой курсор-мурсор? Забудьте! Что, курсор типа быстрее всех что ли? Сделайте таблицу с числами - и счастье наступит! |
5 сен 12, 12:02 [13116534] Ответить | Цитировать Сообщить модератору |
locky Member Откуда: Харьков, Украина Сообщений: 62034 |
версию дублёра - в студию туда же - планы с основного и с дублёра а также план с основного - без функции |
||
5 сен 12, 12:52 [13117065] Ответить | Цитировать Сообщить модератору |
MyNiGoo Member Откуда: Сообщений: 234 |
а попробуйте сделать dbcc freeproccache на сервере с медленным запросом и сразу его выполнить? лучше станет? |
5 сен 12, 12:56 [13117107] Ответить | Цитировать Сообщить модератору |
Glory Member Откуда: Сообщений: 104751 |
Потому что на дублере в 20 раз меньше записей ? |
||
5 сен 12, 13:22 [13117357] Ответить | Цитировать Сообщить модератору |
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] Ответить | Цитировать Сообщить модератору |
alexeyvg Member Откуда: Moscow Сообщений: 31783 |
Может, дублёр загружен меньше или сам сервер быстрее. Ещё можете посмотреть в профайлере затраты ресурсов в запросе - если CPU и Reads одинаковые, то значит всёодинаково, просто дублёр мощнее или не так загружен. Выкидывайте функцию и всё будет быстро. Самое идеальное - просто хранить эту чексумму в таблице и вычислять при изменении данных (на триггере или в процедуре/на клиенте). Или, если таблицы менять нельзя (сторонний продукт), то вычисляйте, как я выше писал. |
||
5 сен 12, 14:17 [13117832] Ответить | Цитировать Сообщить модератору |
alexeyvg Member Откуда: Moscow Сообщений: 31783 |
|
||
5 сен 12, 14:20 [13117854] Ответить | Цитировать Сообщить модератору |
locky Member Откуда: Харьков, Украина Сообщений: 62034 |
планы на продакшене и на дублёре - разные, пусть немного, но всё таки на дублёре есть WITH PREFETCH |
5 сен 12, 14:58 [13118133] Ответить | Цитировать Сообщить модератору |
Kaktus_ Member Откуда: Ростов-на-Дону Сообщений: 315 |
Да, действительно есть отличие в этом Prefetche. Откуда оно взялось и что толком делает я чесно-говоря так и не смог понять. Для себя пока сделал промежуточный вывод что это какой-то косяк и завтра попробую пляски с бубном типа пересохранения функции с другим именем, рестарты sql-сервера и т.п. Ну и если ничего не выйдет - переделаю чтобы работало без функции. :) Если вдруг найду какую-то зависимость - отпишусь. |
||
5 сен 12, 19:24 [13120214] Ответить | Цитировать Сообщить модератору |
StringCheckSum
Guest |
Kaktus_, оба использования StringCheckSum можно вынести на один подзапрос выше. за group by. + опции создания функции посмотри на обоих серверах |
6 сен 12, 11:37 [13122636] Ответить | Цитировать Сообщить модератору |
Все форумы / Microsoft SQL Server | ![]() |