用其他查询替换循环以提高性能 [英] replace loop with other query to increase performance

问题描述

iam正在执行SQL查询
我有@ Table_1，@ Table_2变量
我填写@ Table_1，然后我需要填写@ Table_2，具体取决于@ Table_1中的值
用于eaxmple

iam working on sql query
i have @Table_1,@Table_2 Variable
i fill @Table_1 then i need to fill @Table_2 depend on value in @Table_1
for eaxmple

@Table_1
(
ID bigint,
CategoryID bigint
CategoryType tinyint
)

@Table_2
(
QUantity float
TotalSales float
totalcost float
)

&&我有表值函数(Fn_Table)，它从@TAble_获取参数！填充@ Table2
像下面的代码
**************************

&& i have i table valued function (Fn_Table) that take the paremeter from @TAble_! to fill @Table2
like the following code
**************************

declare @Pks int
declare @Pk int
declare @CategoryID bigint

select @Pks=count(ID) from @Table_1
select @pK=1
WHILE(@Pks>0)
 begin
   select @CategoryID=CategoryID from @Table_1 where ID=pk
  if(@categoryid<>-1)
     insert into @table_2 select quantity,TotalSales ,totalcost  from dbo.fn_table(@CategoryID)
  select @pks=@pks-1
  select @pk=@pk+1
 end

************************************
查询需要很长时间&我认为这是因为循环
所以有人可以在不使用循环的情况下帮助我获得相同的结果吗?

[edit]已添加代码块-OriginalGriff [/edit]

********************************
the query take long time & i think this is because ot the loop
so could anybody help me to get the same result without using loop

[edit]Code blocks added - OriginalGriff[/edit]

推荐答案

declare @Pks int
declare @Pk int
declare @CategoryID bigint
 
set @Pks=count(ID) from @Table_1
set @pK=1
WHILE(@Pks>0)
 begin
   set @CategoryID=CategoryID from @Table_1 where ID=@pk
  if(@categoryid<>-1)
     insert into @table_2 select quantity,TotalSales ,totalcost  from dbo.fn_table(@CategoryID)
  set @pks=@pks-1
  set @pk=@pk+1
 end

这篇关于用其他查询替换循环以提高性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！