优化求解:上万条带参数的查询语句
手头的一个活,可以简化为以下模型:
declare @i int, @j int, @iCount int
set @i = 0
set @j = 0
while(@i < 100)
begin
while(@j < 100)
begin
set @iCount = (select count(*) from table1 where field1 = @i and field2 = @j)
if (@iCount <> 0)
begin
insert into table2(field1, field2) values(@i, @j)
end
set @j = @j +1
end
set @i = @i +1
end
针对以上模型,求最佳效率的解决方案,比如通过临时表、批量执行或把多条select语句合成一条语句等等,诸位以为如何?
------解决方案----------------------try
declare @i int, @j int, @iCount int
set @i = 0
set @j = 0
while(@i < 100)
begin
while(@j < 100)
begin
insert into table2(field1, field2)
select @i, @j
where exists(select 1 from table1 where field1 = @i and field2 = @j)
set @j = @j +1
end
set @i = @i +1
end
------解决方案--------------------insert into table2(field1, field2)
select field1,field2 from table1
WHERE field1 <100 AND field2 <100 GROUP BY field1,field2 HAVING count(1)> 0
------解决方案--------------------楼上正解。