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

Откуда: г. Ангарск
Сообщений: 17
Добрый день!

Подскажите плиз новичку:
есть скрипт, собирающий ветку дерева из таблички, содержащей примерно 75 тыс.записей:

-- begin tran
create table #tmp(id int,level int,name varchar(255))
--
declare @lev int,@i int,@obj int
select @lev=0,@obj=134
insert into #tmp
select obj,@lev,name from Object_Tree
where par=@obj
select @i=@@rowcount
while @i>0 and @lev<6 begin

select @lev=@lev+1
insert into #tmp
select obj,@lev,name from Object_Tree
where par in(select id from #tmp where level=@lev-1)
select @i=@@rowcount
end
drop table #tmp
--rollback

если скрипт выполняется в не транзакции, то время выполнения 00:03,
если в транзакции, то 01:46.

вопрос: каким образом можно значительно уменьшить время выполнения во втором случае?

Заранее благодарен.
19 авг 04, 12:21    [893429]     Ответить | Цитировать Сообщить модератору
 Re: как обойти торможение при transaction?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37101
Алексей.
Добрый день!

Подскажите плиз новичку:
есть скрипт, собирающий ветку дерева из таблички, содержащей примерно 75 тыс.записей:

-- begin tran
create table #tmp(id int,level int,name varchar(255))
--
declare @lev int,@i int,@obj int
select @lev=0,@obj=134
insert into #tmp
select obj,@lev,name from Object_Tree
where par=@obj
select @i=@@rowcount
while @i>0 and @lev<6 begin

select @lev=@lev+1
insert into #tmp
select obj,@lev,name from Object_Tree
where par in(select id from #tmp where level=@lev-1)
select @i=@@rowcount
end
drop table #tmp
--rollback

если скрипт выполняется в не транзакции, то время выполнения 00:03,
если в транзакции, то 01:46.

вопрос: каким образом можно значительно уменьшить время выполнения во втором случае?

Заранее благодарен.

Для начала попробовать заменить временную таблицу табличной переменной - операции с табличными переменными в лог не пишутся.
19 авг 04, 12:28    [893483]     Ответить | Цитировать Сообщить модератору
 Re: как обойти торможение при transaction?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37101
автор
если скрипт выполняется в не транзакции, то время выполнения 00:03,
если в транзакции, то 01:46.

B rollback в конце - серверу требуется некоторое время, чтоб откатить транзакцию.
19 авг 04, 12:29    [893490]     Ответить | Цитировать Сообщить модератору
 Re: как обойти торможение при transaction?  [new]
ura
Member [заблокирован]

Откуда: Киев
Сообщений: 932
для начала заменить временные таблицы на табличные переменные (declare @tmp table(...) )
Хотя такие тормоза скорее всего не из-за этого, а из-за блокировок - тогда можно понизить уровень изоляции транзакций или использовать хинт nolock
19 авг 04, 12:32    [893499]     Ответить | Цитировать Сообщить модератору
 Re: как обойти торможение при transaction?  [new]
Glory
Member

Откуда:
Сообщений: 104760
А может все таки прислушаться к Гавриленко Сергею Алексеевичу ?
При явной транзакции у вас в конце происходит rollback ? А это вовсе не мгоновенная операция.
19 авг 04, 12:40    [893539]     Ответить | Цитировать Сообщить модератору
 Re: как обойти торможение при transaction?  [new]
Гаргалык Влад
Member

Откуда:
Сообщений: 5
Мне кажется что лучше будет сделать копию этой таблицы во временую таблицу и там её обрабатывать. Попробуй так!!
19 авг 04, 13:33    [893833]     Ответить | Цитировать Сообщить модератору
 Re: как обойти торможение при transaction?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37101
Гаргалык Влад
Мне кажется что лучше будет сделать копию этой таблицы во временую таблицу и там её обрабатывать. Попробуй так!!

Зачем???
19 авг 04, 13:38    [893855]     Ответить | Цитировать Сообщить модератору
 Re: как обойти торможение при transaction?  [new]
Алексей.
Member

Откуда: г. Ангарск
Сообщений: 17
Добрый день всем!

заменил rollback на commit, заменил временную таблицу табличной переменной, попробовал изменить TRANSACTION ISOLATION LEVEL (пока не до конца понимаю, на что это влияет и как работает, поэтому перепробовал все возможные значения), в итоге получился скрипт:


SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

begin tran
declare @tmp table(id int,level int,name varchar(255))
--
declare @lev int,@i int,@obj int
select @lev=0,@obj=134
insert into @tmp
select obj,@lev,name from Object_Tree with (nolock) where par=@obj
select @i=@@rowcount
while @i>0 and @lev<6 begin

select @lev=@lev+1
insert into @tmp
select obj,@lev,name from Object_Tree with (nolock)
where par in(select id from @tmp where level=@lev-1)
select @i=@@rowcount
end
commit

время выполнения по-прежнему 01:46. Можно ли еще попробовать что-нибудь изменить?
20 авг 04, 04:54    [895719]     Ответить | Цитировать Сообщить модератору
 Re: как обойти торможение при transaction?  [new]
aleks2
Guest
Задать себе вопрос: а зачам мне здесь транзакция?
-------------------
Правильный ответ: транзакция здесь не нужна.
20 авг 04, 06:33    [895734]     Ответить | Цитировать Сообщить модератору
 Re: как обойти торможение при transaction?  [new]
Алексей.
Member

Откуда: г. Ангарск
Сообщений: 17
2aleks2

здесь я привел только часть кода, тормозящую процесс обработки дерева.
Ес-но запускать транзакцию только для заполнения временной таблички смысла нет
20 авг 04, 07:04    [895746]     Ответить | Цитировать Сообщить модератору
 Re: как обойти торможение при transaction?  [new]
aleks2
Guest
declare @tmp table(id int, level int, name varchar(255),  UNIQUE(id, level))
--
declare @lev int,@i int,@obj int
select @lev=0,@obj=134

insert into @tmp
select obj,0,name from Object_Tree with (nolock) where par=@obj 
select @i=@@rowcount

while @i>0 and @lev<6 begin
  select @lev=@lev+1
  insert into @tmp
  select obj,@lev,name 
      from Object_Tree OT INNER JOIN (select * FROM @tmp WHERE level=@lev-1) T ON OT.par=T.id
  select @i=@@rowcount
end

или
declare @tmp table(id int, level int, name varchar(255),  UNIQUE(id, level))
--
declare @lev int,@i int,@obj int
select @lev=0,@obj=134

insert into @tmp
select obj,0,name from Object_Tree with (nolock) where par=@obj 
select @i=@@rowcount

while @i>0 and @lev<6 begin
  select @lev=@lev+1
  insert into @tmp
  select obj,@lev,name 
      from Object_Tree 
    where exists(select * FROM @tmp WHERE level=@lev-1 AND id=par)
  select @i=@@rowcount
end
20 авг 04, 07:15    [895754]     Ответить | Цитировать Сообщить модератору
 Re: как обойти торможение при transaction?  [new]
aleks2
Guest
А вот еще одна идея посетила, ежели оставаться внутри транзакции:
1) Добавляем в Object_Tree две колонки Level int, GUID uniqueidentifier

begin tran
declare @lev int,@i int,@obj int, @GUID uniqueidentifier

set @GUID=NewID()

select @lev=0, @obj=134

UPDATE Object_Tree
SET Level=0, GUID=@GUID
where par=@obj

select @i=@@rowcount

while @i>0 and @lev<6 begin
select @lev=@lev+1
UPDATE Object_Tree
SET Level=@lev, GUID=@GUID
where exists(select * FROM Object_Tree WHERE GUID=@GUID AND level=@lev-1 AND id=par)
select @i=@@rowcount
end
-- это результат
SELECT obj, Level,name FROM Object_Tree WHERE GUID=@GUID
commit
------------
Но радикальное решение: построить дерево навсегда в Object_Tree и перестраивать 1 раз при изменении Object_Tree. Правда для этого требуется
чтобы объект не мог входить в две разные ветви.
20 авг 04, 08:12    [895800]     Ответить | Цитировать Сообщить модератору
 Re: как обойти торможение при transaction?  [new]
Алексей.
Member

Откуда: г. Ангарск
Сообщений: 17
Фантастика! Скрипт с JOIN отработал за 00:01, с where exists(...) за те же 01:46. Aleks2 - спасибо за помощь. Вот только еще хотелось бы понять логику SQL в обоих случаях.
20 авг 04, 10:04    [896058]     Ответить | Цитировать Сообщить модератору
 Re: как обойти торможение при transaction?  [new]
aleks2
Guest
Очень помогает разглядывание планов исполнения...

Видимо на par Object_Tree есть индекс.
20 авг 04, 10:21    [896130]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить