declare triger_cursor2 cursor for
Select Test_Result_ID From RST_TEST_RESULT where Lab_No = @LabNo;
open triger_cursor2
fetch next from triger_cursor2 into @TestResultID ----就是里慢
while(@@fetch_status=0)
begin
exec refeSearch @TestResultID,@reference='a',@flag='b'
Fetch Next From triger_cursor2 InTo @TestResultID
end
close triger_cursor2
deallocate triger_cursor2
Select Test_Result_ID From RST_TEST_RESULT where Lab_No = @LabNo;
open triger_cursor2
fetch next from triger_cursor2 into @TestResultID ----就是里慢
while(@@fetch_status=0)
begin
exec refeSearch @TestResultID,@reference='a',@flag='b'
Fetch Next From triger_cursor2 InTo @TestResultID
end
close triger_cursor2
deallocate triger_cursor2
fetch next from triger_cursor2 into @TestResultID ----就是这里慢,其它语句删掉,留下这句,还是慢,有建立索引上边的查询语句单独也很快.结果最多也就十几条记录.
Select Test_Result_ID From RST_TEST_RESULT where Lab_No = @LabNo;
--〉
declare triger_cursor2 cursor STATIC READ_ONLY for
Select Test_Result_ID From RST_TEST_RESULT where Lab_No = @LabNo;试下,如果不行,在换成
declare triger_cursor2 cursor FAST_FORWARD for
Select Test_Result_ID From RST_TEST_RESULT where Lab_No = @LabNo;
Select Test_Result_ID into #tb_Test_Result_ID From RST_TEST_RESULT where Lab_No = @LabNo
declare triger_cursor2 cursor for
select Test_Result_ID from #tb_Test_Result_ID
open triger_cursor2
fetch next from triger_cursor2 into @TestResultID
while(@@fetch_status=0)
begin
exec refeSearch @TestResultID,@reference='a',@flag='b'
fetch next from triger_cursor2 into @TestResultID
end
close triger_cursor2
deallocate triger_cursor2
我怀疑是过程 refeSearch 执行的时间特别的长
Select Lab_No From Inserted;
open triger_cursor1 fetch next from triger_cursor1 into @LabNo
while(@@fetch_status=0)
begin
declare triger_cursor2 cursor for
Select Test_Result_ID From RST_TEST_RESULT where Lab_No = @LabNo;
open triger_cursor2
fetch next from triger_cursor2 into @TestResultID
while(@@fetch_status=0)
begin
exec refeSearch @TestResultID,@reference='a',@flag='b'
Fetch Next From triger_cursor2 InTo @TestResultID
end
close triger_cursor2 --
deallocate triger_cursor2 -- exec CalculatePrice @LabNo;Fetch Next From triger_cursor1 InTo @LabNo发现是游标中有游标....应该怎样改速度才快(别人留下的东西)
--Inserted临时表是每次插入数据都增大的吗??
Inserted临时表是主表插入多少数据那么Inserted就有多少记录Select Lab_No,Test_Result_ID into #test From Inserted i,RST_TEST_RESULT rst
where i.Lab_No=rst.Lab_No
declare triger_cursor1 cursor for
Select Lab_No,Test_Result_ID From #test;
open triger_cursor1 fetch next from triger_cursor1 into @LabNo,@TestResultID
while(@@fetch_status=0)
begin
exec refeSearch @TestResultID,@reference='a',@flag='b'
exec CalculatePrice @LabNo;
Fetch Next From triger_cursor1 InTo @LabNo
end
close triger_cursor1
deallocate triger_cursor1
Select Lab_No,Test_Result_ID into #test From Inserted i,RST_TEST_RESULT rst
where i.Lab_No=rst.Lab_No
declare triger_cursor1 cursor for
Select Lab_No,Test_Result_ID From #test;
open triger_cursor1 fetch next from triger_cursor1 into @LabNo,@TestResultID
while(@@fetch_status=0)
begin
exec refeSearch @TestResultID,@reference='a',@flag='b'
exec CalculatePrice @LabNo;
Fetch Next From triger_cursor1 InTo @LabNo,@TestResultID
end
close triger_cursor1
deallocate triger_cursor1
Select Lab_No,Test_Result_ID into #test From Inserted i,RST_TEST_RESULT rst
where i.Lab_No=rst.Lab_No
declare triger_cursor1 cursor for
Select Lab_No,Test_Result_ID From #test;
open triger_cursor1 fetch next from triger_cursor1 into @LabNo,@TestResultID
while(@@fetch_status=0)
begin
print('test')
Fetch Next From triger_cursor1 InTo @LabNo,@TestResultID
end
close triger_cursor1
deallocate triger_cursor1
把那2个过程换成打印语句了试试 看问题到底出在哪里
Select Lab_No,Test_Result_ID into #test From Inserted i,RST_TEST_RESULT rst
where i.Lab_No=rst.Lab_No
declare triger_cursor1 cursor for
Select Lab_No,Test_Result_ID From #test;
open triger_cursor1 fetch next from triger_cursor1 into @LabNo,@TestResultID
while(@@fetch_status=0)
begin
select @LabNo,@TestResultID
Fetch Next From triger_cursor1 InTo @LabNo,@TestResultID
end
close triger_cursor1
deallocate triger_cursor1