Optimization: tens of thousands of query statements with parameters

star123456

The task at hand can be simplified to the following model:

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

Can I achieve the best efficiency through batch execution or combining multiple select statements into one statement?

fanfan1980

--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

wgfxman

insert into table2 (field1, field2)
select field1, field2 from table1
WHERE field1 <100 AND field2 <100 GROUP BY field1, field2 HAVING count (1)> 0

mydriver

The solution is upstairs.

star123456

Thank you, brothers. In fact, my intention was to ask about optimization using temporary tables, batch execution, etc., because this model is too simplified and may not fully simulate the prototype. The select statement in the prototype should not be easy to merge For example, the following writing:
select count (*) from table1 where field1 = 2 * @ i and field2 = @ j + 5
or
select count (*) from table1 where field1 = random number * @ i and field2 = random number * @ j

What I want to ask is how to optimize in this case? Thank you all.

wgfxman

The first can be transformed as follows:
insert into table2 (field1, field2)
select field1, field2 from table1
WHERE field1 <100 * 2 AND field2 <100 + 5 AND field2> = 5
AND field1% 2 = 0
GROUP BY field1, field2

wgfxman

Second, if random numbers are generated each time, there is nothing to optimize!

wgfxman

Group can be removed. . .
As long as there are records, COUNT (*)> 0 can meet