Loop through select result with regards to best performance-CodePudding

I have pretty big tables (> 100 million records) and need to write some stored procedures to loop through records and do some additional staff on each iteration. I've read about for in and cursor approaches. My question is what will be the fastest approach to do it using cursor, for in or maybe something else with regards to performance?

CodePudding user response：

When using PL/pgSQL for your stored procedure, you don't have to make that choice, the database will use a cursor anyway:

(However, PL/pgSQL users do not normally need to worry about that, since FOR loops automatically use a cursor internally to avoid memory problems.)

CodePudding user response：

Best way is not to go with cursor and loop those are always gives slow result. Better to avoid.
try Common table expression it will give fastest Result if there is less CRUD operations.

Declare @RowNo int =1;
 ;with ROWCTE as  
   (  
    SELECT @RowNo as ROWNO    
    UNION ALL  
  SELECT  ROWNO 1  
  FROM  ROWCTE  
  WHERE RowNo < 10000
)  
SELECT * FROM ROWCTE  option (maxrecursion 0)