|
Столкнулся с непонятной для меня проблемой. Процедура с явно указанной транзакцией выполняется 4-5 минут, если не указывать транзакцию, то 5-10 секунд. Процедура копирует структуру теста, в примере копируется около 10000 строк сначала во временную таблицу, затем обновляются ссылки и копируется обратно. Процедура следующая:
create procedure TestCopyNew
(
@PTest_ID int,
@NewTest_ID int output
)
as
declare @Error_Code int,
@Row_Count int
begin
if @PTest_ID > 0
begin
create table #Sections
(
New_Sect_ID int,
Old_Sect_ID int,
Test_ID int,
Sect_Order smallint,
Sect_Name varchar(255),
Sect_Descr text,
Sect_Weight real,
Sect_QuestRND bit,
Sect_MinQuest smallint,
Sect_PassScore real,
Sect_OFF bit,
Sect_Sound varchar(20),
Sect_NextPass int,
Sect_NextNPass int,
Sect_Image varchar(255),
Sect_Style smallint
)
select @Error_Code = @@error
if @Error_Code <> 0
begin
return (-2)
end
create table #Questions
(
New_Quest_ID int,
Old_Quest_ID int,
New_Sect_ID int,
Old_Sect_ID int,
QType_ID smallint,
Quest_Order smallint,
Quest_Text text,
Quest_Descr text,
Quest_Weight real,
Quest_NAnswer real,
Quest_Case bit,
Quest_AnswRND bit,
Quest_OFF bit,
Quest_Image varchar(255)
)
select @Error_Code = @@error
if @Error_Code <> 0
begin
return (-3)
end
create table #Answers
(
New_Quest_ID int,
Old_Quest_ID int,
Answer_Order smallint,
Answer_Text text,
Answer_PCorrect real,
Answer_PWrong real,
Answer_Right bit,
Answer_OFF bit,
Answer_Image varchar(255)
)
select @Error_Code = @@error
if @Error_Code <> 0
begin
return (-4)
end
/*копирование теста (в тестовом примере 1 строка)*/
-- begin transaction
insert into Tests (Test_Name, TType_ID, Course_ID, Test_Descr, Edit_ID,
Test_Weight, Test_MinSect, Test_MaxSect, Test_PassScore, Test_ShowRes,
Test_Sound, Test_Image)
select Test_Name, TType_ID, Course_ID, Test_Descr, Edit_ID,
Test_Weight, Test_MinSect, Test_MaxSect, Test_PassScore, Test_ShowRes,
Test_Sound, Test_Image
from Tests
where Tests.Test_ID = @PTest_ID
select @Error_Code = @@error, @Row_Count = @@rowcount
if (@Error_Code <> 0) or (@Row_Count <> 1)
begin
-- rollback transaction
return (-5)
end
select @NewTest_ID = @@identity
/*копирование секций во временную таблицу (в тестовом примере 38 строк)*/
insert into #Sections (Old_Sect_ID, Test_ID, Sect_Order, Sect_Name, Sect_Descr,
Sect_Weight, Sect_QuestRND, Sect_MinQuest, Sect_PassScore, Sect_OFF,
Sect_Sound, Sect_NextPass, Sect_NextNPass, Sect_Image, Sect_Style)
select Sect_ID, @NewTest_ID, Sect_Order, Sect_Name, Sect_Descr,
Sect_Weight, Sect_QuestRND, Sect_MinQuest, Sect_PassScore, Sect_OFF,
Sect_Sound, Sect_NextPass, Sect_NextNPass, Sect_Image, Sect_Style
from Sections
where Test_ID = @PTest_ID
select @Error_Code = @@error
if @Error_Code <> 0
begin
-- rollback transaction
return (-6)
end
/*копирование вопросов во временную таблицу (в тестовом примере 912 строк)*/
insert into #Questions (Old_Quest_ID, Old_Sect_ID, QType_ID, Quest_Order,
Quest_Text, Quest_Descr, Quest_Weight, Quest_NAnswer, Quest_Case,
Quest_AnswRND, Quest_OFF, Quest_Image)
select Quest_ID, Sect_ID, QType_ID, Quest_Order,
Quest_Text, Quest_Descr, Quest_Weight, Quest_NAnswer, Quest_Case,
Quest_AnswRND, Quest_OFF, Quest_Image
from Questions
where Sect_ID in (select Old_Sect_ID
from #Sections)
select @Error_Code = @@error
if @Error_Code <> 0
begin
-- rollback transaction
return (-7)
end
/*копирование ответов во временную таблицу (в тестовом примере 7296 строк)*/
insert into #Answers (Old_Quest_ID, Answer_Order, Answer_Text,
Answer_PCorrect, Answer_PWrong, Answer_Right, Answer_OFF, Answer_Image)
select Quest_ID, Answer_Order, Answer_Text,
Answer_PCorrect, Answer_PWrong, Answer_Right, Answer_OFF, Answer_Image
from Answers
where Quest_ID in (select Old_Quest_ID
from #Questions)
select @Error_Code = @@error
if @Error_Code <> 0
begin
-- rollback transaction
return (-
end
/*копирование секций из временной таблицы*/
insert into Sections (Test_ID, Sect_Order, Sect_Name, Sect_Descr,
Sect_Weight, Sect_QuestRND, Sect_MinQuest, Sect_PassScore, Sect_OFF,
Sect_Sound, Sect_NextPass, Sect_NextNPass, Sect_Image, Sect_Style)
select Test_ID, Sect_Order, Sect_Name, Sect_Descr,
Sect_Weight, Sect_QuestRND, Sect_MinQuest, Sect_PassScore, Sect_OFF,
Sect_Sound, Sect_NextPass, Sect_NextNPass, Sect_Image, Sect_Style
from #Sections
select @Error_Code = @@error
if @Error_Code <> 0
begin
-- rollback transaction
return (-9)
end
/*обновление секций во временной таблице*/
update #Sections
set New_Sect_ID = (select Sect_ID
from Sections
where Sect_Order = #Sections.Sect_Order and
Test_ID = @NewTest_ID)
select @Error_Code = @@error
if @Error_Code <> 0
begin
-- rollback transaction
return (-10)
end
/*обновление вопросов во временной таблице*/
update #Questions
set #Questions.New_Sect_ID = (select New_Sect_ID
from #Sections
where #Sections.Old_Sect_ID = #Questions.Old_Sect_ID)
select @Error_Code = @@error
if @Error_Code <> 0
begin
-- rollback transaction
return (-11)
end
/*копирование вопросов из временной таблицы*/
insert into Questions (Sect_ID, QType_ID, Quest_Order,
Quest_Text, Quest_Descr, Quest_Weight, Quest_NAnswer, Quest_Case,
Quest_AnswRND, Quest_OFF, Quest_Image)
select New_Sect_ID, QType_ID, Quest_Order,
Quest_Text, Quest_Descr, Quest_Weight, Quest_NAnswer, Quest_Case,
Quest_AnswRND, Quest_OFF, Quest_Image
from #Questions
select @Error_Code = @@error
if @Error_Code <> 0
begin
-- rollback transaction
return (-12)
end
/*обновление вопросов во временной таблице*/
update #Questions
set New_Quest_ID = (select Quest_ID
from Questions
where Questions.Quest_Order = #Questions.Quest_Order and
Questions.Sect_ID = #Questions.New_Sect_ID)
select @Error_Code = @@error
if @Error_Code <> 0
begin
-- rollback transaction
return (-13)
end
/*обновление ответов во временной таблице*/
update #Answers
set New_Quest_ID = (select New_Quest_ID
from #Questions
where #Questions.Old_Quest_ID = #Answers.Old_Quest_ID)
select @Error_Code = @@error
if @Error_Code <> 0
begin
-- rollback transaction
return (-14)
end
/*копирование ответов из временной таблицы*/
insert into Answers (Quest_ID, Answer_Order, Answer_Text,
Answer_PCorrect, Answer_PWrong, Answer_Right, Answer_OFF, Answer_Image)
select New_Quest_ID, Answer_Order, Answer_Text,
Answer_PCorrect, Answer_PWrong, Answer_Right, Answer_OFF, Answer_Image
from #Answers
select @Error_Code = @@error
if @Error_Code <> 0
begin
-- rollback transaction
return (-15)
end
else
begin
-- commit transaction
return (0)
end
end
else
begin
return (-1)
end
end
GO
В тексте видно что объявление транзакций я закоментарил...
|
