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

Откуда: Ростов-на-Дону
Сообщений: 64
Здравствуйте, Уважаемые.

Происходит что-то странное, и я не могу понять что и почему. Надеюсь, вы мне поможете разобраться с моей проблемой.

Есть хранимая процедура, которая пересчитывает остатки товара по дням.
Эта хранимая процедура4 года работала без проблем..... да со временем время ее выполнения росло, но все в пределах разумного.
Но, примерно месяц назад, при запуске ее на сервере клиента я просто не мог дождаться когда она закончит работать, ждал 3-4 часа и просто снимал выполнение. При этом при запуске на той же базе у себя на компе (проверял на 2 своих компах) эта же хранимая процедура работала 20-25 мин.
Позавчера очередной раз забрал базу с сервера клиента и запустил все ту же хранимую процедуру, и она отработала за 34 часа !!!
При этом никаких изменений, кроме самих данных, не вносилось.

Кто нибудь может мне объяснить в чем может быть дело и куда смотреть?

Схема куска хранимой процедуры, на котором все теперь застреёт:
- Получаю курсор
- далее в цикле идет FETCH NEXT FROM ...
- в самом цикле идет Update или Delete (в зависимости от условий)
- выхожу из цикла, закрываю курсор.

Несколько лет это работало без проблем. А сейчас стала почти в 100 раз медленней работать :(

Заранее огромное спасибо за помощь.

P.S. Microsoft SQL Server 2005 Developer Edition
28 апр 14, 14:24    [15946705]     Ответить | Цитировать Сообщить модератору
 Re: Помогите! 100 кратное увеличение времени выполнение хранимой процедуры  [new]
xenix
Guest
Индексы на табличках смотрели?
28 апр 14, 14:34    [15946786]     Ответить | Цитировать Сообщить модератору
 Re: Помогите! 100 кратное увеличение времени выполнение хранимой процедуры  [new]
i__one
Member

Откуда: Ростов-на-Дону
Сообщений: 64
xenix,
наличие индексов или что?

в этом цикле участвуют 2 таблицы:
курсор CurDat :
DECLARE CurDat CURSOR FOR
SELECT * 
FROM #tmp2cor
ORDER BY dat


и таблица fotsd

У таблицы fotsd есть 4 не кластерных индекса
28 апр 14, 14:42    [15946840]     Ответить | Цитировать Сообщить модератору
 Re: Помогите! 100 кратное увеличение времени выполнение хранимой процедуры  [new]
i__one
Member

Откуда: Ростов-на-Дону
Сообщений: 64
fotsd (kp_id INT, skl_id INT, uz_id INT , dat DATETIME, delta NUMERIC(18,0) , kf NUMERIC(18,0), deltakr NUMERIC(18,0), kr NUMERIC(18,0))
28 апр 14, 14:44    [15946859]     Ответить | Цитировать Сообщить модератору
 Re: Помогите! 100 кратное увеличение времени выполнение хранимой процедуры  [new]
i__one
Member

Откуда: Ростов-на-Дону
Сообщений: 64
что еще:
кол-во записей в таблице чуть больше 1 млн.
Недавно был сделан шринк БД
Recovery model БД: full
28 апр 14, 14:51    [15946918]     Ответить | Цитировать Сообщить модератору
 Re: Помогите! 100 кратное увеличение времени выполнение хранимой процедуры  [new]
xenix
Guest
Правильно. Посмотрите еще степень фрагментации индексов
28 апр 14, 14:55    [15946947]     Ответить | Цитировать Сообщить модератору
 Re: Помогите! 100 кратное увеличение времени выполнение хранимой процедуры  [new]
i__one
Member

Откуда: Ростов-на-Дону
Сообщений: 64
xenix,
это?
DBCC SHOWCONTIG scanning 'FOTSD' table...
Table: 'FOTSD' (373576369); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned................................: 14339
- Extents Scanned..............................: 1800
- Extent Switches..............................: 1799
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.61% [1793:1800]
- Extent Scan Fragmentation ...................: 22.72%
- Avg. Bytes Free per Page.....................: 62.2
- Avg. Page Density (full).....................: 99.23%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
28 апр 14, 15:01    [15947000]     Ответить | Цитировать Сообщить модератору
 Re: Помогите! 100 кратное увеличение времени выполнение хранимой процедуры  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10232
Блог
1) Переписывать без курсоров
2) Смотреть, что конкретно делает ваш запрос, когда висит...какие ожидания
28 апр 14, 15:04    [15947015]     Ответить | Цитировать Сообщить модератору
 Re: Помогите! 100 кратное увеличение времени выполнение хранимой процедуры  [new]
i__one
Member

Откуда: Ростов-на-Дону
Сообщений: 64
DBCC SHOWCONTIG (fotsd) WITH  ALL_INDEXES 

выдало следующее:

автор
DBCC SHOWCONTIG scanning 'FOTSD' table...
Table: 'FOTSD' (373576369); index ID: 0, database ID: 5
TABLE level scan performed.
- Pages Scanned................................: 14339
- Extents Scanned..............................: 1800
- Extent Switches..............................: 1799
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.61% [1793:1800]
- Extent Scan Fragmentation ...................: 22.72%
- Avg. Bytes Free per Page.....................: 62.2
- Avg. Page Density (full).....................: 99.23%
DBCC SHOWCONTIG scanning 'FOTSD' table...
Table: 'FOTSD' (373576369); index ID: 2, database ID: 5
LEAF level scan performed.
- Pages Scanned................................: 5417
- Extents Scanned..............................: 686
- Extent Switches..............................: 3755
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 18.05% [678:3756]
- Logical Scan Fragmentation ..................: 64.41%
- Extent Scan Fragmentation ...................: 1.46%
- Avg. Bytes Free per Page.....................: 2614.6
- Avg. Page Density (full).....................: 67.70%
DBCC SHOWCONTIG scanning 'FOTSD' table...
Table: 'FOTSD' (373576369); index ID: 3, database ID: 5
LEAF level scan performed.
- Pages Scanned................................: 5734
- Extents Scanned..............................: 725
- Extent Switches..............................: 4569
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 15.69% [717:4570]
- Logical Scan Fragmentation ..................: 84.62%
- Extent Scan Fragmentation ...................: 8.28%
- Avg. Bytes Free per Page.....................: 2659.7
- Avg. Page Density (full).....................: 67.14%
DBCC SHOWCONTIG scanning 'FOTSD' table...
Table: 'FOTSD' (373576369); index ID: 4, database ID: 5
LEAF level scan performed.
- Pages Scanned................................: 5133
- Extents Scanned..............................: 649
- Extent Switches..............................: 4006
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 16.02% [642:4007]
- Logical Scan Fragmentation ..................: 91.31%
- Extent Scan Fragmentation ...................: 6.63%
- Avg. Bytes Free per Page.....................: 2311.3
- Avg. Page Density (full).....................: 71.44%
DBCC SHOWCONTIG scanning 'FOTSD' table...
Table: 'FOTSD' (373576369); index ID: 5, database ID: 5
LEAF level scan performed.
- Pages Scanned................................: 6128
- Extents Scanned..............................: 778
- Extent Switches..............................: 6127
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 12.50% [766:6128]
- Logical Scan Fragmentation ..................: 99.22%
- Extent Scan Fragmentation ...................: 39.85%
- Avg. Bytes Free per Page.....................: 2582.2
- Avg. Page Density (full).....................: 68.10%
28 апр 14, 15:04    [15947018]     Ответить | Цитировать Сообщить модератору
 Re: Помогите! 100 кратное увеличение времени выполнение хранимой процедуры  [new]
i__one
Member

Откуда: Ростов-на-Дону
Сообщений: 64
Knyazev Alexey
1) Переписывать без курсоров
2) Смотреть, что конкретно делает ваш запрос, когда висит...какие ожидания


Задача: есть некоторые данные, нужно пробежаться по всем строкам этих данных и что-то сделать.
1) как например?
2) да ничего особенного, просто раньше все быстро проходило в цикле, а сейчас медленней (с задержкой). Выставлял PRINT после каждого FETCH. Раньше сообщение печатались молниеносно, а сейчас раз в полсекунды где-то , в общем намного медленней чем раньше

почему до этого все замечательно работало?
28 апр 14, 15:11    [15947078]     Ответить | Цитировать Сообщить модератору
 Re: Помогите! 100 кратное увеличение времени выполнение хранимой процедуры  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10232
Блог
i__one
Задача: есть некоторые данные, нужно пробежаться по всем строкам этих данных и что-то сделать.

это стандартные операции над множеством

i__one
как например?

для начала предоставить исходные данные и что нужно получить

i__one
да ничего особенного

нет таких ожиданий

i__one

почему до этого все замечательно работало?

у вас сервер перед глазами...смотрите, анализируйте...чудес не бывает
28 апр 14, 15:17    [15947122]     Ответить | Цитировать Сообщить модератору
 Re: Помогите! 100 кратное увеличение времени выполнение хранимой процедуры  [new]
xenix
Guest
Интересно было бы попробовать пересоздать индексы и запустить после этого Вашу процедуру (естественно, на тестовом компьютере)
28 апр 14, 15:22    [15947143]     Ответить | Цитировать Сообщить модератору
 Re: Помогите! 100 кратное увеличение времени выполнение хранимой процедуры  [new]
Racer777
Member

Откуда: Москва - Мытищи
Сообщений: 153
i__one
Обновление 1 000 000 записей в курсоре по 0.5 сек - у меня получилось 138 с лишним часов, простая арифметика :)
Смотреть загруженность сервера, влияние других процессов на свой, возможно заблкировать изменяемую таблицу, переписать update на самый простейший, поэксперементировать с индексами, возможно вообще отказаться от курсора.
Все это называется оптимизация запроса.
28 апр 14, 16:35    [15947719]     Ответить | Цитировать Сообщить модератору
 Re: Помогите! 100 кратное увеличение времени выполнение хранимой процедуры  [new]
i__one
Member

Откуда: Ростов-на-Дону
Сообщений: 64
xenix,
запустил DBCC DBREINDEX(fotsd)
потом запустил хранимую процедуру.... после 2 часов ожидания отменил запрос. Ну не должен он столько работать. всегда работал в районе 30 мин
28 апр 14, 16:47    [15947803]     Ответить | Цитировать Сообщить модератору
 Re: Помогите! 100 кратное увеличение времени выполнение хранимой процедуры  [new]
i__one
Member

Откуда: Ростов-на-Дону
Сообщений: 64
Racer777
i__one
Обновление 1 000 000 записей в курсоре по 0.5 сек - у меня получилось 138 с лишним часов, простая арифметика :)
Смотреть загруженность сервера, влияние других процессов на свой, возможно заблкировать изменяемую таблицу, переписать update на самый простейший, поэксперементировать с индексами, возможно вообще отказаться от курсора.
Все это называется оптимизация запроса.


ну 0.5 сек это я так примерно написал.... просто не моментально, а с задержкой :)
Во время запуска хранимой процедуры, загрузка компа не велика. Запущен при этом только SQL сервер. Загрузка ЦП около 30%.

Вот весь фрагмент на котором идут тормоза:

DECLARE CurDat CURSOR FOR
SELECT * 
FROM #tmp2cor
ORDER BY dat

  --*( цикл по датам
OPEN CurDat

DECLARE @currentdat DATETIME
DECLARE @kp_id INT
DECLARE @skl_id INT
DECLARE @uz_id INT
DECLARE @delta NUMERIC (12,3)
DECLARE @deltakr NUMERIC (12,3)

FETCH NEXT FROM CurDat INTO @kp_id,@skl_id,@uz_id,@currentdat,@delta,@deltakr
WHILE @@FETCH_STATUS = 0
BEGIN

-- *( изменить дельту для @currentdat и вычеркнуть строку, если дельта=0
UPDATE fotsd SET delta = fotsd.delta + @delta
    		    ,deltakr = fotsd.deltakr + @deltakr
 WHERE fotsd.dat = @currentdat
  AND fotsd.kp_id=@kp_id
  AND fotsd.skl_id=@skl_id
  AND fotsd.uz_id=@uz_id

IF EXISTS(
   SELECT kp_id 
   FROM fotsd
   WHERE fotsd.dat = @currentdat
    AND fotsd.kp_id=@kp_id
    AND fotsd.skl_id=@skl_id
    AND fotsd.uz_id=@uz_id
    AND fotsd.delta=0
    AND fotsd.deltakr = 0
         )  
DELETE fotsd 
 WHERE fotsd.dat = @currentdat
  AND fotsd.kp_id=@kp_id
  AND fotsd.skl_id=@skl_id
  AND fotsd.uz_id=@uz_id
  AND fotsd.delta=0
  AND fotsd.deltakr = 0
-- *) изменить дельту для @currentdat и вычеркнуть строку, если дельта=0

UPDATE fotsd SET kf = fotsd.kf + @delta
                ,kr = fotsd.kr + @deltakr
 WHERE fotsd.dat >= @currentdat
  AND fotsd.kp_id=@kp_id
  AND fotsd.skl_id=@skl_id
  AND fotsd.uz_id=@uz_id

 FETCH NEXT FROM CurDat INTO @kp_id,@skl_id,@uz_id,@currentdat,@delta,@deltakr

END
CLOSE curdat
DEALLOCATE curdat

--*) цикл по датам
  
 END
 
 DROP TABLE #tmp2cor


куда еще проще UPDATE ?
28 апр 14, 16:53    [15947844]     Ответить | Цитировать Сообщить модератору
 Re: Помогите! 100 кратное увеличение времени выполнение хранимой процедуры  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10232
Блог
i__one
куда еще проще UPDATE ?

вот это говнокодище....зачем тут вообще курсор? тут одним запросом всё делается
28 апр 14, 16:56    [15947862]     Ответить | Цитировать Сообщить модератору
 Re: Помогите! 100 кратное увеличение времени выполнение хранимой процедуры  [new]
i__one
Member

Откуда: Ростов-на-Дону
Сообщений: 64
Knyazev Alexey
i__one
куда еще проще UPDATE ?

вот это говнокодище....зачем тут вообще курсор? тут одним запросом всё делается


если не сложно, подскажите этот один запрос. Буду благодарен.
28 апр 14, 17:01    [15947889]     Ответить | Цитировать Сообщить модератору
 Re: Помогите! 100 кратное увеличение времени выполнение хранимой процедуры  [new]
bacalavr
Member

Откуда:
Сообщений: 311
ваня-таки не дождался =(
=)
28 апр 14, 17:03    [15947906]     Ответить | Цитировать Сообщить модератору
 Re: Помогите! 100 кратное увеличение времени выполнение хранимой процедуры  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10232
Блог
i__one
Knyazev Alexey
пропущено...

вот это говнокодище....зачем тут вообще курсор? тут одним запросом всё делается


если не сложно, подскажите этот один запрос. Буду благодарен.



Обновление данных на основе данных из других таблицhttp://technet.microsoft.com/ru-ru/library/ms177523.aspx
28 апр 14, 17:03    [15947913]     Ответить | Цитировать Сообщить модератору
 Re: Помогите! 100 кратное увеличение времени выполнение хранимой процедуры  [new]
Racer777
Member

Откуда: Москва - Мытищи
Сообщений: 153
i__one,

Простейший Update это не

UPDATE fotsd SET delta = fotsd.delta + @delta
,deltakr = fotsd.deltakr + @deltakr
WHERE fotsd.dat = @currentdat
AND fotsd.kp_id=@kp_id
AND fotsd.skl_id=@skl_id
AND fotsd.uz_id=@uz_id

а

UPDATE fotsd SET delta = fotsd.delta + @delta
,deltakr = fotsd.deltakr + @deltakr
WHERE fotsd.ID = @myID

Для этого нужно написать соотв. запрос для курсора.
В этом случае Update будет работать быстрее в разы.

Или действительно переписать все без использования курсора,
но опять же Update должен быть простым, для этого можно использовать временную таблицу.

Миллион записей это ерунда, вот сто миллионов - это значительно хуже :)
28 апр 14, 17:07    [15947934]     Ответить | Цитировать Сообщить модератору
 Re: Помогите! 100 кратное увеличение времени выполнение хранимой процедуры  [new]
i__one
Member

Откуда: Ростов-на-Дону
Сообщений: 64
Knyazev Alexey,
)))) что это? ))) ссылка на описание конструкции UPDATE ? ))) ну спасибо, я не знал что такая есть...
28 апр 14, 17:09    [15947953]     Ответить | Цитировать Сообщить модератору
 Re: Помогите! 100 кратное увеличение времени выполнение хранимой процедуры  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10232
Блог
i__one
ну спасибо, я не знал что такая есть...

судя по вашим запросам - не знаете! и тем более я указал пример, который вам нужно смотреть
28 апр 14, 17:11    [15947967]     Ответить | Цитировать Сообщить модератору
 Re: Помогите! 100 кратное увеличение времени выполнение хранимой процедуры  [new]
i__one
Member

Откуда: Ростов-на-Дону
Сообщений: 64
Racer777,
у fotsd нет ID. у нее составной ключ, как описано в запросе. я понимаю, что по одному полю будет быстрее, но ключ составной.
28 апр 14, 17:11    [15947972]     Ответить | Цитировать Сообщить модератору
 Re: Помогите! 100 кратное увеличение времени выполнение хранимой процедуры  [new]
i__one
Member

Откуда: Ростов-на-Дону
Сообщений: 64
Knyazev Alexey,

извините, не верно вас понял. сейчас посмотрю внимательней
28 апр 14, 17:12    [15947979]     Ответить | Цитировать Сообщить модератору
 Re: Помогите! 100 кратное увеличение времени выполнение хранимой процедуры  [new]
Racer777
Member

Откуда: Москва - Мытищи
Сообщений: 153
i__one

Что мешает его добавить, или Вы не разработчик ?
28 апр 14, 17:16    [15948017]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить